<jupyter_start><jupyter_text>Inner join   What column to merge on?  First exerciseChicago provides a list of taxicab owners and vehicles licensed to operate within the city, for public safety. Your goal is to merge two tables together. One table is called taxi_owners, with info about the taxi cab company owners, and one is called taxi_veh, with info about each taxi cab vehicle. Both the taxi_owners and taxi_veh tables have been loaded for you and you can explore them in the console.Choose the column you would use to merge the two tables on using the .merge() method.Answer - on='vid'  Your first inner joinYou have been tasked with figuring out what the most popular types of fuel used in Chicago taxis are. To complete the analysis, you need to merge the taxi_owners and taxi_veh tables together on the vid column. You can then use the merged table along with the .value_counts() method to find the most common fuel_type.Since you'll be working with pandas throughout the course, the package will be preloaded for you as pd in each exercise in this course. Also the taxi_owners and taxi_veh DataFrames are loaded for you. 1Merge taxi_owners with taxi_veh on the column vid, and save the result to taxi_own_veh.<jupyter_code># Merge the taxi_owners and taxi_veh tables
taxi_own_veh = taxi_owners.merge(taxi_veh, on = "vid")

# Print the column names of the taxi_own_veh
print(taxi_own_veh.columns)<jupyter_output><empty_output><jupyter_text>2Set the left and right table suffixes for overlapping columns of the merge to _own and _veh, respectively.<jupyter_code># Merge the taxi_owners and taxi_veh tables setting a suffix
taxi_own_veh = taxi_owners.merge(taxi_veh, on='vid', suffixes=("_own", "_veh"))

# Print the column names of taxi_own_veh
print(taxi_own_veh.columns)<jupyter_output><empty_output><jupyter_text>3Select the fuel_type column from taxi_own_veh and print the value_counts() to find the most popular fuel_types used.<jupyter_code># Merge the taxi_owners and taxi_veh tables setting a suffix
taxi_own_veh = taxi_owners.merge(taxi_veh, on='vid', suffixes=('_own','_veh'))

# Print the value_counts to find the most popular fuel_type
print(taxi_own_veh['fuel_type'].value_counts())<jupyter_output><empty_output><jupyter_text>Inner joins and number of rows returnedAll of the merges you have studied to this point are called inner joins. It is necessary to understand that inner joins only return the rows with matching values in both tables. You will explore this further by reviewing the merge between the wards and census tables, then comparing it to merges of copies of these tables that are slightly altered, named wards_altered, and census_altered. The first row of the wards column has been changed in the altered tables. You will examine how this affects the merge between them. The tables have been loaded for you.For this exercise, it is important to know that the wards and census tables start with 50 rows.  1Merge wards and census on the ward column and save the result to wards_census.<jupyter_code># Merge the wards and census tables on the ward column
wards_census = wards.merge(census, on = "ward")

# Print the shape of wards_census
print('wards_census table shape:', wards_census.shape)<jupyter_output><empty_output><jupyter_text>2Merge the wards_altered and census tables on the ward column, and notice the difference in returned rows.<jupyter_code># In the ward column change '1' to '61'
wards_altered = wards
wards_altered.loc[wards['ward'] == '1', "ward"] = '61'

# Merge the wards and census tables on the ward column
wards_altered_census = wards_altered.merge(census, on='ward')

# Print the shape of wards_census
print(wards_altered[['ward']].head())<jupyter_output><empty_output><jupyter_text>3Merge the wards and census_altered tables on the ward column, and notice the difference in returned rows.<jupyter_code># Change '1' to None in `ward` col
census_altered = census
census_altered.loc[census['ward'] == '1', "ward"] = None

# Merge the wards and census tables on the ward column
wards_census_altered = wards.merge(census_altered, on='ward')

# Print the shape of wards_census
print(census_altered[['ward']].head())<jupyter_output><empty_output><jupyter_text>One-to-many mergeA business may have one or multiple owners. In this exercise, you will continue to gain experience with one-to-many merges by merging a table of business owners, called biz_owners, to the licenses table. Recall from the video lesson, with a one-to-many relationship, a row in the left table may be repeated if it is related to multiple rows in the right table. In this lesson, you will explore this further by finding out what is the most common business owner title. (i.e., secretary, CEO, or vice president)The licenses and biz_owners DataFrames are loaded for you.  1Starting with the licenses table on the left, merge it to the biz_owners table on the column account, and save the results to a variable named licenses_owners.<jupyter_code># Merge the licenses and biz_owners table on account
licenses_owners = licenses.merge(biz_owners, on = "account")

# Group the results by title then count the number of accounts
counted_df = licenses_owners.groupby("title").agg({'account':'count'})

# Sort the counted_df in desending order
sorted_df = counted_df.sort_values(by = ["account"], ascending = False)

