<jupyter_start><jupyter_text>Pandas Tidy Data  OverviewThis notebook uses the IMDB dataset from Kaggle, after having been processed by preceeding notebooks.  https://www.kaggle.com/PromptCloudHQ/imdb-dataIMDB-Movie-Data.csvThis notebook is about how to tidy the data for the Genre and Actors columns, so that groupby and other methods will work as expected.There are many ways to convert the list of actors and genres into tidy format.  Two general approaches stand out:1. melt() and merge()2. stack() and join()melt and merge are more general than stack and join, however melt and merge don't retain the index, which in this case results in extra calls to reset_index and set_index.The simplest set of transformation steps I have found for this dataset uses stack and join.<jupyter_code>import pandas as pd
import numpy as np
print(f'Pandas Version: {pd.__version__}')
print(f'Numpy Version:  {np.__version__}')<jupyter_output>Pandas Version: 0.24.1
Numpy Version:  1.15.4<jupyter_text>Read Movie Data SetThis data was read in and preprocessed in Pandas02.  Load that notebook.<jupyter_code>movies = pd.read_hdf('../data/movies.h5')
movies.head(2)<jupyter_output><empty_output><jupyter_text>Examine Genre and Actors<jupyter_code>movies['Genre'].head(5)
# typical way of finding unique values
# only works when cell contains one value, as it does for Director
movies['Director'].value_counts().head()
# this doesnt work as needed for Genre
movies['Genre'].value_counts().head()<jupyter_output><empty_output><jupyter_text>The above is count per genre string, not per genre.<jupyter_code># this doesnt work as needed for Actor
movies['Actors'].value_counts().head()<jupyter_output><empty_output><jupyter_text>The above is count per actor string, not per actor.  Create Long Form Representation of GenreThis has one row per (Genre, Title).**Method Chaining Note**  Method chaining is a concise way to represent a data processing pipeline, however it is difficult to debug.  It is easier to develop adding one method at a time.**Step by Step**1. Write a parse function to convert a comma separated string into a Python list1. Transform Genre Column from string to list of Genres using the parse function2. Transform list of Genres into pd.Series3. Stack (which takes the innermost column index and makes it the innermost row index)4. Set the series name (creates a better name for the following join)5. Remove the unnecessary innermost row index created by stack6. Convert to a DataFrame (to allow for join)7. Join with movies on Title8. Remove the original Genre column<jupyter_code>def parse_string(s):
    """Convert string with comma separated values to list of strings.
    For each string, remove leading and trailing whitespace.
    """
    values = s.split(',')
    return [value.strip() for value in values]
tmp = movies['Genre'].transform(parse_string)
tmp = tmp.transform(pd.Series)
tmp = tmp.stack()
tmp = tmp.rename('Genre')
tmp = tmp.reset_index(level=-1, drop=True)
tmp = tmp.to_frame()
tmp = tmp.join(movies, on='Title', rsuffix='_Original')
tmp = tmp.drop(['Genre_Original'], axis=1)
tmp.head(4)
# save the result
df_genre = tmp.copy()
def is_unique(df, cols):
    """Fast determination of multi-column uniqueness."""
    return not (df.duplicated(subset=cols)).any()
# reset_index() to allow for checking against columns (without index)
# could also set_index(['Title','Genre']) and use df.index.duplicated()
is_unique(df_genre.reset_index(), ['Title', 'Genre'])<jupyter_output><empty_output><jupyter_text>All in One StepIdentical to the above, but without saving the intermediate values.<jupyter_code>(movies['Genre']
    .transform(parse_string)
    .transform(pd.Series)
    .stack()
    .rename('Genre')
    .reset_index(level=-1, drop=True)
    .to_frame()
    .join(movies, on='Title', rsuffix='_Original')
    .drop(['Genre_Original'], axis=1)
    .head(4))
# try it
df_genre[df_genre['Genre'] == 'Sci-Fi'].head()<jupyter_output><empty_output><jupyter_text>Aggregate for Rating and Runtime<jupyter_code># Flatten Multi-index Column
def flatten_columns(df):
    return ['_'.join(col) for col in df.columns.values]
