How to pivot columns correctly

Question

I have a dataframe like this:

    NUMBER          NAME
1   000231          John Stockton
2   009456          Karl Malone
3   100000901       John Stockton
4   100008496       Karl Malone

I want to obtain a new dataframe with:

    NAME            VALUE1  VALUE2
1   John Stockton   000231  100000901
2   Karl Malone     009456  100008496

I think I should use pd.groupby(), but I have no function to pass as an aggregator (I don't need to compute any mean(), min(), or max() value). If I just use pd.groupby() without any aggregator, I get:

In[1]: pd.DataFrame(df.groupby(['NAME']))

Out[1]: 
           0               1
        0  John Stockton   NAME NUMBER 000231 100000901
        1  Karl Malone     NAME NUMBER 009456 100008496

What am I doing wrong? Do I need to pivot the dataframe?

https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe — jezrael, Oct 01 '21 at 13:33
### df1 = df.groupby('NAME').agg(lambda x: [i for i in x]).reset_index() ### df1[['Value1','Value2']] = pd.DataFrame(df1.NUMBER.tolist()) ### df1.drop('NUMBER',axis=1) — EBDS, Oct 01 '21 at 13:49

mozway · Accepted Answer · 2021-10-01T13:31:51.853

1

Actually, you need a bit mode complicated pipeline:

(df.assign(group=df.groupby('NAME').cumcount().add(1)
   .pivot(index='NAME', columns='group', values='NUMBER')
   .rename_axis(None, axis=1)
   .add_prefix('VALUE')
   .reset_index()
)

output:

            NAME  VALUE1     VALUE2
0  John Stockton     231  100000901
1    Karl Malone    9456  100008496

edited Oct 01 '21 at 13:31

answered Oct 01 '21 at 13:29

mozway

81,317
8
19
49

Better is cumcount – jezrael Oct 01 '21 at 13:31
@jezrael yes, I was actually changing it ;) thanks! – mozway Oct 01 '21 at 13:32
But it is dupe, question 10 – jezrael Oct 01 '21 at 13:32
1

what means "question 10"? – mozway Oct 01 '21 at 13:32
in duplicate Q/A – jezrael Oct 01 '21 at 13:33
1

I see, honestly here I think a separate answer is better, huge Q/A like the one you linked to are very difficult to read though – mozway Oct 01 '21 at 13:34
Exactly it is reason why add to comments check question 10 – jezrael Oct 01 '21 at 13:34

How to pivot columns correctly

1 Answers1