<jupyter_start><jupyter_text>Tidying variable values as column names with stack<jupyter_code>state_fruit = pd.read_csv('data/state_fruit.csv', index_col=0)
state_fruit
state_fruit.stack()
state_fruit_tidy = state_fruit.stack().reset_index()
state_fruit_tidy
state_fruit_tidy.columns = ['state', 'fruit', 'weight']
state_fruit_tidy
state_fruit.stack()\
           .rename_axis(['state', 'fruit'])\
state_fruit.stack()\
           .rename_axis(['state', 'fruit'])\
           .reset_index(name='weight')<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>state_fruit2 = pd.read_csv('data/state_fruit2.csv')
state_fruit2
state_fruit2.stack()
state_fruit2.set_index('State').stack()<jupyter_output><empty_output><jupyter_text>Tidying variable values as column names with melt<jupyter_code>state_fruit2 = pd.read_csv('data/state_fruit2.csv')
state_fruit2
state_fruit2.melt(id_vars=['State'],
                 value_vars=['Apple', 'Orange', 'Banana'])
state_fruit2.index=list('abc')
state_fruit2.index.name = 'letter'
state_fruit2
state_fruit2.melt(id_vars=['State'],
                 value_vars=['Apple', 'Orange', 'Banana'],
                 var_name='Fruit',
                 value_name='Weight')<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>state_fruit2.melt()
state_fruit2.melt(id_vars='State')<jupyter_output><empty_output><jupyter_text>Stacking multiple variables simultaneously<jupyter_code>movie = pd.read_csv('data/movie.csv')
actor = movie[['movie_title', 'actor_1_name', 'actor_2_name', 'actor_3_name', 
               'actor_1_facebook_likes', 'actor_2_facebook_likes', 'actor_3_facebook_likes']]
actor.head()
def change_col_name(col_name):
    col_name = col_name.replace('_name', '')
    if 'facebook' in col_name:
        fb_idx = col_name.find('facebook')
        col_name = col_name[:5] + col_name[fb_idx - 1:] + col_name[5:fb_idx-1]
    return col_name
actor2 = actor.rename(columns=change_col_name)
actor2.head()
actor2_tidy = pd.wide_to_long(actor2, 
                              stubnames=['actor', 'actor_facebook_likes'], 
                              i=['movie_title'], 
                              j='actor_num', 
                              sep='_').reset_index()
actor2_tidy.head()
actor2.rename<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>df = pd.read_csv('data/stackme.csv')
df
df2 = df.rename(columns = {'a1':'group1_a1', 'b2':'group1_b2',
                           'd':'group2_a1', 'e':'group2_b2'})
df2
pd.wide_to_long(df2, 
                stubnames=['group1', 'group2'], 
                i=['State', 'Country', 'Test'], 
                j='Label', 
                suffix='.+', 
                sep='_')<jupyter_output><empty_output><jupyter_text>Inverting stacked data<jupyter_code>college = pd.read_csv('data/college.csv', 
                      index_col='INSTNM', 
                      usecols=lambda x: 'UGDS_' in x or x == 'INSTNM')
college.head()
college_stacked = college.stack()
college_stacked.head(18)
college_stacked.unstack().head()
college2 = pd.read_csv('data/college.csv', 
                      usecols=lambda x: 'UGDS_' in x or x == 'INSTNM')
college2.head()
college_melted = college2.melt(id_vars='INSTNM', var_name='Race', value_name='Percentage')
college_melted.head()
college_melted_inv = college_melted.pivot(index='INSTNM', columns='Race', values='Percentage')
college_melted_inv.head()
college2_replication = college_melted_inv.loc[college2['INSTNM'], college2.columns[1:]]\
                                         .reset_index()
college2.equals(college2_replication)<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>college.stack().unstack(0)
college.T<jupyter_output><empty_output><jupyter_text>Unstacking after a grouping aggregation<jupyter_code>employee = pd.read_csv('data/employee.csv')
employee.groupby('RACE')['BASE_SALARY'].mean().astype(int)
agg = employee.groupby(['RACE', 'GENDER'])['BASE_SALARY'].mean().astype(int)
agg
agg.unstack('GENDER')
agg.unstack('RACE')<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>agg2 = employee.groupby(['RACE', 'GENDER'])['BASE_SALARY'].agg(['mean', 'max', 'min']).astype(int)
agg2<jupyter_output><empty_output><jupyter_text>Replicating pivot_table with a groupby aggregation<jupyter_code>flights = pd.read_csv('data/flights.csv')
flights.head()
fp = flights.pivot_table(index='AIRLINE', 
                         columns='ORG_AIR', 
                         values='CANCELLED', 
                         aggfunc='sum',
                         fill_value=0).round(2)
