Nras Nras - 2 months ago 26
Python Question

reindex some DataFrame columns to multi index

At some point in my workflow I end up with a regular pandas DataFrame with some columns and some rows. I want to export this DataFrame into a latex table,using

df.to_latex()
. This worked great, however, I know want to use multicolumn where some columns are part of a multi table. For instance a DataFrame with columns a,b,c,d,e I would want to leave column a as it is, but group up b and c, as well as d and e.

import numpy as np
import pandas as pd

# where I am
data = np.arange(15).reshape(3, 5)
df = pd.DataFrame(data=data, columns=['a', 'b', 'c', 'd', 'e'])


It looks like this:

In [161]: df
Out[161]:
a b c d e
0 0 1 2 3 4
1 5 6 7 8 9
2 10 11 12 13 14


I would like to group columns b and c, as well as d and e, but leave a alone. So my desired output should look like this.

# where I want to be: leave column 'a' alone, group b&c as well as d&e
multi_index = pd.MultiIndex.from_tuples([
('a', ''),
('bc', 'b'),
('bc', 'c'),
('de', 'd'),
('de', 'e'),
])
desired = pd.DataFrame(data, columns=multi_index)


It looks like this:

In [162]: desired
Out[162]:
a bc de
b c d e
0 0 1 2 3 4
1 5 6 7 8 9
2 10 11 12 13 14


In order to get there, i tried a simple reindex. This give me the desired shape, but all columns only got NaN as value.

# how can use df and my multiindexreindex to multi column DataFrame
result = df.reindex(columns=multi_index)


The result looks like described, correct indices but all NaN

In [166]: result
Out[166]:
a bc de
b c e e
0 NaN NaN NaN NaN NaN
1 NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN


How can I get my desired result?

Answer

You can assign the multiIndex to the columns attribute of the data frame directly:

df.columns = multi_index
df

enter image description here

Comments