One-to-many merge
A 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.
This is a part of the course
“Joining Data with pandas”
Exercise instructions
- Starting with the
licensestable on the left, merge it to thebiz_ownerstable on the columnaccount, and save the results to a variable namedlicenses_owners. - Group
licenses_ownersbytitleand count the number of accounts for each title. Save the result ascounted_df - Sort
counted_dfby the number of accounts in descending order, and save this as a variable namedsorted_df. - Use the
.head()method to print the first few rows of thesorted_df.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Merge the licenses and biz_owners table on account
licenses_owners = ____
# Group the results by title then count the number of accounts
counted_df = licenses_owners.groupby(____).agg({'account':'count'})
# Sort the counted_df in descending order
sorted_df = counted_df.sort_values(____)
# Use .head() method to print the first few rows of sorted_df
print(____)This exercise is part of the course
Joining Data with pandas
Learn to combine data from multiple tables by joining data together using pandas.
Learn how you can merge disparate data using inner joins. By combining information from multiple sources you’ll uncover compelling insights that may have previously been hidden. You’ll also learn how the relationship between those sources, such as one-to-one or one-to-many, can affect your result.
Exercise 1: Inner joinExercise 2: What column to merge on?Exercise 3: Your first inner joinExercise 4: Inner joins and number of rows returnedExercise 5: One-to-many relationshipsExercise 6: One-to-many classificationExercise 7: One-to-many mergeExercise 8: Merging multiple DataFramesExercise 9: Total riders in a monthExercise 10: Three table mergeExercise 11: One-to-many merge with multiple tablesWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.