Replace dummy values in multiple columns with corresponding string values from another pandas dataframe

Question

I have two pandas dataframes with the second having dummy values I pulled from the first, like so:

df1
    Col_0   Col_1   Col_2   Col_3    ...Col_27
0   A       535     C       Mission
1   A       536     C       Mission
2   A       541     C       Fair Oaks
3   A       5455    C       Valley
4   A       55      C       Sunset
5   A       55      C       Green
6   B       West    C       4th
7   B       East    C       Bainbridge
8   C       Pearl   B       West
9   C       Main    B       South
10  C       First   C       Allen

df2 = pd.get_dummies(df1[['Col_0', 'Col_2', 'Col_4', 'Col_6', 'Col_8', 'Col_10', 'Col_12', 'Col_14', 'Col_16', 'Col_18', 'Col_20', 'Col_22','Col_24', 'Col_26']])
df2
    Col_0_A Col_0_B Col_0_C Col_2_B Col_2_C ...Col__26_E
0   1       0       0       0       1
1   1       0       0       0       1
2   1       0       0       0       1
3   1       0       0       0       1
4   1       0       0       0       1
5   1       0       0       0       1
6   0       1       0       0       1
7   0       1       0       0       1
8   0       0       1       1       0
9   0       0       1       1       0
10  0       0       1       0       1


df3
    A     B     C       B      C         ...E
0   535                        Mission
1   536                        Mission
2   541                        Fair Oaks
3   5455                       Valley
4   55                         Sunset
5   55                         Green
6         West                 4th
7         East                 Bainbridge
8               Pearl   West    
9               Main    South   
10              First          Allen

And I need to create another dataframe, df3, where the 1's in df2 are replaced by the values in df1 Col_1, Col_3, and so on. The columns in df2 have prefixes for the corresponding columns in df1. Df1 goes up to Col_27, so imagine df2 having something like 150 columns and 25,000 rows. I've gotten this far but have no idea how to map these two together. Hope this all makes sense. Thanks

score 1 · Answer 1 · answered Aug 12 '20 at 23:49

From the beginning create the column pair

df=pd.concat([df1[x].str.get_dummies().mul(df1[y],axis=0)  for x , y in zip(df1.columns[::2],df1.columns[1::2])],axis=1) 
Out[135]: 
       A     B      C      B           C
0    535                         Mission
1    536                         Mission
2    541                        FairOaks
3   5455                          Valley
4     55                          Sunset
5     55                           Green
6         West                       4th
7         East                Bainbridge
8               Pearl   West            
9                Main  South            
10              First              Allen

Replace dummy values in multiple columns with corresponding string values from another pandas dataframe

1 Answers1