I have this dataframe:
| date | name | value1 | value 2 |
|---|---|---|---|
| 2021-01-01 | first | 1 | 2 |
| 2021-01-01 | second | 5 | 7 |
| 2021-01-02 | first | 6 | 8 |
| 2021-01-02 | second | 3 | 9 |
And I want to have this one:
| date | values | first | second |
|---|---|---|---|
| 2021-01-01 | value1 | 1 | 5 |
| 2021-01-01 | value2 | 2 | 7 |
| 2021-01-02 | value1 | 6 | 3 |
| 2021-01-02 | value2 | 8 | 9 |
How can I do this ? I tried some pivot, transpose and pivot_table, but it doesn't work
Thanks a lot
Use df.pivot() + df.stack()
(df.pivot(index='date', columns='name', values=['value1', 'value2']) .stack(level=0) .rename_axis(index=['date', 'values'], columns=None) .reset_index() ) date values first second 0 2021-01-01 value1 1 5 1 2021-01-01 value2 2 7 2 2021-01-02 value1 6 3 3 2021-01-02 value2 8 9 
concatcols = ['date', 'name'] pd.concat({ k: d.xs(k).rename_axis(None).T.rename_axis('value') for k, d in df.set_index(cols).groupby('date') }, names=['date']).reset_index() date value first second 0 2021-01-01 value1 1 5 1 2021-01-01 value2 2 7 2 2021-01-02 value1 6 3 3 2021-01-02 value2 8 9 melt(df.melt(['date', 'name'], value_name='v', var_name='value') .set_index(['date', 'name', 'value']).v.unstack('name') .reset_index().rename_axis(None, axis=1) ) date value first second 0 2021-01-01 value1 1 5 1 2021-01-01 value2 2 7 2 2021-01-02 value1 6 3 3 2021-01-02 value2 8 9 for loopdat = {} for d, n, v1, v2 in zip(*map(df.get, df)): dat.setdefault((d, 'value1'), {}).setdefault(n, v1) dat.setdefault((d, 'value2'), {}).setdefault(n, v2) pd.DataFrame.from_dict(dat, 'index').rename_axis(['date', 'value']).reset_index() date value first second 0 2021-01-01 value1 1 5 1 2021-01-01 value2 2 7 2 2021-01-02 value1 6 3 3 2021-01-02 value2 8 9 