CODEWITHSUNDEEP
pythonpandasdataframecsv
mchd
mchd

Reputation: 3163

How to sum values in a column grouped by another column

I have this CSV file:

ID,NAME,CITY,COUNTRY,CPERSON,EMPLCNT,CONTRCNT,CONTRCOST
00000001,Breadpot,Sydney,Australia,[email protected],250,48,1024.00
00000002,Hoviz,Manchester,UK,[email protected],150,7,900.00
00000003,Hoviz,London,UK,[email protected],1500,12800,10510.50
00000004,Grenns,London,UK,[email protected],200,12800,128.30
00000005,Magnolia,Chicago,USA,[email protected],1024,25600,512000.00
00000006,Dozen,San Francisco,USA,[email protected],1000,5,1000.20
00000007,Sun,San Francisco,USA,[email protected],2000,2,10000.01

What I want to do is to find the COUNTRY with the largest number of CONTRCNT. Some countries appear more than once in the dataframe so I need to find the country with the largest sum of CONTRCNT.

I thought about summing up the CONTRCNT for all countries and then finding the largest one but I want to do this in a way that is not bruteforce. I actually want to know how I can use Pandas' groupby function to solve this problem.

Upvotes: 0

Views: 437

Answers (2)

n_195
n_195

Reputation: 13

You can try this

import pandas as pd

df = pd.read_csv("data.csv")
print(df,"\n")

country = df.groupby('COUNTRY')['CONTRCNT'].sum()
country = country[country==country.max()]
print(country,"\n")

# Once groupby is used, the particular columns becomes index, so it can be accessed using below statement
print(country.index.values, "\n")

# Index is used as -1 in case there are multiple data with same value, and data is sorted and we will be needing last data value only
print("Country with the largest number of customers' contracts:", country.index.values[-1], "({} contracts)".format(country[-1]))

This should give your desired output

Upvotes: 0

BENY
BENY

Reputation: 323226

So you can groupby with sum then do idxmax

df.groupby('COUNTRY')['CONTRCOST'].sum().idxmax()

Then

s = df.groupby('COUNTRY')['CONTRCOST'].sum()
s[s==s.max()]

Upvotes: 2

Related Questions