Pandas change single column head to multi index header - python

Question

I want to take a dataframe that has single head and add mutli-index head columns. The columns that will be multi head are all IDs. I then have IDs and names in a 2nd dataframe.

Here is my example data

df = pd.DataFrame({"A": ['fay','fee','fie','foe']})

df1 = pd.DataFrame(np.random.randint(0,10,size=(4,4)), columns=['012','015','016','018'])
df1 = pd.merge(df,df1, right_index = True, left_index = True)

df2 = pd.DataFrame({'id':['018','015','012','016'], 'name':['boom','bing', 'bota','bada']})
dict_id = dict(zip(df2.id,df2.name))

for the next part how do I make it multilayered like show below?

        bota  bing bada boom
     A  012   015  016  018
____________________________
0   fay   0    3    1    5
1   fee   1    2    9    7
2   fie   5    1    0    3
3   foe   0    7    8    5

look at crosstabs or pivots.

Merlin
– Merlin

2022-09-07 04:52:27 +00:00
Commented Sep 7, 2022 at 4:52 — Merlin
– Merlin, Commented Sep 7, 2022 at 4:52

jezrael · Accepted Answer · 2022-09-07 04:56:08Z

1

Create MultiIndex by Index.map with assign list to columns names:

df1.columns = [df1.columns.map(dict_id).fillna(''), df1.columns]
print (df1)
       bota bing bada boom
     A  012  015  016  018
0  fay    9    7    4    4
1  fee    2    3    1    1
2  fie    6    4    5    1
3  foe    8    6    4    4

answered Sep 7, 2022 at 4:56

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ynjxsjmh · Accepted Answer · 2022-09-07 04:56:37Z

1

We can build MultiIndex with MultiIndex.from_arrays

df1.columns = pd.MultiIndex.from_arrays([df1.columns.map(dict_id).fillna(''), df1.columns])

print(df1)

       bota bing bada boom
     A  012  015  016  018
0  fay    4    1    5    5
1  fee    7    7    9    5
2  fie    9    9    3    2
3  foe    7    9    5    4

answered Sep 7, 2022 at 4:56

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

Pandas change single column head to multi index header - python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related