Search code examples
pythonpandasmulti-index

Pandas add a second level index to the columns using a list

I have a dataframe with column headings (and for my real data multi-level row indexes). I want to add a second level index to the columns based on a list I have.

import pandas as pd

data = {"apple": [7,5,6,4,7,5,8,6],
       "strawberry": [3,5,2,1,3,0,4,2],
       "banana": [1,2,1,2,2,2,1,3],
        "chocolate" : [5,8,4,2,1,6,4,5],
        "cake":[4,4,5,1,3,0,0,3]
       }

df = pd.DataFrame(data)
food_cat = ["fv","fv","fv","j","j"]

I am wanting something that looks like this:

example desired output

I tried to use How to add a second level column header/index to dataframe by matching to dictionary values? - however couldn't get it working (and not ideal as I'd need to figure out how to automate the dictionary, which I don't have).

I also tried adding the list as a row in the dataframe and converting that row to a second level index as in this answer using

df.loc[len(df)] = food_cat
df = pd.MultiIndex.from_arrays(df.columns, df.iloc[len(df)-1])

but got the error Check if lengths of all arrays are equal or not, TypeError: Input must be a list / sequence of array-likes.

I also tried using df = pd.MultiIndex.from_arrays(df.columns, np.array(food_cat)) with import numpy as np but got the same error.

I feel like this should be a simple task (it is for rows), and there are a lot of questions asked, but I was struggling to find something I could duplicate to adapt to my data.

Solution

  • Pandas multi index creation requires a list(or list like) passed as an argument:

    df.columns = pd.MultiIndex.from_arrays([food_cat, df.columns])

df

     fv                           j
  apple strawberry banana chocolate cake
0     7          3      1         5    4
1     5          5      2         8    4
2     6          2      1         4    5
3     4          1      2         2    1
4     7          3      2         1    3
5     5          0      2         6    0
6     8          4      1         4    0
7     6          2      3         5    3