Dataframe sorting on product name - jupiter notebook

Question

I have an excel document with the following columns: number, name, street, time, day, product 1, product 2, ..... , product 40 I have imported this with: df = pd.read_excel('Filename.xlsm', sheet_name="input")

Then i have made a excel file for each product which sorts it on day and time

productname= df['number', 'name', 'street', 'day', 'time', 'product 1']

productname1= productname.dropna(subset=['product 1'])

productname2= productname1.sort_values(by=['day', 'time'], ascending=True, inplace=False))

productname2.to_excel('product_1.xlsx', index=False, sheet_name='product_1')

this is then repeated for all products in the excel file. and also some other different things but that is not important. I know it can be looped but for keeping organised it works since products are added and subtracted from time to time and the people that have to maintain the system don't know much about coding.

My question now is that i want to have a excel file with as columns: number, name, street, time, day, Product_name (so product 1 or product 10 f.e.), product value (so what is written for that product).

and this needs to be sorted in the same way on day and time but then also on product name this cannot be mixed up [See the example image][1]

I am very stuck with how to achieve this, is there anybody that is able to help me? [1]: https://i.stack.imgur.com/Xmhb0.png

abokey · Answer

You can use pandas.DataFrame.melt to get your expected output.

Try this :

import pandas as pd

df = pd.read_excel('Filename.xlsm', sheet_name="input")

out = (df.melt(['number','name', 'street', 'time', 'day'], 
         var_name='product name', 
         value_name='product value')
 .dropna(subset='product value')
 .sort_values(by=['product name', 'day', 'time'], ascending=True)
 .reset_index(drop=True)
)

Dataframe sorting on product name - jupiter notebook

1 Answers

`>>> display(out)`