Pandas dataframe pivoting
th3an0maly
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:
dateindex is gone- I needed a row per
date-idcombination
I've also gone through several articles on SO and elsewhere, including this one.
piRSquared
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
Related Related
- 1
Pivoting Dataframe with Pandas
- 2
Pivoting pandas dataframe by rank on id
- 3
Pandas Dataframe Stacking versus Pivoting
- 4
Resampling, grouping, pivoting a pandas dataframe
- 5
Pivoting a pandas dataframe with duplicate index values
- 6
Pivoting a Pandas dataframe with a gapless daterange as index
- 7
pivoting pandas dataframe into prefixed cols, not a MultiIndex
- 8
Pivoting a pandas dataframe to generate a (seaborn) heatmap
- 9
How to efficiently columnize (=pivoting) pandas DataFrame (with groupby)?
- 10
Pivoting pandas DataFrame -- AssertionError: Index length did not match values
- 11
Pivoting a Pandas Dataframe containing strings - 'No numeric types to aggregate' error
- 12
Pivoting data in pandas
- 13
Pandas: Pivoting and plotting workflow
- 14
Pandas pivoting with boolean
- 15
dataframe slicing and pivoting then into multiple dataframes
- 16
Multi-index pivoting in Pandas
- 17
Data wrangling with Python Pandas and pivoting
- 18
Pivoting tables with duplicated data in pandas
- 19
Pandas: Pivoting with multi-index data
- 20
Pivoting (or reshaping) table in pandas into hierarchical columns
- 21
Un-pivoting/Stacking a pivot table with Python Pandas
- 22
pivoting pandas df - turn column values into column names
- 23
Simple pivoting of DataFrame in python should work. Is this a bug when passing 'values' argument? If so, how to fix it?
- 24
Simple pivoting of DataFrame in python should work. Is this a bug when passing 'values' argument? If so, how to fix it?
- 25
Pivoting in sql
- 26
How to pivoting
- 27
Pivoting Integer
- 28
Pandas Dataframe
- 29
Pandas Dataframe
Comments