Pandas - Exploding data into multiple rows and creating a new DataFrame

Question

I have a DataFrame that looks as below:

id| products| quantity| price| time
101| prod_a, prod_b| 2, 1| 10,5| 2020-01-01, 2020-01-03
102| prod_a| 2| 5| 2020-01-03
103| prod_c| 21| 32| 2020-03-02

I have each column that has a particular metric value stored. I am trying to break this such that I can get the below structure:

id, products, quantity, price, time
101, prod_a, 2, 10, 2020-01-01 
101, prod_b, 1, 5, 2020-01-03 
102, prod_a, 2, 5, 2020-01-03
103, prod_c, 21, 32, 2020-03-02

I tried to convert each of the column to a Pandas series as below and then try concat into a master DataFrame as below:

s1 = df.products.str.split(',', expand=True).stack().str.strip().reset_index(level=1, drop=True)
s2 = df.quantity.str.split(',', expand=True).stack().str.strip().reset_index(level=1, drop=True)
s3 = df.price.str.split(',', expand=True).stack().str.strip().reset_index(level=1, drop=True)
s4 = df. time.str.split(',', expand=True).stack().str.strip().reset_index(level=1, drop=True)


df = pd.concat([s1,s2,s3, s4], axis=1, keys=['products', 'quantity', 'price', 'time]).reset_index()

However doing the above throws the below error

ValueError: cannot reindex from a duplicate axis

I just replicated your code with pandas 1.0.3 and it worked (for the exception of missing ' after time on the last line) — Matteo Felici
– Matteo Felici, Commented Sep 8, 2020 at 15:29

Scott Boston · Accepted Answer · 2020-09-08 15:32:37Z

3

One way is to do it like this, using pd.Series.explode:

df.set_index('id').apply(lambda x: x.str.split(',').explode()).reset_index()

Output:

    id  products  quantity  price         time
0  101    prod_a         2     10   2020-01-01
1  101    prod_b         1      5   2020-01-03
2  102    prod_a         2      5   2020-01-03
3  103    prod_c        21     32   2020-03-02

answered Sep 8, 2020 at 15:32

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

Collectives™ on Stack Overflow

Pandas - Exploding data into multiple rows and creating a new DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related