Unpivot dataframe and join pandas

Question

I have this dataframe:

+-------+-----+---------+
| group | id  |  value  |
+-------+-----+---------+
| A     |  92 | 123.123 |
| A     | 105 |    9034 |
| A     | 999 |     421 |
| B     |  92 |   32019 |
| B     | 105 |    3281 |
+-------+-----+---------+

I'd like to pivot the 'group' column so that its values become part of the 'value' column name and the observations are joined by 'id' like this:

+-----+---------+---------+
| id  | A_value | B_value |
+-----+---------+---------+
|  92 | 123.123 | 32019   |
| 105 |    9034 | 3281    |
| 999 |     421 | nan     |
+-----+---------+---------+

What's the best way to go about doing this?

score 5 · Accepted Answer · answered Jul 25 '18 at 20:10

Use set_index, unstack and flatten multiindex:

df_out = df.set_index(['id','group']).unstack()
df_out.columns = df_out.columns.map('{0[1]}_{0[0]}'.format)
df_out = df_out.reset_index()
print(df_out)

Output:

    id      A_value      B_value
0   92      123.123      32019.0
1  105     9034.000       3281.0
2  999      421.000          NaN

W Stokvis · Answer 2 · 2018-07-25T20:16:03.330

3

Simple solution using pivot, add_suffix (via @ScottBoston) and reset_index

df.pivot(index = 'id',columns = 'group',values = 'value')\
       .add_suffix('_value')\
       .reset_index()
group     id   A_value  B_value
0       92.0   123.123  32019.0
1      105.0  9034.000   3281.0
2      999.0   421.000      NaN

edited Jul 25 '18 at 20:16

answered Jul 25 '18 at 20:12

W Stokvis

1,409
8
15

2

Look at using `add_suffix('_value')` before `reset_index`. – Scott Boston Jul 25 '18 at 20:13
1

@ScottBoston updated. Thanks! – W Stokvis Jul 25 '18 at 20:16
add_suffix adds a string to all the columns correct? how would you do it if there was also another values column in the original dataframe? – jerbear Jul 25 '18 at 20:24
@jerbear You can use this: `.rename({'A':'A_value', 'B':'B_value'}, axis = 1)` – W Stokvis Jul 25 '18 at 20:28

Unpivot dataframe and join pandas

2 Answers2