<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Pivot Table on Pandas
In this notebook, we focus on the pivot_table feature of pandas.
Import Modules
Step1:  Import data
Step2:  Pivot Table
In order to build up a pivot table, we must specify an index.
Step3:  Note that the default aggregation function is np.mean. We can specify the aggregation function in the aggfunc parameter, as shown below.
Step4:  For simplicity, we will stick with the default aggregation function. 
We also want to see value, but we need to change it into floats first
Step5:  We could also choose more than one column as index
Step6:  columns provide an additional way to segment the data
Step7:  Note that NaN implies that there is no data here
The default aggfunc is avg but we could use other functions such as np.sum
Step8:  Use margins=True to show the total numbers
Step9:  We should use avg for value but sum for sold, and we do not want to see distance_to_CBD for now
Step10:  Advanced Filtering over Pivot Table
We firstly build a pivot table
Step11:  We can just look at data from one city
Step12:  We can also specify multiple values
Step13:  Note
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
import plotly.plotly as py
import plotly.graph_objs as go
Explanation: Pivot Table on Pandas
In this notebook, we focus on the pivot_table feature of pandas.
Import Modules
End of explanation
df = pd.read_csv('./asset/sydney_housing_market.txt', sep='\t')
df.head()
Explanation: Import data
End of explanation
pd.pivot_table(df, index=['type'])
Explanation: Pivot Table
In order to build up a pivot table, we must specify an index.
End of explanation
pd.pivot_table(df, index=['type'], aggfunc={'distance_to_CBD':np.mean, 'sold':np.sum})
Explanation: Note that the default aggregation function is np.mean. We can specify the aggregation function in the aggfunc parameter, as shown below.
End of explanation
df['value']=df['value'].replace('[\$,]','',regex=True).astype(float)
pd.pivot_table(df, index=['type'])
Explanation: For simplicity, we will stick with the default aggregation function. 
We also want to see value, but we need to change it into floats first
End of explanation
pd.pivot_table(df, index=['type','council'])
Explanation: We could also choose more than one column as index
End of explanation
pd.pivot_table(df, index=['council'], columns=['type'])
Explanation: columns provide an additional way to segment the data
End of explanation
pd.pivot_table(df, index=['type'],aggfunc=np.sum)
Explanation: Note that NaN implies that there is no data here
The default aggfunc is avg but we could use other functions such as np.sum
End of explanation
pd.pivot_table(df, index=['type','council'], aggfunc=np.sum, margins=True)
Explanation: Use margins=True to show the total numbers
End of explanation
pd.pivot_table(df, index=['council','suburb'], 
               columns=['type'], 
               values=['sold', 'value'], 
               aggfunc={'sold':np.sum, 'value':np.mean}, 
               margins=True)
Explanation: We should use avg for value but sum for sold, and we do not want to see distance_to_CBD for now
End of explanation
table = pd.pivot_table(df, index=['council'], columns=['type'], values=['sold'], aggfunc=np.sum, margins=True)
table
Explanation: Advanced Filtering over Pivot Table
We firstly build a pivot table
End of explanation
table.query('council==["Randwick"]')
Explanation: We can just look at data from one city
End of explanation
table.query('council==["Rockdale","Lane Cove"]')
Explanation: We can also specify multiple values
End of explanation
plot_table = table[:-1]  # get rid of ALL
plot_table.sold.house
table.sold.house
plot_table.index
trace1 = go.Bar(
    x=plot_table.index,
    y=plot_table.sold.house,
    name='House'
)
trace2 = go.Bar(
    x=plot_table.index,
    y=plot_table.sold.unit,
    name='Unit'
)
data = [trace1, trace2]
layout = go.Layout(
    barmode='group'
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='pandas-notebook-plot2')
Explanation: Note: we can not query a pivot table from the columns. So you should put item in index if you want to query it.
Plot the data using plot.ly
Before you can execute the code below, you need to install plotly, register a free account with them, and create a profile that contains your own API key. See https://plot.ly/python/getting-started/
End of explanation
<END_TASK>