0

I have a csv file and there are Date, count, and service column. There are many date,count and service columns but this is the example I will write below.

Number  Count   Service       Number    Count   service
0        13   NO SERVICE        0        10 
1        14   tcpmux            1        10 
2         9   compressnet       2        14

So I want the answer like :

Number   Total Count    Service
0            23         NO SERVICE
1            24         tcpmux
2            23         compressnet

How do I do the code in pandas

import pandas as pd
df =pd.read_csv ("/Users/mani/Desktop/monthly report/geoip/2017-20dstipsum12.csv")
hasil =  df.groupby(['NUMBER']).sum()
hasil.to_csv('gotttt.txt', sep='\t', encoding='utf-8')
Angeline
  • 109
  • 10

1 Answers1

0

If columns Number are same in all data:

#sum all column Count
df['Total Count'] = df['Count'].sum(axis=1)
#select first and third column and join Total Count column
df = df.iloc[:, [0,2]].join(df['Total Count'])
print (df)
   Number  Total Count   Total Service
0       0           23      NO SERVICE
1       1           24          tcpmux  
2       2           23     compressnet

In newer version of pandas are deduplicated columns names in read_csv, so need filter for select columns:

print (df)
   Number  Count      Service  Number.1  Count.1 Service.1
0       0     13   NO SERVICE         0       10          
1       1     14       tcpmux         1       10          
2       2      9  compressnet         2       14 

df['Total Count'] = df.filter(like='Count').sum(axis=1)

df = df[['Number','Total Count','Service']]
print (df)
   Number  Total Count   Total Service
0       0           23      NO SERVICE  
1       1           24          tcpmux 
2       2           23     compressnet
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252