# Use .head() method to print the first few rows of sorted_df
print(sorted_df.head())<jupyter_output><empty_output><jupyter_text>Total riders in a monthYour goal is to find the total number of rides provided to passengers passing through the Wilson station (station_name == 'Wilson') when riding Chicago's public transportation system on weekdays (day_type == 'Weekday') in July (month == 7). Luckily, Chicago provides this detailed data, but it is in three different tables. You will work on merging these tables together to answer the question. This data is different from the business related data you have seen so far, but all the information you need to answer the question is below.The cal, ridership, and stations DataFrames have been loaded for you. The relationship between the tables can be seen in the diagram below.  1Merge the ridership and cal tables together, starting with the ridership table on the left and save the result to the variable ridership_cal. If you code takes too long to run, your merge conditions might be incorrect.<jupyter_code># Merge the ridership and cal tables
ridership_cal = ridership.merge(cal, on = ["year", "month", "day"])<jupyter_output><empty_output><jupyter_text>2Extend the previous merge to three tables by also merging the stations table.<jupyter_code># Merge the ridership, cal, and stations tables
ridership_cal_stations = ridership.merge(cal, on=['year','month','day']) \
            				.merge(stations, on = "station_id")<jupyter_output><empty_output><jupyter_text>3Create a variable called filter_criteria to select the appropriate rows from the merged table so that you can sum the rides column.<jupyter_code># Merge the ridership, cal, and stations tables
ridership_cal_stations = ridership.merge(cal, on=['year','month','day']) \
							.merge(stations, on='station_id')

# Create a filter to filter ridership_cal_stations
filter_criteria = ((ridership_cal_stations['month'] == 7) 
                   & (ridership_cal_stations['day_type'] == "Weekday") 
                   & (ridership_cal_stations['station_name'] == "Wilson"))

# Use .loc and the filter to select for rides
print(ridership_cal_stations.loc[filter_criteria, 'rides'].sum())<jupyter_output><empty_output><jupyter_text>Three table mergeTo solidify the concept of a three DataFrame merge, practice another exercise. A reasonable extension of our review of Chicago business data would include looking at demographics information about the neighborhoods where the businesses are. A table with the median income by zip code has been provided to you. You will merge the licenses and wards tables with this new income-by-zip-code table called zip_demo.The licenses, wards, and zip_demo DataFrames have been loaded for you.**Instructions**Starting with the licenses table, merge to it the zip_demo table on the zip column. Then merge the resulting table to the wards table on the ward column. Save result of the three merged tables to a variable named licenses_zip_ward.Group the results of the three merged tables by the column alderman and find the median income.<jupyter_code># Merge licenses and zip_demo, on zip; and merge the wards on ward
licenses_zip_ward = licenses.merge(zip_demo, on = "zip") \
            			.merge(wards, on = "ward")

# Print the results by alderman and show median income
print(licenses_zip_ward.groupby("alderman").agg({'income':'median'}))<jupyter_output><empty_output><jupyter_text>One-to-many merge with multiple tablesIn this exercise, assume that you are looking to start a business in the city of Chicago. Your perfect idea is to start a company that uses goats to mow the lawn for other businesses. However, you have to choose a location in the city to put your goat farm. You need a location with a great deal of space and relatively few businesses and people around to avoid complaints about the smell. You will need to merge three tables to help you choose your location. The land_use table has info on the percentage of vacant land by city ward. The census table has population by ward, and the licenses table lists businesses by ward.The land_use, census, and licenses tables have been loaded for you.  1Merge land_use and census on the ward column. Merge the result of this with licenses on the ward column, using the suffix _cen for the left table and _lic for the right table. Save this to the variable land_cen_lic.<jupyter_code># Merge land_use and census and merge result with licenses including suffixes
land_cen_lic = land_use.merge(census, on = "ward") \
        .merge(licenses, on = "ward", suffixes = ("_cen", "_lic"))<jupyter_output><empty_output><jupyter_text>2Group land_cen_lic by ward, pop_2010 (the population in 2010), and vacant, then count the number of accounts. Save the results to pop_vac_lic.<jupyter_code># Merge land_use and census and merge result with licenses including suffixes
land_cen_lic = land_use.merge(census, on='ward') \
                    .merge(licenses, on='ward', suffixes=('_cen','_lic'))

# Group by ward, pop_2010, and vacant, then count the # of accounts
pop_vac_lic = land_cen_lic.groupby(["ward", "pop_2010", "vacant"], 
                                   as_index=False).agg({'account':'count'})<jupyter_output><empty_output><jupyter_text>3Sort pop_vac_lic by vacant, account, andpop_2010 in descending, ascending, and ascending order respectively. Save it as sorted_pop_vac_lic.<jupyter_code># Merge land_use and census and merge result with licenses including suffixes
land_cen_lic = land_use.merge(census, on='ward') \
                    .merge(licenses, on='ward', suffixes=('_cen','_lic'))

# Group by ward, pop_2010, and vacant, then count the # of accounts
pop_vac_lic = land_cen_lic.groupby(['ward','pop_2010','vacant'], 
                                   as_index=False).agg({'account':'count'})

# Sort pop_vac_lic and print the results
sorted_pop_vac_lic = pop_vac_lic.sort_values(by = ["vacant", "account", "pop_2010"], 
                                             ascending=[False, True, True])

