Python：从多个文件的多个列中获取平均值

Question

我正在尝试写一个程序，这个程序可以接收一个或多个文件作为输入，然后总结每个文件中两列的平均值。

比如我有两个文件：

文件1：

ID    Feature    Total    Percent
1.2    ABC    300    75
1.4    CDE    129    68

文件2：

ID    Feature   Total    Percent
1.2    ABC    289    34
1.4    CDE    56    94

我想要逐个处理每个文件，并把百分比转换成数字，使用的方法是：

def ReadFile(File):
    LineCount = 0
    f = open(File)
    Header =  f.readline()
    Lines = f.readlines()
    for Line in Lines:
        Info = Line.strip("\n").split("\t")
        ID, Feature, Total, Percent= Info[0], Info[1], int(Info[2]), int(Info[3])
        Num = (Percent/100.0)*Total

我不太确定最好的方法是什么来存储输出，以便我能得到每个文件的ID、特征、总数和百分比。最终，我想创建一个输出文件，里面包含所有文件的平均百分比。在上面的例子中，我会得到：

ID    Feature    AveragePercent
1.2    ABC    54.9    #(((75/100.0)*300)+((34/100.0)*289)) / (300+289))
1.4    CDE    75.9    #(((68/100.0)*129)+((94/100.0)*56)) / (129+56))

Answer 1

我用一些文件进行了测试，这些文件的内容是用制表符（也就是Tab键）分隔的，里面有ID、特征、总数和百分比（就像你输入的文件一样）。结果非常好，输出的内容正是你想要的：

globalResultsFromReadDictionary = {}

def ReadFile(File):
    LineCount = 0
    f = open(File)
    Header =  f.readline()
    Lines = f.readlines()
    for Line in Lines:
        Info = Line.strip("\n").split("\t")
        ID, Feature, Total, Percent = Info[0], Info[1], int(Info[2]), int(Info[3])

        #Adding to dictionary
        key = ID + "\t" + Feature
        if(key in globalResultsFromReadDictionary):
            globalResultsFromReadDictionary[key].append([Total, Percent])
        else:
            globalResultsFromReadDictionary[key] = [[Total, Percent]]

def createFinalReport(File):
    overallReportFile = open(File, 'w'); #the file to write the report to

    overallReportFile.write('ID\tFeature\tAvg%\n') #writing the header

    for idFeatureCombinationKey in globalResultsFromReadDictionary:

        #Tallying up the total and sum of percent*total for each element of the Id-Feature combination
        sumOfTotals = 0
        sumOfPortionOfTotals = 0
        for totalPercentCombination in globalResultsFromReadDictionary[idFeatureCombinationKey]:
            sumOfTotals += totalPercentCombination[0]
            sumOfPortionOfTotals += (totalPercentCombination[0]*(totalPercentCombination[1]/100))

        #Write to the line (idFeatureCombinationKey is 'ID \t Feature', so can just write that)
        overallReportFile.write(idFeatureCombinationKey + '\t' + str(round((sumOfPortionOfTotals/sumOfTotals)*100, 1)) + '\n')

    overallReportFile.close()

#Calling the functions
ReadFile('File1.txt');
ReadFile('File2.txt');
createFinalReport('dd.txt');

Answer 2

用字典来处理这个问题非常合适。（我把处理头部的部分留给你自己去做）

import fileinput

data = {}
for line in fileinput.input(['file1', 'file2']):
    idx, ft, values = line.split(None, 2)
    key = idx, ft     #use ID, Feature tuple as a key.
    tot, per = map(int, values.split())
    if key not in data:
        data[key] = {'num': 0, 'den': 0}
    data[key]['num'] += (per/100.0) * tot
    data[key]['den'] += tot

现在 data 里面包含了：

{('1.2', 'ABC'): {'num': 323.26, 'den': 589},
 ('1.4', 'CDE'): {'num': 140.36, 'den': 185}}

接下来我们可以遍历这个字典，计算出我们想要的结果：

for (idx, ft), v in data.items():
    print idx, ft, round(v['num']/v['den']*100, 1)

输出结果：

1.2 ABC 54.9
1.4 CDE 75.9

Answer 3

使用 Pandas 模块是个不错的选择。假设你的文件名是 '1.txt' 和 '2.txt'，下面的代码会把你所有的输入、输出和中间计算结果都存储在 Pandas 的 DataFrame 实例 df 中。此外，感兴趣的信息会被打印到文件 'out.txt' 中。

import pandas as pd
import numpy as np

file_names = ['1.txt', '2.txt']
df = None

for f_name in file_names:
    df_tmp = pd.read_csv(f_name, sep = '\t') 
    df = df_tmp if df is None else pd.concat([df,df_tmp])

df['Absolute'] = df['Percent'] * df['Total'] 
df['Sum_Total'] = df.groupby('Feature')['Total'].transform(np.sum)
df['Sum_Absolute'] = df.groupby('Feature')['Absolute'].transform(np.sum)
df['AveragePercent'] =  df['Sum_Absolute'] / df['Sum_Total'] 

df_out = df[['ID','Feature','AveragePercent']].drop_duplicates()

df_out.to_csv('out.txt', sep = "\t", index = False)

Answer 4

你需要在读取文件的时候保存一些数据。比如说，你有一个叫做 files 的变量，里面存放了一些文件路径的列表。

data = {}
for filepath in files:
  f = open(filepath, "r")
  f.readline()
  for line in f.readlines():
    info = line.strip().split("\t")
    id, feature, total, percent = info[0], info[1], int(info[2]), int(info[3])
    if id in data:
      data[id].total += total * (percent / 100.0)
      data[id].count += total
    else:
      data[id] = {"feature": feature, "total": total * (percent / 100.0), "count": total}

# Output
out = open("outfile", "w")
out.write("ID\tFeature\tAveragePercentage")
for id in data:
  out.write(str(id) + "\t" + data.feature + "\t" + str(data.total / data.count) + "\n")