Pandas, create columns after groupby

Question

Regarding the Pandas DataFrame 'test_df':

 id_customer   id_order   product_name
    3             78        product1
    3             79        product2
    3             80        product3
    7             100       product4
    9             109       product5

After a groupby on 'id_customer' how is it possible to get:

 id_customer order_1     order_2   product_name_1   product_name_2
    3          78           79           product1         product2
    7          100                       product4      
    9          109                       product5

The goal is to retrieve the minimum between 2 and the number of line matching each 'id_customer' after the groupby, and then, if possible, fill all the above fields.

I started with

def order_to_col(my_dataframe_df,my_list):
  for num in range(0,min(len(my_list),2)):
    my_dataframe_df['order_'+str(num)] = my_list[num]

test_df = test_df.groupby('id_customer').apply(lambda x: order_to_col(test_df,list(x.id_order)))

but I'm quit sure it's not the good approach

would putting the information into lists be an acceptable alternative? — Yojimbo, Feb 10 '15 at 18:40

score 1 · Accepted Answer · answered Feb 10 '15 at 18:49

Note: I recommend using head to do this rather than using multiple columns:

In [11]: g = df.groupby('id_customer')

In [12]: g.head(2)
Out[12]:
   id_customer  id_order product_name
0            3        78     product1
1            3        79     product2
3            7       100     product4
4            9       109     product5

You can combine the 0th and 1st using nth and then concat these:

In [21]: g = df.groupby('id_customer')

In [22]: g[['id_order', 'product_name']].nth(0)
Out[22]:
             id_order product_name
id_customer
3                  78     product1
7                 100     product4
9                 109     product5

In [23]: g[['id_order', 'product_name']].nth(1)
Out[23]:
             id_order product_name
id_customer
3                  79     product2

In [24]: a = g[['id_order', 'product_name']].nth(0)
         b = g[['id_order', 'product_name']].nth(1)

In [25]: pd.concat([a, b], axis=1)
Out[25]:
             id_order product_name  id_order product_name
id_customer
3                  78     product1        79     product2
7                 100     product4       NaN          NaN
9                 109     product5       NaN          NaN

Pandas, create columns after groupby

1 Answers1

Linked