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"