Sum of values from csv using pandas

Question

I want to sum all the value from the 3rd column to get the result in a new csv file with the first and second column using pandas which a think is more efficient.

The maximum values that can be added together are between 0 and 2

the addition will be ignored if there is a value or character other than 0.5 ,1 or 2

Sample of the csv file :

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

what I'm looking for :

323|2013-06-03 00:00:00|0
323|2013-06-03 01:00:00|1
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
323|2013-06-03 05:00:00|0.5
323|2013-06-03 06:00:00|0
323|2013-06-03 07:00:00|3.5
323|2013-06-03 08:00:00|0.5

I've tried without pandas it give me some weird result

Answer 1

You can just sum and set param axis=1, as suggested in a previous answer here

Answer 2

Use, this:

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

Output:

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 3

Note that pd.read_csv() will throw an error if reading a csv with a variable number of columns, unless you supply column names in advance. This should do it:

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']]

Gives:

   Index                 Date  Final
0    323  2013-06-03 00:00:00    0.0
1    323  2013-06-03 01:00:00    1.0
2    323  2013-06-03 02:00:00    1.5
3    323  2013-06-03 03:00:00    1.5
4    323  2013-06-03 04:00:00    0.0
5    323  2013-06-03 05:00:00    0.5
6    323  2013-06-03 06:00:00    0.0
7    323  2013-06-03 07:00:00    3.5
8    323  2013-06-03 08:00:00    0.5

Here is a more succinct approach (it's very similar to @Scott Boston's answer below, but avoids the creation of a separate dataframe). Setting the first two columns of your csv as the indices of your dataframe allows you to conditionally filter the rest of your dataframe that contain only float values:

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)

Gives:

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 4

How about this?

for row in df.rows:
   row[row.columns[2]]=sum(row[row.columns[>1]])