pivoting and pivot table¶

import pandas as pd

sales = pd.DataFrame({'eggs': {0: 47, 1: 110, 2: 221, 3: 77, 4: 132, 5: 205},
 'month': {0: 1, 1: 2, 2: 1, 3: 2, 4: 1, 5: 2},
 'salt': {0: 12.0, 1: 50.0, 2: 89.0, 3: 87.0, 4: None, 5: 60.0},
 'spam': {0: 17, 1: 31, 2: 72, 3: 20, 4: 52, 5: 55},
 'state': {0: 'CA', 1: 'CA', 2: 'NY', 3: 'NY', 4: 'TX', 5: 'TX'}})

sales

sales.pivot('month','state','spam')

sales.pivot(index = 'month',
            columns = 'state',
            values = 'spam')

sales.pivot(index = 'month',
            columns = 'state')

sales.pivot('state','month')

Pivoting a single variable¶

### df.pivot()
- Suppose you started a blog for a band, and you would like to log how many visitors you have had, and how many signed-up for your newsletter.

users = pd.DataFrame({'city': {0: 'Austin', 1: 'Dallas', 2: 'Austin', 3: 'Dallas'},
 'signups': {0: 7, 1: 12, 2: 3, 3: 5},
 'visitors': {0: 139, 1: 237, 2: 326, 3: 456},
 'weekday': {0: 'Sun', 1: 'Sun', 2: 'Mon', 3: 'Mon'}})

users

users.pivot('weekday','city','visitors')

Pivoting all variables

users.pivot(index='weekday', columns='city')

Stacking & unstacking DataFrames¶

### stacking and unstacking DataFrames
- ### with a MultiIndex

users

users2  = users.set_index(['city','weekday']).sort_index()
users2

### Pay attention to the index, and notice that the index levels are ['city', 'weekday']. So 'weekday' - the second entry - has position 1.
- ### This position is what corresponds to the level parameter in .stack() and .unstack() calls.
  - ### Alternatively, you can specify 'weekday' as the level instead of its position.

users2.unstack(level = 'weekday')

users2.unstack()

users3 = users2.unstack(1)
users3

stack¶

users3

users3.index

Index([u'Austin', u'Dallas'], dtype='object', name=u'city')

users3.stack()

Stack 2¶

### Your job in this exercise is to unstack and then stack the 'city' level, as you did previously for 'weekday'. Note that you won't get the same DataFrame.

users2

users2.unstack(level = 0)

users2.unstack(level = 'city')

users2.unstack(level = 'city').stack()

__¶

users.unstack()

city      0    Austin
          1    Dallas
          2    Austin
          3    Dallas
signups   0         7
          1        12
          2         3
          3         5
visitors  0       139
          1       237
          2       326
          3       456
weekday   0       Sun
          1       Sun
          2       Mon
          3       Mon
dtype: object

pd.melt(users)

Creating a multi-level index¶

users2 = users.set_index(['city','weekday'])
users2

users2.unstack('weekday')

users2.unstack('city')

users2

Unstacking a multi-index¶

users2.unstack(1)

users2.unstack(level = 0)

Stacking DataFrames¶

users2

users2.stack()

city    weekday          
Austin  Sun      signups       7
                 visitors    139
Dallas  Sun      signups      12
                 visitors    237
Austin  Mon      signups       3
                 visitors    326
Dallas  Mon      signups       5
                 visitors    456
dtype: int64

Restoring the index order¶

Swapping levels¶

### use .swaplevel(0, 1) to flip the index levels.

users2

users2.swaplevel()

users2.swaplevel(0,1)

users2.swaplevel().sort_index()

Tidy Data¶

Melting DataFrames¶

the goal of melting is to restore a pivoted DataFrame to its original form, or to change it from a wide shape to a long shape.¶

users

### You can explicitly specify the columns that should remain in the reshaped DataFrame with id_vars, and list which columns to convert into values with value_vars.
- ### if you don't pass a name to the values in pd.melt(), you will lose the name of your variable. You can fix this by using the value_name keyword argument.

grouped = users.pivot('city','weekday','visitors')
grouped

restore grouped DataFrame¶

### pd.melt()

grouped

reset index¶

test = grouped.copy()
test

test.index.name = 'cities'
test

test.reset_index()

manually reset index¶

grouped['city']=grouped.index
grouped

grouped

pd.melt(grouped)

Specifying id_vars¶

pd.melt(grouped,'city')

pd.melt(grouped,'Sun')

pd.melt(grouped,id_vars='Mon')

Specifying value_name¶

pd.melt(grouped, id_vars=['city'],
 var_name='whichdayofweek', value_name='visitors')

Obtaining key-value pairs with melt()¶

### Sometimes, all you need is some key-value pairs, and the context does not matter. If said context is in the index, you can easily obtain what you want.
- ### For example, in the users DataFrame, the visitors and signups columns lend themselves well to being represented as key-value pairs.
  - #### So if you created a hierarchical index with 'city' and 'weekday' columns as the index, you can easily extract key-value pairs for the 'visitors' and 'signups' columns by melting users and specifying col_level=0.

users

# Set the new index: users_idx
users_idx = users.set_index(['city', 'weekday'])
users_idx

pd.melt(users_idx, col_level=0)

users

pd.melt(users)

Pivot tables¶

### df.pivot() requires Unique index-column pairs to identify values in the new table
- ## df.pivot_table()
  - ### reshape df by summarizing
    - ### can use aggregation func
      - #### default aggregation is average
        
        ### keyword, aggfunc e.g. df.pivot_table(..., aggfunc = 'count')

users

users.pivot_table(index='weekday', columns='city')

users.pivot('weekday','city')

specify aggregation func¶

users.pivot_table(index='weekday', columns='city',aggfunc='count')

can only pass index, values will be averaged¶

#### You can also use aggregation functions with in a pivot table by specifying the aggfunc parameter. In this exercise, you will practice using the 'count' and len aggregation functions - which produce the same result - on the users DataFrame.

users.pivot_table(index='weekday')

users.pivot_table(index='weekday',aggfunc='count')

users.pivot_table(index='weekday',aggfunc=len)

Using margins in pivot tables¶

### Sometimes it's useful to add totals in the margins of a pivot table. You can do this with the argument margins=True
- ### using margins in a pivot table along with a new aggregation function: sum.

users

# Create the DataFrame with the appropriate pivot table: signups_and_visitors
users.pivot_table(index='weekday', aggfunc=sum)

# Add in the margins: signups_and_visitors_total 
users.pivot_table(index='weekday', aggfunc=sum, margins=True)

	eggs	month	salt	spam	state
0	47	1	12.0	17	CA
1	110	2	50.0	31	CA
2	221	1	89.0	72	NY
3	77	2	87.0	20	NY
4	132	1	NaN	52	TX
5	205	2	60.0	55	TX

	eggs			salt			spam
state	CA	NY	TX	CA	NY	TX	CA	NY	TX
month
1	47	221	132	12.0	89.0	NaN	17	72	52
2	110	77	205	50.0	87.0	60.0	31	20	55

		signups	visitors
city	weekday
Austin	Mon	3	326
Austin	Sun	7	139
Dallas	Mon	5	456
Dallas	Sun	12	237

	variable	value
0	signups	7
1	signups	12
2	signups	3
3	signups	5
4	visitors	139
5	visitors	237
6	visitors	326
7	visitors	456