# Print the top few rows of sorted_pop_vac_lic
print(sorted_pop_vac_lic.head())<jupyter_output><empty_output><jupyter_text>Merging tables with different join types  Counting missing rows with left joinThe Movie Database is supported by volunteers going out into the world, collecting data, and entering it into the database. This includes financial data, such as movie budget and revenue. If you wanted to know which movies are still missing data, you could use a left join to identify them. Practice using a left join by merging the movies table and the financials table.The movies and financials tables have been loaded for you.  2Merge the movies table, as the left table, with the financials table using a left join, and save the result to movies_financials.<jupyter_code># Merge movies and financials with a left join
movies_financials = movies.merge(financials, on = 'id', how = 'left')<jupyter_output><empty_output><jupyter_text>3Count the number of rows in movies_financials with a null value in the budget column.<jupyter_code># Merge the movies table with the financials table with a left join
movies_financials = movies.merge(financials, on='id', how='left')

# Count the number of rows in the budget column that are missing
number_of_missing_fin = movies_financials['budget'].isnull().sum()

# Print the number of movies missing financials
print(number_of_missing_fin)<jupyter_output><empty_output><jupyter_text>Enriching a datasetSetting how='left' with the .merge()method is a useful technique for enriching or enhancing a dataset with additional information from a different table. In this exercise, you will start off with a sample of movie data from the movie series Toy Story. Your goal is to enrich this data by adding the marketing tag line for each movie. You will compare the results of a left join versus an inner join.The toy_story DataFrame contains the Toy Story movies. The toy_story and taglines DataFrames have been loaded for you.  1Merge toy_story and taglines on the id column with a left join, and save the result as toystory_tag.<jupyter_code># Merge the toy_story and taglines tables with a left join
toystory_tag = toy_story.merge(taglines, on = 'id', how = 'left')

# Print the rows and shape of toystory_tag
print(toystory_tag)
print(toystory_tag.shape)<jupyter_output><empty_output><jupyter_text>2With toy_story as the left table, merge to it taglines on the id column with an inner join, and save as toystory_tag.<jupyter_code># Merge the toy_story and taglines tables with a inner join
toystory_tag = toy_story.merge(taglines, on = "id")

# Print the rows and shape of toystory_tag
print(toystory_tag)
print(toystory_tag.shape)<jupyter_output><empty_output><jupyter_text>How many rows with a left join?Select the true statement about left joins.Try running the following code statements in the console.left_table.merge(one_to_one, on='id', how='left').shapeleft_table.merge(one_to_many, on='id', how='left').shapeNote that the left_table starts out with 4 rows.**Answer**The output of a one-to-many merge with a left join will have greater than or equal rows than the left table.  Right join to find unique moviesMost of the recent big-budget science fiction movies can also be classified as action movies. You are given a table of science fiction movies called scifi_movies and another table of action movies called action_movies. Your goal is to find which movies are considered only science fiction movies. Once you have this table, you can merge the movies table in to see the movie names. Since this exercise is related to science fiction movies, use a right join as your superhero power to solve this problem.The movies, scifi_movies, and action_movies tables have been loaded for you.  1Merge action_movies and scifi_movies tables with a right join on movie_id. Save the result as action_scifi.<jupyter_code># Merge action_movies to scifi_movies with right join
action_scifi = action_movies.merge(scifi_movies, on = 'movie_id', how = "right")<jupyter_output><empty_output><jupyter_text>2Update the merge to add suffixes, where '_act' and '_sci' are suffixes for the left and right tables, respectively.<jupyter_code># Merge action_movies to scifi_movies with right join
action_scifi = action_movies.merge(scifi_movies, on='movie_id', how='right',
                                   suffixes = ('_act', '_sci'))

# Print the first few rows of action_scifi to see the structure
print(action_scifi.head())<jupyter_output><empty_output><jupyter_text>3From action_scifi, subset only the rows where the genre_act column is null.<jupyter_code># Merge action_movies to the scifi_movies with right join
action_scifi = action_movies.merge(scifi_movies, on='movie_id', how='right',
                                   suffixes=('_act','_sci'))

# From action_scifi, select only the rows where the genre_act column is null
scifi_only = action_scifi[action_scifi["genre_act"].isnull()]<jupyter_output><empty_output><jupyter_text>4Merge movies and scifi_only using the id column in the left table and the movie_id column in the right table with an inner join.<jupyter_code># Merge action_movies to the scifi_movies with right join
action_scifi = action_movies.merge(scifi_movies, on='movie_id', how='right',
                                   suffixes=('_act','_sci'))

# From action_scifi, select only the rows where the genre_act column is null
scifi_only = action_scifi[action_scifi['genre_act'].isnull()]

# Merge the movies and scifi_only tables with an inner join
movies_and_scifi_only = movies.merge(scifi_only, left_on = "id", right_on = "movie_id")

