<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Pandas and SQL
Pandas is a widely used python package for data analysis. We will mainly focus on pandas.DataFrame. Informally, you can think of a dataframe as an advanced relational table (or a spreadsheet in an Excel file). There, you can easily perform many useful tasks quickly, such as aggregate analysis, and data enrichment or selection. 
In this notebook, we focus on supporting common SQL operations on dataframes. More documentation can be found at 
* "Comparison with SQL"
Step1:  Import data
We can easily create a DataFrame from a CSV or a tab-delimited file. You can creata a dataframe object in other ways, e.g., from multiple Series or from a dictionary. 
Then we can 
* use shape to see the number of rows and columns
* use head(x) or tail(x) to view the first or last x rows of the dataFrame. The default value of x is 5. 
* pandas is also smart in that it will intelligently print part of the dataframe content if it is too large.
Step2:  SELECT
Columns can be identified by its index (0-based) or its name.
Step3:  WHERE
Step4:  When dealing with multiple conditions, pandas uses &amp; and |. Each condition must be bracketed.
Step5:  Under the hood, the conditions determines a Boolean array.
Step6:  GROUP BY
In pandas, we uses groupby() to split a dataset into groups; we can apply some function (e.g., aggregation) and combine the groups together.
We use size() to return the number of rows of each group (like COUNT in SQL)
Step7:  We use agg() to apply multiple functions at once, and pass a list of columns to groupby() to grouping multiple columns
Step8:  JOIN
Step9:  We use pd.merge() to join two DataFrames, where you can specify the join key
Step10:  We can also specify the join type
Step11:  UNION
Step12:  use pd.concat() to union two tables without removing duplicates (i.e., UNION ALL in SQL)
Step13:  use drop_duplicates() to remove duplicate rows
Step14:  Adding Columns
Step15:  Pivoting and Visualization
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
import plotly.plotly as py
import plotly.graph_objs as go
Explanation: Pandas and SQL
Pandas is a widely used python package for data analysis. We will mainly focus on pandas.DataFrame. Informally, you can think of a dataframe as an advanced relational table (or a spreadsheet in an Excel file). There, you can easily perform many useful tasks quickly, such as aggregate analysis, and data enrichment or selection. 
In this notebook, we focus on supporting common SQL operations on dataframes. More documentation can be found at 
* "Comparison with SQL": http://pandas.pydata.org/pandas-docs/stable/comparison_with_sql.html
* Dataframe/Pandas documentation: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html
We also use the plotly plotting library. You need to install it, register an account, and perform initialization (c.f., https://plot.ly/python/getting-started/), before the following code can work. Alternatively, just comment out plotly import below and the code that generates the plot. 
Import Modules
End of explanation
df = pd.read_csv('./asset/lecture_data.txt', sep='\t')  # to read an excel file, use read_excel()
df.head()
df
df.describe()
Explanation: Import data
We can easily create a DataFrame from a CSV or a tab-delimited file. You can creata a dataframe object in other ways, e.g., from multiple Series or from a dictionary. 
Then we can 
* use shape to see the number of rows and columns
* use head(x) or tail(x) to view the first or last x rows of the dataFrame. The default value of x is 5. 
* pandas is also smart in that it will intelligently print part of the dataframe content if it is too large.
End of explanation
df.location.head()
# show location and dollars_sold
df[[0, 3]].head()
# df[['location','dollars_sold']].head() # also okay
Explanation: SELECT
Columns can be identified by its index (0-based) or its name.
End of explanation
df[df['location'] == 'Vancouver'].head()
Explanation: WHERE
End of explanation
# location at Vancouver and dollars_sold more than 500, except the 1st quarter
df[(df['location'] == 'Vancouver') & (df['time'] != 'Q1') & (df['dollars_sold'] > 500)]
Explanation: When dealing with multiple conditions, pandas uses &amp; and |. Each condition must be bracketed.
End of explanation
df['time'] == 'Q1'
Explanation: Under the hood, the conditions determines a Boolean array.
End of explanation
df.groupby('location').size()
Explanation: GROUP BY
In pandas, we uses groupby() to split a dataset into groups; we can apply some function (e.g., aggregation) and combine the groups together.
We use size() to return the number of rows of each group (like COUNT in SQL)
End of explanation
df.groupby(['location','item']).agg({'dollars_sold': [np.mean,np.sum]})
Explanation: We use agg() to apply multiple functions at once, and pass a list of columns to groupby() to grouping multiple columns
End of explanation
# import another table about the population of each city
df2 = pd.read_csv('./asset/population_1.txt', sep='\t')
df2.head()
Explanation: JOIN
End of explanation
pd.merge(df,df2,on='location').head()
Explanation: We use pd.merge() to join two DataFrames, where you can specify the join key
End of explanation
pd.merge(df,df2,on='location',how='left').tail()
Explanation: We can also specify the join type
End of explanation
# import another part of the population table
df3=pd.read_csv('./asset/population_2.txt', sep='\t')
df3
Explanation: UNION
End of explanation
pd.concat([df2,df3])
Explanation: use pd.concat() to union two tables without removing duplicates (i.e., UNION ALL in SQL)
End of explanation
pd.concat([df2,df3]).drop_duplicates()
Explanation: use drop_duplicates() to remove duplicate rows
End of explanation
df_city = pd.concat([df2,df3]).drop_duplicates()
df_city['big city'] = pd.Series(df_city['population'] > 1000000, index=df_city.index)
df_city
Explanation: Adding Columns
End of explanation
table = pd.pivot_table(df, index = 'location', columns = 'time', aggfunc=np.sum)
table
pd.pivot_table(df, index = ['location', 'item'], columns = 'time', aggfunc=np.sum, margins=True)
trace1 = go.Bar(
    x=table.index,
    y=table.dollars_sold.Q1,
    name='Q1'
)
trace2 = go.Bar(
    x=table.index,
    y=table.dollars_sold.Q2,
    name='Q2'
)
trace3 = go.Bar(
    x=table.index,
    y=table.dollars_sold.Q3,
    name='Q3'
)
trace4 = go.Bar(
    x=table.index,
    y=table.dollars_sold.Q4,
    name='Q4'
)
data = [trace1, trace2, trace3, trace4]
layout = go.Layout(
    barmode='group'
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='sales-plot')
Explanation: Pivoting and Visualization
End of explanation
<END_TASK>