fp.head()
fg = flights.groupby(['AIRLINE', 'ORG_AIR'])['CANCELLED'].sum()
fg.head()
fg_unstack = fg.unstack('ORG_AIR', fill_value=0)
fg_unstack.head()
fp.equals(fg_unstack)<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>fp2 = flights.pivot_table(index=['AIRLINE', 'MONTH'],
                          columns=['ORG_AIR', 'CANCELLED'],
                          values=['DEP_DELAY', 'DIST'],
                          aggfunc=[np.mean, np.sum],
                          fill_value=0)
fp2.head()
flights.groupby(['AIRLINE', 'MONTH', 'ORG_AIR', 'CANCELLED'])['DEP_DELAY', 'DIST'] \
       .agg(['mean', 'sum']) \
       .unstack(['ORG_AIR', 'CANCELLED'], fill_value=0) \
       .swaplevel(0, 1, axis='columns') \
       .head()<jupyter_output><empty_output><jupyter_text>Renaming axis levels for easy reshaping<jupyter_code>college = pd.read_csv('data/college.csv')
cg = college.groupby(['STABBR', 'RELAFFIL'])['UGDS', 'SATMTMID'] \
            .agg(['count', 'min', 'max']).head(6)
cg
cg = cg.rename_axis(['AGG_COLS', 'AGG_FUNCS'], axis='columns')
cg
cg.stack('AGG_FUNCS').head()
cg.stack('AGG_FUNCS').swaplevel('AGG_FUNCS', 'STABBR', axis='index').head()
cg.stack('AGG_FUNCS') \
  .swaplevel('AGG_FUNCS', 'STABBR', axis='index') \
  .sort_index(level='RELAFFIL', axis='index') \
  .sort_index(level='AGG_COLS', axis='columns').head(6)
cg.stack('AGG_FUNCS').unstack(['RELAFFIL', 'STABBR'])
cg.stack(['AGG_FUNCS', 'AGG_COLS']).head(12)<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>cg.rename_axis([None, None], axis='index').rename_axis([None, None], axis='columns')<jupyter_output><empty_output><jupyter_text>Tidying when multiple variables are stored as column names<jupyter_code>weightlifting = pd.read_csv('data/weightlifting_men.csv')
weightlifting
wl_melt = weightlifting.melt(id_vars='Weight Category', 
                             var_name='sex_age', 
                             value_name='Qual Total')
wl_melt.head()
sex_age = wl_melt['sex_age'].str.split(expand=True)
sex_age.head()
sex_age.columns = ['Sex', 'Age Group']
sex_age.head()
sex_age['Sex'] = sex_age['Sex'].str[0]
sex_age.head()
wl_tidy = pd.concat([sex_age, wl_melt[['Weight Category', 'Qual Total']]], axis='columns')
wl_tidy.head()
sex_age[['Weight Category', 'Qual Total']] = wl_melt[['Weight Category', 'Qual Total']]<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>new_cols = {'Sex':wl_melt.sex_age.str[0], 
                'Age Group':wl_melt.sex_age.str.extract('(\d{2}[-+](?:\d{2})?)', expand=False)}
wl_tidy2 = wl_melt.assign(**new_cols).drop('sex_age', axis='columns')
wl_tidy2.head()
wl_tidy2.sort_index(axis=1).equals(wl_tidy.sort_index(axis=1))<jupyter_output><empty_output><jupyter_text>Tidying when multiple variables are stored as column values<jupyter_code>inspections = pd.read_csv('data/restaurant_inspections.csv', parse_dates=['Date'])
inspections.head(10)
inspections.pivot(index=['Name', 'Date'], columns='Info', values='Value')
inspections.set_index(['Name','Date', 'Info']).head(10)
inspections.set_index(['Name','Date', 'Info']).unstack('Info').head()
insp_tidy = inspections.set_index(['Name','Date', 'Info']) \
                               .unstack('Info') \
                               .reset_index(col_level=-1)
insp_tidy.head()
insp_tidy.columns = insp_tidy.columns.droplevel(0).rename(None)
insp_tidy.head()
inspections.set_index(['Name','Date', 'Info']) \
          .squeeze() \
          .unstack('Info') \
          .reset_index() \
          .rename_axis(None, axis='columns')<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>inspections.pivot_table(index=['Name', 'Date'], 
                        columns='Info', 
                        values='Value', 
                        aggfunc='first') \
           .reset_index()\
           .rename_axis(None, axis='columns')<jupyter_output><empty_output><jupyter_text>Tidying when two or more values are stored in the same cell<jupyter_code>cities = pd.read_csv('data/texas_cities.csv')
