CODEWITHSUNDEEP
pythonpandasdataframe
user248237
user248237

Reputation:

grouping pandas dataframe by two columns (or more)?

I have the following dataframe:

mydf = pandas.DataFrame({"cat": ["first", "first", "first", "second", "second", "third"], "class": ["A", "A", "A", "B", "B", "C"], "name": ["a1", "a2", "a3", "b1", "b2", "c1"], "val": [1,5,1,1,2,10]})

I want to create a dataframe that makes summary statistics about the val column of items with the same class id. For this I use groupby as follows:

mydf.groupby("class").val.sum()

that's the correct behavior, but I'd like to retain the cat column information in the resulting df. can that be done? do I have to merge/join that info in later? I tried:

mydf.groupby(["cat", "class"]).val.sum()

but this uses hierarchical indexing. I'd like to have a plain dataframe back that just has the cat value for each group, where the group by is class. The output should be a dataframe (not series) with the values of cat and class, where the val entries are summed over each entry that has the same class:

cat     class    val
first   A         7
second  B         3
third   C        10

is this possible?

Upvotes: 11

Views: 15777

Answers (1)

waitingkuo
waitingkuo

Reputation: 93784

Use reset_index

In [9]: mydf.groupby(['cat', "class"]).val.sum().reset_index()
Out[9]: 
      cat class  val
0   first     A    7
1  second     B    3
2   third     C   10

EDIT

set level=1 if you want to set cat as index

In [10]: mydf.groupby(['cat', "class"]).val.sum().reset_index(level=1)
Out[10]: 
       class  val
cat              
first      A    7
second     B    3
third      C   10

You can also set as_index=False to get the same output

In [29]: mydf.groupby(['cat', "class"], as_index=False).val.sum()
Out[29]: 
      cat class  val
0   first     A    7
1  second     B    3
2   third     C   10

Upvotes: 14

Related Questions