# data is tidy with respect to Genre, so groupby will work correctly
df_agg = df_genre.groupby('Genre').agg(
    {'Rating':['mean','count'], 'Runtime':['mean','count']})
df_agg.columns = flatten_columns(df_agg)
df_agg.head()<jupyter_output><empty_output><jupyter_text>Note: Avg of Averages != Avg<jupyter_code># avg != avg of per genre averages
df_genre['Rating'].mean() == df_agg['Rating_mean'].mean()
np.round([df_genre['Rating'].mean(), df_agg['Rating_mean'].mean()], 2)<jupyter_output><empty_output><jupyter_text>Genre Querys  Query: Top 3 Genres by Rating & At Least 20 Movies<jupyter_code>criteria_count = df_agg['Rating_count'] >= 20
df_agg[criteria_count].nlargest(n=3, columns=['Rating_mean'])<jupyter_output><empty_output><jupyter_text>Query: Top 3 Genres by Runtime & Better than Avg Rating & At Least 20 Movies<jupyter_code>criteria_rating = df_agg['Rating_mean'] > df_genre['Rating'].mean()
df_agg[criteria_count & criteria_rating].nlargest(n=3, columns=['Runtime_mean'])<jupyter_output><empty_output><jupyter_text>Create Long Form Representation of ActorThis has one row per (Actor, Title).This is identical to the processing for Genre above.<jupyter_code>tmp = movies['Actors'].transform(parse_string)
tmp = tmp.transform(pd.Series)
tmp = tmp.stack()
tmp = tmp.rename('Actor')
tmp = tmp.reset_index(level=-1, drop=True)
tmp = tmp.to_frame()
tmp = tmp.join(movies, on='Title')
tmp = tmp.drop(['Actors'], axis=1)
tmp.head(4)
(tmp.reset_index().duplicated(subset=['Title', 'Actor'], keep=False)).any()
# save the result, only a few columns are used in the following analysis
df_actor = tmp[['Actor','Rating','Runtime']].copy()<jupyter_output><empty_output><jupyter_text>Actor Queries  Example: Query for Brad Pitt Movies<jupyter_code>df_actor[df_actor['Actor'] == 'Brad Pitt']
# Flatten Multi-index Column
def flatten_columns(df):
    return ['_'.join(col) for col in df.columns.values]
# data is tidy with respect to Actor, so groupby will work correctly
df_agg = df_actor.groupby('Actor').agg(
    {'Rating':['mean','count'], 'Runtime':['mean','count']})
df_agg.columns = flatten_columns(df_agg)
df_agg.head()<jupyter_output><empty_output><jupyter_text>Query: Top 15 Actors by Movie Count, Sorted by Rating<jupyter_code># find the 15 actors who are in the most movies and sort by Rating
actor_top15 = df_agg.nlargest(n=15, columns=['Rating_count'])
actor_top15.sort_values(by='Rating_mean', ascending=False)<jupyter_output><empty_output><jupyter_text>Query: Top 15 Actors by Movie Count, Display Top 5 by Rating<jupyter_code># very similar to above query
actor_top15.nlargest(n=5, columns=['Rating_mean'])<jupyter_output><empty_output><jupyter_text>Query: Actors with Above Avg Rating & in at least 5 movies, Display Top 5 by Runtime<jupyter_code>criteria_above_avg = df_agg['Rating_mean'] > df_actor['Rating'].mean()
criteria_common = df_agg['Rating_count'] >= 5