# Print the first few rows and shape of movies_and_scifi_only
print(movies_and_scifi_only.head())
print(movies_and_scifi_only.shape)<jupyter_output><empty_output><jupyter_text>Popular genres with right joinWhat are the genres of the most popular movies? To answer this question, you need to merge data from the movies and movie_to_genres tables. In a table called pop_movies, the top 10 most popular movies in the movies table have been selected. To ensure that you are analyzing all of the popular movies, merge it with the movie_to_genres table using a right join. To complete your analysis, count the number of different genres. Also, the two tables can be merged by the movie ID. However, in pop_movies that column is called id, and in movies_to_genres it's called movie_id.The pop_movies and movie_to_genres tables have been loaded for you.**Instructions**Merge movie_to_genres and pop_movies using a right join. Save the results as genres_movies.Group genres_movies by genre and count the number of id values.<jupyter_code># Use right join to merge the movie_to_genres and pop_movies tables
genres_movies = movie_to_genres.merge(pop_movies, how='right', 
                                      left_on = "movie_id", 
                                      right_on = "id")

# Count the number of genres
genre_count = genres_movies.groupby('genre').agg({'id':'count'})

# Plot a bar chart of the genre_count
genre_count.plot(kind='bar')
plt.show()<jupyter_output><empty_output><jupyter_text>Using outer join to select actorsOne cool aspect of using an outer join is that, because it returns all rows from both merged tables and null where they do not match, you can use it to find rows that do not have a match in the other table. To try for yourself, you have been given two tables with a list of actors from two popular movies: Iron Man 1 and Iron Man 2. Most of the actors played in both movies. Use an outer join to find actors who did not act in both movies.The Iron Man 1 table is called iron_1_actors, and Iron Man 2 table is called iron_2_actors. Both tables have been loaded for you and a few rows printed so you can see the structure.**Instructions**Save to iron_1_and_2 the merge of iron_1_actors (left) with iron_2_actors tables with an outer join on the id column, and set suffixes to ('_1','_2').Create an index that returns True if name_1 or name_2 are null, and False otherwise.<jupyter_code># Merge iron_1_actors to iron_2_actors on id with outer join using suffixes
iron_1_and_2 = iron_1_actors.merge(iron_2_actors,
                                     on = "id",
                                     how = "outer",
                                     suffixes=("_1", "_2"))

# Create an index that returns true if name_1 or name_2 are null
m = ((iron_1_and_2['name_1'].isnull()) | 
     (iron_1_and_2['name_2'].isnull()))

# Print the first few rows of iron_1_and_2
print(iron_1_and_2[m].head())<jupyter_output><empty_output><jupyter_text>Self joinMerging a table to itself can be useful when you want to compare values in a column to other values in the same column. In this exercise, you will practice this by creating a table that for each movie will list the movie director and a member of the crew on one row. You have been given a table called crews, which has columns id, job, and name. First, merge the table to itself using the movie ID. This merge will give you a larger table where for each movie, every job is matched against each other. Then select only those rows with a director in the left table, and avoid having a row where the director's job is listed in both the left and right tables. This filtering will remove job combinations that aren't with the director.The crews table has been loaded for you.  1To a variable called crews_self_merged, merge the crews table to itself on the id column using an inner join, setting the suffixes to '_dir' and '_crew' for the left and right tables respectively.<jupyter_code># Merge the crews table to itself
crews_self_merged = crews.merge(crews, on = "id", suffixes = ('_dir', '_crew'))<jupyter_output><empty_output><jupyter_text>2Create a Boolean index, named boolean_filter, that selects rows from the left table with the job of 'Director' and avoids rows with the job of 'Director' in the right table.<jupyter_code># Merge the crews table to itself
crews_self_merged = crews.merge(crews, on='id', how='inner',
                                suffixes=('_dir','_crew'))

# Create a Boolean index to select the appropriate
boolean_filter = ((crews_self_merged['job_dir'] == "Director") & 
     (crews_self_merged['job_crew'] != "Director"))
direct_crews = crews_self_merged[boolean_filter]<jupyter_output><empty_output><jupyter_text>3Use the .head() method to print the first few rows of direct_crews.<jupyter_code># Merge the crews table to itself
crews_self_merged = crews.merge(crews, on='id', how='inner',
                                suffixes=('_dir','_crew'))

# Create a boolean index to select the appropriate rows
boolean_filter = ((crews_self_merged['job_dir'] == 'Director') & 
                  (crews_self_merged['job_crew'] != 'Director'))
direct_crews = crews_self_merged[boolean_filter]

# Print the first few rows of direct_crews
print(direct_crews.head())<jupyter_output><empty_output><jupyter_text>Index merge for movie ratingsTo practice merging on indexes, you will merge movies and a table called ratings that holds info about movie ratings. Make sure your merge returns all of the rows from the movies table and not all the rows of ratings table need to be included in the result.The movies and ratings tables have been loaded for you.**Instructions**Merge movies and ratings on the index and save to a variable called movies_ratings, ensuring that all of the rows from the movies table are returned.<jupyter_code># Merge to the movies table the ratings table on the index
movies_ratings = movies.merge(ratings, on = 'id')

