TDM 10200: Project 6 - Manipulating Data

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 data provides numerous opportunities for data exploration with 7,141,922 rows of data from 2006 and 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:

The Ice Cream dataset is on the smaller side, containing just a few hundred rows of data. This dataset contains information about four different ice cream brands' products, with product information that would make purchasing easier, such as the description, how the product was rated, and what ingredients it contains.

Some of the columns (description, ingredients) of this dataset are not particularly useful given our current skillset, and can make looking at rows of this data not so easy. Which makes taking specific columns (like the rating column) and isolating them for work ideal.

The rating column is each ice cream’s customer rating on a scale of 0 - 5.

The flights dataset is much too large to work with all at once, so we are going to be working with the 2006 subset. This dataset contains all of the same columns as the other flights datasets, just only for the flights for which the year was 2006.

Use the pandas library to read in the 2006 flights subset data as myDF.

You will notice a star (see below) at the beginning of the code line for this dataset, as it takes some time to load in Anvil due to its large size:

Create pa_flights containing only the flights that have a destination of the Philadelphia International Airport (PHL).

Remember, when subsetting a dataframe in Python, we can select to keep only the data which matches a certain condition:

Using this same approach, you can filter flights with destination "PHL".

If you look at the shape of this pa_flights, you will see that this subset still contains many rows of data. PHL is a very popular airport, with many flights leaving from and arriving there. Show how many unique Origin spots there are for flights going to PHL.

Using the flights' origins, use .groupby() to find the average AirTime for each flight in the pa_flights data. The standard way of doing this is df.groupby("col1")["col2"].mean(). This output will show the average air time for each unique flight origin, with a destination of PHL.

Make a second temporary dataframe containing the data for flights ending at PHX (Dest column). Then use .groupby() to find the average AirTime across each flight origin, and sort to find the top results.

In Question 1, we only used the destination of two selected airports. Here, use the top 20 destinations when comparing each average air time of each origin-and-destination pair.

The function .value_counts() will return the names and values of a search. These results default to sorting by highest to lowest value. Take the top 20 most popular destination airports and make a new variable top_20.

We have already used .isin() just a bit in Project 4: it checks whether or not a value is included in another set of values. In this example, we can use .isin() to filter myDF so that we only keep the rows where the destination (Dest) is one of the values inside the list top_20.

This will give you a new dataframe, containing only the flights going to any of those top 20 destinations:

In the .groupby() method, we are also able to work with multiple column pairs. For example, we can show the average AirTime across each Origin and Dest pair for the top 20 destination pairs as follows:

For this question, read in the ice cream products file from /anvil/projects/tdm/data/icecream/combined/products.csv as ice_cream.

View the value counts of the rating column. This shows the counts of each rating (from 0 to 5), and is helpful, but there is something more we can find.

To better understand this column and how each ice cream was received, we could add labels to each range of rating.

The .cut() and .qcut() functions in Python are used for dividing continuous data into "bins". These two functions allow for you to group data points, which is useful for analyzing trends or patterns within those groups.

With the ice_cream dataset, use .qcut() with q=4 to classify the four rating ranges. Apply labels to define each range:

For this question, read in the 1997 flights subset dataset as my_flights. This dataset has the same structure as the 2006 flights data we used earlier, but it only includes flights from the year 1997.

In this data, the DepTime column tracks what time each flight departs. This column does not display time like we would expect, instead using a range from 1 (00:01am) to 2400 (midnight). There are a lot of different values in this column.

One way to make this data more readable is to add some set ranges using the .cut() function. Like before with the ice cream data, this would allow us to analyze the data based on a smaller number of sets rather than each individual time.

Build a .cut() function to break the DepTime column up into sections. Display the value counts of this to get each groups' number of occurrences.

Add a corresponding label to each bin of DepTime. For an example of a full set of labels you could use: "Late Night", "Early Morning", "Morning", "Late Morning", "Afternoon", "Early Evening", "Evening", "Night".

Save the DepTime bins and their labels as the new column depart_times and print the value counts of depart_times.

Using the same method you used to create depart_times, break ArrTime into bins with labels, and create the column arrival_times.

Still within the 1997 flights dataset, there are the columns Month, DayOfWeek, and AirTime. Something interesting we could find is how the day of the week and the month out of the year affect the total air time.

Use the form df[df["col"] == value] to make a saved selection of rows for a specific month. For example:

View the value counts of the DayOfWeek column for this month (march in our example). As expected, the counts of the flights for each day of this month are all within reasonable range of each other.

Do this again, just on all of the months (together) from the original dataframe.

To view the total AirTime for each DayOfWeek, use the .groupby() method. You should have 7 value categories

Using .groupby(), we are able to create a more complicated dataframe, where we choose an x-axis, a y-axis, and for what values the cells in the table are being calculated. In this case, use AirTime as the values, and show Month and DayOfWeek on the axes, as following:

Create a second dataframe using the same example code, but calculate the mean rather than the sum.