Pandas - Changing header in a Dataframe using data in another Dataframe

Question

I am trying to rename columns of my Dataframe using data available in another Dataframe. Given below are the dataframes I currently have:

Dataframe 1:

emp_id, Week 41, Week 40, Week 39, Week 38
101, 3, 5, 5, 7 
102, 2, 6, 4, 3
103, 7, 5, 3, 6

Dataframe 2:

week, date
Week 35, Aug-29 - Sep-03
Week 36, Sep-05 - Sep-10
Week 37, Sep-12 - Sep-17
Week 38, Sep-19 - Sep-24
Week 39, Sep-26 - Oct-01
Week 40, Oct-03 - Oct-08
Week 41, Oct-10 - Oct-15

Mapping of these Dataframe is using the Week number to date and use that in the main Dataframe

I would like to get the below as the final output:

emp_id, Oct-10 - Oct-15, Oct-03 - Oct-08, Sep-26 - Oct-01, Sep-19 - Sep-24
101, 3, 5, 5, 7 
102, 2, 6, 4, 3
103, 7, 5, 3, 6

Assuming that the dataframes are df1 and df2 respectively, do you sharing the output of print(df1.dtypes) and print(df2.dtypes)? — Gonçalo Peres
– Gonçalo Peres, Commented Oct 12, 2022 at 12:40

jezrael · Accepted Answer · 2022-10-12 12:46:04Z

3

Use DataFrame.rename by dictionary from df2:

df1 = df1.rename(columns=df2.set_index('week')['date'].to_dict())

answered Oct 12, 2022 at 12:46

jezrael

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

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

Comments

PaulS · Accepted Answer · 2022-10-12 12:46:55Z

1

Another possible solution:

(df1.melt(id_vars='emp_id', var_name='week')
 .merge(df2).pivot(index='emp_id', columns='date', values='value'))

Output:

date     Oct-03 - Oct-08   Oct-10 - Oct-15   Sep-19 - Sep-24   Sep-26 - Oct-01
emp_id                                                                        
101                    5                 3                 7                 5
102                    6                 2                 3                 4
103                    5                 7                 6                 3

answered Oct 12, 2022 at 12:46

PaulS

27.1k3 gold badges18 silver badges40 bronze badges

Comments

tlentali · Accepted Answer · 2022-10-12 12:50:14Z

1

We can do that by using map like so :

df1.columns = df1.columns.map(df2.set_index('week')['date'].get)

Or also by using the rename method :

d = dict(zip(df2['week'], df2['date']))
df1 = df1.rename(columns=d)

Output :

    emp_id  Oct-10 - Oct-15 Oct-03 - Oct-08 Sep-26 - Oct-01 Sep-19 - Sep-24
0   101     3               5               5               7
1   102     2               6               4               3
2   103     7               5               3               6

edited Oct 12, 2022 at 12:50

answered Oct 12, 2022 at 12:42

tlentali

3,4632 gold badges18 silver badges23 bronze badges

Collectives™ on Stack Overflow

Pandas - Changing header in a Dataframe using data in another Dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related