Pandas dataframe pivoting

Question

I have the following pandas DataFrame:

            id               quantity  cost  type
2016-06-18  1700057817       2         2383  A
2016-06-18  1700057817       1         744   B
2016-06-19  1700057817       5         934   A

Here, the dates are the index. I need the table to be pivoted like this:

            id          A-quantity  A-cost  B-quantity  B-cost
2016-06-18  1700057817  2           2383    1           744
2016-06-19  1700057817  5           934     NA          NA

What I've tried so far:

I've tried many usages of pivot. This is as close as I've gotten:

>>> df.pivot(index='id', columns='type')

            quantity   cost               
type         A    B     A     B  
id                              
1700057817   2    1     2383  744

Problems with this:

1. date index is gone
2. I needed a row per date-id combination

I've also gone through several articles on SO and elsewhere, including this one.

Answer 1

You could set_index with append=True followed by unstack and keep the MultiIndex:

df.set_index(['id', 'type'], append=True).unstack()

Or forcibly reformat to what you asked for:

# step-one same as above
df1 = df.set_index(['id', 'type'], append=True).unstack()
# collapse MultiIndex columns into '-' separated string
df1.columns = df1.columns.swaplevel(0, 1).to_series().str.join('-')
# move 'Id' from the index back into dataframe proper
df1 = df1.reset_index(1)
df1