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

Question

I have 2 data frames which look like a following:

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 data frames 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 the 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 to merge and join queries with the transpose of one df1 but did not able to get the desired output? Any guidance and help would be really appreciated?

Answer 1

We need to 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')

 Learn Python for Data Science to improve your technical knowledge.