• The pivot table takes simple column-wise data as input, and groups the entries into a two-dimensional table that provides a multidimensional summarization of the data.
• Pivot tables as essentially a multidimensional version of groupby aggregation.
• That is, you split-apply-combine, but both the split and the combine happen accross not a one-dimensional index, but across a two-dimensional grid.

Motivating Pivot Tables

• We'll use the database of passengers on the Titanic, available through the Seaborn library.

# In[1]
titanic=sns.load_dataset('titanic')
titanic.head()

# Out[1]
  survived	pclass	   sex	 age	sibsp	parch	   fare	embarked	class	  who	adult_male	deck	embark_town	alive	alone
0	     0	     3	  male	22.0	    1	    0	 7.2500	       S	Third	  man	      True	 NaN	Southampton	   no	False
1	     1	     1	female	38.0	    1	    0	71.2833	       C	First	woman	     False	   C	  Cherbourg	  yes	False
2	     1	     3	female	26.0	    0	    0	 7.9250	       S	Third	woman	     False	 NaN	Southampton	  yes	 True
3	     1	     1	female	35.0	    1	    0	53.1000	       S	First	woman	     False	   C	Southampton	  yes	False
4	     0	     3	  male	35.0	    0	    0	 8.0500	       S	Third	  man	      True	 NaN	Southampton	   no	 True

Pivot Tables by Hand

• We might begin by grouping according to sex, survival status, or combination thereof.

# In[2]
titanic.groupby('sex')[['survived']].mean() # make a DataFrame
"""titanic.groupby('sex')['survived'].mean() return Series"""

# Out[2]
        survived
sex	
female	0.742038
male	0.188908

---

# In[3]
titanic.groupby(['sex','class'])['survived'].aggregate('mean').unstack()

# Out[3]
class	First	    Second	    Third
sex			
female	0.968085	0.921053	0.500000
male	0.368852	0.157407	0.135447

• The above code gives us a better idea of how both sex and class affected survival, but the code is starting to look a bit garbled(=confused).
• The long string of code is not particularly easy to read or use.
• This two-dimensional groupby is common enough that Pandas includes a convenience routine, pivot_table, which succinctly(간단명료한) handles this type of multidimensional aggregation.

Pivot Table Syntax

• Here is the equivalent to the preceding operation using the DataFrame.pivot_table method

# In[4]
titanic.pivot_table('survived',index='sex',columns='class',aggfunc='mean')

# Out[4]
class	First	    Second	    Third
sex			
female	0.968085	0.921053	0.500000
male	0.368852	0.157407	0.135447

Multilevel Pivot Tables

• Just as in a groupby, the grouping in pivot tables can be specified with multiple levels and via a number of options.
• We'll bin the age using the pd.cut function.

# In[5]
age=pd.cut(titanic['age'],[0,18,80])
titanic.pivot_table('survived',['sex',age],'class')

# Out[5]
class	        First	    Second	    Third
sex	   age			
female	(0, 18]	0.909091	1.000000	0.511628
       (18, 80]	0.972973	0.900000	0.423729
male	(0, 18]	0.800000	0.600000	0.215686
       (18, 80]	0.375000	0.071429	0.133663

• We can apply the same strategy when working with the columns as well; let's add info on the fare paid, using pd.qcut to automatically compute quantiles.

# In[6]
fare=pd.qcut(titanic['fare'],2)
titanic.pivot_table('survived',['sex',age],[fare,'class'])

# Out[6]
fare	       (-0.001, 14.454]	                  (14.454, 512.329]
class	       First	  Second	   Third	   First	  Second	   Third
sex	    age						
female	(0, 18]	 NaN	1.000000	0.714286	0.909091	1.000000	0.318182
       (18, 80]	 NaN	0.880000	0.444444	0.972973	0.914286	0.391304
male	(0, 18]	 NaN	0.000000	0.260870	0.800000	0.818182	0.178571
       (18, 80]	 0.0	0.098039	0.125000	0.391304	0.030303	0.192308

• The result is a four-dimensional aggregation with hierarchical indices, shown in a grid demonstrating the relationship between the values.

If you don't know about the pd.cut and pd.qcut, please reference this url :
Difference of pd.cut() and pd.qcut()

Additional Pivot Table Options

• The full call signature of the DataFrame.pivot_table method is as follows.

DataFrame.pivot_table(data, values=None, index=None, columns=None, 
aggfunc='mean', fill_value=None, margins=False, dropna=True, 
margins_name='All', observed=False, sort=True)

• fill_value and dropna have to do with missing data and are fairly straightforward.

---

• The aggfunc keyword controls what type of aggregation is applied, which is mean by default.
• As with groupby, the aggregation specification can be a string representing one of several common choices like sum , mean, count etc.
• Or a function that implements an aggregation like np.sum(), min(), sum() etc.
• Additionally, it can be specified as a dictionary mapping a column to any of the desired options.

# In[7]
titanic.pivot_table(index='sex',columns='class',
aggfunc={'survived':sum,'fare':'mean'})

# Out[7]
        fare	                            survived
class	First	    Second	    Third	    First	Second	Third
sex						
female	106.125798	21.970121	16.118810	91	    70	    72
male	 67.226127	19.741782	12.661633	45	    17	    47

• We've omitted the values keyword here; when specifying a mapping for aggfunc, this is determined automatically.

• At times it's useful to compute totals along each grouping. This can be done via the margins keyword.

# In[8]
titanic.pivot_table('survived',index='sex',columns='class',margins=True)

# Out[8]
class	First	    Second	    Third	    All
sex				
female	0.968085	0.921053	0.500000	0.742038
male	0.368852	0.157407	0.135447	0.188908
All	    0.629630	0.472826	0.242363	0.383838

• The margin label can be specified with the margins_name keyword; it defaults to "All"