Select a specific group of a grouped dataframe with pandas

Question

I have the following dataframe:

df.index = df['Date']
df.groupby([df.index.month, df['Category'])['Amount'].sum()

Date   Category     Amount     
1      A            -125.35
       B            -40.00
                    ...
12     A            505.15
       B            -209.00

I would like to report the sum of the Amount for every Category B like:

Date   Category     Amount     
1      B            -40.00
                    ...
12     B            -209.00

I tried the df.get_group method but this method needs tuple that contains the Date and Category key. Is there a way to filter out only the Categories with B?

df.query('Category == "B"').groupby([df.index.month, df['Category'])['Amount'].sum() ? — Erfan
– Erfan, Commented May 28, 2020 at 20:36
This results in an Error: ValueError: Grouper and axis must be same length — job vink
– job vink, Commented May 28, 2020 at 20:47
df.query('Category == "B"').groupby([df['Date'].dt.month, df['Category'])['Amount'].sum(), now you can also skip your line of settings the new index — Erfan
– Erfan, Commented May 28, 2020 at 20:48
I see, it's becaue df['Date'].dt.month points to original index. Try cat_b = df.query('Category == "B"') Then after that cat_b.groupby([cat_b.dt.month, 'Category'])['Amount'].sum() — Erfan
– Erfan, Commented May 28, 2020 at 20:58

Quang Hoang · Accepted Answer · 2020-05-28 21:38:50Z

3

You can use IndexSlice:

# groupby here
df_group = df.groupby([df.index.month, df['Category'])['Amount'].sum()

# report only Category B
df_group.loc[pd.IndexSlice[:,'B'],:]

Or query:

# query works with index level name too
df_group.query('Category=="B"')

Output:

               Amount
Date Category        
1    B          -40.0
12   B         -209.0

answered May 28, 2020 at 21:38

Quang Hoang

151k11 gold badges63 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ListenSoftware Louise Ai Agent · Accepted Answer · 2021-02-16 17:52:57Z

0

apply a filter to your groupby dataframe where Category equals B

filter=df['Category']=='B'
df[filter].groupby([df.index.month, df['Category'])['Amount'].sum()

answered Feb 16, 2021 at 17:52

ListenSoftware Louise Ai Agent

4,3332 gold badges31 silver badges39 bronze badges

Collectives™ on Stack Overflow

Select a specific group of a grouped dataframe with pandas

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related