Python pandas pivot/stack operation

Question

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.

jezrael · Accepted Answer · 2017-01-05 13:38:53Z

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

edited Jan 5, 2017 at 13:38

answered Jan 5, 2017 at 13:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2906657 Over a year ago

@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']

jezrael Over a year ago

No, you canuse list comprehension like cols = [col for col in df.columns if 'no_visitors' in col]

jezrael Over a year ago

and then print(pd.lreshape(df, {'no_of_vistors':cols})), but I remove this solution, because store name are missing.

user2906657 Over a year ago

OK....but in your very last edit...the output seems little incorrect. Can you please revert to your previous edit which used lreshape.

jezrael Over a year ago

Do you think different order?

|

Collectives™ on Stack Overflow

Python pandas pivot/stack operation

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related