How to join csv and sum value in column using python

user_10907221 0
Viewed 609

I have a csv file like this:

file1.csv
date        item1    spec    count
2019-03-29  book     blue     10
2019-03-30  shirt    green    11
...
2019-04-25  book     blue     20
2019-04-26  shirt    green    15

and another file csv like this:

file2.csv
date        item1    spec    count
2019-03-29  book     blue     5
2019-03-30  shirt    green    4
...
2019-04-25  book     blue     7
2019-04-26  shirt    green    8

and I want to combine that file to be like this:

fileResult.csv
date        item1    spec    count
2019-03-29  book     blue     15
2019-03-30  shirt    green    15
...
2019-04-25  book     blue     27
2019-04-26  shirt    green    23

Could someone help me to solve this problem?

thank you very much

pandas csv python-3.x python
3 Answers

If both of your dataframes have the same shape (same amount of rows, columns) and the data is aligned as in your example, we can simply do:

df1['count'] = df1['count'] + df2['count']

print(df1)
         date  item1   spec  count
0  2019-03-29   book   blue     15
1  2019-03-30  shirt  green     15
2  2019-04-25   book   blue     27
3  2019-04-26  shirt  green     23

Else use merge:

final = df1.merge(df2, on=['date', 'item1', 'spec'])
final['count'] = final.pop('count_x')+ final.pop('count_y')

         date  item1   spec  count
0  2019-03-29   book   blue     15
1  2019-03-30  shirt  green     15
2  2019-04-25   book   blue     27
3  2019-04-26  shirt  green     23
user_90812670

First read csv and save in dataframe
Concat both files using pd.concat()
groupby date,item,spec and sum the count using groupby() 

df1 = pd.read_csv('csv1.csv')
df2 = pd.read_csv('csv2.csv')
df3 = pd.concat([df1,df2])
final_result= df3.groupby(['date','item','spec'],as_index=False)['count'].sum()

I hope it would solve your problem

user_116079860
user_107667660
Related