Guys i have this excel data here as

and i want to convert it to something like this

ignore PG order

i achived this through power query. im new to pandas and python and would love to learn how would i achive this with pandas. i thougt of using split at OE NO. but stuck there

CodePudding user response：

The most important part of your problem is to clean your data before melt:

d = {'V-Bright NO.': 'Factory Part', 'SMP NO.': 'STD', 'USAP NO.': 'USA', 'OE NO.': 'OEM'}
p = {'STD': 1, 'USA': 2, 'OEM': 3}
c = ['FactoryLinecode', 'Factory Part', 'Linecode', 'InterchangePart', 'Priority']

out = (df.rename(columns=d)[d.values()]
         .melt(['Factory Part'], var_name='Linecode', value_name='InterchangePart')
         .assign(InterchangePart=lambda x: x['InterchangePart'].str.split('\n'),
                 Priority=lambda x: x['Linecode'].map(p), FactoryLinecode='VBR')
         .explode('InterchangePart')[c])

Output: