Creating multiindex header in Pandas

Question

I have a data frame in form of a time series looking like this:

and a second table with additional information to the according column(names) like this:

Now, I want to combine the two, adding specific information from the second table into the header of the first one. With a result like this:

I have the feeling the solution to this is quite trivial, but somehow I just cannot get my head around it. Any help/suggestions/hints on how to approach this?

MWE to create to tables:

import pandas as pd
df = pd.DataFrame([[1, 2, 3], [4, 5, 6], [7, 8, 9]],columns=['a', 'b', 'c'])
df2 = pd.DataFrame([['a','b','c'],['a_desc','b_desc','c_desc'],['a_unit','b_unit','c_unit']]).T
df2.columns=['MSR','OBJDESC','UNIT']

can you supply your data as text?

Matt
– Matt

2021-02-18 11:56:39 +00:00
Commented Feb 18, 2021 at 11:56 — Matt
– Matt, Commented Feb 18, 2021 at 11:56
@Matt Hi, I updated my question as a bit of a MWE.

Lorzen
– Lorzen

2021-02-19 15:05:33 +00:00
Commented Feb 19, 2021 at 15:05 — Lorzen
– Lorzen, Commented Feb 19, 2021 at 15:05

Matt · Accepted Answer · 2021-02-18 12:01:14Z

1

You could get a metadata dict for each of the original column names and then update the original df

# store the column metadata you want in the header here
header_metadata = {}

# loop through your second df
for i, row in df2.iterrows():
    # get the column name that this corresponds to
    column_name = row.pop('MSR')
    
    # we don't want `scale` metadta
    row.pop('SCALE')
    
    # we will want to add the data in dict(row) to our first df
    header_metadata[column_name] = dict(row)

# rename the columns of your first df
df1.columns = (
    '\n'.join((c, *header_metadata[c]))
    for c in df1.columns
)

answered Feb 18, 2021 at 12:01

Matt

1,3141 gold badge10 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Creating multiindex header in Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related