# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import time
from cricsheet.fow_analysis.collapses.extract_collapses import return_collapses

# ## Load and Format Single Match

filepath = '../data/raw/csv/howstat/fall_of_wickets/'
file = 'fow_1999.csv'
df = pd.read_csv(filepath+file, index_col=0, parse_dates=[2], infer_datetime_format=True)

df.groupby(['MatchId','MatchInnings', 'Team']).apply(return_collapses).reset_index()

# Questions to answer:
# - Number of collapses by Team, by year (unique collapses, innings with a collapse)
# - Positions most often involved
# - Batters most often involved

# ## Load and Format All Matches

# ### Experiments - Concatenating Dfs
#
# ~2500 matches. 
# 1) What is the most efficient way to load all dataframes?
#
# 2) Is it more efficient to load and format one-by-one, or concatenate into a single df and groupby the whole df
#

# +
# Try with 100 dataframes initially

n = 100
filepath = '../data/raw/csv/howstat/fall_of_wickets/'
# -

# #### Method 1: Load all using glob generator + concat

import glob
import os

# +
start = time.time()
all_files = glob.glob(os.path.join(filepath, "*.csv"))
all_files_to_load = all_files[:]

df_from_each_file = (pd.read_csv(f, index_col=0, parse_dates=[2], infer_datetime_format=True) for f in all_files_to_load)
concatenated_df   = pd.concat(df_from_each_file, ignore_index=True)

end = time.time()
time_taken = end - start
print(f'{time_taken} seconds')
# -

"""
Attempts:
13 seconds
6.19 seconds

"""

# #### Method 2: os.listdir + concat

# +
from os import listdir

start = time.time()
df = pd.concat([pd.read_csv(f'{filepath}/{f}', index_col=0, parse_dates=[2], infer_datetime_format=True) for f in os.listdir(filepath) if f.endswith('.csv')])

end = time.time()
time_taken = end - start
print(f'{time_taken} seconds')
# -

"""
Attempts:
6.37 seconds
6.012
"""

# #### Method 3: Dask

# +
import dask.dataframe as dd

start = time.time()
df = dd.read_csv(f'{filepath}*.csv')

end = time.time()
time_taken = end - start
print(f'{time_taken} seconds')

# +
start = time.time()
df = df.compute()

end = time.time()
time_taken = end - start
print(f'{time_taken} seconds')
# -

"""
Attempts:
33 seconds
"""

df.info()

# I prefer Method 1

# ### Apply Chosen Method

# +
import glob
import os

filepath_fow = '../data/raw/csv/howstat/fall_of_wickets/'

all_fow = glob.glob(os.path.join(filepath_fow, "*.csv"))
all_fow_to_load = all_fow[:]

df_fow_from_each_file = (pd.read_csv(f, index_col=0, parse_dates=[2], infer_datetime_format=True) for f in all_fow_to_load)
concatenated_df_fow   = pd.concat(df_fow_from_each_file, ignore_index=True)
# -

concatenated_df_fow.info()

#concatenated_df_fow.to_csv('../data/interim/howstat/fall_of_wickets/fow_all.csv')
concatenated_df_fow = pd.read_csv('../data/interim/howstat/fall_of_wickets/fow_all.csv')

# +
filepath_scores = '../data/raw/csv/howstat/scorecards/'

all_scores = glob.glob(os.path.join(filepath_scores, "*.csv"))
all_scores_to_load = all_scores[:]

df_scores_from_each_file = (pd.read_csv(f, index_col=0, parse_dates=[2], infer_datetime_format=True) for f in all_scores_to_load)
concatenated_df_scores   = pd.concat(df_scores_from_each_file, ignore_index=True)
# -

concatenated_df_scores = concatenated_df_scores[['MatchId', 'MatchInnings', 'Team', 'TeamInnings', 'Player', 'R', 'BF']]
concatenated_df_scores['BattingPosition'] = concatenated_df_scores.groupby(['MatchId','MatchInnings', 'Team']).cumcount() + 1

concatenated_df_scores.info()

#concatenated_df_scores.to_csv('../data/interim/howstat/scorecards/scorecards_all.csv')
concatenated_df_scores = pd.read_csv('../data/interim/howstat/scorecards/scorecards_all.csv')

# ### Experiments - Fuzzy Matching
#
#
# We want to get some information from the scorecards into the Fall of Wickets objects. Unfortunately the batter names don't match exactly (scorecards have initials as well). We need to do some fuzzy matching before joining info from the scorecards.

df_fow_to_merge = concatenated_df_fow[concatenated_df_fow.MatchId<=10]
df_scores_to_merge = concatenated_df_scores[concatenated_df_scores.MatchId<=10]

# #### Method 1: fuzzy wuzzy

# +
from cricsheet.utils import fuzzy_merge

start = time.time()

df_merged = fuzzy_merge(df_fow_to_merge, df_scores_to_merge, 'Player', 'Player', 80)

end = time.time()
time_taken = end - start
print(f'{time_taken} seconds')
# -

# 2 seconds for 10 matches.
#
# 183 seconds for 100 matches. 
#
# We have 2400 so this won't scale. Estimate: >2hrs to match all.

