0

I'm using pandas dataframes and have two major things to do. I don't know which is the best order but I need to turn Food column into multiple columns titled pizza, burger, and hot dog as column names (pretty much pivot) and then unpivot columns pd.iloc[:,2:] so that the first date column through X-number of date columns is turned into a single column.

Steps taken so far are to split the df into 3 separate dfs (one is just Name, two is Food and pivot, three is unstack and then reset index on each). I don't think this is the correct approach. I just need the basic workflow to collapse into a single large df (relational dfs won't work for this specific task).

SAMPLE DATA:

Name Food 1/1/2018 2/1/2018 3/1/2018 4/1/2018 Mike pizza 1 0 1 1 Mike burger 0 3 0 0 Mike hot dog 0 0 0 0 Bob pizza 0 1 0 0 Bob burger 2 0 2 2 Bob hot dog 1.5 0 1.5 1.5

WHAT I NEED:

Name pizza burger hot dog Mike 1/1/2018 1 0 0 Mike 2/1/2018 0 3 0 Mike 3/1/2018 1 0 0 Mike 4/1/2018 1 0 0 Bob 1/1/2018 0 2 1.5 Bob 2/1/2018 1 0 0 Bob 3/1/2018 0 2 1.5 Bob 4/1/2018 0 2 1.5
Improve this question

2 Answers 2

Reset to default
1

Try like this:

df.set_index(['Name', 'Food']).stack().unstack('Food') Food burger hot dog pizza Name Bob 1/1/2018 2.0 1.5 0.0 2/1/2018 0.0 0.0 1.0 3/1/2018 2.0 1.5 0.0 4/1/2018 2.0 1.5 0.0 Mike 1/1/2018 0.0 0.0 1.0 2/1/2018 3.0 0.0 0.0 3/1/2018 0.0 0.0 1.0 4/1/2018 0.0 0.0 1.0

If formatting is an issue, just reset the index and then rename your columns to appropriate names:

df.set_index(['Name', 'Food']).stack().unstack('Food').reset_index().rename(columns={'level_1':'date'}) Food Name date burger hot dog pizza 0 Bob 1/1/2018 2.0 1.5 0.0 1 Bob 2/1/2018 0.0 0.0 1.0 2 Bob 3/1/2018 2.0 1.5 0.0 3 Bob 4/1/2018 2.0 1.5 0.0 4 Mike 1/1/2018 0.0 0.0 1.0 5 Mike 2/1/2018 3.0 0.0 0.0 6 Mike 3/1/2018 0.0 0.0 1.0 7 Mike 4/1/2018 0.0 0.0 1.0
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Great, this worked. Quick question, if I had other columns in the original data set that I wanted to preserve as columns as they originally are, is that an easy parameter fix?
IIUC, just stick them in the list you pass to set_index
0

Since you mention 'unpivot' (melt)

df.melt(['Name','Food']).pivot_table(index=['Name','variable'],columns='Food',values='value') Out[752]: Food burger hotdog pizza Name variable Bob 1/1/2018 2.0 1.5 0.0 2/1/2018 0.0 0.0 1.0 3/1/2018 2.0 1.5 0.0 4/1/2018 2.0 1.5 0.0 Mike 1/1/2018 0.0 0.0 1.0 2/1/2018 3.0 0.0 0.0 3/1/2018 0.0 0.0 1.0 4/1/2018 0.0 0.0 1.0
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.