Self join
Merging 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.
This exercise is part of the course
Joining Data with pandas
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Merge the crews table to itself
crews_self_merged = ____