# Print the first few rows of movies_ratings
print(movies_ratings.head())<jupyter_output><empty_output><jupyter_text>Do sequels earn more?It is time to put together many of the aspects that you have learned in this chapter. In this exercise, you'll find out which movie sequels earned the most compared to the original movie. To answer this question, you will merge a modified version of the sequels and financials tables where their index is the movie ID. You will need to choose a merge type that will return all of the rows from the sequels table and not all the rows of financials table need to be included in the result. From there, you will join the resulting table to itself so that you can compare the revenue values of the original movie to the sequel. Next, you will calculate the difference between the two revenues and sort the resulting dataset.The sequels and financials tables have been provided.  1With the sequels table on the left, merge to it the financials table on index named id, ensuring that all the rows from the sequels are returned and some rows from the other table may not be returned, Save the results to sequels_fin.<jupyter_code># Merge sequels and financials on index id
sequels_fin = sequels.merge(financials, on = 'id', how = 'left')<jupyter_output><empty_output><jupyter_text>2Merge the sequels_fin table to itself with an inner join, where the left and right tables merge on sequel and id respectively with suffixes equal to ('_org','_seq'), saving to orig_seq.<jupyter_code># Merge sequels and financials on index id
sequels_fin = sequels.merge(financials, on='id', how='left')

# Self merge with suffixes as inner join with left on sequel and right on id
orig_seq = sequels_fin.merge(sequels_fin, how="inner", left_on="sequel", 
                             right_on="id", right_index=True,
                             suffixes=('_org','_seq'))

# Add calculation to subtract revenue_org from revenue_seq 
orig_seq['diff'] = orig_seq['revenue_seq'] - orig_seq['revenue_org']<jupyter_output><empty_output><jupyter_text>3Select the title_org, title_seq, and diff columns of orig_seq and save this as titles_diff.<jupyter_code># Merge sequels and financials on index id
sequels_fin = sequels.merge(financials, on='id', how='left')

# Self merge with suffixes as inner join with left on sequel and right on id
orig_seq = sequels_fin.merge(sequels_fin, how='inner', left_on='sequel', 
                             right_on='id', right_index=True,
                             suffixes=('_org','_seq'))

# Add calculation to subtract revenue_org from revenue_seq 
orig_seq['diff'] = orig_seq['revenue_seq'] - orig_seq['revenue_org']

# Select the title_org, title_seq, and diff 
titles_diff = orig_seq[["title_org", "title_seq", "diff"]]<jupyter_output><empty_output><jupyter_text>4Sort by titles_diff by diff in descending order and print the first few rows.<jupyter_code># Merge sequels and financials on index id
sequels_fin = sequels.merge(financials, on='id', how='left')

# Self merge with suffixes as inner join with left on sequel and right on id
orig_seq = sequels_fin.merge(sequels_fin, how='inner', left_on='sequel', 
                             right_on='id', right_index=True,
                             suffixes=('_org','_seq'))

# Add calculation to subtract revenue_org from revenue_seq 
orig_seq['diff'] = orig_seq['revenue_seq'] - orig_seq['revenue_org']

# Select the title_org, title_seq, and diff 
titles_diff = orig_seq[['title_org','title_seq','diff']]

# Print the first rows of the sorted titles_diff
print(titles_diff.sort_values(by = "diff", ascending = False).head())<jupyter_output><empty_output><jupyter_text>Advanced Merging and Concatenating  Steps of a semi-joinIn the last video, you were shown how to perform a semi-join with pandas. In this exercise, you'll solidify your understanding of the necessary steps. Recall that a semi-join filters the left table to only the rows where a match exists in both the left and right tables.**Instructions**Sort the steps in the correct order of the technique shown to perform a semi-join in pandas.**Answer**Merge the left and right tables on key column using an inner-join.Search if the key column in the left table is in the merged tables using the .isin() method creating a Boolean Series.Subset the rows of the left table.<jupyter_code># Example
genres_tracks = genres.merge(top_tracks, on='gid', how='left', indicator=True)
gid_list = genres_tracks.loc[genres_tracks['_merge'] == 'left_only','gid']
non_top_genres = genres[genres['gid'].isin(gid_list)]
print(non_top_genres.head())<jupyter_output><empty_output><jupyter_text>Performing and anti-joinIn our music streaming company dataset, each customer is assigned an employee representative to assist them. In this exercise, filter the employee table by a table of top customers, returning only those employees who are not assigned to a customer. The results should resemble the results of an anti-join. The company's leadership will assign these employees additional training so that they can work with high valued customers.The top_cust and employees tables have been provided for you.  1Merge employees and top_cust with a left join, setting indicator argument to True. Save the result to empl_cust.<jupyter_code># Merge employees and top_cust
empl_cust = employees.merge(top_cust, on="srid", 
                            how="left", indicator=True)<jupyter_output><empty_output><jupyter_text>4Select the srid column of empl_cust and the rows where _merge is 'left_only'. Save the result to srid_list.<jupyter_code># Select the srid column where _merge is left_only
