使用pandas的csv值总和

Question

我想把第3列中的所有值求和，得到一个新的csv文件，第一列和第二列使用pandas，这是一种更有效的思考。在

可以相加的最大值介于0和2之间

如果存在0.5、1或2以外的值或字符，则将忽略加法

csv文件示例：

https://pastebin.com/WwDWqU3U

encounterId|chartTime|11885|67187|6711|6711|6710|1356|1357|1358|1359|1360|1361|1362|1366|140|140

325|2014-01-01 00:00:00|0
325|2014-01-01 01:00:00|0|0|0
325|2014-01-01 02:00:00|0
325|2014-01-01 03:00:00|0|0|0
325|2014-01-01 04:00:00|0
325|2014-01-01 05:00:00|1
325|2014-01-01 06:00:00|0|0|0
325|2014-01-01 07:00:00|1|0|0.5|1
325|2014-01-01 08:00:00|0
325|2014-01-01 09:00:00|1|0|0
325|2014-01-01 10:00:00|0
325|2014-01-01 11:00:00|1|0|0
325|2014-01-01 12:00:00|0
325|2014-01-01 13:00:00|0|0|0.5|1
325|2014-01-01 14:00:00|0
325|2014-01-01 15:00:00|0

我要找的是：

^{pr2}$

我试过没有熊猫，结果很奇怪

Answer 1

请注意，pd.read_csv()如果读取列数可变的csv，则会抛出错误，除非您事先提供列名。这应该做到：

import pandas as pd
import numpy as np

df = pd.read_csv('sample.txt', names=['Index','Date','Val1','Val2','Val3','Val4'], sep='|')

df[df[['Val1','Val2','Val3','Val4']]>2] = np.nan

df['Final'] = df.iloc[:,2:].sum(axis=1)

df = df[['Index','Date','Final']]

给出：

^{pr2}$

这里有一个更简洁的方法（它非常类似于@Scott Boston下面的答案，但是避免了创建单独的数据帧）。将csv的前两列设置为dataframe的索引，可以有条件地过滤只包含float值的其余dataframe：

df = pd.read_csv('sample.txt', names=['Index','Date','Val1','Val2','Val3','Val4'], sep='|').set_index(['Index','Date'])

df['Final'] = df[(df>0) & (df<=2)].sum(axis=1)

df.reset_index()[['Index','Date','Final']].to_csv('output.csv', index=False, header=False)

给出：

323,2013-06-03 00:00:00,0.0
323,2013-06-03 01:00:00,1.0
323,2013-06-03 02:00:00,1.5
323,2013-06-03 03:00:00,1.5
323,2013-06-03 04:00:00,0.0
323,2013-06-03 05:00:00,0.5
323,2013-06-03 06:00:00,0.0
323,2013-06-03 07:00:00,3.5
323,2013-06-03 08:00:00,0.5

Answer 2

使用，这个：

from io import StringIO
csvfile = StringIO("""323|2013-06-03 00:00:00|0|0|0
323|2013-06-03 01:00:00|1|
323|2013-06-03 02:00:00|1|0|0.5|86
323|2013-06-03 03:00:00|1|0|0.5|0
323|2013-06-03 04:00:00|0
323|2013-06-03 05:00:00|0|0|0.5|0
323|2013-06-03 06:00:00|0
323|2013-06-03 07:00:00|1|0|0.5|2
323|2013-06-03 08:00:00|0|0.5""")

df = pd.read_csv(csvfile, sep='|', names=['ID','date','A','B','C','D'])

df_out = df.set_index(['ID','date'])

df_out.where((df_out>0) & (df_out<=2), 0)\
      .sum(1)\
      .reset_index()\
      .to_csv('outfile.csv', index=False, header=False)

!type outfile.csv

输出：

^{pr2}$

Answer 3

您只需求和并设置param axis=1，如前一个答案here