How to Group then Transpose Dataframe without summarization

user_14372416 0
Viewed 41

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
pandas-groupby dataframe python
2 Answers

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
user_78789910

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
user_100359850
Related