Grouping data in a dataframe-CodePudding

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.

CodePudding user response：

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