One of the skills in working with data is to pull multiple data sets together. There are often features we would like to use in training our machine learning model that aren't necessarily in the dataset we have in front of us. For example, we may be looking at attendance figures at baseball games, trying to predict what they will be for future games. However, we only have team statistics. The attendance certainly depends on the weather at the stadium (or in the city) on the day of a game. So we would like to be able to gather the weather data and add it to our model. We'll go through how to do something like that.
We start with data from the Iowa open database of class "E" liquor licencee purchases. I have filtered the data to include data from 10/1/2016 to 12/31/2016. I have also added up the sales for each day to each store. Note that I've added an argument na.strings="". This will turn any blank entries into NA values so we know we have a missing entry.
In [1]:
iowadf <- read.csv("Class05_iowa_data.csv", stringsAsFactors=FALSE, na.strings="")
head(iowadf)
In [2]:
# The sales data looks like it isn't a float like we want it to be (the presence of a $ in front is my clue that there may be something wrong.) Let's look at the data types to be sure.
str(iowadf)
In [3]:
# Sure enough. We need to get the real values from that column. We'll create a new column for that data and use the regex parser to get the number.
iowadf$SalesToStore<-gsub("\\$","",iowadf$Total.Sales.to.Store..Dollars.)
# We also need to convert the output to a float.
iowadf$SalesToStore <- as.numeric(iowadf$SalesToStore)
print(str(iowadf))
head(iowadf)
In [4]:
# We also need to convert the date. We'll try this and see if it parses the date correctly.
iowadf$SalesDate <- as.POSIXct(iowadf$Date, format="%m/%d/%Y")
print(str(iowadf))
head(iowadf)
In [5]:
# Let's do a bit more data exploration here to see if there are any other issues. For example, let's see if there are any NA values in the dataframe. That may indicate a problem. We'll first check the entire dataframe.
# This looks for any null value then combines them together. It will only be true if there are any null values.
any(is.na(iowadf))
In [6]:
#Let's see how many data points have null values. We'll look at the total rows and
print(paste("Initial rows:",nrow(iowadf)))
iowadfcl <- iowadf[complete.cases(iowadf),]
print(paste("Initial rows:",nrow(iowadfcl)))
any(is.na(iowadfcl))
We only lose 25 rows out of 13,000. I'm going to go with that- it simplifies further computations.
We now want to narrow down the data in order to make it easier to work with joining datasets. Let's get the top-purchasing store and use just that store for our further work. We first need to do an analysis step: we want to add up all the sales for each store. In other words, we want to group by store, then sum up the StoreSales.
This is a good tutorial on how to use the dplyr package in R, which will will be using here.
The first step is to use the group_by() function to group the data into sections that all have something in common. For our case, we will group by the "Store Number". Dplyr will go through that column, find all the unique values, then group them together. We then want to summarize() the results from the grouping. The function n() will count the number of entries in each group. We assign the output to a new column called count. The pipe operator %>% tells dplr to take the item on the left and send it to the item on the right.
In [8]:
library(dplyr)
iowadf %>% group_by(Store.Number) %>% summarize(count=n())
Instead of the count, what if we want the sum of total sales to the stores, sorted by the maximum first? We create a new pipeline that groups, summs, then sorts. Finally, we assign the output of this to a new dataframe.
In [9]:
storesum <- iowadf %>% group_by(Store.Number) %>% summarize(TotalSales=sum(SalesToStore)) %>% arrange(desc(TotalSales))
head(storesum,10)
It looks like the store number 2633 purchased more liquor than any other store, so we want only that subset of the data. We subset the data by looking only for rows where the store number matches 2633. This returns a column of True/False values where the Store Number matches our store.
In [10]:
subsetrows <- iowadfcl$Store.Number==2633
head(subsetrows)
We then index the dataframe based on those rows. We get a dataframe that only has data from the store we want. Again, notice the , after subsetrows. R indexes dataframes using a [rows,columns] format. By leaving the columns entry blank, we get all of the columns in the dataframe.
In [11]:
topstoredf <- iowadfcl[subsetrows,]
head(topstoredf)
There is a lot of duplicated information here. We only really care about the SalesDate and the SalesToStore column. So we'll drop the other columns in this dataframe and assign it the same name (effectively dropping the columns).
In [12]:
topstoredf <- subset(topstoredf, select=-c(Date, Store.Number, City, Zip.Code, County, Total.Sales.to.Store..Dollars.))
We now want to sort the data by date so that it is listed in chronological order. We will use the sort_values() function (in place). We tell dplyr which column to sort by.
In [13]:
topstoredf <- topstoredf %>% arrange(SalesDate)
head(topstoredf)
Before we move on, let's look at this data. It is always a good idea to have some idea of what the data look like!
In [14]:
library(ggplot2)
options(repr.plot.width=5, repr.plot.height=5)
ggplot(topstoredf) + geom_point(aes(SalesDate,SalesToStore))
Now we need another dataset to work with. Let's try pulling the Dow Jones Industrial average index for each day. We'll download the data from here. We will then turn the date column into the appropriate datetime format.
In [16]:
djiadf <- read.csv('Class05_DJIA_data.csv',stringsAsFactors=FALSE)
djiadf$DATE <- as.POSIXct(djiadf$DATE)
djiadf$VALUE <- as.numeric(djiadf$VALUE)
str(djiadf)
In [17]:
# We'll rename the second column to something a little more descriptive
djiadf <- rename(djiadf, DowJonesAvg=VALUE)
#Let's check for problems:
all(complete.cases(djiadf))
In [18]:
# Looks like there are two problem rows. Let's identify which rows by indexing the dataframe any row that isn't complete
djiadf[!complete.cases(djiadf),]
In [19]:
# These were both holidays and shouldn't be included in the data set anyway! We can drop them in place, modifying our dataframe.
djiadf<- djiadf[complete.cases(djiadf),]
all(complete.cases(djiadf))
And, of course, let's plot it to see what it looks like.
In [20]:
ggplot(djiadf) + geom_point(aes(DATE,DowJonesAvg))
We now want to join the data from the store and the Dow Jones Industrial average. This will let us plot the two together to see if there is any correlation between the DJIA and the purchases that the store is making.
In order to join two dataframes we need to know how to align the rows or how to match up the data. The easiest way to do this is to try it out with an example to see how it works. See this tutorial for more details on merging data in R. We start by creating two small test dataframes.
In [21]:
df_a <- data.frame(
patient_id=c('A', 'B', 'C', 'D', 'E'),
first_name= c('Alex', 'Amy', 'Allen', 'Alice', 'Ayoung'),
last_name= c('Anderson', 'Ackerman', 'Ali', 'Aoni', 'Atiches'),
visit_number= c(1,2,3,4,5),stringsAsFactors=FALSE)
df_a
In [22]:
df_b <- data.frame(
doctor_id= c('G', 'H', 'I', 'A', 'B'),
first_name= c('Billy', 'Brian', 'Bran', 'Bryce', 'Betty'),
last_name= c('Bonder', 'Black', 'Balwner', 'Brice', 'Btisan'),
visit_number= c(4,5,6,7,8),stringsAsFactors=FALSE)
df_b
In [23]:
str(df_a)
How do we tack df_b on the end of df_a? This is useful if you have pieces of a dataset that you want to combine together. Notice that there are now NA values in the table! The first dataframe didn't have the doctor_id column, so R filled in those values with NA. Same for the second dataframe and the patient_id values. We could drop those columns if we wanted to.
In [24]:
bind_rows(df_a, df_b)
What if we want to join together the rows that have the same visit_number? Looking at the table, we see that Billy Bonder and Alice Aoni have the same visit_number. Let's merge the two dataframes together to see how that works.
In [25]:
merge(df_a,df_b,by="visit_number")
So we now have a much smaller dataframe that only has the rows where visit_number was the same in both. Note that R also has changed the column names. This is because there were duplicate names and it can't have that to maintain unique addressing. Look at the documentation for R to see how to change the suffixes that R appends.
What if, instead, we wanted to merge rows where the patient_id from one matches the doctor_id from the other? We can do that, too. The two dataframes are given the designations x and y for the first and second dataframes, respectively. So we need to identify the columns in each to match.
In [26]:
merge(df_a,df_b,by.x='patient_id',by.y='doctor_id')
What if we want to keep all of the entries in the left dataframe and only add in values on the right where they match? We can do that, too!
In [27]:
merge(df_a,df_b,by='visit_number',all.x=TRUE)
Not surprisingly, we can do the right-side, too.
In [28]:
merge(df_a,df_b,by='visit_number',all.y=TRUE)
Finally, we can do both and include all the data from both dataframes, matching where possible. That is called an "outer" join.
In [29]:
merge(df_a,df_b,by='visit_number',all=TRUE)
How do you choose the merge index? We used both the visit_number and the patient/doctor_id values. The key is to be able to merge on some common feature. This may be date (like we show below), but it could also be on zip code, a city name, the month number, financial quarter, or any other piece of information that you have common between your two datasets. You need to make sure that the merge indices have the same data type and are in the same format. For example, the computer doesn't know that 'DALLAS' and 'Dallas' are the same city. We can use the tools we've previously covered to clean up the data prior to attempting the merge.
In [30]:
storeDJ <- merge(topstoredf, djiadf, by.x='SalesDate',by.y='DATE')
head(storeDJ)
Now we can do what we wanted to do: plot the data to see if there is a correlation here!
In [31]:
ggplot(storeDJ) + geom_point(aes(DowJonesAvg,SalesToStore))
Well, after all that work, it doesn't look like there is anything there... Perhaps that isn't too surprising as the purchases by a single store may not depend on a macro-scale indicator like the Dow Jones Industrial average. But we didn't know that until we checked.
Instead of looking at the sales to one store, what if we look at the total sales to all of the stores in the state of Iowa? Go through the process of grouping that data and joining it with the DJIA data. Plot the results and show me before you leave.
In [ ]: