CODEWITHSUNDEEP
pythonpandas
user2906657
user2906657

Reputation: 541

Python pandas pivot/stack operation

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.

Upvotes: 1

Views: 86

Answers (1)

jezrael
jezrael

Reputation: 863791

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

Upvotes: 1

Related Questions