Pandas: Pivoting With Duplicate Values

Question

I want to pivot a dataframe with duplicate values in one column to expose the associated values in new columns, as in the example below. From the Pandas documentation I just can't work out how to go from this...

name   car    model
rob    mazda  626
rob    bmw    328
james  audi   a4
james  VW     golf
tom    audi   a6
tom    ford   focus

To this...

name   car_1  model_1  car_2  model_2
rob    mazda  626      bmw    328
james  audi   a4       VW     golf
tom    audi   a6       ford   focus

Answer 1

x = df.groupby('name')['car','model'] \
      .apply(lambda x: pd.DataFrame(x.values.tolist(),
             columns=['car','model'])) \
      .unstack()
x.columns = ['{0[0]}_{0[1]}'.format(tup) for tup in x.columns]

Result:

In [152]: x
Out[152]:
       car_0 car_1 model_0 model_1
name
james   audi    VW      a4    golf
rob    mazda   bmw     626     328
tom     audi  ford      a6   focus

how to sort columns:

In [157]: x.loc[:, x.columns.str[::-1].sort_values().str[::-1]]
Out[157]:
      model_0  car_0 model_1 car_1
name
james      a4   audi    golf    VW
rob       626  mazda     328   bmw
tom        a6   audi   focus  ford

Answer 2

We can set the index using groupby and cumcount

i = df.groupby('name').cumcount() + 1
df.set_index(['name', i2]).unstack()

         car       model       
           1     2     1      2
name                           
james   audi    VW    a4   golf
rob    mazda   bmw   626    328
tom     audi  ford    a6  focus

Or we can collapse the pd.MultiIndex columns

i = df.groupby('name').cumcount() + 1
d1 = df.set_index(['name', i2]).unstack().sort_index(1, 1)
d1.columns = d1.columns.to_series().map('{0[0]}_{0[1]}'.format)
d1


       car_1 model_1 car_2 model_2
name                              
james   audi      a4    VW    golf
rob    mazda     626   bmw     328
tom     audi      a6  ford   focus