<jupyter_start><jupyter_text>Utility function to split data in given time range<jupyter_code>def split_data(dir_loc, time_rnge_list, op_path_list):
    os.chdir(dir_loc)
    for fl in os.listdir():
        if fl != '.DS_Store':
            fl_name = os.path.basename(fl).split('.')[0]
            df = pd.read_csv(fl)
            for tme_rnge, op_path in zip(time_rnge_list, op_path_list):
                df1 = df.query(f'YEAR >= {tme_rnge[0]} & YEAR <={tme_rnge[1]}')
                df1['pop_wt'] = 
                df1.to_csv(f'{op_path}/{fl_name}_{tme_rnge[0]}_{tme_rnge[1]}.csv', index=False)<jupyter_output><empty_output><jupyter_text>Split into 2 categories - 1990, 2002 ; 2003, 2015<jupyter_code>split_data(dir_loc='/Users/salma/Research/us_crime_data_analysis/data/final_all_core_dm',
          time_rnge_list=[[1990, 2002], [2003, 2015]],
          op_path_list=['/Users/salma/Research/us_crime_data_analysis/data/final_core_dm_90_02',
                       '/Users/salma/Research/us_crime_data_analysis/data/final_core_dm_03_15'])
#### read the required data files

## counts
fnl_large_core_dm_counts_90_02 = pd.read_csv('/Users/salma/Research/us_crime_data_analysis/data/final_core_dm_90_02/final_main_large_cities_core_counts_dm_1990_2002.csv')
fnl_medium_core_dm_counts_90_02 = pd.read_csv('/Users/salma/Research/us_crime_data_analysis/data/final_core_dm_90_02/final_main_medium_cities_core_counts_dm_1990_2002.csv')
fnl_small_core_dm_counts_90_02 = pd.read_csv('/Users/salma/Research/us_crime_data_analysis/data/final_core_dm_90_02/final_main_small_cities_core_counts_dm_1990_2002.csv')

## rates
fnl_large_core_dm_rates_03_15 = pd.read_csv('/Users/salma/Research/us_crime_data_analysis/data/final_core_dm_03_15/final_main_large_cities_core_counts_dm_2003_2015.csv')
fnl_medium_core_dm_rates_03_15 = pd.read_csv('/Users/salma/Research/us_crime_data_analysis/data/final_core_dm_03_15/final_main_medium_cities_core_counts_dm_2003_2015.csv')
fnl_small_core_dm_rates_03_15 = pd.read_csv('/Users/salma/Research/us_crime_data_analysis/data/final_core_dm_03_15/final_main_small_cities_core_counts_dm_2003_2015.csv')<jupyter_output><empty_output><jupyter_text>Get middle reference year to get pop col for weighting<jupyter_code>def create_pop_wt_col_by_time_range(req_dfs, time_range):
    for df in req_dfs:
        df_wt = df.query(f'YEAR >= {time_range[0]} and YEAR <= {time_range[1]}').loc[:, ['ORI', 'YEAR', 'fnl_population']]
        df_wt['pop_wt'] = df_wt.groupby('ORI')['fnl_population'].head(1)
        
        df_wt.dropna(subset = ['pop_wt'], inplace=True)
        
        df = df.merge(df_wt, on='ORI', how='left')
        df.rename({'YEAR_x': 'YEAR', 'fnl_population_x': 'fnl_population'}, axis=1, inplace=True)
        df.drop(['YEAR_y', 'fnl_population_y'], axis=1, inplace=True)
        #print(df.loc[:, ['ORI', 'YEAR', 'fnl_population', 'pop_wt']].head(50))
        #print(df.query('ORI == "AL00102"').loc[:, ['ORI', 'YEAR', 'fnl_population', 'pop_wt']])
        print(set(df.loc[df['pop_wt'].isnull()]['ORI']).__len__())
        print(set(df.loc[df['pop_wt'].isnull()]['ORI']))
        print('\n')
    
create_pop_wt_col_by_time_range([fnl_large_core_dm_counts_90_02, fnl_medium_core_dm_counts_90_02, fnl_small_core_dm_counts_90_02],
                 time_range=[1994, 1998])<jupyter_output>3
{'KS04606', 'KS10502', 'KS04605'}


28
{'IL01680', 'OH02300', 'TN09500', 'MT00701', 'IL01001', 'KS02301', 'IL08200', 'IN05300', 'IL04500', 'MI63808', 'IN04800', 'NM02601', 'AZ01405', 'LA04400', 'OH07300', 'PA03504', 'MS03000', 'TN07800', 'IL05701', 'KS04609', 'IL04921', 'IL09902', 'OH08300', 'OH04704', 'IL05802', 'IL01683', 'KS08100', 'OH08500'}


276
{'IN00900', 'KY06000', 'KS01801', 'NY05955', 'TN05500', 'IL05300', 'KY00200', 'OH05722', 'IL03700', 'KS00802', 'KS06300', 'PA02603', 'IN02400', 'KS08900', 'MO10800', 'IL02227', 'OH07601', 'NM00900', 'IL09913', 'MA00523', 'IL01633', 'KS00502', 'OH07807', 'MS06900', 'MS04700', 'KS05701', 'IL01004', 'IL05000', 'IL06901', 'TN02300', 'MS07000', 'IL01692', 'IL06800', 'GA03900', 'IL02220', 'IA02300', 'IN08000', 'KS08700', 'KY00700', 'KY07601', 'KY09600', 'IN07000', 'IL08100', 'WA01726', 'IL04926', 'OH07501', 'IL04903', 'IL06102', 'IL0163B', 'TN02600', 'MO11500', 'IL09200', 'MO05300', 'OH03201', 'OH05401', 'IN03201', 'MS01700', 'MS06600', 'MO02[...]
{'fraction_non_alphanumeric': 0.17198335644937587, 'fraction_numerical': 0.14539990753582988, 'mean_word_length': 5.792778649921507, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 14, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '564889', 'license': 'MIT', 'n_tokens_mistral': 2223, 'n_tokens_neox': 1786, 'n_words': 224, 'path': 'exploratory_data_analysis/time_data_splitter_ref_pop_finder.ipynb', 'repo_name': 'salma-shaik/us_crime_data_analysis', 'size': '4326'}