Skip to content Skip to sidebar Skip to footer

Python Pandas Melting Data To Multiple Columns And Coulmn Names In Another Column

I have a dataframe which I want to melt the data into multiple target columns. The below code I used grp2 = pd.lreshape(grp1, cols.groupby(cols.str.split('_').str[1])).sort_values(

Solution 1:

Use:


df = df.set_index(['Client','INDUSTRY'])
df.columns = df.columns.str.split('_', expand=True)
df = df.stack(0).reset_index().rename(columns={'level_2':'Year_Half'})
print (df)
   Client INDUSTRY Year_Half  12MO  6MO
0     XXX      AAA    1H2016     1    1
1     XXX      AAA    1H2017     0    0
2     XXX      AAA    2H2016     1    0
3     XXX      AAA    2H2017     0    0
4     YYY      BBB    1H2016     0    0
5     YYY      BBB    1H2017     0    1
6     YYY      BBB    2H2016     0    0
7     YYY      BBB    2H2017     1    0
8     ZZZ      CCC    1H2016     0    1
9     ZZZ      CCC    1H2017     1    0
10    ZZZ      CCC    2H2016     0    1
11    ZZZ      CCC    2H2017     1    0
12    XXX      AAA    1H2016     1    1
13    XXX      AAA    1H2017     0    0
14    XXX      AAA    2H2016     1    0
15    XXX      AAA    2H2017     0    0

If only 6MO and 12MO values and ordering of columns is important:

df = df.set_index(['Client','INDUSTRY'])
df.columns = df.columns.str.split('_', expand=True)
df = (df.stack(0)
       .reindex_axis(['6MO','12MO'], 1)
       .reset_index()
       .rename(columns={'level_2':'Year_Half'}))
print (df)
   Client INDUSTRY Year_Half  6MO  12MO
0     XXX      AAA    1H2016    1     1
1     XXX      AAA    1H2017    0     0
2     XXX      AAA    2H2016    0     1
3     XXX      AAA    2H2017    0     0
4     YYY      BBB    1H2016    0     0
5     YYY      BBB    1H2017    1     0
6     YYY      BBB    2H2016    0     0
7     YYY      BBB    2H2017    0     1
8     ZZZ      CCC    1H2016    1     0
9     ZZZ      CCC    1H2017    0     1
10    ZZZ      CCC    2H2016    1     0
11    ZZZ      CCC    2H2017    0     1
12    XXX      AAA    1H2016    1     1
13    XXX      AAA    1H2017    0     0
14    XXX      AAA    2H2016    0     1
15    XXX      AAA    2H2017    0     0

Post a Comment for "Python Pandas Melting Data To Multiple Columns And Coulmn Names In Another Column"