CODEWITHSUNDEEP
pythonpandas
Gayatri
Gayatri

Reputation: 2253

Merge with a pivot table changes the column names

I am trying to merge a pandas dataframe with a pivot table and it changes the column names. Can I retain the original column names from pivot without having them merged into a single column?

df1:

  pn    number_of_records   date    
A103310     0               2017-09-01  
B103309     0               2017-06-01
C103308     0               2017-03-01
D103307     2               2016-12-01
E103306     2               2016-09-01

df2 which is a pivot table:

  pn                   t1
                  a1    b1    c1
A103310         3432    324   124
B103309         342     123   984
C103308         435     487   245
D103307         879     358   234
E103306         988     432   235

doing a merge on this dataframe gives me:

df1_df2 = pd.merge(df1,df2,how="left",on="pn")

gives me the column names as:

pn  number_of_records date (t1,a1)  (t1,b1)  (t1,c1)

How can I instead have them as:

pn  number_of_records date         t1
                             a1    b1   c1

in the dataframe after the merge?

Upvotes: 1

Views: 281

Answers (1)

piRSquared
piRSquared

Reputation: 294488

Add a level to the columns of df1

pd.concat([df1], axis=1, keys=['']).swaplevel(0, 1, 1).merge(df2, on='pn')

        pn number_of_records        date    t1          
                                            a1   b1   c1
0  A103310                 0  2017-09-01  3432  324  124
1  B103309                 0  2017-06-01   342  123  984
2  C103308                 0  2017-03-01   435  487  245
3  D103307                 2  2016-12-01   879  358  234
4  E103306                 2  2016-09-01   988  432  235

Upvotes: 1

Related Questions