Lecture 13: Merging DataFrames [SUGGESTED SOLUTIONS]

We will often find ourselves with variables spread across different datasets and files. We merge datasets together by matching up the two datasets on one or more variables. For example, I might have GDP data by country from the Penn World Tables, and demographic data by country from the World Bank. We would merge these two datasets and match up the observations by country.

Here's an outline of today's lecture:

1. Apartment Data from Zillow

1. Merging Two DataFrames into One DataFrame

1. Plotting Results

1. Apartment Data from Zillow ¶

We are working with data from Zillow on rental listing prices. I would like to see how studio and one bedroom prices differ from each other across cities. Unfortunately, Zillow only lets me download one series at a time. (Hey, the data are free so I'm not complaining...maybe) I have downloaded the studio and one bedroom rental listing pricing and now I would like to create one DataFrame with all the data.

Time to get to work.

A few things to note.¶

1. There are more regions in the one-bedroom data (i.e., there are more rows).
2. Date is listed as columns.

What do we want?¶

1. One DataFrame with one-bedroom and studio prices.
2. A multiIndex of regions and date on the rows (or a column for region and a column for date).
3. Columns of studio and one bedroom prices.

Make the data long¶

We could assign indexes and reshape this using stack or we can use melt and skip the indexing.

There are 17,368 observations (i..e, region-date pairs) in the studio dataframe.

Rename variables¶

Repeat for one bedroom data¶

The studio data look great. Get the one bedroom data set up.

There are 23,072 observations (i..e, region-date pairs) in the one-bedroom dataframe so there are more region-date pairs in these data.

2. Merging DataFrames into One DataFrame (top)¶

Now that we're done with the wind-up, time to do what this notebook is about: Merging two sets of data.

We want to match the two DataFrames together according to the region-date pairs. In database-ese, we refer to variables we are matching as keys and the keys, here, are region and date. We could match these keys in potentially different ways. For example, there may be several observations for a given key in one set of data that the other set. What to do then? We have to tell the computer how to join (more database-ese) the two DataFrames. There are four different options for join:

1. inner: keep the intersection of the keys

1. left: keep all the keys from the left DataFrame

1. right: keep all the keys from right DataFrame

1. outer: keep all the keys from both DataFrames

We specify the join type with the how parameter. The default is inner, but for sanity's sake, be explicit about your join.

Back to our example. We want to merge the one-bedroom and studio dataframes. Both are organized by the region and date and these keys form a unique observation for each dataframe. Let's try joining by inner which implicitly will drop any observations in one dataframe but not the other. Had we matched by outer we would have kept these regardlessof which dataframe the extra observations reside. What would left and right do?

There are only 12,669 region-date pairs in the merged data. That's less than either the studio or the one-bedroom data.

An Alternative Approach¶

So far we've been merging dataframes using the pandas package (ie, pd.merge()) but since dataframes are pandas objects, you can also merge dataframes using the method .merge(). For example, we could have done the same inner merge with the following code:

The difference is that in taking this approach we're starting with the studios data frame as our "left" and onebeds is implicitly our "right" dataframe. All other logic regarding how the merge / join works is the same.

Practice¶

Try joining by left, right, and outer. What happens to the number of observations?

Set the index to region and date¶

Back to the case where we're using the 'inner' join. Let's set the index as region and date.

Try an outer merge¶

Notice that 2010-01 is missing, even though the studio data have an entry for it. Let's try an outer join.

That makes more sense for us.

3. Plotting Results (top)¶

We now have the DataFrames combined into one object so let's start doing some exploratory data analysis (EDA) by plotting the results and looking for interesting patterns. Let's start close to home and look at what's happening to rental prices in Atlanta, GA.

It's interesting that studio rental prices exceed one bedroom prices after 2015. I wonder whyt? Note that I put the average median rental price of each line in the label using a different-looking format syntax. BTW I like putting summary statistics in labels. I think it's a nice way to convey information.

Let's also take a look at Florida. Maybe the trends are different.

Interesting...one bedroom apartments are always cheaper than studios whereas in Atlanta there was a crossing between the two somewhere in 2015. Maybe this reflects studios being located in more desirable neighborhoods?

Practice¶

I downloaded data from MovieLens. There are five files in your data folder.

1. Load the movies.csv and the ratings.csv files as DataFrames. The movies file lists movie names and genre while the ratings file includes ratings (think Amazon "stars") by different viewers. NB, both files are in a sub-folder cleverly called "MovieLens."

1. What size are the two DataFrames?

1. There are many ratings per movie. Merge the two files on the movieId. What kind of join should you use?

1. What size is the resulting DataFrame?

1. Collapse the data so you have the median rating for each movie.

1. Create a histogram of the ratings (i.e., ax.hist()). Use 10 bins. Use the density=True parameter to plot the density rather than the counts.

1. Load the tags.csv file. It's another file from MovieLens. These are topics or themes covered in each movie so each movie will contain many tags.

1. Merge it into your MovieLens data. What keys should you use? Hint: Look at the column names. What type of join?

Let's get a look at the kinds of tags we have. Try the code below, which introduces us to the value_counts() method of DataFrame.

print(df['tag'].value_counts())

I don't know what 'atmospheric' means -- sounds fancy -- while 'Heoric Bloodshed' sounds terrifying. That tag only appears once and applied to John Wick. Makes sense, I guess. Let's take a look at the tag 'superhero'.

1. Compute the average rating for all movies and for movies tagged 'superhero'.

1. Let's see how the ratings of superhero movies compare to all movies. We will plot two histograms on the same axes.
   • Plot the histogram of movies tagged 'superhero'. Use 10 bins. Make the plot blue. Set alpha=0.25.
   • Plot the histogram of all movies. Use 10 bins. Make the plot red. Set alpha=0.25.
   • Add a legend: plt.legend(). Put it in a spot that makes sense using the loc= command in plt.legend().
   • Add the mean ratings for all movies and for superhero movies as text to the histogram: ax.annotate().
   • Get rid of the top and right spines: ax.spines['top'].set_visible(False)
   • Add axes and a chart title that make sense.