Group by and Filter with Pandas without loosing groupby

Question

I'm a begginer in the subject and didn't find anything to help me here so far. I'm struggling in grouping my data and then filtering with a value I need.

Like the example,

Table

I need to Know, for example, how many Red Cars Juan bought. (Red Cars sells for each client). When I try, I loose the group or the filter, I can't do both.

Can someone help me or suggest a post please?

Edit1.

With the help of the community, I find this as my solution:

df = df.loc[:, df.columns.intersection(['Name', 'Car colour', 'Amount'])]

df = df.query('Car colour == Red')

df.groupby(['Name', 'Car colour'])['Amount'].sum().reset_index()

Does this answer your question? [Python pandas groupby aggregate on multiple columns, then pivot](https://stackoverflow.com/questions/43172970/python-pandas-groupby-aggregate-on-multiple-columns-then-pivot) — nick, Aug 16 '21 at 18:58

score 1 · Answer 1 · answered Aug 16 '21 at 18:52

1

GroupBy.sum

df.groupby(['Name','Car Color']).sum()

output:

import pandas as pd

data = {"Name": ["Juan", "Wilson", "Carlos", "Juan", "Juan", "Wilson", "Juan", "Carlos"],
        "Car Color": ["Red", "Blue", "Yellow", "Red", "Red", "Red", "Red", "Green"],
        "Amount": [24, 28, 40, 22, 29, 33, 31, 50]}
df = pd.DataFrame(data)
print(df)

answered Aug 16 '21 at 18:52

Piotr Żak

2,046
5
18
30

1

Thanks! This is a simpler way for fast consulting. – GuiR Aug 16 '21 at 19:28

score 1 · Answer 2 · answered Aug 16 '21 at 18:56

You can group by multiple columns by passing a list of column names to the groupby function, then taking the sum of each group.

import pandas as pd

df = pd.DataFrame({'Name': ['Juan', 'Wilson', 'Carlos', 'Juan', 'Juan', 'Wilson', 'Juan'],
                   'Car Color': ['Red', 'Blue', 'Yellow', 'Red', 'Red', 'Red', 'Green'],
                   'Amount': [1, 1, 1, 1, 1, 1, 1]})
print(df)

agg_df = df.groupby(['Name', 'Car Color']).sum()
print(agg_df)

Output:

Name   Car Color        
Carlos Yellow          1
Juan   Green           1
       Red             3
Wilson Blue            1
       Red             1

Note that the resulting dataframe has a multi-index, so you can get the number of red cars that Juan bought by passing a tuple of values to loc.

cars = agg_df.loc[[('Juan', 'Red')]]
print(cars)

Output:

                Amount
Name Car Color        
Juan Red             3

Thanks! I appreciate the hint with the .loc function. – GuiR Aug 16 '21 at 19:29 — GuiR, Aug 16 '21 at 19:29

score 1 · Accepted Answer · answered Aug 16 '21 at 19:01

1

If you want to consider amount sold by group of Name and Car_color then try

df.groupby(['Name', 'Car colour'])['Amount'].sum().reset_index()
#    Name   Car colour  Amount
 0    Juan      green       1
 1    Juan        red       3
 2  Wilson       blue       1
 3  carlos     yellow       1

answered Aug 16 '21 at 19:01

Satya

5,470
17
47
72

1

You can pass `as_index=False` in `groupby`, i.e. `df.groupby(['Name', 'Car Color'], as_index=False)['Amount'].sum()`. – Henry Yik Aug 16 '21 at 19:09
The point I was missing is passing the ['Amount'] after the groupby, thanks! – GuiR Aug 16 '21 at 19:26

Group by and Filter with Pandas without loosing groupby

3 Answers3