def wrangle_fielding(p_raw, p_wrangled): """Drops cols > 90% null, converts fieldnames, optimizes datatypes and persists data """ if p_wrangled.joinpath('fielding.csv').exists(): logger.info('Skipping wrangle of Fielding.csv - already performed') return os.chdir(p_raw) fielding = pd.read_csv('Fielding.csv') fielding.rename(columns=get_fieldname_mapping(), inplace=True) fielding.columns = fielding.columns.str.lower() # drop any column that is more than 90% null filt = fielding.isna().mean() > 0.90 if filt.any(): drop_cols = fielding.columns[filt] logger.warning( f'Cols > 90% missing being dropped: {" ".join(drop_cols)}') fielding.drop(drop_cols, axis=1, inplace=True) dh.optimize_df_dtypes(fielding) msg = dh.df_info(fielding) logger.info('fielding\n{}'.format(msg)) # persist os.chdir(p_wrangled) dh.to_csv_with_types(fielding, 'fielding.csv')
def wrangle_basic(p_raw, p_wrangled, filename): """Basic Wrangle: converts fieldnames, optimizes datatypes and persists data """ filename_lower = str(filename).lower() wrangled_file = p_wrangled.joinpath(filename_lower) if wrangled_file.exists(): logger.info(f'Skipping wrangle of {filename} - already performed') return os.chdir(p_raw) df = pd.read_csv(filename) df.rename(columns=get_fieldname_mapping(), inplace=True) df.columns = df.columns.str.lower() # downcast integers and convert float to Int64, if data permits dh.optimize_df_dtypes(df) msg = dh.df_info(df) logger.info(f'{filename}\n{msg}') # persist with optimized datatypes os.chdir(p_wrangled) dh.to_csv_with_types(df, wrangled_file)
def wrangle_people(p_raw, p_wrangled): """Custom parsing of dates, converts fieldnames, optimizes datatypes and persists data """ if p_wrangled.joinpath('people.csv').exists(): logger.info('Skipping wrangle of People.csv - already performed') return os.chdir(p_raw) people = pd.read_csv('People.csv', parse_dates=['debut', 'finalGame']) people.rename(columns=get_fieldname_mapping(), inplace=True) people.columns = people.columns.str.lower() people['birth_date'] = people.apply(lambda x: to_date(x, 'birth'), axis=1) people['death_date'] = people.apply(lambda x: to_date(x, 'death'), axis=1) people = people.drop([ 'birth_year', 'birth_month', 'birth_day', 'death_year', 'death_month', 'death_day' ], axis=1) msg = dh.df_info(people) logger.info('people\n{}'.format(msg)) # persist as a csv file with data types os.chdir(p_wrangled) dh.to_csv_with_types(people, 'people.csv')
def create_batting(player_game, game_start, p_retrosheet_wrangled): """Create batting.csv for batting attributes per player per game.""" # column names of the batting attributes b_cols = [col for col in player_game.columns if col.startswith('b_')] # Note: any player who is in a game in any role, will have b_g = 1 # even if b_pa == 0 (no plate appearances) # fields which uniquely identify a record pkey = ['game_id', 'player_id'] # fields to join to other "tables" fkey = ['team_id'] batting = player_game.loc[:, pkey + fkey + b_cols].copy() # remove b_ from the column names, except for b_2b and b_3b b_cols_new = {col: col[2:] for col in b_cols} b_cols_new['b_2b'] = 'double' b_cols_new['b_3b'] = 'triple' b_cols_new['b_gdp'] = 'gidp' # to match Lahman b_cols_new['b_hp'] = 'hbp' # to match Lahman batting.rename(columns=b_cols_new, inplace=True) # add game_start.dt.year as many queries use year batting = pd.merge(batting, game_start[['game_id', 'game_start']]) batting['year'] = batting['game_start'].dt.year.astype('int16') dh.optimize_df_dtypes(batting, ignore=['year']) logger.info('Writing and compressing batting. This could take several minutes ...') dh.to_csv_with_types(batting, p_retrosheet_wrangled / 'batting.csv.gz')
def wrangle_parks(data_dir, retrosheet_wrangle): parks_filename = data_dir / 'retrosheet/raw/misc/parkcode.txt' parks = pd.read_csv(parks_filename, parse_dates=['START', 'END']) cols = [col.lower() for col in parks.columns] parks.columns = cols parks = parks.rename(columns={'parkid': 'park_id'}) dh.to_csv_with_types(parks, retrosheet_wrangle / 'parks.csv')
def create_fielding(player_game, game_start, p_retrosheet_wrangled): """Create fielding.csv for fielding attributes per player per game.""" # column names for fielding attributes f_cols = [col for col in player_game.columns if col.startswith('f_')] # create orig_cols dictionary which maps fielder's pos to original fielding columns names # create new_cols dictionary which maps fielder's pos to new fielding column names # pos: P, C, 1B, 2B, 3B, SS, LF, CF, RF # column name pattern: f_{pos}_{stat} orig_cols = collections.defaultdict(list) new_cols = collections.defaultdict(list) for col in f_cols: match = re.search(r'f_(\w{1,2})_(\w*)', col) pos = match.group(1) stat = match.group(2) orig_cols[pos].append(col) stat = stat.replace('out', 'inn_outs') # to match Lahman new_cols[pos].append(stat) # full pkey will be: ['game_id', 'player_id', 'pos'] pkey = ['game_id', 'player_id'] # fields to join to other "tables" fkey = ['team_id'] """For each record created by cwdaily, create up to 9 new records, one per position. Each record will temporarily go in its own dataframe and then be concatenated. Each dataframe has the same columns.""" dfs = [] for pos in orig_cols.keys(): # if all fielding attributes for this pos are 0 then the player did not play that pos # note: all attributes are unsigned integers f_filt = player_game[orig_cols[pos]].sum(axis=1) == 0 df = pd.DataFrame() df[pkey + fkey + new_cols[pos]] = \ player_game.loc[~f_filt, pkey + fkey + orig_cols[pos]].copy() # add the position column to the df # use upper case to match Lahman position values df.insert(2, 'pos', pos.upper()) # orig_cols['c'] has pb and xi columns # all other positions do not have pb and xi if pos != 'c': df[f'pb'] = 0 df[f'xi'] = 0 dfs.append(df) fielding = pd.concat(dfs, ignore_index=True) # add game_start.dt.year as many queries use year fielding = pd.merge(fielding, game_start[['game_id', 'game_start']]) fielding['year'] = fielding['game_start'].dt.year.astype('int16') dh.optimize_df_dtypes(fielding, ignore=['year']) logger.info('Writing and compressing fielding. This could take several minutes ...') dh.to_csv_with_types(fielding, p_retrosheet_wrangled / 'fielding.csv.gz')
def create_pitching(player_game, game_start, p_retrosheet_wrangled): """Create pitching.csv for pitching attributes per player per game.""" # column names of the pitching attributes p_cols = [col for col in player_game.columns if col.startswith('p_')] # if all pitching attributes are 0 then the player did not pitch # note: all attributes are unsigned integers, so if their sum is zero, all are zero p_filt = player_game[p_cols].sum(axis=1) == 0 # fields which uniquely identify a record pkey = ['game_id', 'player_id'] # fields to join to other "tables" fkey = ['team_id'] # data with some non-zero attributes pitching = player_game.loc[~p_filt, pkey + fkey + p_cols].copy() # remove p_ from the column names, except for p_2b and p_3b p_cols_new = {col: col[2:] for col in p_cols} p_cols_new['p_2b'] = 'double' p_cols_new['p_3b'] = 'triple' p_cols_new['p_gdp'] = 'gidp' # to match Lahman p_cols_new['p_hp'] = 'hbp' # to match Lahman pitching.rename(columns=p_cols_new, inplace=True) # add game_start.dt.year as many queries use year pitching = pd.merge(pitching, game_start[['game_id', 'game_start']]) pitching['year'] = pitching['game_start'].dt.year.astype('int16') dh.optimize_df_dtypes(pitching, ignore=['year']) logger.info('Writing and compressing pitching. This could take several minutes ...') dh.to_csv_with_types(pitching, p_retrosheet_wrangled / 'pitching.csv.gz')
def wrangle_teams(data_dir, retrosheet_wrangle): team_dir = data_dir / 'retrosheet/raw/event/regular' dfs = [] team_files = team_dir.glob('TEAM*') for team in sorted(team_files): year = int(team.name[-4:]) df = pd.read_csv(team, header=None, names=['team_id', 'lg_id', 'city', 'name']) df.insert(1, 'year', year) dfs.append(df) retro_teams = pd.concat(dfs, ignore_index=True) dh.to_csv_with_types(retro_teams, retrosheet_wrangle / 'teams.csv')
def collect_parsed_files(parse_dir, collect_dir, parser, use_datatypes): """Collect all parsed files and optimize datatypes. """ os.chdir(parse_dir) # read the augmented files, not the ones created by cwevent if parser == 'cwevent': dailyfiles = glob.glob(f'{parser}*_plus.csv') else: dailyfiles = glob.glob(f'{parser}*.csv') dailyfiles.sort() logger.info( f'Collecting {len(dailyfiles)} {parser} parsed csv files into single dataframe ...' ) if use_datatypes: # this can save gigabytes of RAM by using precomputed datatypes logger.info('Using precomputed data types') if parser == 'cwdaily': filename = '../player_game_types.csv' elif parser == 'cwgame': filename = '../game_types.csv' elif parser == 'cwevent': filename = '../event_types.csv' else: raise ValueError(f'Unrecognized parser: {parser}') dates, dtypes = dh.read_types(filename) dtypes = {key.upper(): value for key, value in dtypes.items()} df = pd.concat((pd.read_csv(f, parse_dates=dates, dtype=dtypes) for f in dailyfiles), ignore_index=True, copy=False) logger.info(f'Optimized Memory Usage: {dh.mem_usage(df)}') else: # This could use twice the RAM required to hold the unoptimized DataFrame! # cwgame parser will output the line score (line_tx) like: 001001001 # but without double quotes around it, so it gets interpreted as a number. # Specify dtype for line score fields to get around this. df = pd.concat( (pd.read_csv(f, dtype={ 'AWAY_LINE_TX': str, 'HOME_LINE_TX': str }) for f in dailyfiles), ignore_index=True, copy=False) logger.info(f'Unoptimized Memory Usage: {dh.mem_usage(df)}') logger.info('Optimizing Data Types to reduce memory ...') # for cwdaily, optimize_df_dtypes reduces the size of the dataframe by a factor of 3 dh.optimize_df_dtypes(df) logger.info(f'Optimized Memory Usage: {dh.mem_usage(df)}') # convert to lower case df.columns = df.columns.str.lower() # drop any column that is more than 99% null filt = df.isna().mean() > 0.99 if filt.any(): drop_cols = df.columns[filt] logger.warning( f'Cols > 99% missing being dropped: {" ".join(drop_cols)}') df.drop(drop_cols, axis=1, inplace=True) # persist optimized dataframe # gzip chosen over xy because this runs on client computer and gzip is faster logger.info( 'persisting dataframe using compression - this could take several minutes ...' ) os.chdir(collect_dir) if parser == 'cwdaily': filename = 'player_game.csv.gz' elif parser == 'cwgame': filename = 'game.csv.gz' elif parser == 'cwevent': # was wrangled in parser to save RAM, write to wrangled dir filename = 'event.csv.gz' else: raise ValueError(f'Unrecognized parser: {parser}') dh.to_csv_with_types(df, filename) logger.info(f'{parser} data persisted')
def wrangle_game(game, p_retrosheet_wrangled): """Tidy the Game Data There are 3 types of data: data specific to a game -- the 'game' columns below data specific to the home team for that game -- the 'home' columns below data specific to the away team for that game -- the 'away' columns below The attributes for the home team are identical to the attributes for the away team. This suggests breaking this out into 2 csv files. 1. team_game.csv with key (game_id, team_id) -- stats per team per game (e.g. runs scored) 2. game.csv with key (game_id) -- stats per game (e.g. attendance) """ home_cols = [col for col in game.columns if col.startswith('home')] away_cols = [col for col in game.columns if col.startswith('away')] game_cols = [col for col in game.columns if not col.startswith('home') and not col.startswith('away')] game_tidy = game[game_cols].copy() home_team_game = game[['game_id'] + home_cols].copy() away_team_game = game[['game_id'] + away_cols].copy() home_team_game['bat_last'] = True away_team_game['bat_last'] = False home_team_game = dh.move_column_after(home_team_game, 'game_id', 'bat_last') away_team_game = dh.move_column_after(away_team_game, 'game_id', 'bat_last') # remove leading 'home_' and 'away_' suffix from fields home_team_game.rename(columns=lambda col: col[5:] if col.startswith('home_') else col, inplace=True) away_team_game.rename(columns=lambda col: col[5:] if col.startswith('away_') else col, inplace=True) # include opponent team_id in each row home_team_game.insert(4, 'opponent_team_id', away_team_game['team_id']) away_team_game.insert(4, 'opponent_team_id', home_team_game['team_id']) team_game = pd.concat([home_team_game, away_team_game]) # improve column names names = {col: col.replace('_ct', '') for col in team_game.columns if col.endswith('_ct')} # handle invalid identifiers names['2b_ct'] = 'double' names['3b_ct'] = 'triple' # pitcher_ct (number of pitchers) is a good name though, keep it names.pop('pitcher_ct') # additional fields to rename for consistency names['bi_ct'] = 'rbi' names['gdp_ct'] = 'gidp' names['hits_ct'] = 'h' names['hp_ct'] = 'hbp' names['err_ct'] = 'e' names['score_ct'] = 'r' team_game = team_game.rename(columns=names) # create new datetime column game_tidy['game_start'] = game_tidy.apply(parse_datetime, axis=1) game_tidy = dh.move_column_after(game_tidy, 'game_id', 'game_start') # these fields are no longer necessary game_tidy = game_tidy.drop(['start_game_tm', 'game_dt', 'game_dy'], axis=1) # add the game_start column to team_game to simplify queries team_game = pd.merge(team_game, game_tidy[['game_id', 'game_start']]) team_game['year'] = team_game['game_start'].dt.year.astype('int16') logger.info('Writing and compressing team_game. This could take several minutes ...') dh.optimize_df_dtypes(team_game, ignore=['year']) dh.to_csv_with_types(team_game, p_retrosheet_wrangled / 'team_game.csv.gz') # convert designated hitter to True/False and rename game_tidy['dh'] = False filt = game_tidy['dh_fl'] == 'T' game_tidy.loc[filt, 'dh'] = True game_tidy.drop('dh_fl', axis=1, inplace=True) # convert impossible attendance values to null and rename filt = game_tidy['attend_park_ct'] <= 0 impossible_values = game_tidy.loc[filt, 'attend_park_ct'].unique() game_tidy['attendance'] = game_tidy['attend_park_ct'].replace(impossible_values, np.nan) game_tidy.drop('attend_park_ct', axis=1, inplace=True) # convert impossible temperature values to null and rename filt = game_tidy['temp_park_ct'] <= 0 impossible_values = game_tidy.loc[filt, 'temp_park_ct'].unique() game_tidy['temperature'] = game_tidy['temp_park_ct'].replace(impossible_values, np.nan) game_tidy.drop('temp_park_ct', axis=1, inplace=True) # replace code values with strings # http://chadwick.sourceforge.net/doc/cwgame.html#cwtools-cwgame-winddirection direction = { 0: 'unknown', 1: 'to_lf', 2: 'to_cf', 3: 'to_rf', 4: 'l_to_r', 5: 'from_lf', 6: 'from_cf', 7: 'from_rf', 8: 'r_to_l'} game_tidy['wind_direction'] = \ game_tidy['wind_direction_park_cd'].map(direction).replace('unknown', np.nan) game_tidy.drop('wind_direction_park_cd', axis=1, inplace=True) # http://chadwick.sourceforge.net/doc/cwgame.html#cwtools-cwgame-windspeed # convert impossible wind speed values to null and rename filt = game_tidy['wind_speed_park_ct'] < 0 impossible_values = game_tidy.loc[filt, 'wind_speed_park_ct'].unique() game_tidy['wind_speed'] = game_tidy['wind_speed_park_ct'].replace(impossible_values, np.nan) game_tidy.drop('wind_speed_park_ct', axis=1, inplace=True) # replace code values with strings # http://chadwick.sourceforge.net/doc/cwgame.html#cwtools-cwgame-fieldcondition condition = { 0: 'unknown', 1: 'soaked', 2: 'wet', 3: 'damp', 4: 'dry'} game_tidy['field_condition'] = \ game_tidy['field_park_cd'].map(condition).replace('unknown', np.nan) game_tidy.drop('field_park_cd', axis=1, inplace=True) # replace code values with strings # http://chadwick.sourceforge.net/doc/cwgame.html#cwtools-cwgame-precipitation precip = { 0: 'unknown', 1: 'none', 2: 'drizzle', 3: 'showers', 4: 'rain', 5: 'snow'} game_tidy['precip_type'] = \ game_tidy['precip_park_cd'].map(precip).replace('unknown', np.nan) game_tidy.drop('precip_park_cd', axis=1, inplace=True) # replace code values with strings # http://chadwick.sourceforge.net/doc/cwgame.html#cwtools-cwgame-sky sky = { 0: 'unknown', 1: 'sunny', 2: 'cloudy', 3: 'overcast', 4: 'night', 5: 'dome'} game_tidy['sky_condition'] = \ game_tidy['sky_park_cd'].map(sky).replace('unknown', np.nan) game_tidy.drop('sky_park_cd', axis=1, inplace=True) logger.info('Writing and compressing game. This could take several minutes ...') dh.optimize_df_dtypes(game_tidy) dh.to_csv_with_types(game_tidy, p_retrosheet_wrangled / 'game.csv.gz') # to add game date to other tables return game_tidy[['game_id', 'game_start']]