# #### Method 2: fuzzy-pandas

# +
import fuzzy_pandas as fpd

start = time.time()

matches = fpd.fuzzy_merge(df_fow_to_merge, df_scores_to_merge,
                          left_on=['Player'],
                          right_on=['Player'], 
                          method='levenshtein',
                          ignore_case=True,
                          keep='match', 
                          join='left-outer', 
                          threshold=0.3)

end = time.time()
time_taken = end - start
print(f'{time_taken} seconds')
# -

# 15 seconds for 10 matches.
#
# 51 seconds for 20 matches. 
#
# We have 2400 so this won't scale. Estimate: >2hrs to match all.

# #### Method 3: difflib

# +
import difflib
from functools import partial

start = time.time()

f = partial(
    difflib.get_close_matches, possibilities=df_scores_to_merge['Player'].tolist(), n=1, cutoff=0.3)

matches = df_fow_to_merge['Player'].map(f).str[0].fillna('')
scores = [
    difflib.SequenceMatcher(None, x, y).ratio() 
    for x, y in zip(matches, df_fow_to_merge['Player'])
]

df_fuzzy_matched = df_fow_to_merge.assign(best=matches, score=scores)

end = time.time()
time_taken = end - start
print(f'{time_taken} seconds')
# -

# 0.85 seconds for 10 matches.
#
# 64 seconds for 100 matches.
#
# 548 seconds for 300 matches.

df_fuzzy_matched['score'].describe()

# All of the above approaches match all of df_fow with all of df_scorecard.
# That isn't necessary. We can do it for each Match, since the batters will match for each match.

# ### Load and Fuzzy-match a Match at a time

# +
import glob
import os

filepath_scores = '../data/raw/csv/howstat/scorecards/'

all_scores = glob.glob(os.path.join(filepath_scores, "*.csv"))
all_scores_to_load = all_scores[:100]


filepath_fow = '../data/raw/csv/howstat/fall_of_wickets/'

all_fow = glob.glob(os.path.join(filepath_fow, "*.csv"))
all_fow_to_load = all_fow[:100]


# +
import difflib
from functools import partial

def fuzzy_match(df1, df2, left_on, right_on):
    
    f = partial(
        difflib.get_close_matches, possibilities=df2[right_on].tolist(), n=2, cutoff=0.3)

    matches = df1[left_on].map(f).str[0].fillna('')
    scores = [
        difflib.SequenceMatcher(None, x, y).ratio() 
        for x, y in zip(matches, df1[left_on])
    ]

    df_fuzzy_matched = df1.assign(best=matches, score=scores)
    
    return df_fuzzy_matched


# +
# check the match_ids match
l_merged_df = []

# for each fow file:
for i in range(len(all_fow_to_load)):
    #print(f'file: {i}')
    
    # read fow, read scorecard
    df_fow = pd.read_csv(all_fow_to_load[i], index_col=0, parse_dates=[2], infer_datetime_format=True)
    df_scores = pd.read_csv(all_scores_to_load[i], index_col=0, parse_dates=[2], infer_datetime_format=True)

    # select cols in scorecard, rename
    df_scores = df_scores[['MatchId', 'MatchInnings', 'Team', 'TeamInnings', 'Player', 'R', 'BF']]
    df_scores['BattingPosition'] = df_scores.groupby(['MatchId','MatchInnings', 'Team']).cumcount() + 1
    
    l_innings = []
    for innings in df_fow.MatchInnings.unique():
        #print(f'innings: {innings}')
        df_fow_innings = df_fow[df_fow.MatchInnings==innings]
        df_scores_innings = df_scores[df_scores.MatchInnings==innings]
        
        # fuzzy match on Player
        df_matched_innings = fuzzy_match(df_fow_innings, df_scores_innings, 'Player', 'Player')

        # merge cols from scores
        df_merged_innings = df_matched_innings.merge(df_scores_innings, 
                                  how='left', 
                                  left_on=['MatchId', 'MatchInnings', 'Team', 'TeamInnings', 'best'], 
                                  right_on=['MatchId', 'MatchInnings', 'Team', 'TeamInnings', 'Player']
                                 )

        # reformat
        df_merged_innings.drop(['Player_x', 'Player_y'], axis=1, inplace=True)
        df_merged_innings = df_merged_innings.rename({'best':'Player'}, axis=1)
        df_merged_innings['Player'] = df_merged_innings['Player'].apply(lambda x: re.sub('[!,*)@#%(&$_?.^†]', '', x))

        l_innings.append(df_merged_innings)        
    
    df_merged_match = pd.concat(l_innings)
    
    l_merged_df.append(df_merged_match)
    

# -

df = pd.concat(l_merged_df)

from cricsheet.fow_analysis.collapses.preprocess_data import load_all_and_process

df = load_all_and_process()

df

# +
start = time.time()

df_grouped = df.groupby(['MatchId','MatchInnings', 'Team']).apply(return_collapses).reset_index()

end = time.time()
time_taken = end - start
print(f'{time_taken} seconds')
# -

df_grouped.to_csv('../data/processed/collapses/all_collapses.csv')

StarcoderdataJupyterScriptsDedupFiltered