cities
geolocations = cities.Geolocation.str.split(pat='. ', expand=True)
geolocations.columns = ['latitude', 'latitude direction', 'longitude', 'longitude direction']
geolocations
geolocations = geolocations.astype({'latitude':'float', 'longitude':'float'})
geolocations.dtypes
cities_tidy = pd.concat([cities['City'], geolocations], axis='columns')
cities_tidy
pd.concat([cities['City'], geolocations], axis='columns')<jupyter_output><empty_output><jupyter_text>How it works...<jupyter_code>temp = geolocations.apply(pd.to_numeric, errors='ignore')
temp
temp.dtypes<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>cities.Geolocation.str.split(pat='° |, ', expand=True)
cities.Geolocation.str.extract('([0-9.]+). (N|S), ([0-9.]+). (E|W)', expand=True)<jupyter_output><empty_output><jupyter_text>Tidying when variables are stored in column names and values<jupyter_code>sensors = pd.read_csv('data/sensors.csv')
sensors
sensors.melt(id_vars=['Group', 'Property'], var_name='Year').head(6)
sensors.melt(id_vars=['Group', 'Property'], var_name='Year') \
       .pivot_table(index=['Group', 'Year'], columns='Property', values='value') \
       .reset_index() \
       .rename_axis(None, axis='columns')<jupyter_output><empty_output><jupyter_text>There's more...<jupyter_code>sensors.set_index(['Group', 'Property']) \
       .stack() \
       .unstack('Property') \
       .rename_axis(['Group', 'Year'], axis='index') \
       .rename_axis(None, axis='columns') \
       .reset_index()<jupyter_output><empty_output><jupyter_text>Tidying when multiple observational units are stored in the same table<jupyter_code>movie = pd.read_csv('data/movie_altered.csv')
movie.head()
movie.insert(0, 'id', np.arange(len(movie)))
movie.head()
stubnames = ['director', 'director_fb_likes', 'actor', 'actor_fb_likes']
movie_long = pd.wide_to_long(movie, 
                                 stubnames=stubnames, 
                                 i='id', 
                                 j='num', 
                                 sep='_').reset_index()
movie_long['num'] = movie_long['num'].astype(int)
movie_long.head(9)
movie_table = movie_long[['id','title', 'year', 'duration', 'rating']]
director_table = movie_long[['id', 'director', 'num', 'director_fb_likes']]
actor_table = movie_long[['id', 'actor', 'num', 'actor_fb_likes']]
movie_table.head(9)
director_table.head(9)
actor_table.head(9)
movie_table = movie_table.drop_duplicates().reset_index(drop=True)
director_table = director_table.dropna().reset_index(drop=True)
actor_table = actor_table.dropna().reset_index(drop=True)
movie_table.head()
director_table.head()
movie.memory_usage(deep=True).sum()
movie_table.memory_usage(deep=True).sum() + \
director_table.memory_usage(deep=True).sum() + \
actor_table.memory_usage(deep=True).sum()
director_cat = pd.Categorical(director_table['director'])
director_table.insert(1, 'director_id', director_cat.codes)

actor_cat = pd.Categorical(actor_table['actor'])
actor_table.insert(1, 'actor_id', actor_cat.codes)

director_table.head()
actor_table.head()
director_associative = director_table[['id', 'director_id', 'num']]
dcols = ['director_id', 'director', 'director_fb_likes']
director_unique = director_table[dcols].drop_duplicates().reset_index(drop=True)
director_associative.head()                     
director_unique.head()
actor_associative = actor_table[['id', 'actor_id', 'num']]
acols = ['actor_id', 'actor', 'actor_fb_likes']
actor_unique = actor_table[acols].drop_duplicates().reset_index(drop=True)
actor_associative.head()
actor_unique.head()
movie_table.memory_usage(deep=True).sum() + \
director_associative.memory_usage(deep=True).sum() + \
director_unique.memory_usage(deep=True).sum() + \
actor_associative.memory_usage(deep=True).sum() + \
actor_unique.memory_usage(deep=True).sum()
movie_table.head()
actors = actor_associative.merge(actor_unique, on='actor_id') \
                          .drop('actor_id', 1) \
                          .pivot_table(index='id', columns='num', aggfunc='first')

actors.columns = actors.columns.get_level_values(0) + '_' + \
                 actors.columns.get_level_values(1).astype(str)

directors = director_associative.merge(director_unique, on='director_id') \
                          .drop('director_id', 1) \
                          .pivot_table(index='id', columns='num', aggfunc='first')

directors.columns = directors.columns.get_level_values(0) + '_' + \
                    directors.columns.get_level_values(1).astype(str)
actors.head()
directors.head()
movie2 = movie_table.merge(directors.reset_index(), on='id', how='left') \
                    .merge(actors.reset_index(), on='id', how='left')
movie2.head()
movie.equals(movie2[movie.columns])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16927549194991057, 'fraction_numerical': 0.008199165175909361, 'mean_word_length': 4.356087824351297, 'pattern_counts': {'":': 0, '<': 97, '<?xml version=': 0, '>': 97, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '217233', 'license': 'MIT', 'n_tokens_mistral': 4915, 'n_tokens_neox': 4711, 'n_words': 584, 'path': 'Module 3/Chapter08/Chapter 08 Restructuring Data into Tidy Form.ipynb', 'repo_name': 'PacktPublishing/-Numerical-Computing-with-Python', 'size': '13416'}