Pfthb

I have data that looks like this:

df = pd.DataFrame(data=[list('ABCDE'), 
 ['Crude Oil', 'Natural Gas', 'Gasoline', 'Diesel', 'Bitumen'],
 ['Natural Gas', 'Salt water', 'Waste water', 'Motor oil', 'Sour Gas'],
 ['Oil', 'Gas', 'Refined', 'Refined', 'Oil'],
 ['Gas', 'Water', 'Water', 'Oil', 'Gas'],
 list(np.random.randint(10, 100, 5)),
 list(np.random.randint(10, 100, 5))]
 ).T
df.columns =['ID', 'Substance1', 'Substance2', 'Category1', 'Category2', 'Quantity1', 'Quantity2']

 ID Substance1 Substance2 Category1 Category2 Quantity1 Quantity2
0 A Crude Oil Natural Gas Oil Gas 85 14
1 B Natural Gas Salt water Gas Water 95 78
2 C Gasoline Waste water Refined Water 33 25
3 D Diesel Motor oil Refined Oil 49 54
4 E Bitumen Sour Gas Oil Gas 92 86

The Category and Quantity columns refer to the corresponding the Substance columns.

I want to expand the Category columns as a new column for each unique value and have the Quantity value as the cell value. Non-existant categories would be NaN. So the resulting frame would look like this:

 ID Oil Gas Water Refined
0 A 85 14 NaN NaN
1 B NaN 95 78 NaN
2 C NaN NaN 25 33
3 D 54 NaN NaN 49 
4 E 92 86 NaN NaN

I tried .melt() followed by .pivot_table() but for some reason values get duplicated across the new category columns.

You need to use pd.melt then groupby:

np.random.seed(0)

df = pd.DataFrame(data=[list('ABCDE'), 
 ['Crude Oil', 'Natural Gas', 'Gasoline', 'Diesel', 'Bitumen'],
 ['Natural Gas', 'Salt water', 'Waste water', 'Motor oil', 'Sour Gas'],
 ['Oil', 'Gas', 'Refined', 'Refined', 'Oil'],
 ['Gas', 'Water', 'Water', 'Oil', 'Gas'],
 list(np.random.randint(10, 100, 5)),
 list(np.random.randint(10, 100, 5))]
 ).T
df.columns =['ID', 'Substance1', 'Substance2', 'Category1', 'Category2', 'Quantity1', 'Quantity2']

pd.wide_to_long(df,['Substance','Category','Quantity'], 'ID','Num','','.+')
 .groupby(['ID','Category'])['Quantity'].sum()
 .unstack().reset_index()

Output:

Category ID Gas Oil Refined Water
0 A 19.0 54.0 NaN NaN
1 B 57.0 NaN NaN 93.0
2 C NaN NaN 74.0 31.0
3 D NaN 46.0 77.0 NaN
4 E 97.0 77.0 NaN NaN

Here is my semi-manual approach:

>>> df
 ID Substance1 Substance2 Category1 Category2 Quantity1 Quantity2
0 A Crude Oil Natural Gas Oil Gas 74 49
1 B Natural Gas Salt water Gas Water 75 91
2 C Gasoline Waste water Refined Water 24 38
3 D Diesel Motor oil Refined Oil 19 95
4 E Bitumen Sour Gas Oil Gas 50 35
>>> newdf=pd.DataFrame(columns=set(df[['Category1','Category2']].values.flatten()),index=df.index)
>>> for name in newdf: 
 newdf[name]=pd.concat([df[df['Category1']==name]['Quantity1'],df[df['Category2']==name]['Quantity2']])
...
>>> newdf
 Gas Oil Water Refined
0 49 74 NaN NaN
1 75 NaN 91 NaN
2 NaN NaN 38 24
3 NaN 95 NaN 19
4 35 50 NaN NaN