Merging row data using panda's in python

Question

I am trying to write a small python application that creates a csv file that contains data for a recipe system,

Imagine the following structure of excel data

Manufacturer    Product Data 1  Data 2  Data 3
Test 1  Product 1   1   2   3
Test 1  Product 2   4   5   6
Test 2  Product 1   1   2   3
Test 3  Product 1   1   2   3
Test 3  Product 1   4   5   6
Test 3  Product 1   7   8   9

When merged i woudl like the data to be displayed in following format,

Test 1  Product 1   1   2   3   0   0   0   0   0   0
Test 2  Product 2   4   5   6   0   0   0   0   0   0
Test 2  Product 1   1   2   3   0   0   0   0   0   0
Test 3  Product 1   1   2   3   4   5   6   7   8   9

Any help would be greatfully recieved, so far i can read the panda dataset and convert to a CSV

Regards Lee

Answer 1

Use melt, groupby, pd.Series, and unstack:

(df.melt(['Manufacturer','Product'])
  .groupby(['Manufacturer','Product'])['value']
  .apply(lambda x: pd.Series(x.tolist()))
  .unstack(fill_value=0)
  .reset_index())

Output:

  Manufacturer    Product  0  1  2  3  4  5  6  7  8
0       Test 1  Product 1  1  2  3  0  0  0  0  0  0
1       Test 1  Product 2  4  5  6  0  0  0  0  0  0
2       Test 2  Product 1  1  2  3  0  0  0  0  0  0
3       Test 3  Product 1  1  4  7  2  5  8  3  6  9