Priority and split and adding a new column in pandas

Question

Guys i have this excel data here as

and i want to convert it to something like this

FACTORYLINECODE is just VBR

SMP NO = STD(priority 1)

USAP NO = USA(priority 2)

OE NO. = OEM(priority 3)

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

What you need is a `melt`, then cleaning of the data. If you need help you must provide reproducible data and code and a fair attempt to solve it by yourself. — mozway, Jul 29 '22 at 21:03
@mozway. i spilt the OEM NO. through str.split ("\n") but after that i think i need to pivot it but i dont know exactly how thats why i posted this question. — Fustavo Gringe, Jul 29 '22 at 21:13

score 2 · Answer 1 · answered Jul 29 '22 at 21:14

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:

FactoryLinecode	Factory Part	Linecode	InterchangePart	Priority
VBR	VB-9400	STD	UF499	1
VBR	VB-9400	USA	REPK504603	2
VBR	VB-9400	OEM	2730126640	3
VBR	VB-9400	OEM	0986221077	3

it's really different (except for plain text)? https://stackoverflow.com/q/73170960/15239951 — Corralien, Jul 29 '22 at 21:32
I could copy the data ;) but yes much too often the pandas question are lacking an attempt — mozway, Jul 29 '22 at 21:33

Priority and split and adding a new column in pandas

1 Answers1