For those that are manipulating data using the Pandas package in Python, it might be useful to turn a list of values into separate rows. Having a list of values in a column can be used to represent intersectional points in our data. For example, the different flavors of a donut, fabrics used in clothing and genres of a movie. However, when analyzing this data to find the count, average or other aggregate it is useful to expand the list with a row per element.

Expanding lists allows us to get a more accurate representation of the data overall. However, this will cause fanout and the overall count and aggregates will be inaccurate. Once this caveat is made known to your audience, then the data will be interpretable.

For this walkthrough I will be using an IMDB dataset — title.basics.tsv.gz.
Which you can find here, if you want to follow along. In this walkthrough I will be focusing on expanding the genres section for the movies, so that it can be used for tallying up total genre counts.

I will take you step-by-step as we first get a quick snippet of the table, and how we clean and later transform the data. I am assuming that Pandas is installed already on your device, if not there are other walkthroughs already created for this purpose — including this post!

Preview the data

First, let’s take a quick preview into our data. We will do this by using the Pandas built-in head() function.

df_imdb_title_basics.head(2)

Next, we will further look into the data and see if there are any nulls or other anomalies that need to be corrected. Let’s use the built-in info() function to detect nulls already in nan form.

df_imdb_title_basics.info()

Here we can see that there are 5408 nulls in the genre column. Since this is our area of interest and the nulls account for less than 5% of the original table, then we can safely remove these.

Dealing with nulls and other possible null values

We will also be looking at missing data that could be expressed as a value other than null. For example, values that could be indicative of missing data include the following:

• Impossible values
• Null categories
• 0’s where not logical

For this we will look at the value counts of all distinct values in each column of the DataFrame (df). The results below are truncated, showing only one column.

for column in df_imdb_title_basics.columns:
    print(df_imdb_title_basics[column].value_counts())
    
print(df_imdb_title_basics.info())
df_imdb_title_basics.describe()

Here we can see the value counts for each genre, that some of these genres are part of a combination of genres and that some are TV shows and not movies. Since, there are only a few odd values present in the dataset, we won’t correct for these, as it doesn’t affect our calculations. We will continue by dropping null values (we will be renaming our working df as well, just in case we would need to recall the original df).

# Copying df into a new df object
df_imdb_title_genre = df_imdb_title_basics.copy()

# Removing nulls 
df_imdb_title_genre.dropna(subset=["genres"],inplace=True)

# Verify new df 
print(df_imdb_title_genre.info())
df_imdb_title_genre.head(2)

We can see that the new number of rows is based on the original count of the non-null objects in the genre column. Now we can expand this column to get the count of genres that were part of multiple genre categories.

Column value expansion

# Converting genre column into a list 
df_imdb_title_genre["genres"] =  ["genres"].str.split(",")

# Again copying into another df to preserve if needed later
df_imdb_title_genre_expanded = df_imdb_title_genre.copy()

# Expand list into rows
rows = [] # Initiate list
# Use lambda function to create a new row per genre in lists
_ = df_imdb_title_genre_expanded.apply(lambda row: [rows.append([row['tconst'], row['primary_title'], row["original_title"], row["start_year"], row["runtime_minutes"], genre]) 
                         for genre in row.genres], axis=1)

# Set new df to a DataFrame using the expanded rows df_imdb_title_genre_expanded = pd.DataFrame(rows, columns=df_imdb_title_genre_expanded.columns)

This give us the above, and we can see that the title ‘Sunghursh’ has had its list expanded into rows.

I hope this short post has been useful in learning how to clean and expand data with values in lists.

To recap we looked as null values and potential null values, then expanded a list within a column into rows by using the underscore as a variable placeholder and a lambda function to create a new row based on the values found within the list.

Please take some time to try the above exercises, either using the same dataset or another of your choosing.