Function in Pandas for reshape

Question

I'm having a dataset which as the following

customer    products    Sales
1           a           10
1           a           10
2           b           20
3           c           30

How can I reshape and to do that in python and pandas? I've tried with the pivot tools but since I have duplicated CUSTOMER ID it's not working...

Products                
customerID  a   b   c   
1           10          
1           10          
2              20       
3                   30  



{' update': {209: 'Originator',
 211: 'Originator', 
212: 'Originator',
 213: 'Originator',
 214: 'Originator'}, 
'CUSTOMER ID': {209: 1000368,
 211: 1000368, 212: 1000968, 213: 1000968, 214: 1000968},
 'NET SALES VALUE SANOFI':{209: 426881.0,
 211: 332103.0, 212: 882666.0, 213: 882666.0, 214: 294222.0},
 'PRODUCT FAMILY': {209: 'APROVEL',
 211: 'APROVEL', 212: 'APROVEL', 213: 'APROVEL', 214: 'APROVEL'}, 
'CHANNEL DEFINITION':
 {209: 'PHARMACY', 211: 'PHARMACY', 212: 'PHARMACY', 213: 'PHARMACY', 214: 'PHARMACY'}, 
'index': {209: 209, 211: 211, 212: 212, 213: 213, 214: 214}


CUSTOMER ID 1228675 non-null int64 
DISTRIBUTOR ID 1228675 non-null float64 
PRODUCT FAMILY 1228675 non-null 
object GROSS SALES QUANTITY 1228675 
non-null int64 GROSS SALES VALUE 1228675 
non-null int64 NET SALES VALUE 1228675 
non-null int64 DISCOUNT VALUES 1228675 
non-null int64 CHANNEL DEFINITION 1228675 non-null object

what i tried also : ONLY_PHARMA.pivot_table(values = "NET SALES VALUE ", index = ["CUSTOMER ID"], columns = "PRODUCT FAMILY").reset_index()

what im getting now a mix of float and Int....?? Why?

ID        A           B          C
1000167  NaN   2.380122e+05      244767.466667

or im having : 

ValueError: negative dimensions are not allowed

OR I've done which also return me floats and int:

pvt = pd.pivot_table(ONLY_PHARMA.reset_index(), index=['CUSTOMER ID'],
                              columns='PRODUCT FAMILY', values='NET SALES VALUE' , fill_value='') \
               .reset_index()

Answer 1

You can use cumcount with set_index + unstack for reshape:

g = df.groupby(['customer', 'products']).cumcount()
df = (
      df.set_index([g, 'customer', 'products'])['Sales']
       .unstack().sort_index(level=1)
       .reset_index(level=0, drop=True)
      )
print (df)
products     a     b     c
customer                  
1         10.0   NaN   NaN
1         10.0   NaN   NaN
2          NaN  20.0   NaN
3          NaN   NaN  30.0

Notice:

If duplicated values, maybe need aggregation, check how to pivot a dataframe

Answer 2

Here's fairly straight-forward way assuming you have a unique index, given your input of:

   customer products  Sales
0         1        a     10
1         1        a     10
2         2        b     20
3         3        c     30

Pivot it to columnise the products and rejoin to just the customer column on the original frame, eg:

new_df = df[['customer']].join(df.pivot(columns='products', values='Sales'))

This'll give you:

   customer     a     b     c
0         1  10.0   NaN   NaN
1         1  10.0   NaN   NaN
2         2   NaN  20.0   NaN
3         3   NaN   NaN  30.0

Then sort out your indexing / filling blank values.

Answer 3

Another method using str.get_dummies.

pd.concat([df, df.products.str.get_dummies().multiply(df["Sales"], axis="index")], 
          axis=1)


   customer products    Sales   a   b   c
0   1       a           10      10  0   0
1   1       a           10      10  0   0
2   2       b           20      0   20  0
3   3       c           30      0   0   30

df.products.str.get_dummies() creates dummy variables as follows

    a   b   c
0   1   0   0
1   1   0   0
2   0   1   0
3   0   0   1

We then need to multiply this dummy variable table with df["Sales"]. This is achieved by df.products.str.get_dummies().multiply(df["Sales"], axis="index") (See reference for more information.)

    a   b   c
0   10  0   0
1   10  0   0
2   0   20  0
3   0   0   30

Reference

how to multiply multiple columns by a column in Pandas

Note: to replace 0 with np.nan, you need to add .replace(0, np.nan) like

pd.concat([df, df.products.str.get_dummies().replace(0, np.nan).mul(df["Sales"], axis="index")], axis=1)

Answer 4

Your question is unclear. In case of duplicate key, we usually aggregate values. Is that what you want ? Try this:

df.pivot_table(index='customer', columns='products', values ='Sales', aggfunc='sum')

products customer   a    b    c
   0         1    20.0  NaN  NaN
   1         2    NaN   20.0 NaN
   2         3    NaN   NaN 30.0