df_agg[criteria_above_avg & criteria_common].nlargest(n=5, columns=['Runtime_mean'])<jupyter_output><empty_output><jupyter_text>Query: Actors Top 20% by Rating & Actors Top 20% by Count, Display Top 5 by Runtime<jupyter_code># rating for top 20% of actors
top_20percent_rating = df_actor['Rating'].quantile(q=0.80)
top_20percent_rating
# Note
df_actor['Rating'].quantile(q=0.80) == df_agg['Rating_mean'].quantile(q=0.80)
# Note
df_actor['Rating'].quantile(q=0.80), df_agg['Rating_mean'].quantile(q=0.80)
# number of movies for top 20% cutoff
top_20percent_count = df_agg['Rating_count'].quantile(q=0.80)
top_20percent_count
criteria_top20_rated = df_agg['Rating_mean'] >= top_20percent_rating
criteria_top20_count = df_agg['Rating_count'] >= top_20percent_count
top_2020 = df_agg[criteria_top20_rated & criteria_top20_count].nlargest(n=5, columns=['Runtime_mean'])
top_2020<jupyter_output><empty_output><jupyter_text>Query: Number of Actors: Top 20% by Rating & Top 20% by Count<jupyter_code>(criteria_top20_rated & criteria_top20_count).sum()<jupyter_output><empty_output><jupyter_text>Genre and Actor QueriesThis requires one row per Genre, Actor, Title.This is just a join of df_genre and df_actor, removing duplicate columns or unnecessary columns.<jupyter_code>tmp1 = df_actor.drop(['Rating','Runtime'], axis=1)
tmp2 = df_genre.drop(['Actors'], axis=1)
df_genre_actor = tmp1.join(tmp2)
df_genre_actor.head()
# verify that there are no dups for Title, Actor, Genre
((df_genre_actor
    .reset_index()
    .duplicated(subset=['Title', 'Actor', 'Genre'], keep=False))
    .all())<jupyter_output><empty_output><jupyter_text>Query: For top 15 Actors by Count, Which Genre Had Highest Mean Rating<jupyter_code># top 15 actors by movie count was computed above
actor_top15.index
criteria = df_genre_actor['Actor'].isin(actor_top15.index)
df_genre_actor_common = df_genre_actor[criteria]
df_genre_actor_common.head()
# aggregate the mean Rating over each genre for each actor
df_genre_actor_agg = (
    df_genre_actor_common
        .groupby(['Actor', 'Genre'])
        .agg({'Rating':'mean'})
        .add_suffix('_Mean')
        .reset_index())
df_genre_actor_agg.head()
# find the best movie genre per top15 actor
# keep ties (idxmax does not keep ties)
rating_max = df_genre_actor_agg.groupby('Actor')['Rating_Mean'].max()
pd.merge(df_genre_actor_agg, rating_max, on=['Actor', 'Rating_Mean'])<jupyter_output><empty_output><jupyter_text>Spot Check by HandUse a completely different technique to spot check the result.<jupyter_code>pitt_movies = movies['Actors'].str.contains('<NAME>')
history_movies = movies['Genre'].str.contains('History')
pitt_history_movies = movies[pitt_movies & history_movies]
pitt_history_movies
pitt_history_movies['Rating'].mean()
df = df_genre_actor_agg
df[(df['Actor'] == '<NAME>') & (df['Genre'] == 'History')]<jupyter_output><empty_output><jupyter_text>Above results are the same.<jupyter_code>war_movies = movies['Genre'].str.contains('War')
pitt_war_movies = movies[pitt_movies & war_movies]
pitt_war_movies
pitt_war_movies['Rating'].mean()
df[(df['Actor'] == '<NAME>') & (df['Genre'] == 'War')]<jupyter_output><empty_output><jupyter_text>Above results are the same.<jupyter_code># look at top 5 average movie rating by genre for Brad Pitt
df[df['Actor'] == '<NAME>'].nlargest(n=5, columns=['Rating_Mean'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12637256790599113, 'fraction_numerical': 0.01406280100173377, 'mean_word_length': 6.696812453669385, 'pattern_counts': {'":': 0, '<': 92, '<?xml version=': 0, '>': 98, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '51654', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3597, 'n_tokens_neox': 3446, 'n_words': 927, 'path': 'python/Pandas04.ipynb', 'repo_name': 'sdiehl28/tutorial-jupyter-notebooks', 'size': '10394'}