srid_list = empl_cust.loc[empl_cust["_merge"] == "left_only", 'srid']<jupyter_output><empty_output><jupyter_text>3Subset the employees table and select those rows where the srid is in the variable srid_list and print the results.<jupyter_code># Get employees not working with top customers
print(employees[employees["srid"].isin(srid_list)])<jupyter_output><empty_output><jupyter_text>Performing a semi-joinSome of the tracks that have generated the most significant amount of revenue are from TV-shows or are other non-musical audio. You have been given a table of invoices that include top revenue-generating items. Additionally, you have a table of non-musical tracks from the streaming service. In this exercise, you'll use a semi-join to find the top revenue-generating non-musical tracks..The tables non_mus_tcks, top_invoices, and genres have been loaded for you.**Instructions**- Merge non_mus_tcks and top_invoices on tid using an inner join. Save the result as tracks_invoices.- Use .isin() to subset the rows of non_mus_tck where tid is in the tid column of tracks_invoices. Save the result as top_tracks.- Group top_tracks by gid and count the tid rows. Save the result to cnt_by_gid.- Merge cnt_by_gid with the genres table on gid and print the result.<jupyter_code># Merge the non_mus_tcks and top_invoices tables on tid
tracks_invoices = non_mus_tcks.merge(top_invoices, on='tid')

# Use .isin() to subset non_mus_tcks to rows with tid in tracks_invoices
top_tracks = non_mus_tcks[non_mus_tcks['tid'].isin(tracks_invoices['tid'])]

# Group the top_tracks by gid and count the tid rows
cnt_by_gid = top_tracks.groupby(['gid'], as_index=False).agg({'tid':'count'})

# Merge the genres table to cnt_by_gid on gid and print
print(cnt_by_gid.merge(genres, on='gid'))<jupyter_output><empty_output><jupyter_text>Merging Ordered and Time-Series DataIn this final chapter, you’ll step up a gear and learn to apply pandas' specialized methods for merging time-series and ordered data together with real-world financial and economic data from the city of Chicago. You’ll also learn how to query resulting tables using a SQL-style format, and unpivot data using the melt method.  Sumary.merge() method- Columns to join on:** on, left_on, right_on- Type of join** how (left, right, inner, outer)** **default** inner- Overlapping column names** suffixes- Calling the method** df1.merge(df2)- fill_method** Forward fill: ffillDifferents with merge_ordered() method:- **Default** type of join: outer.- Calling the function: pd.merge_ordered(df1, df2)  Correlation between GDP and S&P500In this exercise, you want to analyze stock returns from the S&P 500. You believe there may be a relationship between the returns of the S&P 500 and the GDP of the US. Merge the different datasets together to compute the correlation.Two tables have been provided for you, named sp500, and gdp. As always, pandas has been imported for you as pd.  1- Use merge_ordered() to merge gdp and sp500 using a left join on year and date. Save the results as gdp_sp500.- Print gdp_sp500 and look at the returns for the year 2018.<jupyter_code># Use merge_ordered() to merge gdp and sp500 on year and date
gdp_sp500 = pd.merge_ordered(gdp, sp500, left_on="year", right_on="date", 
                             how="left")

# Print gdp_sp500
print(gdp_sp500)<jupyter_output><empty_output><jupyter_text>2Use merge_ordered(), again similar to before, to merge gdp and sp500 use the function's ability to interpolate missing data to forward fill the missing value for returns, assigning this table to the variable gdp_sp500.<jupyter_code># Use merge_ordered() to merge gdp and sp500, interpolate missing value
gdp_sp500 = pd.merge_ordered(gdp, sp500, left_on="year", right_on="date", 
                             how="left", fill_method="ffill")


# Print gdp_sp500
print (gdp_sp500)<jupyter_output><empty_output><jupyter_text>3- Subset the gdp_sp500 table, select the gdp and returns columns, and save as gdp_returns.- Print the correlation matrix of the gdp_returns table.<jupyter_code># Use merge_ordered() to merge gdp and sp500, interpolate missing value
gdp_sp500 = pd.merge_ordered(gdp, sp500, left_on='year', right_on='date', 
                             how='left',  fill_method='ffill')

# Subset the gdp and returns columns
gdp_returns = gdp_sp500[["gdp", "returns"]]

# Print gdp_returns correlation
print (gdp_returns.corr())<jupyter_output><empty_output><jupyter_text>Phillips curve using merge_ordered()There is an economic theory developed by <NAME> which states that inflation and unemployment have an inverse relationship. The theory claims that with economic growth comes inflation, which in turn should lead to more jobs and less unemployment.You will take two tables of data from the U.S. Bureau of Labor Statistics, containing unemployment and inflation data over different periods, and create a Phillips curve. The tables have different frequencies. One table has a data entry every six months, while the other has a data entry every month. You will need to use the entries where you have data within both tables.The tables unemployment and inflation have been loaded for you.**Instructions**Use merge_ordered() to merge the inflation and unemployment tables on date with an inner join, and save the results as inflation_unemploy.Print the inflation_unemploy variable.Using inflation_unemploy, create a scatter plot with unemployment_rate on the horizontal axis and cpi (inflation) on the vertical axis.<jupyter_code># Use merge_ordered() to merge inflation, unemployment with inner join
inflation_unemploy = pd.merge_ordered(inflation, unemployment, 
on='date',
how='inner' )

