TDM 10100: Project 8 - Dates and Date-Times

This dataset contains data about restaurant recommendations. The focus was on the challenge of making restaurant recommendations to customers. This is a record of all orders made by the customers from various venders that were included in the competition. Each customer is not limited to a single order and can even order from multiple locations.

With data from about 35,000 customers, there is ample opportunity for data exploration. The orders are stored across 26 columns that focus on the details of each order, vender, and customer combination. The columns we will be focusing on are item_count and created_at. item_count tracks the number of items within each order. created_at marks the year, month, day, hour, minute, and second the order was created in the system.

The flights dataset is huge, with files from 1987 to 2023, each with respective subset datasets just to make the data reasonable to work with. The flights dataset provides numerous opportunities for data exploration with 5,411,843 rows from 1997 alone. These subsets contain information about when each flight took place, as well as different factors like how long they took, specifics like flight numbers, and more. There are, of course, empty or messy values, but there is so much data that this does not make too much of an impact for what we will be doing.

There are 29 columns and millions of rows of data. Some of these columns include:

Read in the Restaurant Orders dataset as orders from /anvil/projects/tdm/data/restaurant/orders.csv. Many of the entries are blank when we first look at the head of the dataset. This could be un-recorded data, errors, or just instances where the columns did not apply to the customer. This is a fairly large dataset, and the columns we will be using are more consistent, so it is OK to ignore where there are gaps.

If we look at the created_at column, it shows date and time character data (if you read the data with read.csv()). When data is of class Date, it represents actual calendar dates (without time information) rather than just written entries. You can convert character strings into Date objects using as.Date().

Overwrite the created_at column or make a new column containing the created_at entries as actual dates:

In R, there is a package lubridate that is designed to make working with date and time data simpler and more efficient. This package is a part of the tidyverse, and provides us with methods to complete date and time related manipulations easier than with base R. To learn a bit about how to begin using lubridate, look through the introduction and cheatsheet.

Within lubridate, there are numerous functions provided that make manipulating date/time data a lot simpler. This includes but is not limited to:

With the Date created_at column, make two new columns. Your first column, num_day_of_the_week, will contain the day of the week data for the numerical date values:

Your second column, day_of_the_week, will contain the day of the week data with the correct labels applied to each day of the week value:

Show the table of each of these new columns, then make a table displaying their values against each other.

Now, take the month values from created_at, and save them as a new month column (we used label=TRUE for the names of the months):

Do the same for years for a new year column. From these new columns, check out how the data is distributed in their respective tables (table for month and table for year).

There is a column item_count that tracks how many items were in each order. Use tapply() to see how the total counts of items in the orders were distributed across the months.

You may see that some of the months still have NA as their value even after using na.rm=TRUE. To see why this is, search through the orders dataset for where the month column is equal to "Mar" (or March if you specified abbr = FALSE when creating month).

Save your tapply() function to a variable, then create a barplot from it. This helps to visualize that there were actually no items ordered in March, April, or May.

Use tapply() again, this time to show the total item count for each (year, month) pairing.

Make this into a barplot to help visualize how the orders were distributed throughout the years. Remember to use beside=TRUE and legend=TRUE (as well as your other customizations) to help this plot’s readability.

Read in the 1997 Flights dataset as flights from /anvil/projects/tdm/data/flights/subset/1997.csv.

Another useful function in lubridate is ymd() which is a method often used with paste() to easily combine three columns (year column, month column, day column) to create one Date column containing values of YYYY-MM-DD data. Lets make a new column full_dates that contains data in the format YYYY-MM-DD:

This combining of columns that we’ve just done is actually the opposite of how we split up the created_at column in Question 1. Now that you know how to split and merge dates, you could continue to do so in an endless loop, splitting, merging, splitting again, …​
But for the rest of this project, let’s just set this aside.

In the DepTime column, there are values from 1 to 2400. BUT 2400 is not valid in POSIXct. POSIXct is a class used to store date and time information. The valid range is 00 - 23 for hours, 00 - 59 for minutes/seconds. So 2400 is not valid, and needs to be converted to 0 to represent midnight on the following day.
Use flights$DepTime[flights$DepTime == 2400] <- 0 to replace each 2400 entry in DepTime with 0.

To make the DepTime column display times in a more readable format instead of military shorthand, we need to convert its values. Use the floor() function to divide the DepTime column by 100, and save this as a new column depHour:

Take the fractional part from dividing DepTime by 100 and save this as depMinute.

For the final part of this question, make a column date_times. You should use make_datetime(), and should include the Year, Month, DayofMonth, depHour, and depMinute columns. This new column will include date and time values for each flight’s departure in a format like how the created_at column from the orders dataset was, only we do not include seconds here.

Make a dataframe bostonDF that contains only the values from flights that had an Origin of BOS.

Flights departing from Boston could have many different arrival locations. But one thing that is fair to guess is that the average flight distance across each of the different months of the year would be fairly similar. Start by looking at how many Boston flights there were per month. These values are relatively similar, and February, of course, has the least occurrences, given that it has the least number of possible flight days.

Use tapply() to show the average flight Distance across the different months for flights within the bostonDF. Save this as boston_distance.

Plot boston_distance as a line plot with type='b'.

Create dataframes with Origin for the flights PHX, MDW, and SEA, respectively. Perform all of the steps you did with bostonDF for each of these, resulting in four plots total.

What if you wanted to compare the average flight distance across the months for the different origin locations? It is hard to do when the plots are separate, and are at different scales.

Plot boston_distance again, this time using specific y-limits of minimum 300, maximum 1200. To add on lines representing each of your other plots, use lines().
Your cell should contain:

The initial plotting with boston_distance sets up the space, and then each lines() adds the additional plot lines to the visualization space:

In Question 4, we were looking at the average distance per month for each of four flight origins. Use tapply() here to find the total (sum) distance per month for each of those same four flight origins.

These values will be a lot greater than the ones from Question 4, because those were the averages, and these will count the hundreds of thousands of millions of miles from the flights.

Combine these four tapply() functions in one plot. You can use the same plotting code from the previous question, but remember that in this case, the distances will come from the sum, not the average:

This plot may not look quite right. The reason for this is that the plot space is created when the plot() was made; in our case, the limits of the area are set to the min and max values from the Boston flights.

Find the max() and min() values from each of your total flight distances across all four locations, and set the y limits accordingly, matching above the highest max value and below the lowest min value.