pandas pivot table to data frame
59,603
After pivoting, convert the dataframe to records and then back to dataframe:
flattened = pd.DataFrame(pivoted.to_records())
# subject date ('pills', 250) ('pills', 500) ('pills', 1000)
#0 1 10/10/2012 4.0 NaN NaN
#1 1 10/11/2012 4.0 NaN NaN
#2 1 10/12/2012 NaN 2.0 NaN
#3 2 1/6/2014 NaN NaN 1.0
#4 2 1/7/2014 1.0 1.0 NaN
#5 2 1/8/2014 3.0 NaN NaN
You can now "repair" the column names, if you want:
flattened.columns = [hdr.replace("('pills', ", "strength.").replace(")", "") \
for hdr in flattened.columns]
flattened
# subject date strength.250 strength.500 strength.1000
#0 1 10/10/2012 4.0 NaN NaN
#1 1 10/11/2012 4.0 NaN NaN
#2 1 10/12/2012 NaN 2.0 NaN
#3 2 1/6/2014 NaN NaN 1.0
#4 2 1/7/2014 1.0 1.0 NaN
#5 2 1/8/2014 3.0 NaN NaN
It's awkward, but it works.
Author by
alma123
Updated on July 09, 2022Comments
-
alma123 almost 2 years
I have a dataframe (df) that looks like this:
+---------+-------+------------+----------+ | subject | pills | date | strength | +---------+-------+------------+----------+ | 1 | 4 | 10/10/2012 | 250 | | 1 | 4 | 10/11/2012 | 250 | | 1 | 2 | 10/12/2012 | 500 | | 2 | 1 | 1/6/2014 | 1000 | | 2 | 1 | 1/7/2014 | 250 | | 2 | 1 | 1/7/2014 | 500 | | 2 | 3 | 1/8/2014 | 250 | +---------+-------+------------+----------+
When I use reshape in R, I get what I want:
reshape(df, idvar = c("subject","date"), timevar = 'strength', direction = "wide") +---------+------------+--------------+--------------+---------------+ | subject | date | strength.250 | strength.500 | strength.1000 | +---------+------------+--------------+--------------+---------------+ | 1 | 10/10/2012 | 4 | NA | NA | | 1 | 10/11/2012 | 4 | NA | NA | | 1 | 10/12/2012 | NA | 2 | NA | | 2 | 1/6/2014 | NA | NA | 1 | | 2 | 1/7/2014 | 1 | 1 | NA | | 2 | 1/8/2014 | 3 | NA | NA | +---------+------------+--------------+--------------+---------------+
Using pandas:
df.pivot_table(df, index=['subject','date'],columns='strength') +---------+------------+-------+----+-----+ | | | pills | +---------+------------+-------+----+-----+ | | strength | 250 | 500| 1000| +---------+------------+-------+----+-----+ | subject | date | | | | +---------+------------+-------+----+-----+ | 1 | 10/10/2012 | 4 | NA | NA | | | 10/11/2012 | 4 | NA | NA | | | 10/12/2012 | NA | 2 | NA | +---------+------------+-------+----+-----+ | 2 | 1/6/2014 | NA | NA | 1 | | | 1/7/2014 | 1 | 1 | NA | | | 1/8/2014 | 3 | NA | NA | +---------+------------+-------+----+-----+
How do I get exactly the same output as in R with pandas? I only want 1 header.