Find the sum of a column by grouping two columns

Question

For this dataset, i want to find the sum of Value(£) for each combination of the three columns together for Year, Length Group and Port of Landing. So for example, one sum value will be for the year 2016, the Length group 10m&Under and the Port of Landing Aberdaran.

What have you tried? The pandas documentation for `groupby` is pretty thorough. — Tim Roberts, Apr 27 '22 at 18:46
`output = df.groupby(["Year","Length Group","Port of Landing"])["Value(£)"].sum()` — not_speshal, Apr 27 '22 at 18:48
I want the value column to be the sum of values for that said groupby. for some reason it has no column name — Will Marshall, Apr 27 '22 at 19:25
I want the output to be in a dataframe including the Year, Length group, Port of Landing and the overall value for the combinations — Will Marshall, Apr 27 '22 at 19:30

score 1 · Accepted Answer · answered Apr 27 '22 at 19:37

1

Given the response you have back to @berkayln, I think you want to project that column back to your original dataframe... Does this suit your need ?

df['sumPerYearLengthGroupPortOfLanding']=df.groupby(['Year','Length Group','Port of Landing'])['Value(£)'].transform(lambda x: x.sum())

answered Apr 27 '22 at 19:37

Daniel Weigel

1,097
2
8
14

1

Thank you , this works perfectly. Sorry if i didnt make that clear! – Will Marshall Apr 27 '22 at 19:56

score 0 · Answer 2 · answered Apr 27 '22 at 18:59

0

You can try this one:

dataframe.groupby(['Year','Length Group','Port of Landing'])['Value(£)'].sum()

That should work.

answered Apr 27 '22 at 18:59

berkayln

935
1
8
14

Is this still in the form of a dataframe? – Will Marshall Apr 27 '22 at 19:09
This is a pandas serie which is a column of a dataframe. – berkayln Apr 28 '22 at 04:03

score 0 · Answer 3 · answered Apr 27 '22 at 20:23

You can use pd.DataFrame.groupby to aggregate the data.

# Change the order if you want a different hierarchy 
grp_cols = ["Year", "Length Group", "Port of Landing"]
df.groupby(grp_cols)["Value(£)"].sum()

You can also do them one-by-one as such:

for col in grp_cols:
    df.groupby(col)["Value(£)"].sum()

You can also use .loc to get 2016 only.

df.loc[df.Year == 2016]["Value(£)"].sum()

The pd.DataFrame.groupby functionality allows you to aggregate using other functions other than .sum, including customized functions that operate on the sub-dataframes.

Find the sum of a column by grouping two columns

3 Answers3