<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 2. Refine the Data
"Data is messy"
Missing e.g. Check for missing or incomplete data
Quality e.g. Check for duplicates, accuracy, unusual data
Parse e.g. extract year and month from date
Convert e.g. free text to coded value
Derive e.g. gender from title
Calculate e.g. percentages, proportion
Remove e.g. remove redundant data
Merge e.g. first and surname for full name
Aggregate e.g. rollup by year, cluster by area
Filter e.g. exclude based on location
Sample e.g. extract a representative data
Summary e.g. show summary stats like mean
Step1:  2.1 Missing Data
By “missing” data we simply mean null or “not present for whatever reason”. Lets see if we can find the missing data in our data set either because it exists and was not collected or it never existed
Step2:  Pandas will represent missing value by NaN
What can we do this with missing value?
- Drop these rows / columns? Use .dropna(how='any')
- Fill with a dummy value? Use .fillna(value=dummy)
- Impute the cell with the most recent value? Use .fillna(method='ffill')
- Interpolate the amount in a linear fashion? Use .interpolate()
We use the inplace = True operator to avoid making a copy of the dataframe and changing the dataframe itself
Step3:  Exercise
Fill the missing value with a backward fill.
Fill the missing values with the mean for the column.
2.2 Quality of the Data
Lets check for completeness.
Say, do we have data on each date for all the 51 states?
Step4:  Lets check the dates and see if they are all continuous
Step5:  Exercise Show the list of dates that were missing. Hint Leverage df2. Both df1 and df2 have date as index
2.3  Parse the Data
Lets see if we can get the year, month, week and weekdays from the date. Pandas has got good built in functionality for timeseries data using the DatetimeIndex method
Step6:  2.4 Aggregate the Data
To aggregate, we typically use the “group by” function, which involves the following steps
Splitting the data into groups based on some criteria
Applying a function to each group independently
Combining the results into a data structure
Step7:  Pivot Table
Step8:  Exercise Get a cross tabulation
Step9:  Exercise Express median rent as a proportion of california's median rent (Compute it as a new column)
2.6 Merge the Data
Lets merge the demographic dataset with the price dataset
Step10:  What happened? Why is there no data in the dataframe?
Step11:  2.7 Filter the Data
Lets start by filtering the data 
- by location
- by Year
- by location & Year
Step12:  Exercise Find the % of hispanic population for the state with max white population (use df_demo)
2.8 Summarise the Data
We can use the describe function to get the summary stats for each column in the data frame
Step13:  We can also use convenience functions like sum(), count(), mean() etc. to calculate these
Step14:  2.9 Sample the Data
Step15:  2.10 Quirks in Pandas
Step16:  But changes when you copy
Step17:  Fix the issue
<ASSISTANT_TASK:>
Python Code:
# Load the libraries
import numpy as np
import pandas as pd
# Load the data again!
df = pd.read_csv("data/Weed_Price.csv", parse_dates=[-1])
Explanation: 2. Refine the Data
"Data is messy"
Missing e.g. Check for missing or incomplete data
Quality e.g. Check for duplicates, accuracy, unusual data
Parse e.g. extract year and month from date
Convert e.g. free text to coded value
Derive e.g. gender from title
Calculate e.g. percentages, proportion
Remove e.g. remove redundant data
Merge e.g. first and surname for full name
Aggregate e.g. rollup by year, cluster by area
Filter e.g. exclude based on location
Sample e.g. extract a representative data
Summary e.g. show summary stats like mean
End of explanation
# Lets start the count to seeing about missing data
df.count()
df['LowQ'].isnull()
# We can see the bottom rows which have NaN values
df.tail()
Explanation: 2.1 Missing Data
By “missing” data we simply mean null or “not present for whatever reason”. Lets see if we can find the missing data in our data set either because it exists and was not collected or it never existed
End of explanation
# Lets sort this data frame by State and Date
df.sort(columns=['State','date'], inplace=True)
# Lets fill the missing value with last available value
df.fillna(method = "ffill", inplace=True)
df.count()
Explanation: Pandas will represent missing value by NaN
What can we do this with missing value?
- Drop these rows / columns? Use .dropna(how='any')
- Fill with a dummy value? Use .fillna(value=dummy)
- Impute the cell with the most recent value? Use .fillna(method='ffill')
- Interpolate the amount in a linear fashion? Use .interpolate()
We use the inplace = True operator to avoid making a copy of the dataframe and changing the dataframe itself
End of explanation
df["State"].value_counts()
Explanation: Exercise
Fill the missing value with a backward fill.
Fill the missing values with the mean for the column.
2.2 Quality of the Data
Lets check for completeness.
Say, do we have data on each date for all the 51 states?
End of explanation
df1 = df[df.State=='California'].copy()
df2 = df[df.State=='California'].copy()
df1.shape
print "Earliest Date:",df1.date.min(), "\n", "Latest Date:",df1.date.max(), "\n",  \
"Number of days in between them:", df1.date.max() - df1.date.min() 
df1.groupby(['date']).size()
df1.set_index("date", inplace=True)
df2.set_index("date", inplace=True)
idx = pd.date_range(df1.index.min(), df1.index.max())
idx
df1 = df1.reindex(idx, fill_value=0)
df1.shape
Explanation: Lets check the dates and see if they are all continuous
End of explanation
df['year'] = pd.DatetimeIndex(df['date']).year
df['month'] = pd.DatetimeIndex(df['date']).month
df['week'] = pd.DatetimeIndex(df['date']).week
df['weekday'] = pd.DatetimeIndex(df['date']).weekday
df.dtypes
df.tail()
df['year'].value_counts()
df['month'].value_counts()
df["weekday"].value_counts()
Explanation: Exercise Show the list of dates that were missing. Hint Leverage df2. Both df1 and df2 have date as index
2.3  Parse the Data
Lets see if we can get the year, month, week and weekdays from the date. Pandas has got good built in functionality for timeseries data using the DatetimeIndex method
End of explanation
df_mean = df.groupby("State", as_index=False).mean()
df_mean.shape
df_mean.head()
Explanation: 2.4 Aggregate the Data
To aggregate, we typically use the “group by” function, which involves the following steps
Splitting the data into groups based on some criteria
Applying a function to each group independently
Combining the results into a data structure
End of explanation
df.head()
pd.pivot_table(df, values='HighQ', index=['State'], columns=["weekday"] )
Explanation: Pivot Table
End of explanation
df_demo = pd.read_csv("data/Demographics_State.csv")
df_demo.head()
df_demo["percent_other"] = 100 - df_demo["percent_white"] - df_demo["percent_black"] - df_demo["percent_asian"] - df_demo["percent_hispanic"]
df_demo.head()
Explanation: Exercise Get a cross tabulation: for each state, for each month, get the prices for the weekday as shown in the output 
2.5 Derive the Data
Lets us load the demographic dataset and create a new column for others in the population
End of explanation
# Let us change the column name region to State
df_demo = df_demo.rename(columns={'region':'State'})
df_demo.head()
# We can now merge Demographic and Price mean data into one single data frame
df_merge = pd.merge(df_mean, df_demo, how='inner', on='State')
df_merge.head()
Explanation: Exercise Express median rent as a proportion of california's median rent (Compute it as a new column)
2.6 Merge the Data
Lets merge the demographic dataset with the price dataset
End of explanation
# Change the State in df_mean to lower case
df_mean['State'] = df_mean['State'].str.lower()
df_mean.head()
# We can now merge Demographic and Price mean data into one single data frame
df_merge = pd.merge(df_mean, df_demo, how='inner', on='State')
df_merge.head()
Explanation: What happened? Why is there no data in the dataframe?
End of explanation
# Filter data for location California
df_cal = df[df["State"] == "California"]
df_cal.shape
# Filter data for year
df_2014 = df[df["year"] == 2014]
df_2014.shape
df_cal_2014 = df[(df["year"] == 2014) & (df["State"] == "California")]
df_cal_2014.shape
df_cal_2014.head()
Explanation: 2.7 Filter the Data
Lets start by filtering the data 
- by location
- by Year
- by location & Year
End of explanation
df.describe()
Explanation: Exercise Find the % of hispanic population for the state with max white population (use df_demo)
2.8 Summarise the Data
We can use the describe function to get the summary stats for each column in the data frame
End of explanation
df.HighQ.mean()
# Lets do this the hard way
df.HighQ.sum()
df.HighQ.count()
df.HighQ.sum()/df.HighQ.count()
df.HighQ.median()
Explanation: We can also use convenience functions like sum(), count(), mean() etc. to calculate these
End of explanation
?df.sample
df_ca_sample = df[df.State=='California'].sample(n = 50, replace = True, random_state=123)
df_ca_sample.duplicated()
df_ca_sample.loc[8572]
Explanation: 2.9 Sample the Data
End of explanation
df_ca_sample.iat[0, 0] = "Cal"
df_ca_sample.head()
Explanation: 2.10 Quirks in Pandas
End of explanation
df_ca_sample2 = df_ca_sample
df_ca_sample2.iat[0, 0] = "CA"
df_ca_sample2.head()
df_ca_sample.head()
Explanation: But changes when you copy
End of explanation
df_ca_sample3 = df_ca_sample2.copy()
df_ca_sample3.head()
df_ca_sample3.iat[0, 0] = "CALIFORNIA"
df_ca_sample3.head()
df_ca_sample2.head()
Explanation: Fix the issue
End of explanation
<END_TASK>