Processing Anime Data
Background
We will use pyjanitor to showcase how to conveniently chain methods together to perform data cleaning in one shot. We We first define and register a series of dataframe methods with pandas_flavor. Then we chain the dataframe methods together with pyjanitor methods to complete the data cleaning process. The below example shows a one-shot script followed by a step-by-step detail of each part of the method chain.
We have adapted a TidyTuesday analysis that was originally performed in R. The original text from TidyTuesday will be shown in blockquotes.
Note: TidyTuesday is based on the principles discussed and made popular by Hadley Wickham in his paper Tidy Data.
The original text from TidyTuesday will be shown in blockquotes. Here is a description of the Anime data set that we will use.
This week's data comes from Tam Nguyen and MyAnimeList.net via Kaggle. According to Wikipedia - "MyAnimeList, often abbreviated as MAL, is an anime and manga social networking and social cataloging application website. The site provides its users with a list-like system to organize and score anime and manga. It facilitates finding users who share similar tastes and provides a large database on anime and manga. The site claims to have 4.4 million anime and 775,000 manga entries. In 2015, the site received 120 million visitors a month."
Anime without rankings or popularity scores were excluded. Producers, genre, and studio were converted from lists to tidy observations, so there will be repetitions of shows with multiple producers, genres, etc. The raw data is also uploaded.
Lots of interesting ways to explore the data this week!
Import libraries and load data
# Import pyjanitor and pandas
# Supress user warnings when we try overwriting our custom pandas flavor functions
import warnings
import janitor
import pandas as pd
import pandas_flavor as pf
warnings.filterwarnings("ignore")
One-Shot
filename = "https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2019/2019-04-23/raw_anime.csv"
df = pd.read_csv(filename)
@pf.register_dataframe_method
def str_remove(df, column_name: str, pat: str, *args, **kwargs):
"""Wrapper around df.str.replace"""
df[column_name] = df[column_name].str.replace(pat, "", *args, **kwargs)
return df
@pf.register_dataframe_method
def str_trim(df, column_name: str, *args, **kwargs):
"""Wrapper around df.str.strip"""
df[column_name] = df[column_name].str.strip(*args, **kwargs)
return df
@pf.register_dataframe_method
def explode(df: pd.DataFrame, column_name: str, sep: str):
"""
For rows with a list of values, this function will create new
rows for each value in the list
"""
df["id"] = df.index
wdf = (
pd.DataFrame(df[column_name].str.split(sep).fillna("").tolist())
.stack()
.reset_index()
)
# exploded_column = column_name
wdf.columns = ["id", "depth", column_name] # plural form to singular form
# wdf[column_name] = wdf[column_name].apply(lambda x: x.strip()) # trim
wdf.drop("depth", axis=1, inplace=True)
return pd.merge(df, wdf, on="id", suffixes=("_drop", "")).drop(
columns=["id", column_name + "_drop"]
)
@pf.register_dataframe_method
def str_word(
df,
column_name: str,
start: int = None,
stop: int = None,
pat: str = " ",
*args,
**kwargs,
):
"""
Wrapper around `df.str.split` with additional `start` and `end` arguments
to select a slice of the list of words.
"""
df[column_name] = df[column_name].str.split(pat).str[start:stop]
return df
@pf.register_dataframe_method
def str_join(df, column_name: str, sep: str, *args, **kwargs):
"""
Wrapper around `df.str.join`
Joins items in a list.
"""
df[column_name] = df[column_name].str.join(sep)
return df
@pf.register_dataframe_method
def str_slice(
df, column_name: str, start: int = None, stop: int = None, *args, **kwargs
):
"""
Wrapper around `df.str.slice
"""
df[column_name] = df[column_name].str[start:stop]
return df
clean_df = (
df.str_remove(column_name="producers", pat="\[|\]")
.explode(column_name="producers", sep=",")
.str_remove(column_name="producers", pat="'")
.str_trim("producers")
.str_remove(column_name="genre", pat="\[|\]")
.explode(column_name="genre", sep=",")
.str_remove(column_name="genre", pat="'")
.str_trim(column_name="genre")
.str_remove(column_name="studio", pat="\[|\]")
.explode(column_name="studio", sep=",")
.str_remove(column_name="studio", pat="'")
.str_trim(column_name="studio")
.str_remove(column_name="aired", pat="\{|\}|'from':\s*|'to':\s*")
.str_word(column_name="aired", start=0, stop=2, pat=",")
.str_join(column_name="aired", sep=",")
.deconcatenate_column(
column_name="aired", new_column_names=["start_date", "end_date"], sep=","
)
.remove_columns(column_names=["aired"])
.str_remove(column_name="start_date", pat="'")
.str_slice(column_name="start_date", start=0, stop=10)
.str_remove(column_name="end_date", pat="'")
.str_slice(column_name="end_date", start=0, stop=11)
.to_datetime("start_date", format="%Y-%m-%d", errors="coerce")
.to_datetime("end_date", format="%Y-%m-%d", errors="coerce")
.fill_empty(columns=["rank", "popularity"], value=0)
.filter_on("rank != 0 & popularity != 0")
)
clean_df.head()
Multi-Step
Data Dictionary
Heads up the dataset is about 97 mb - if you want to free up some space, drop the synopsis and background, they are long strings, or broadcast, premiered, related as they are redundant or less useful.
| variable | class | description |
|---|---|---|
| animeID | double | Anime ID (as in https://myanimelist.net/anime/animeID) |
| name | character | anime title - extracted from the site. |
| title_english | character | title in English (sometimes is different, sometimes is missing) |
| title_japanese | character | title in Japanese (if Anime is Chinese or Korean, the title, if available, in the respective language) |
| title_synonyms | character | other variants of the title |
| type | character | anime type (e.g. TV, Movie, OVA) |
| source | character | source of anime (i.e original, manga, game, music, visual novel etc.) |
| producers | character | producers |
| genre | character | genre |
| studio | character | studio |
| episodes | double | number of episodes |
| status | character | Aired or not aired |
| airing | logical | True/False is still airing |
| start_date | double | Start date (ymd) |
| end_date | double | End date (ymd) |
| duration | character | Per episode duration or entire duration, text string |
| rating | character | Age rating |
| score | double | Score (higher = better) |
| scored_by | double | Number of users that scored |
| rank | double | Rank - weight according to MyAnimeList formula |
| popularity | double | based on how many members/users have the respective anime in their list |
| members | double | number members that added this anime in their list |
| favorites | double | number members that favorites these in their list |
| synopsis | character | long string with anime synopsis |
| background | character | long string with production background and other things |
| premiered | character | anime premiered on season/year |
| broadcast | character | when is (regularly) broadcasted |
| related | character | dictionary: related animes, series, games etc. |
Step 0: Load data
filename = "https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2019/2019-04-23/raw_anime.csv"
df = pd.read_csv(filename)
df.head(3).T
Step 1: Clean producers column
The first step tries to clean up the producers column by removing some brackets ('[]') and trim off some empty spaces
clean_df <- raw_df %>% # Producers mutate(producers = str_remove(producers, "\\["), producers = str_remove(producers, "\\]"))
What is mutate? This link compares R's mutate to be similar to pandas' df.assign.
However, df.assign returns a new DataFrame whereas mutate adds a new variable while preserving the previous ones.
Therefore, for this example, I will compare mutate to be similar to df['col'] = X
As we can see, this is looks like a list of items but in string form
# Let's see what we trying to remove
df.loc[df["producers"].str.contains("\[", na=False), "producers"].head()
Let's use pandas flavor to create a custom method for just removing some strings so we don't have to use str.replace so many times.
@pf.register_dataframe_method
def str_remove(df, column_name: str, pat: str, *args, **kwargs):
"""
Wrapper around df.str.replace
The function will loop through regex patterns and remove them from the desired column.
:param df: A pandas DataFrame.
:param column_name: A `str` indicating which column the string removal action is to be made.
:param pat: A regex pattern to match and remove.
"""
if not isinstance(pat, str):
raise TypeError(
f"Pattern should be a valid regex pattern. Received pattern: {pat} with dtype: {type(pat)}"
)
df[column_name] = df[column_name].str.replace(pat, "", *args, **kwargs)
return df
clean_df = df.str_remove(column_name="producers", pat="\[|\]")
With brackets removed.
clean_df["producers"].head()
Brackets are removed. Now the next part
separate_rows(producers, sep = ",") %>%
It seems like separate rows will go through each value of the column, and if the value is a list, will create a new row for each value in the list with the remaining column values being the same. This is commonly known as an explode method but it is not yet implemented in pandas. We will need a function for this (code adopted from here).
@pf.register_dataframe_method
def explode(df: pd.DataFrame, column_name: str, sep: str):
"""
For rows with a list of values, this function will create new rows for each value in the list
:param df: A pandas DataFrame.
:param column_name: A `str` indicating which column the string removal action is to be made.
:param sep: The delimiter. Example delimiters include `|`, `, `, `,` etc.
"""
df["id"] = df.index
wdf = (
pd.DataFrame(df[column_name].str.split(sep).fillna("").tolist())
.stack()
.reset_index()
)
# exploded_column = column_name
wdf.columns = ["id", "depth", column_name] # plural form to singular form
# wdf[column_name] = wdf[column_name].apply(lambda x: x.strip()) # trim
wdf.drop("depth", axis=1, inplace=True)
return pd.merge(df, wdf, on="id", suffixes=("_drop", "")).drop(
columns=["id", column_name + "_drop"]
)
clean_df = clean_df.explode(column_name="producers", sep=",")
Now every producer is its own row.
clean_df["producers"].head()