<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Introduction to data analytics with pandas
Quentin Caudron
PyData Seattle, July 2017
Systems check
Do you have a working Python installation, with the pandas package ?
Step1:  Note 
Step2:  Note 
Step3:  Note 
Step4:  Let's just look at the first few rows.
Step5:  We have an index, and three columns 
Step6:  Definitely a string. We'll note this as something to fix after we finish looking around.
Note 
Step7:  Note 
Step8:  What else can we find out ?
Step9:  Looks like we also have some missing data - we have 671 rows, but the coffees column only has 658 entries.
Note 
Step10:  Note 
Step11:  The contributor column makes sense as object, because we expect strings there; but surely the timestamp should be a timestamp-type, and coffees should be numerical ?
Let's inspect what's in the timestamp column.
Step12:  It looks like the timestamp field was read from CSV as a string. That makes sense - CSV files are very basic. We'll have pandas interpret these strings as datetimes for us automatically.
Note 
Step13:  #### The coffees column contains NaNs.
Step14:  The coffees column is of type float.
Step15:  Let's have pandas parse the timestamp strings to datetime objects.
Step16:  So where do we stand ?
Step17:  Note 
Step18:  pandas is plotting the coffees against the index, which is just a series of integers.
Note 
Step19:  We have some very uneven spacing in places. We might start by cutting off the last few points of this time-series, which is missing a lot of data.
We'll inspect the last few points of this time-series.
Step20:  After mid-March, things start getting spaced rather erratically.
Let's cut off the tail of the time-series, anything after 2013-03-01.
Step21:  Note 
Step22:  1. Contributions to the time-series
Who are our main contributors ?
Step23:  Note 
Step24:  On which weekdays were contributions made ?
Step25:  Can we replace these integers with actual weekdays ?
Step26:  Let's group by these weekdays.
Step27:  Note 
Step28:  2. Weekday trends
First, we'll set our timestamps to the dataframe's index
Step29:  Let's add some rows at midnight on every day.
Step30:  Note 
Step31:  Note 
Step32:  Note 
Step33:  We're now ready to resample the time-series at a daily frequency.
Step34:  Let's begin by figuring out how many coffees are made on any given day.
Step35:  Note 
Step36:  Let's order this series and then plot it.
Step37:  Wednesdays was seminar day...
3. Coffee per person
We can now pull in data on how many people were in the department.
Step38:  Let's join the datasets.
Step39:  Note 
Step40:  We can now plot this column.
Step41:  Those are strange plateaus. We'll pull in another dataset, telling us when the machine was broken.
Step42:  Note 
Step43:  A quick trick to plot this as a time-series...
Step44:  Note 
Step45:  We'll bring in this numerical representation of status column into our dataframe too.
Step46:  Let's plot both the coffees per person and the numerical status.
Step47:  We see a strong weekday-weekend effect. Resampling weekly will fix that.
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
Explanation: Introduction to data analytics with pandas
Quentin Caudron
PyData Seattle, July 2017
Systems check
Do you have a working Python installation, with the pandas package ?
End of explanation
import pandas as pd
%matplotlib inline
Explanation: Note : This cell should run without raising a traceback. Assuming it runs, you can also try printing the value of pd.__version__ to see what version of pandas you have installed.
A little about me
Lapsed computational physicist
PhD computational neuroscience, postdoc statistical epidemiology
Data Scientist at CBRE - www.cbredev.com
ATOM at Seattle
A little about the hero of this story
<center><img src="images/coffee_machine.jpg" width="400px" /></center>
We'll be analysing a real-world dataset together. It's about my favourite thing in the world : coffee. This dataset was collected at the Mathematics Institute at the University of Warwick. It's a time-series dataset, describing the total number of coffees made by our espresso machine by a certain date.
A little about this workshop
We'll be running through an analysis of this dataset as a way to expose you to the pandas API. The aim is to develop a little familiarity with how to work with pandas.
Slides are available at https://github.com/QCaudron/pydata_pandas. One notebook contains solutions; beware of spoilers.
The notebooks contain notes about what we're doing that I'll skip during this workshop, but try to explain on the way.
The pandas API is enormous. The documentation is excellent, don't hesitate to look things up.
Key questions
The dataset : total number of coffees made vs time.
Who are the main contributors to this dataset, and when are contributions generally made ?
What are the department's weekday coffee habits ?
How much coffee are people drinking ?
Let's begin
End of explanation
# Read data from data/coffees.csv
data = pd.read_csv("data/coffees.csv")
Explanation: Note : The second line here tells matplotlib to plot directly under the cell where any plotting code is called. pandas uses matplotlib to generate graphs, and without this, the graphs would appear outside the Jupyter notebook when you called plt.show() - but we just want them to appear without having to do this.
http://ipython.readthedocs.io/en/stable/interactive/plotting.html#id1
Importing the data
Let's import the coffee data from CSV.
End of explanation
data
Explanation: Note : pandas can read from many data formats : CSV, JSON, Excel, HDF5, SQL, and more.
http://pandas.pydata.org/pandas-docs/version/0.20/io.html
What does this data look like ?
End of explanation
# .head()
data.head()
Explanation: Let's just look at the first few rows.
End of explanation
# .loc or .iloc
data.loc[2]
Explanation: We have an index, and three columns : timestamp, coffees, and contributor.
Uh-oh. Why is there a string of text, testing, in our coffee numbers ? What's going on in the coffees column in the row after that ?
Note : df.head(n=10) would show the first ten rows. The default is n=5.
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.head.html
Let's look at that string in the third row.
End of explanation
# [] indexing on a series
data.coffees[:5]
Explanation: Definitely a string. We'll note this as something to fix after we finish looking around.
Note : .loc uses a label-based lookup, which means that the value you pass into the square brackets must be in the index. Another method, .iloc, is integer-location-based, so .iloc[2] would return the third row. In this case, they're the same, but had we changed our index, as we'll see later, things would work differently.
Indexing a dataframe with [] directly returns a pd.Series or pd.DataFrame by searching over columns, not rows. Indexing a pd.Series with [] is like indexing a dataframe with .iloc.
https://pandas.pydata.org/pandas-docs/stable/indexing.html
We should also take a look at that NaN. In fact, let's look at the first five values in coffees.
End of explanation
print("Dataset length :")
# len()
print(len(data))
Explanation: Note : here, we're indexing a series ( a pd.Series object ). From a pd.DataFrame ( here, data ), when you access a single column ( data.coffees or data["coffees"] ), the object returned is a pd.Series. From that, indexing directly with [] works in an integer-location-based manner, and like with numpy arrays, you can take slices ( [:5] ).
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.html
How long is the dataset ?
End of explanation
# .describe()
data.describe()
Explanation: What else can we find out ?
End of explanation
# .isnull() and boolean indexing with []
data[data.coffees.isnull()]
Explanation: Looks like we also have some missing data - we have 671 rows, but the coffees column only has 658 entries.
Note : .describe() returns different things based on what's in the dataframe, as we'll see later. For numerical columns, it will return things like the mean, standard deviation, and percentiles. For object columns ( strings or datetimes ), it will return the most frequent entry and the first and last items. For all columns, .describe() will return the count of objects in that column ( not counting NaNs ) and the unique number of entries. You can determine what's returned using .describe()'s keyword arguments.
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.describe.html
Let's look at the dataframe where coffees is null.
End of explanation
# .dtypes
data.dtypes
Explanation: Note : .isnull() returns a boolean array ( an array of Trues and Falses ), that you can then use to index the dataframe directly. Here, our boolean array tells us which entries in the coffees column are null, and we use that to index against the full dataframe - so we get back every column in the dataframe, but only those rows where coffees is null.
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.isnull.html
What type of Python objects are the columns ?
End of explanation
# print the first element of the series with [] indexing
print(data.timestamp[0])
# print its type()
print(type(data.timestamp[0]))
Explanation: The contributor column makes sense as object, because we expect strings there; but surely the timestamp should be a timestamp-type, and coffees should be numerical ?
Let's inspect what's in the timestamp column.
End of explanation
# cast the coffees column using pd.to_numeric, and coerce errors
data.coffees = pd.to_numeric(data.coffees, errors="coerce")
data.head()
Explanation: It looks like the timestamp field was read from CSV as a string. That makes sense - CSV files are very basic. We'll have pandas interpret these strings as datetimes for us automatically.
Note : here's an example of using direct [] indexing on a pd.Series. We're accessing the first entry, just to see what type of object we have there.
On our first pass, what problems did we find ?
The timestamp column contains strings; these need to be datetimes
The coffees column contains some null values and at least one string
Cleaning the data
The coffees column should only contain numerical data.
End of explanation
# Use .dropna() using a subset, and pass inplace
data.dropna(subset=["coffees"], inplace=True)
data.head()
Explanation: #### The coffees column contains NaNs.
End of explanation
# Cast to int using .astype()
data.coffees = data.coffees.astype(int)
data.head()
Explanation: The coffees column is of type float.
End of explanation
# pd.to_datetime()
data.timestamp = pd.to_datetime(data.timestamp)
# Confirm dtypes
data.dtypes
Explanation: Let's have pandas parse the timestamp strings to datetime objects.
End of explanation
# .describe(), passing the include kwarg to see all information
data.describe(include="all")
# What do the first few rows look like ?
data.head()
Explanation: So where do we stand ?
End of explanation
# .plot() on the coffees series
data.coffees.plot()
Explanation: Note : .describe(include="all") is describing all attributes of all columns, but some don't make sense based on the column's dtype. For example, the contributor column has no first and last attributes, because those describe the first and last entries in an ordered series. That makes sense for the timestamp - those have an intuitive definition of sorting - but not so much for strings ( alphabetical order doesn't really matter when they're arbitrary strings ). Similary, the timestamp column has no mean or other numerical traits. What does it mean to calculate the mean timestamp ?
The time-series at a glance
Let's begin by visualising the coffee counts.
End of explanation
# .plot() on the dataframe, setting x to the timestamp, with dot-dash style
data.plot(x=data.timestamp, style=".-")
Explanation: pandas is plotting the coffees against the index, which is just a series of integers.
Note : .plot() on a pd.Series will plot the data against the index. On a pd.DataFrame, the .plot() method allows plotting of one column against another.
By default, .plot() renders a line graph, but you can specify which type of plot you'd like - bar, line, histogram, area, scatter, etc..
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.plot.html
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.plot.html
Let's use the dataframe's plot() method rather than that of the series.
End of explanation
# .tail() with ten rows
data.tail(n=10)
Explanation: We have some very uneven spacing in places. We might start by cutting off the last few points of this time-series, which is missing a lot of data.
We'll inspect the last few points of this time-series.
End of explanation
# Use conditional indexing against the timestamp
data = data[data.timestamp < "2013-03-01"]
data.tail()
Explanation: After mid-March, things start getting spaced rather erratically.
Let's cut off the tail of the time-series, anything after 2013-03-01.
End of explanation
# Once again, plot the data against the timestamp
data.plot(x=data.timestamp, style=".-")
Explanation: Note : this is another example of boolean indexing. data.timestamp &lt; "2013-03-01" is a boolean array, and can be passed into the dataframe immediately in [], much like with a np.ndarray.
One final look.
End of explanation
# .value_counts()
data.contributor.value_counts()
Explanation: 1. Contributions to the time-series
Who are our main contributors ?
End of explanation
# .plot() a bar chart from the value counts
data.contributor.value_counts().plot(kind="bar")
Explanation: Note : .value_counts() counts the unique values in a series. It's similar to doing a .groupby() followed by a .count(), as we'll see soon.
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.value_counts.html
Let's plot this.
End of explanation
# Create a series of the weekdays for each entry using .dt.weekday
weekdays = data.timestamp.dt.weekday
# assign() it to our dataframe
data = data.assign(weekdays=weekdays)
data.head()
Explanation: On which weekdays were contributions made ?
End of explanation
weekday_names = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
weekday_dict = {key: weekday_names[key] for key in range(7)}
# Use .apply() to apply a custom function to the weekdays column
data.weekdays = data.weekdays.apply(lambda x: weekday_dict[x])
data.head()
Explanation: Can we replace these integers with actual weekdays ?
End of explanation
# .groupby() the weekdays and then .count() rows in each group
weekday_counts = data.groupby("weekdays").count()
# We can reorder this dataframe by our weekday_names list
weekday_counts = weekday_counts.loc[weekday_names]
weekday_counts
Explanation: Let's group by these weekdays.
End of explanation
# .plot() a bar chart of data in weekday_counts
weekday_counts.timestamp.plot(kind="bar", title="Datapoints added on each weekday")
Explanation: Note : this first line could be replaced by weekday_counts = data.weekdays.value_counts(), with the only difference being that that would return a series to us, and here, we got back a dataframe.
We can now visualise these weekday counts.
End of explanation
# Set the dataframe's .index property
data.index = data.timestamp
# Let's drop the timestamp column, as we no longer need it
data.drop(["timestamp"], axis=1, inplace=True)
data.head()
Explanation: 2. Weekday trends
First, we'll set our timestamps to the dataframe's index
End of explanation
# pd.date_range, with daily frequency, and normalisation
midnights = pd.date_range(data.index[0], data.index[-1], freq="D", normalize=True)
midnights
Explanation: Let's add some rows at midnight on every day.
End of explanation
# Take the union of the existing and new indices
new_index = midnights.union(data.index)
new_index
Explanation: Note : pd.date_range creates a fixed-frequency DatetimeIndex. normalize=True ensures these datetimes are at midnight, and not at whatever time the starting point is.
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.date_range.html
Let's take the union of this index and our dataset's index.
End of explanation
# .reindex() the dataframe
upsampled_data = data.reindex(new_index)
upsampled_data.head(10)
Explanation: Note : the union of these indices is just a new index where entries from both indices are present. It's sorted by time.
Now we can reindex our dataframe with this new index.
End of explanation
# .interpolate the upsampled_data using the time method
upsampled_data = upsampled_data.interpolate(method="time")
upsampled_data.head(10)
Explanation: Note : .reindex() keeps any values that conform to the new index, and inserts NaNs where we have no values.
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html
We can fill in these NaNs using interpolation.
End of explanation
# .resample() followed by .asfreq()
daily_data = upsampled_data.resample("D").asfreq()
# Drop the contributor column, we no longer need it
daily_data = daily_data.drop(["contributor"], axis=1)
# Generate a column of weekday names
daily_data["weekdays"] = daily_data.index.weekday_name  # We did it the slow way before...
daily_data.head()
# Let's plot the data once more, to see how we're doing
daily_data.plot(figsize=(15, 4), style=".")
Explanation: We're now ready to resample the time-series at a daily frequency.
End of explanation
# Use .diff() on the coffees column; follow up with .shift()
coffees_made = daily_data.coffees.diff().shift(-1)
# Add this as a column to the dataframe
daily_data["coffees_made_today"] = coffees_made
daily_data.head()
Explanation: Let's begin by figuring out how many coffees are made on any given day.
End of explanation
# .groupby weekdays, take the mean, and grab the coffees_made_today column
coffees_by_day = daily_data.groupby("weekdays").mean().coffees_made_today
coffees_by_day
Explanation: Note : we use .shift() here because if we look at the .diff() between a Monday and a Tuesday, those coffees are attributed to the Tuesday. However, what we want to say is "this many coffees were made at some point on the Monday", so we shift the entire series up one.
Now we can group this by weekday.
End of explanation
# Sort coffees_by_day by our list of weekday names
coffees_by_day = coffees_by_day[weekday_names]
# Plot a bar chart
coffees_by_day.plot(kind="bar")
Explanation: Let's order this series and then plot it.
End of explanation
# Bring in data/department_members.csv;
# have the first column be the index, and parse the dates
people = pd.read_csv("data/department_members.csv", index_col=[0], parse_dates=True)
people.head()
Explanation: Wednesdays was seminar day...
3. Coffee per person
We can now pull in data on how many people were in the department.
End of explanation
# Use an outer join, then interpolate over missing values using nearest values
daily_data = daily_data.join(people, how="outer").interpolate(method="nearest")
daily_data.head()
Explanation: Let's join the datasets.
End of explanation
# New column is the ratio of coffees made on a given day to number of members in the department
daily_data["coffees_per_person"] = daily_data.coffees_made_today / daily_data.members
# Let's drop those remaining NaNs while we're at it
daily_data.dropna(inplace=True)
daily_data.head()
Explanation: Note : by default, inner joins are performed. That is, if a row from one of the datasets has an index that isn't in the other dataset, that row is dropped. You can specify whether you want outer, left, or right joins, as well plenty of other useful options. The pandas API for joining or merging datasets is very developed.
https://pandas.pydata.org/pandas-docs/stable/merging.html
Let's create a column for the number of coffees consumed per person.
End of explanation
# Plot the coffees_per_person column
daily_data.coffees_per_person.plot()
Explanation: We can now plot this column.
End of explanation
# pd.read_csv(); try using data/coffee_status.csv
# parse_dates as kwarg; also pass index_col
machine_status = pd.read_csv("data/coffee_status.csv", parse_dates=["date"], index_col="date")
machine_status.head()
Explanation: Those are strange plateaus. We'll pull in another dataset, telling us when the machine was broken.
End of explanation
# .value_counts()
machine_status.status.value_counts()
Explanation: Note : the parse_dates keyword argument takes several values. By passing in a list of strings, we're telling pandas to attempt to parse the dates in columns with those names.
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html
What values are in the status column ?
End of explanation
# Make a pd.Series from the status series where things are OK
numerical_status = machine_status.status == "OK"
numerical_status.plot()
Explanation: A quick trick to plot this as a time-series...
End of explanation
# .join()
daily_data = daily_data.join(machine_status)
daily_data.head()
Explanation: Note : the first line here creates a boolean pd.Series, holding the value True when machine_status.status is "OK", and False otherwise. Because it's a pd.Series, its index stays the same as that of machine_status, which was a DatetimeIndex. Then, we can plot the boolean series ( True appearing as 1, and False appearing as 0 ), and just quickly scan to see that there are long areas where the coffee machine was operations, with short bouts ( thankfully ! ) of the machine being broken.
Let's join the datasets on the date field !
End of explanation
# Column depicting when the status was "OK"
# Cast the series to ints before as you create a new column in the dataframe
daily_data["numerical_status"] = (daily_data.status == "OK").astype(int)
daily_data.head()
Explanation: We'll bring in this numerical representation of status column into our dataframe too.
End of explanation
# Plot both columns on the same graph, using default args
daily_data[["coffees_per_person", "numerical_status"]].plot()
Explanation: Let's plot both the coffees per person and the numerical status.
End of explanation
# Resample weekly, taking the mean of each week to get a weekly value
weekly_data = daily_data.resample("W").mean()
weekly_data[["coffees_per_person", "numerical_status"]].plot()
Explanation: We see a strong weekday-weekend effect. Resampling weekly will fix that.
End of explanation
<END_TASK>