# Print inflation_unemploy 
print(inflation_unemploy)

# Plot a scatter plot of unemployment_rate vs cpi of inflation_unemploy
inflation_unemploy.plot(kind='scatter', x='unemployment_rate', y='cpi')
plt.show()<jupyter_output><empty_output><jupyter_text>merge_ordered() caution, multiple columnsWhen using merge_ordered() to merge on multiple columns, the order is important when you combine it with the forward fill feature. The function sorts the merge on columns in the order provided. In this exercise, we will merge GDP and population data from the World Bank for the Australia and Sweden, reversing the order of the merge on columns. The frequency of the series are different, the GDP values are quarterly, and the population is yearly. Use the forward fill feature to fill in the missing data. Depending on the order provided, the fill forward will use unintended data to fill in the missing values.The tables gdp and pop have been loaded.  1Use merge_ordered() on gdp and pop, merging on columns date and country with the fill feature, save to ctry_date.<jupyter_code># Merge gdp and pop on date and country with fill and notice rows 2 and 3
ctry_date = pd.merge_ordered(gdp, pop, 
                            on=['date', 'country'],
                             fill_method='ffill')

# Print ctry_date
print(ctry_date)<jupyter_output><empty_output><jupyter_text>2Perform the same merge of gdp and pop, but join on country and date (reverse of step 1) with the fill feature, saving this as date_ctry.<jupyter_code># Merge gdp and pop on country and date with fill
date_ctry = pd.merge_ordered(gdp, pop, 
                            on=['country', 'date'],
                             fill_method='ffill')

# Print date_ctry
print(date_ctry)<jupyter_output><empty_output><jupyter_text>Using merge_asof() to study stocksYou have a feed of stock market prices that you record. You attempt to track the price every five minutes. Still, due to some network latency, the prices you record are roughly every 5 minutes. You pull your price logs for three banks, JP Morgan (JPM), Wells Fargo (WFC), and Bank Of America (BAC). You want to know how the price change of the two other banks compare to JP Morgan. Therefore, you will need to merge these three logs into one table. Afterward, you will use the pandas .diff() method to compute the price change over time. Finally, plot the price changes so you can review your analysis.The three log files have been loaded for you as tables named jpm, wells, and bac.**Instructions**- Use merge_asof() to merge jpm (left table) and wells together on the date_time column, where the rows with the nearest times are matched, and with suffixes=('', '_wells'). Save to jpm_wells.- Use merge_asof() to merge jpm_wells (left table) and bac together on the date_time column, where the rows with the closest times are matched, and with suffixes=('_jpm', '_bac'). Save to jpm_wells_bac.- Using price_diffs, create a line plot of the close price of JPM, WFC, and BAC only.<jupyter_code># Use merge_asof() to merge jpm and wells
jpm_wells = pd.merge_asof(jpm, wells, 
                            on='date_time' ,
                            suffixes=('', '_wells'),
                            direction='nearest'
)


# Use merge_asof() to merge jpm_wells and bac
jpm_wells_bac = pd.merge_asof(jpm_wells, bac, 
                            on='date_time' ,
                            suffixes=('_jpm', '_bac'),
                            direction='nearest'
)


# Compute price diff
price_diffs = jpm_wells_bac.diff()

# Plot the price diff of the close of jpm, wells and bac only
price_diffs.plot(y=['close_jpm', 'close_wells', 'close_bac'])
plt.show()<jupyter_output><empty_output><jupyter_text>Using merge_asof() to create datasetThe merge_asof() function can be used to create datasets where you have a table of start and stop dates, and you want to use them to create a flag in another table. You have been given gdp, which is a table of quarterly GDP values of the US during the 1980s. Additionally, the table recession has been given to you. It holds the starting date of every US recession since 1980, and the date when the recession was declared to be over. Use merge_asof() to merge the tables and create a status flag if a quarter was during a recession. Finally, to check your work, plot the data in a bar chart.The tables gdp and recession have been loaded for you.**Instructions**- Using merge_asof(), merge gdp and recession on date, with gdp as the left table. Save to the variable gdp_recession.- Create a list using a list comprehension and a conditional expression, named is_recession, where for each row if the gdp_recession['econ_status'] value is equal to 'recession' then enter 'r' else 'g'.- Using gdp_recession, plot a bar chart of gdp versus date, setting the color argument equal to is_recession.<jupyter_code># Merge gdp and recession on date using merge_asof()
gdp_recession = pd.merge_asof(gdp, recession, on='date')

# Create a list based on the row value of gdp_recession['econ_status']
is_recession = ['r' if s=='recession' else 'g' for s in gdp_recession['econ_status']]

