I have a python dataframe with a few columns, let's say that it looks like this:
| Heading 1 | Values |
|---|---|
| A | 1 |
| A | 2 |
| B | 9 |
| B | 8 |
| B | 6 |
What I want to is to "pivot" or group the table so it would look something like:
| Heading 1 | Value 1 | Value 2 | Value 3 |
|---|---|---|---|
| A | 1 | 2 | |
| B | 9 | 8 | 6 |
I was trying to group the table or pivot/unpivot it by several ways, but i cannot figure out how to do it properly.
You can derive a new column that will hold a row number (so to speak) for each partition of heading 1.
df = pd.DataFrame({"heading 1":['A','A','B','B','B'], "Values":[1,2,9,8,6]})
df['rn'] = df.groupby(['heading 1']).cumcount() + 1
heading 1 Values rn
0 A 1 1
1 A 2 2
2 B 9 1
3 B 8 2
4 B 6 3
Then you can pivot, using the newly derived column as your columns argument:
df = df.pivot(index='heading 1', columns='rn', values='Values').reset_index()
rn heading 1 1 2 3
0 A 1.0 2.0 NaN
1 B 9.0 8.0 6.0
