Nras Nras - 3 months ago 32
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

. 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
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
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
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?


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

df.columns = multi_index

enter image description here