1

It seems a basic pivot operation. I want to do it in python pandas. My dataset looks like:

Date weekno no_visitors_store1 no_visitors_store2 no_visitors_store3 2015/01/01 1 45 34 46 2015/01/02 1 40 32 100 2015/01/03 1 45 30 46 2015/01/04 1 45 11 10

I want to pivot in such a way so that I want to transpose only last three columns so that I can have store as a column and their coreesponding numbers.

Date weekno store no_of_vistors 2015/01/01 1 store1 45 2015/01/01 1 store2 34 2015/01/01 1 store3 46 2015/01/02 1 store1 40 2015/01/02 1 store2 32 2015/01/02 1 store3 100

There is a stack option to the do the above task. But not sure how to use it.

Improve this question

1 Answer 1

Reset to default
1

You can replace first column names and then use melt:

df.columns = df.columns.str.replace('no_visitors_','') print(pd.melt(df, id_vars=['Date','weekno'], value_name='no_of_vistors', var_name='store')) Date weekno store no_of_vistors 0 2015/01/01 1 store1 45 1 2015/01/02 1 store1 40 2 2015/01/03 1 store1 45 3 2015/01/04 1 store1 45 4 2015/01/01 1 store2 34 5 2015/01/02 1 store2 32 6 2015/01/03 1 store2 30 7 2015/01/04 1 store2 11 8 2015/01/01 1 store3 46 9 2015/01/02 1 store3 100 10 2015/01/03 1 store3 46 11 2015/01/04 1 store3 10

Another solution with set_index, stack and reset_index

print(df.set_index(['Date','weekno']) .stack().reset_index(name='no_of_vistors') .rename(columns={'level_2':'store'})) Date weekno store no_of_vistors 0 2015/01/01 1 store1 45 1 2015/01/01 1 store2 34 2 2015/01/01 1 store3 46 3 2015/01/02 1 store1 40 4 2015/01/02 1 store2 32 5 2015/01/02 1 store3 100 6 2015/01/03 1 store1 45 7 2015/01/03 1 store2 30 8 2015/01/03 1 store3 46 9 2015/01/04 1 store1 45 10 2015/01/04 1 store2 11 11 2015/01/04 1 store3 10

Solution with lreshape, but column store is missing:

cols = [col for col in df.columns if 'no_visitors' in col] print(pd.lreshape(df, {'no_of_vistors':cols})) Date weekno no_of_vistors 0 2015/01/01 1 45 1 2015/01/02 1 40 2 2015/01/03 1 45 3 2015/01/04 1 45 4 2015/01/01 1 34 5 2015/01/02 1 32 6 2015/01/03 1 30 7 2015/01/04 1 11 8 2015/01/01 1 46 9 2015/01/02 1 100 10 2015/01/03 1 46 11 2015/01/04 1 10
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@jezreal Thank you very much for your articulate response. Use of melt function is great. So one thing-if in real I have 25 stores-do I have to hard code each of them in lreshape statement?. Can I use something like ['no_visitors_store1'-- 'no_visitors_store2']
No, you canuse list comprehension like cols = [col for col in df.columns if 'no_visitors' in col]
and then print(pd.lreshape(df, {'no_of_vistors':cols})), but I remove this solution, because store name are missing.
OK....but in your very last edit...the output seems little incorrect. Can you please revert to your previous edit which used lreshape.
Do you think different order?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.