# Plot a bar chart of gdp_recession
gdp_recession.plot(kind='bar', y='gdp', x='date', color=is_recession, rot=90)
plt.show()<jupyter_output><empty_output><jupyter_text>Subsetting rows with .query()In this exercise, you will revisit GDP and population data for Australia and Sweden from the World Bank and expand on it using the .query() method. You'll merge the two tables and compute the GDP per capita. Afterwards, you'll use the .query() method to sub-select the rows and create a plot. Recall that you will need to merge on multiple columns in the proper order.The tables gdp and pop have been loaded for you.  1Use merge_ordered() on gdp and pop on columns country and date with the fill feature, save to gdp_pop and print.<jupyter_code># Merge gdp and pop on date and country with fill
gdp_pop = pd.merge_ordered(gdp, pop, on=['country','date'], fill_method='ffill')<jupyter_output><empty_output><jupyter_text>2Add a column named gdp_per_capita to gdp_pop that divides gdp by pop.<jupyter_code># Add a column named gdp_per_capita to gdp_pop that divides the gdp by pop
gdp_pop['gdp_per_capita'] = gdp_pop['gdp'] / gdp_pop['pop']<jupyter_output><empty_output><jupyter_text>3Pivot gdp_pop so values='gdp_per_capita', index='date', and columns='country', save as gdp_pivot.<jupyter_code># Pivot table of gdp_per_capita, where index is date and columns is country
gdp_pivot = gdp_pop.pivot_table('gdp_per_capita', 'date', 'country')<jupyter_output><empty_output><jupyter_text>4Use .query() to select rows from gdp_pivot where date is greater than equal to 1991-01-01". Save as recent_gdp_pop.<jupyter_code># Select dates equal to or greater than 1991-01-01
recent_gdp_pop = gdp_pivot.query('date >= "1991-01-01"')

# Plot recent_gdp_pop
recent_gdp_pop.plot(rot=90)
plt.show()<jupyter_output><empty_output><jupyter_text>Reshaping data with .melt()The melt method transform a wide format dataframe in a tall format.**Example**social_fin_tall = social_fin.melt(id_vars=['financial', 'company'], value_vars=['2018', '2017'], var_name=['year'], value_name='dollars']  Using .melt() to reshape government dataThe US Bureau of Labor Statistics (BLS) often provides data series in an easy-to-read format - it has a separate column for each month, and each year is a different row. Unfortunately, this wide format makes it difficult to plot this information over time. In this exercise, you will reshape a table of US unemployment rate data from the BLS into a form you can plot using .melt(). You will need to add a date column to the table and sort by it to plot the data correctly.The unemployment rate data has been loaded for you in a table called ur_wide. You are encouraged to view the table in the console before beginning the exercise.**Instructions**Use .melt() to unpivot all of the columns of ur_wide except year and ensure that the columns with the months and values are named month and unempl_rate, respectively. Save the result as ur_tall.Add a column to ur_tall named date which combines the year and month columns as year-month format into a larger string, and converts it to a date data type.Sort ur_tall by date and save as ur_sorted.Using ur_sorted, plot unempl_rate on the y-axis and date on the x-axis.<jupyter_code># unpivot everything besides the year column
ur_tall = ur_wide.melt(id_vars=['year'],
                        var_name=['month'],
                        value_name='unempl_rate')


# Create a date column using the month and year columns of ur_tall
ur_tall['date'] = pd.to_datetime(ur_tall['year'] + '-' + ur_tall['month'])

# Sort ur_tall by date in ascending order
ur_sorted = ur_tall.sort_values(by=['date'])

# Plot the unempl_rate by date
ur_sorted.plot(y='unempl_rate', x='date')
plt.show()<jupyter_output><empty_output><jupyter_text>Using .melt() for stocks vs bond performanceIt is widespread knowledge that the price of bonds is inversely related to the price of stocks. In this last exercise, you'll review many of the topics in this chapter to confirm this. You have been given a table of percent change of the US 10-year treasury bond price. It is in a wide format where there is a separate column for each year. You will need to use the .melt() method to reshape this table.Additionally, you will use the .query() method to filter out unneeded data. You will merge this table with a table of the percent change of the Dow Jones Industrial stock index price. Finally, you will plot data.The tables ten_yr and dji have been loaded for you.**Instructions**Use .melt() on ten_yr to unpivot everything except the metric column, setting var_name='date' and value_name='close'. Save the result to bond_perc.Using the .query() method, select only those rows were metric equals 'close', and save to bond_perc_close.Use merge_ordered() to merge dji (left table) and bond_perc_close on date with an inner join, and set suffixes equal to ('_dow', '_bond'). Save the result to dow_bond.Using dow_bond, plot only the Dow and bond values.<jupyter_code># Use melt on ten_yr, unpivot everything besides the metric column
bond_perc = ten_yr.melt(id_vars=['metric'],
                        var_name=['date'],
                        value_name='close'
)

# Use query on bond_perc to select only the rows where metric=close
bond_perc_close = bond_perc.query('metric == "close"')

# Merge (ordered) dji and bond_perc_close on date with an inner join
dow_bond = pd.merge_ordered(dji, bond_perc_close, on='date', how='inner', suffixes=('_dow', '_bond'))


# Plot only the close_dow and close_bond columns
dow_bond.plot(y=['close_dow', 'close_bond'], x='date', rot=90)
plt.show()<jupyter_output><empty_output>