Setting titles in Pandas

February 15, 2022

I am using the pivot function from Pandas. My intention is to aggregate values by taking the sum. Below you can see data and code

df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
                         "bar", "bar", "bar", "bar"],
                   "B": ["one", "one", "one", "two", "two",
                         "one", "one", "two", "two"],
                   "C": ["small", "large", "large", "small",
                         "small", "large", "small", "small",
                         "large"],
                   "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
                   "E": [2, 4, 5, 5, 6, 6, 8, 9, 9]})

table = pd.pivot_table(df, values=['D','E'], index=['A'],
                    columns=['C'], aggfunc=np.sum, fill_value=0)

So this function works well and gives good results. But after this new problem arise. Namely, now columns have double titles (e.g C/A, D/LARGE etc). You can see the table below

Now I want to have these titles in separate columns: C_A, D_large,D_small, E_large and E_small.

So can anybody help me how to solve this problem?

>Solution :

You can concatenate the index name with the column name, then join the multiindex columns together

table.index.name = table.columns.names[-1] + '_' + table.index.name
table.columns = table.columns.map('_'.join)

Result:

>>> table
     D_large  D_small  E_large  E_small
C_A                                    
bar       11       11       15       17
foo        4        7        9       13