Create new rows out of columns with multiple items in Python

Question

I have these codes and I need to create a data frame similar to the picture attached - Thanks

import pandas as pd

Product = [(100, 'Item1, Item2'),
         (101, 'Item1, Item3'),
         (102, 'Item4')]
labels = ['product', 'info']
ProductA = pd.DataFrame.from_records(Product, columns=labels)

Cust = [('A', 200),
           ('A', 202),
           ('B', 202),
           ('C', 200),
           ('C', 204),
           ('B', 202),
           ('A', 200),
           ('C', 204)]
labels = ['customer', 'product']
Cust1 = pd.DataFrame.from_records(Cust, columns=labels)

Yes - I was able to create some dummy variables but they show as a list not as a singly items — Ameer B.
– Ameer B., Commented Mar 18, 2019 at 15:58

BENY · Accepted Answer · 2019-03-18 16:02:55Z

1

merge with get_dummies

dfA.merge(dfB).set_index('customer').tags.str.get_dummies(', ').sum(level=0,axis=0)
Out[549]: 
          chocolate  filled  glazed  sprinkles
customer                                      
A                 3       1       0          2
C                 1       0       2          1
B                 2       2       0          0

answered Mar 18, 2019 at 16:02

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Friedlander Over a year ago

nice! never seen str.get_dummies before

Josh Friedlander · Accepted Answer · 2019-03-18 15:58:32Z

IIUC possible with merge, split, melt and concat:

dfB = dfB.merge(dfA, on='product')
dfB = pd.concat([dfB.iloc[:,:-1], dfB.tags.str.split(',', expand=True)], axis=1)
dfB = dfB.melt(id_vars=['customer', 'product']).drop(columns = ['product', 'variable'])
dfB = pd.concat([dfB.customer, pd.get_dummies(dfB['value'])], axis=1)
dfB

Output:

   customer   filled sprinkles chocolate  glazed
0         A        0        0        1        0
1         C        0        0        1        0
2         A        0        0        1        0
3         A        0        0        1        0
4         B        0        0        1        0
5         B        0        0        1        0
6         C        0        0        0        1
7         C        0        0        0        1
8         A        0        1        0        0
9         C        0        1        0        0
10        A        0        1        0        0
11        A        1        0        0        0
12        B        1        0        0        0
13        B        1        0        0        0

Collectives™ on Stack Overflow

Create new rows out of columns with multiple items in Python

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related