Python通过groupby进行求和运算，但排除非数字数据

Question

如何在 python 中使用 csv 文件中的 groupby 进行求和运算，但从该 groupby 中排除一些非数字数据？例如。我有 csv 文件:

id  | filename                  | #Line_Changed
-----------------------------------------------
  1 | analyze/dir_list.txt      |            16
  2 | metrics/metrics1.csv      |            11
  3 | metrics/metrics2.csv      |            15
  4 | analyze/dir_list.txt      |    =>
  5 | metrics/metrics1.csv      |            11
  6 | metrics/metrics2.csv      |    bin
  7 | metrics/metrics2.csv      |             4
  8 | analyze/dir_list.txt      |             4

我想按“文件名”列进行分组，并且仅计算仅包含数字数据的行的总和，并排除非数字数据。结果应该如下所示:

  filename                  | SUM #Line_Changed
 -----------------------------------------------
  analyze/dir_list.txt      |            20
  metrics/metrics1.csv      |            22
  metrics/metrics2.csv      |            19

到目前为止我做了什么:

df = pd.read_csv('diffhistogram.csv')
by_fn = df.groupby('filename')
mydata = {}
for name in ['#line_changed']:
    mydata['SUM ' + name] = by_fn[name].sum()
output = pd.DataFrame(mydata)
print(output)

但输出假定“#line_changed”列中的数据为字符串:

  filename                  | SUM #Line_Changed
 -----------------------------------------------
  analyze/dir_list.txt      |         16=>4
  metrics/metrics1.csv      |          1111
  metrics/metrics2.csv      |        15bin4  

有没有办法可以指定 sum() 运算中要包含哪些数字数据以及要排除哪些非数字数据？

Answer 1

我认为你需要to_numeric使用参数 errors='coerce' 将非数字转换为 NaN，然后 groupby + sum 忽略此行:

df = (pd.to_numeric(df['#Line_Changed'], errors='coerce')
       .groupby(df['filename'])
       .sum()
       .to_frame()
       .add_prefix('SUM ')
       .reset_index())

print (df)
               filename  SUM #Line_Changed
0  analyze/dir_list.txt               20.0
1  metrics/metrics1.csv               22.0
2  metrics/metrics2.csv               19.0

或者分配给用于groupby的新列:

df['SUM #Line_Changed'] = pd.to_numeric(df['#Line_Changed'], errors='coerce')
df = df.groupby('filename', as_index=False)['SUM #Line_Changed'].sum()

print (df)
               filename  SUM #Line_Changed
0  analyze/dir_list.txt               20.0
1  metrics/metrics1.csv               22.0
2  metrics/metrics2.csv               19.0

详细信息:

df['SUM #Line_Changed'] = pd.to_numeric(df['#Line_Changed'], errors='coerce')
print (df)
   id              filename #Line_Changed  SUM #Line_Changed
0   1  analyze/dir_list.txt            16               16.0
1   2  metrics/metrics1.csv            11               11.0
2   3  metrics/metrics2.csv            15               15.0
3   4  analyze/dir_list.txt            =>                NaN
4   5  metrics/metrics1.csv            11               11.0
5   6  metrics/metrics2.csv           bin                NaN
6   7  metrics/metrics2.csv             4                4.0
7   8  analyze/dir_list.txt             4                4.0

编辑:

如果想从原始DataFrame中删除非数字行:

df['#Line_Changed'] = pd.to_numeric(df['#Line_Changed'], errors='coerce')
df = df.dropna(subset=['#Line_Changed'])
print (df)
   id              filename  #Line_Changed
0   1  analyze/dir_list.txt           16.0
1   2  metrics/metrics1.csv           11.0
2   3  metrics/metrics2.csv           15.0
4   5  metrics/metrics1.csv           11.0
6   7  metrics/metrics2.csv            4.0
7   8  analyze/dir_list.txt            4.0