<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Processing time with pandas
We've touched a little bit on time so far - mostly how tragic it is to parse - but pandas can do some neat things with it once you figure out how it works.
Let's open up some data from the Census bureau - we're going to use New Home Sales. The data is formatted... oddly, so I've done the importing and joining for you below.
Step1:  Changing the index to the datetime
Normally the index of the column is just a number.
Step2:  It's the column on the far left - 0, 1, 2, 3, 4... boring and useless! If we replace the index with the datetime, though, we can start to have some fun
Step3:  Selecting specific(-ish) dates via the index
Now that our index is a datetime, we can select date ranges much more easily.
Step4:  List slices with datetimes
We can also use list slicing with datetimes!
Just for review, you can use 
Step5:  Instead of using boring ol' numbers, we can use dates instead.
Step6:  Info on our time series
If you try to .plot, pandas will automatically use the index (the date) as the x axis for you.
Step7:  Hmmm, looks like something might have happened at some point. Maybe we want to see some numbers instead of a graph? To do aggregate statistics on time series in pandas we use a method called .resample(), and we're going to tell it to group the data by year.
Step8:  That still looks like too much data! What about every decade?
Step9:  Cyclical data
It seems like winter might be a time where not very many houses are sold. Let's see if that's true!
Step10:  More details
You can also use max and min and all of your other aggregate friends with .resample. For example, what's the largest number of houses hold in a given year?
<ASSISTANT_TASK:>
Python Code:
data_df = pd.read_excel("RESSALES-mf.xlsx", sheetname='data')
data_df.head()
categories_df = pd.read_excel("RESSALES-mf.xlsx", sheetname='categories')
data_types_df = pd.read_excel("RESSALES-mf.xlsx", sheetname='data_types')
error_types_df = pd.read_excel("RESSALES-mf.xlsx", sheetname='error_types')
geo_levels_df = pd.read_excel("RESSALES-mf.xlsx", sheetname='geo_levels')
periods_df = pd.read_excel("RESSALES-mf.xlsx", sheetname='periods')
categories_df.head(2)
# it auto-merges cat_idx in our original dataframe with cat_idx in categories_df
# it auto-merges dt_idx in our original dataframe with dt_idx in data_types_df
# it auto-merges geo_idx in our original dataframe with geo_idx in geo_levels_df
# it auto-merges per_idx in our original dataframe with per_idx in periods_df
df = data_df.merge(categories_df).merge(data_types_df).merge(geo_levels_df).merge(periods_df)
# We only want to look at the total number of homes sold across entire the united states
df = df[(df['cat_code'] == 'SOLD') & (df['geo_code'] == 'US') & (df['dt_code'] == 'TOTAL')]
# We don't merge error_types_df because all of the errors are the same
df['et_idx'].value_counts()
df.head(2)
# Now let's remove the join columns to keep things clean
df = df.drop(['per_idx', 'cat_idx', 'dt_idx', 'et_idx', 'geo_idx'], axis=1)
df.head()
# At least we can see 'per_name' (period name) is already a datetime!
df.info()
Explanation: Processing time with pandas
We've touched a little bit on time so far - mostly how tragic it is to parse - but pandas can do some neat things with it once you figure out how it works.
Let's open up some data from the Census bureau - we're going to use New Home Sales. The data is formatted... oddly, so I've done the importing and joining for you below.
End of explanation
df.head(3)
Explanation: Changing the index to the datetime
Normally the index of the column is just a number.
End of explanation
# First we move it over into the index column
df.index = df['per_name']
df.head(2)
# Then we delete the per_name column because we don't need it any more...
del df['per_name']
df.head(2)
Explanation: It's the column on the far left - 0, 1, 2, 3, 4... boring and useless! If we replace the index with the datetime, though, we can start to have some fun
End of explanation
# Everything in March, 1963
df['1963-3']
# Everything in 2010
df['2010']
Explanation: Selecting specific(-ish) dates via the index
Now that our index is a datetime, we can select date ranges much more easily.
End of explanation
# Make our list of fruits
ranked_fruits = ('banana', 'orange', 'apple', 'blueberries', 'strawberries')
# Start from the beginning, get the first two
ranked_fruits[:2]
# Start from two, get up until the fourth element
ranked_fruits[2:4]
# Starting from the third element, get all the rest
ranked_fruits[3:]
Explanation: List slices with datetimes
We can also use list slicing with datetimes!
Just for review, you can use : to only select certain parts of a list:
End of explanation
# Everything after 2001
df["2001":]
# Everything between June 1990 and March 1995
df["1990-06":"1995-03"]
Explanation: Instead of using boring ol' numbers, we can use dates instead.
End of explanation
df.plot(y='val')
Explanation: Info on our time series
If you try to .plot, pandas will automatically use the index (the date) as the x axis for you.
End of explanation
# http://stackoverflow.com/a/17001474 gives us a list of what we can pass to 'resample'
df.resample('A').median()
Explanation: Hmmm, looks like something might have happened at some point. Maybe we want to see some numbers instead of a graph? To do aggregate statistics on time series in pandas we use a method called .resample(), and we're going to tell it to group the data by year.
End of explanation
# If 'A' is every year, 10A is every 5 years
df.resample('5A').median()
# We can graph these!
df.plot(y='val', label="Monthly")
df.resample('A').median().plot(y='val', label="Annual")
df.resample('10A').median().plot(y='val', label="Decade")
# We can graph these ALL ON THE SAME PLOT!
# we store the 'ax' from the first .plot and pass it to the others
ax = df.plot(y='val', label="Monthly")
df.resample('A').median().plot(y='val', ax=ax, label="Annual")
df.resample('10A').median().plot(y='val', ax=ax, label="Decade")
# Which year had the worst month?
df.resample('A').median()
Explanation: That still looks like too much data! What about every decade?
End of explanation
# Group by the month, check the median
df.groupby(by=df.index.month).median()
# Group by the month, check the median, plot the results
df.groupby(by=df.index.month).median().plot(y='val')
# Group by the month, check the median, plot the results
ax = df.groupby(by=df.index.month).median().plot(y='val', legend=False)
ax.set_xticks([1,2,3,4,5,6,7,8,9,10,11, 12])
ax.set_xticklabels(['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'])
ax.set_ylabel("Houses sold (in thousands)")
ax.set_title("House sales by month, 1963-2016")
Explanation: Cyclical data
It seems like winter might be a time where not very many houses are sold. Let's see if that's true!
End of explanation
df.resample('A')['val'].max().plot()
# The fewest?
df.resample('A')['val'].min().plot()
# We now know we can look at the range
ax = df.resample('A')['val'].median().plot()
df.resample('A')['val'].max().plot(ax=ax)
df.resample('A')['val'].min().plot(ax=ax)
# We now know we can look at the range IN AN EVEN COOLER WAY
ax = df.resample('A')['val'].median().plot()
x_values = df.resample('A').index
min_values = df.resample('A')['val'].min()
max_values = df.resample('A')['val'].max()
ax.fill_between(x_values, min_values, max_values, alpha=0.5)
ax.set_ylim([0,130])
ax.set_ylabel("Houses sold (in thousands)")
ax.set_xlabel("Year")
ax.set_title("The Housing Bubble")
Explanation: More details
You can also use max and min and all of your other aggregate friends with .resample. For example, what's the largest number of houses hold in a given year?
End of explanation
<END_TASK>