CODEWITHSUNDEEP
pythonpython-3.xpandas
user2293224
user2293224

Reputation: 2220

Python Pandas: rows value of column in one dataframe became column in other dataframe

I have two dataframes which look like a follow:

df:

     ID      Season Time_of_day KVA   Agriculture   Commercial  Industrial  Residential Agri_cap    
    162     Spring  Day         409.71  8.0          221.0         11.0        365.0     126.0  
    162     Spring  Evening     409.05  8.0          221.0         11.0        365.0     126.0  
    162     Autmun  Morning     388.95  8.0          221.0         11.0        365.0     126.0  
    162     Autmun  Night       242.32  8.0          221.0         11.0        365.0     126.0  
    162     Summer  Day         290.52  8.0          221.0         11.0        365.0     126.0

df1:

    ID  Classification  Time_of_day Spring_prop Summer_prop Winter_prop Autmun_prop
    162 Agriculture     Day           0.238636      0.125000    0.261364    126.0
    162 Agriculture     Evening       0.285714      0.428571    0.285714    126.0
    162 Agriculture     Morning       0.312500      0.000000    0.125000    126.0
    162 Agriculture     Night         0.718750      0.000000    0.000000    126.0
    162 Commercial      Day           0.123732      0.069122    0.101264    5864.0
    162 Commercial      Evening       0.276018      0.413058    0.233355    5864.0
    162 Commercial      Morning       0.058824      0.000000    0.169683    5864.0
    162 Commercial      Night         0.407240      0.000000    0.000000    5864.0
    162 Council         Day           0.375000      0.250000    0.375000    15.0
    162 Council         Evening       0.285714      0.428571    0.285714    15.0
    162 Council         Night         1.000000      0.000000    0.000000    15.0
    162 Industrial      Day           0.095960      0.090909    0.085859    74.0
    162 Industrial      Evening       0.103896      0.142857    0.103896    74.0
    162 Industrial      Morning       0.045455      0.000000    0.045455    74.0
    162 Industrial      Night         0.409091      0.000000    0.000000    74.0
    162 Residential     Day           0.147068      0.096110    0.143342    5593.0
    162 Residential     Evening       0.280626      0.417221    0.265753    5593.0
    162 Residential     Morning       0.069863      0.000000    0.238356    5593.0
    162 Residential     Night         0.376125      0.000000    0.000000    5593.0

I want to merge these two dataframes in such a way that row values of columns named "Classification, Summer_prop, Winter_prop, Spring_prop, and Autmun_prop" in df1 combined in a way (as shown in the desired output) and becomes the columns in the final data frame. Here is the desired df I want:

Desired output

final_df:

 ID  Time_of_day KVA   Agriculture   Commercial  Industrial  Residential Agri_cap Agriculture_Spring_prop   Agriculture_Summer_prop    Agriculture_Winter_prop   Agriculture_Autmun_prop  Commerical_Spring_prop   Commerical_Summer_prop     Commerical_Winter_prop     Commerical_Autmun_prop Industrial_Spring_prop    Industrial_Summer_prop    Industrial_Winter_prop    Industrial_Autmun_prop Residential_Spring_prop    Residential_Summer_prop   Residential_Winter_prop  Residential_Autmun_prop

162  Day       409.71   8.0          221.0        11.0        365.0      126.0   0.238          0.125 0.261 126.0 0.123 0.0691  0.101 5864.0  0.095  0.090  0.0858  74.0  0.1470  0.096  0.1433 5593.0
162  Evening   409.05   8.0          221.0        11.0        365.0      126.0   0.285          0.428  0.2857  126.0   0.276  0.413  0.2333 5864.0  0.1038  0.142  0.103 74.0  0.280 0.417  0.2657 5593.0

I tried merge and join queries with transpose of one df1 but did not able to get the desired output? Any guidance and help would be really appreciated?

Upvotes: 0

Views: 38

Answers (1)

BENY
BENY

Reputation: 323226

We need do pivot for the df1 then do merge

s=df1.set_index(['ID','Classification','Time_of_day']).unstack('Classification')
s.columns=s.columns.map('_'.join)
df=df.merge(s.reset_index(),on='ID')

Upvotes: 2

Related Questions