pandas-groupby

Group dataframe and get sum AND count?

by

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 … Read more

Count unique values per groups with Pandas [duplicate]

by

You need nunique: df = df.groupby(‘domain’)[‘ID’].nunique() print (df) domain ‘facebook.com’ 1 ‘google.com’ 1 ‘twitter.com’ 2 ‘vk.com’ 3 Name: ID, dtype: int64 If you need to strip ‘ characters: df = df.ID.groupby([df.domain.str.strip(“‘”)]).nunique() print (df) domain facebook.com 1 google.com 1 twitter.com 2 vk.com 3 Name: ID, dtype: int64 Or as Jon Clements commented: df.groupby(df.domain.str.strip(“‘”))[‘ID’].nunique() You can retain … Read more

GroupBy pandas DataFrame and select most common value

by

Pandas >= 0.16 pd.Series.mode is available! Use groupby, GroupBy.agg, and apply the pd.Series.mode function to each group: source.groupby([‘Country’,’City’])[‘Short name’].agg(pd.Series.mode) Country City Russia Sankt-Petersburg Spb USA New-York NY Name: Short name, dtype: object If this is needed as a DataFrame, use source.groupby([‘Country’,’City’])[‘Short name’].agg(pd.Series.mode).to_frame() Short name Country City Russia Sankt-Petersburg Spb USA New-York NY The useful thing … Read more

Concatenate strings from several rows using Pandas groupby

by

You can groupby the ‘name’ and ‘month’ columns, then call transform which will return data aligned to the original df and apply a lambda where we join the text entries: In [119]: df[‘text’] = df[[‘name’,’text’,’month’]].groupby([‘name’,’month’])[‘text’].transform(lambda x: ‘,’.join(x)) df[[‘name’,’text’,’month’]].drop_duplicates() Out[119]: name text month 0 name1 hej,du 11 2 name1 aj,oj 12 4 name2 fin,katt 11 6 … Read more

How to access pandas groupby dataframe by key

by

You can use the get_group method: In [21]: gb.get_group(‘foo’) Out[21]: A B C 0 foo 1.624345 5 2 foo -0.528172 11 4 foo 0.865408 14 Note: This doesn’t require creating an intermediary dictionary / copy of every subdataframe for every group, so will be much more memory-efficient than creating the naive dictionary with dict(iter(gb)). This … Read more

Multiple aggregations of the same column using pandas GroupBy.agg()

by

As of 2022-06-20, the below is the accepted practice for aggregations: df.groupby(‘dummy’).agg( Mean=(‘returns’, np.mean), Sum=(‘returns’, np.sum)) Below the fold included for historical versions of pandas. You can simply pass the functions as a list: In [20]: df.groupby(“dummy”).agg({“returns”: [np.mean, np.sum]}) Out[20]: mean sum dummy 1 0.036901 0.369012 or as a dictionary: In [21]: df.groupby(‘dummy’).agg({‘returns’: {‘Mean’: np.mean, … Read more

How to loop over grouped Pandas dataframe?

by

df.groupby(‘l_customer_id_i’).agg(lambda x: ‘,’.join(x)) does already return a dataframe, so you cannot loop over the groups anymore. In general: df.groupby(…) returns a GroupBy object (a DataFrameGroupBy or SeriesGroupBy), and with this, you can iterate through the groups (as explained in the docs here). You can do something like: grouped = df.groupby(‘A’) for name, group in grouped: … Read more