I need to Group ID from below Dataframe then Transpose the Value with new Dynamic incremental Header
data = {'ID': ['A', 'B', 'B', 'B', 'C', 'C', 'D', 'D', 'D', 'D'],
'Value': [30, 760, 740, 755, 1 ,4, 56, 34, 76, 12]}
df = pd.DataFrame(data,columns=['ID', 'Value'])
ID Value
0 A 30
1 B 760
2 B 740
3 B 755
4 C 1
5 C 4
6 D 56
7 D 34
8 D 76
9 D 12
I need the output to be something like this
ID Value1 Value2 Value3 Value4
0 A 30
1 B 760 740 755
2 C 1 4
3 D 56 34 76 12
CodePudding user response:
The following will get you well on your way there:
df.pivot(columns='ID').T.fillna('').reset_index().drop(columns='level_0')
Producing:
ID 0 1 2 3 4 5 6 7 8 9
0 A 30
1 B 760 740 755
2 C 1 4
3 D 56 34 76 12
CodePudding user response:
Try:
out = (
df.assign(tmp="Value" (df.groupby("ID").cumcount() 1).astype(str))
.pivot(index="ID", columns="tmp", values="Value")
.fillna("")
.reset_index()
)
out.columns.name = ""
print(out)
Prints:
ID Value1 Value2 Value3 Value4
0 A 30.0
1 B 760.0 740.0 755.0
2 C 1.0 4.0
3 D 56.0 34.0 76.0 12.0