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

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

CodePudding user response：

Use DataFrame.rename by dictionary from df2:

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

CodePudding user response：

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

CodePudding user response：

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