How do you do text to Columns & Unpivoting at the same time in python?? using Pandas maybe?

Question

I want to make this:

Into This:

I already have the first table in a data frame in python as df3. How do I get what the output I want using pandas preferably in python?

Answer 1

Assuming this input:

  ID Invoice     DO other_cols
0  A       a  1,2,3        xxx
1  B       b    4,5        xxx
2  C       c      6        xxx

You could use assign+str.split to transform your string into list, and explode to generate one row per item in the list:

(df.assign(DO=df['DO'].str.split(','))
   .explode('DO')
)

output:

  ID Invoice DO other_cols
0  A       a  1        xxx
0  A       a  2        xxx
0  A       a  3        xxx
1  B       b  4        xxx
1  B       b  5        xxx
2  C       c  6        xxx