Group dataframe and get sum AND count?
try this:
In [110]: (df.groupby('Company Name')
.....: .agg({'Organisation Name':'count', 'Amount': 'sum'})
.....: .reset_index()
.....: .rename(columns={'Organisation Name':'Organisation Count'})
.....: )
Out[110]:
Company Name Amount Organisation Count
0 Vifor Pharma UK Ltd 4207.93 5
or if you don't want to reset index:
df.groupby('Company Name')['Amount'].agg(['sum','count'])
or
df.groupby('Company Name').agg({'Amount': ['sum','count']})
Demo:
In [98]: df.groupby('Company Name')['Amount'].agg(['sum','count'])
Out[98]:
sum count
Company Name
Vifor Pharma UK Ltd 4207.93 5
In [99]: df.groupby('Company Name').agg({'Amount': ['sum','count']})
Out[99]:
Amount
sum count
Company Name
Vifor Pharma UK Ltd 4207.93 5
How do I Pandas group-by to get sum?
Use GroupBy.sum
:
df.groupby(['Fruit','Name']).sum()
Out[31]:
Number
Fruit Name
Apples Bob 16
Mike 9
Steve 10
Grapes Bob 35
Tom 87
Tony 15
Oranges Bob 67
Mike 57
Tom 15
Tony 1
To specify the column to sum, use this: df.groupby(['Name', 'Fruit'])['Number'].sum()
Get statistics for each group (such as count, mean, etc) using pandas GroupBy?
On groupby
object, the agg
function can take a list to apply several aggregation methods at once. This should give you the result you need:
df[['col1', 'col2', 'col3', 'col4']].groupby(['col1', 'col2']).agg(['mean', 'count'])
Groupby sum and count on multiple columns in python
It can be done using pivot_table
this way:
>>> df1=pd.pivot_table(df, index=['country','month'],values=['revenue','profit','ebit'],aggfunc=np.sum)
>>> df1
ebit profit revenue
country month
Canada 201411 5 10 15
UK 201410 5 10 20
USA 201409 5 12 19
>>> df2=pd.pivot_table(df, index=['country','month'], values='ID',aggfunc=len).rename('count')
>>> df2
country month
Canada 201411 1
UK 201410 1
USA 201409 2
>>> pd.concat([df1,df2],axis=1)
ebit profit revenue count
country month
Canada 201411 5 10 15 1
UK 201410 5 10 20 1
USA 201409 5 12 19 2
UPDATE
It can be done in one-line using pivot_table
and providing a dict of functions to apply to each column in the aggfunc
argument:
pd.pivot_table(
df,
index=['country','month'],
aggfunc={'revenue': np.sum, 'profit': np.sum, 'ebit': np.sum, 'ID': len}
).rename(columns={'ID': 'count'})
count ebit profit revenue
country month
Canada 201411 1 5 10 15
UK 201410 1 5 10 20
USA 201409 2 5 12 19
Pandas Groupby and Sum Only One Column
The only way to do this would be to include C in your groupby (the groupby function can accept a list).
Give this a try:
df.groupby(['A','C'])['B'].sum()
One other thing to note, if you need to work with df after the aggregation you can also use the as_index=False
option to return a dataframe object. This one gave me problems when I was first working with Pandas. Example:
df.groupby(['A','C'], as_index=False)['B'].sum()
groupby, sum and count to one table
One option is to count the size and sum the columns for each group separately and then join them by index:
df.groupby("C")['A'].agg({"number": 'size'}).join(df.groupby('C').sum())
number A B
# C
# a 2 11 8
# b 2 14 12
# c 2 8 5
# d 2 11 12
# e 1 7 2
You can also do df.groupby('C').agg(["sum", "size"])
which gives an extra duplicated size column, but if you are fine with that, it should also work.
Group dataframe and get sum AND count?
try this:
In [110]: (df.groupby('Company Name')
.....: .agg({'Organisation Name':'count', 'Amount': 'sum'})
.....: .reset_index()
.....: .rename(columns={'Organisation Name':'Organisation Count'})
.....: )
Out[110]:
Company Name Amount Organisation Count
0 Vifor Pharma UK Ltd 4207.93 5
or if you don't want to reset index:
df.groupby('Company Name')['Amount'].agg(['sum','count'])
or
df.groupby('Company Name').agg({'Amount': ['sum','count']})
Demo:
In [98]: df.groupby('Company Name')['Amount'].agg(['sum','count'])
Out[98]:
sum count
Company Name
Vifor Pharma UK Ltd 4207.93 5
In [99]: df.groupby('Company Name').agg({'Amount': ['sum','count']})
Out[99]:
Amount
sum count
Company Name
Vifor Pharma UK Ltd 4207.93 5
How to make pandas groupby().count() sum values rather than rows?
I think what you want to use is GroupBy.sum
How to distinct (count), group by and sum data in DataFrame in Python?
In your case do
out = df.groupby(['name','date'],as_index=False).agg({'sum':'sum','order':'nunique'})
Out[652]:
name date sum order
0 A 20220501 34.1 2
1 B 20220502 77.6 3
Groupby two columns, sum, count and display output values in separate column (pandas)
You can use .GroupBy.transform()
to set the values for columns pwr
and count
. Then .set_index()
on the 4 columns except type
to get a layout similar to the desired output:
df['pwr'] = df.groupby(['id', 'date'])['pwr'].transform('sum')
df['count'] = df.groupby(['id', 'date'])['pwr'].transform('count')
df.set_index(['id', 'date', 'pwr', 'count'])
Output:
type
id date pwr count
aa q321 11 2 hey
2 hello
q425 40 2 hi
2 no
bb q122 3 2 ok
2 cool
q422 15 3 sure
3 sure
3 ok
Related Topics
Child Processes Created with Python Multiprocessing Module Won't Print
How to Convert an H:Mm:Ss Time String to Seconds in Python
Selenium: Get Coordinates or Dimensions of Element with Python
Convert Year/Month/Day to Day of Year in Python
How to Make Lists Contain Only Distinct Element in Python
Python Float to Int Conversion
Python JSON.Loads Fails with 'Valueerror: Invalid Control Character At: Line 1 Column 33 (Char 33)'
How to Crop to Largest Interior Bounding Box in Opencv
How to Use MySQLdb with Python and Django in Osx 10.6
Convert a Python Dict to a String and Back
Nltk-Based Text Processing with Pandas
How to Merge Multiple Lists into One List in Python