organizing data in excel files with python -

i have excel file looks similar this:

**part**  **quantity**  **category**  **board**  part1        4            res         board1  part2        3            cap         board4  part3        2            cap         board3               3            cap         board2               4            cap         board4  part4        3            dio         board2 

ad infinitum. ideally, want can take 5-6 boards listed , create column them, , place respective quantities each individual part, in other words,

**part**  **category**  **board1**  **board2**  **board3**  **board4**  part1        res           4           0           0           0  part2        cap           0           0           0           0   part3        cap           0           3           2           4          part4        dio           0           3           0           0 

i not require other entries filled zeros. can left unfilled cells. keep in mind, start excel files not grouped above, say, part numbers repeated, , every row filled. however, cannot have duplicte parts why use pandas groupby command organize them , remove duplicates. here simple script have @ present:

import pandas pd import numpy np import sys import subprocess   #load csv file df = pd.read_excel('/home/linux/.../raw-inventory.xlsx')  #group part number organize = df.groupby(['part','value','description','qty','board']).sum()  #open .csv organize.to_excel('organizedlist.xlsx')  #opening outputted file subprocess.call(organize, shell=true)  print('done') 

i've been looking around in pandas , xlsxwriter , haven't yet seen way this. maybe on here has idea?

it may better use original .xlsx file well, there have way remove duplicate parts. that's why use groupby pandas function. preserves other data, , eliminates multiple instances beneath 'part' column. suggestions, simple complicated, recommended python packages, etc, appreciated!