How to use groupby with multiindex columns

user_13599311 0
Viewed 132

I have a multidimensional dataframe which was read from an excel sheet and finally looks something like the input table shown below. The dataframe has 2 level column header specified.

df_1  = pd.DataFrame(df.values[2:], columns=[header_1a, header_1b])
pd.MultiIndex.from_frame(df_1)

Input table:

d = {
    "Hier_1": ["a1", "a1", "a1", "a1", "a1", "a2", "a2"],
    "Hier_2": ["b1", "b1", "b1", "b2", "b2", "b3", "b3"],
    "Hier_3": ["c1", "c2", "c3", "c1", "c2", "c4", "c5"],
    "Param_1": [1, 1, 1, 2, 2, 3, 3],
    "Param_2": ["p", "q", "r", "s", "t", "u", "v"],
}
df = pd.DataFrame(d)
df = df.set_index(["Hier_1", "Hier_2", "Hier_3"])
print(df)
                     Param_1 Param_2
Hier_1 Hier_2 Hier_3                 
a1     b1     c1            1       p
              c2            1       q
              c3            1       r
       b2     c1            2       s
              c2            2       t
a2     b3     c4            3       u
              c5            3       v

I want an output table that groups using Hier 1 and Hier 2 as keys. I want it to group such that param 1 results in a sum of the Param 1 values for a given set of Hier 1 and Hier 2. I want output param 2 column to show a list of all Param 2 values for a given Hier 1 and Hier 2.

want an output table like this:

Instance Name   |               |        
________________|_______________|________
Hier 1  |Hier 2 |Param 1        |Param 2
________|_______|_______________|________
a1      |b1     |3              |p, q, r
a1      |b2     |4              |s, t
a2      |b3     |6              |u, v

The following works for a dataframe with just one/one-level column header. But I could not get anything to work for a dataframe with 2-level column header. I always get a key error.

df_1_grouped = df_1.groupby(['Hier1', 'Hier2'], as_index=False).agg({'Param 1': 'sum', 'Param 2': lambda x: tuple(x)})
pandas-groupby multi-index pandas python
1 Answers

You can groupby the levels, keep the two columns and use lambda. 

df_result = (
    df[["Param_1", "Param_2"]]
    .groupby(level=[0, 1])
    .agg(lambda x: x.sum() if x.dtype == "int64" else ", ".join(x))
)
print(df_result)

               Param_1  Param_2
Hier_1 Hier_2                  
a1     b1            3  p, q, r
       b2            4     s, t
a2     b3            6     u, v
user_108886550
Related