I know how to unstack rows into columns, but how to deal with the following table?
| date | dummy | avg | lable |
|---|---|---|---|
| 1-19 | 1 | 20 | l1 |
| 1-19 | 0 | 40 | l1 |
| 1-27 | 1 | 100 | l2 |
| 1-27 | 0 | 140 | l2 |
Expecting tables:
| date | avg_t | avg_c | lable |
|---|---|---|---|
| 1-19 | 20 | 40 | l1 |
| 1-27 | 100 | 140 | l2 |
The "avg" is 20 when "dummy" equals to 1 and it is renamed to "avg_t" as a column. Similar as the column "avg_c".
I tried:
df.groupby(['dummy','avg']).size().unstack
It does not work.
>Solution :
Create new column by DataFrame.assign with Series.map and then use DataFrame.pivot with DataFrame.add_prefix:
df = (df.assign(new = df['dummy'].map({0:'c', 1:'t'}))
.pivot(['date','lable'], 'new', 'avg')
.add_prefix('avg_')
.reset_index()
.rename_axis(None, axis=1))
print (df)
date lable avg_c avg_t
0 1-19 l1 40 20
1 1-27 l2 140 100