<jupyter_start><jupyter_text>Table of Contents1&nbsp;&nbsp;Preparation1.1&nbsp;&nbsp;Paths1.2&nbsp;&nbsp;Read school locations from each school year2&nbsp;&nbsp;Harmonize school names3&nbsp;&nbsp;Adjust addresses and coordinates for schools with multiple differing entries3.1&nbsp;&nbsp;Find schools which changed location4&nbsp;&nbsp;Convert to geopandas dataframe5&nbsp;&nbsp;Save **Description**: Loads and preprocesses school locations, harmonizes school names according to the conventions used in this project, creates a preliminary treatment indicator, and then saves the data.---<jupyter_code>%matplotlib inline
import pickle
import re
import sys
from itertools import combinations
from pathlib import Path

import geopandas as gpd
import pandas as pd
import numpy as np
from shapely.geometry import Point

sys.path.append('../..')
from src.calculate.distances import vincenty
from src.prepare_data.routes import check_school_name_id_unique
from src.prepare_data.school_locations import (split_school, take_last_values,
                                               uppercase_school_categories,
                                               load_locations)<jupyter_output><empty_output><jupyter_text>Preparation Paths<jupyter_code>data_path = Path('../../data')
school_locations_path = data_path / 'raw/school_locations'
location_files = [('CPS_School_Locations_SY1516.csv',
                   'SY1516'), ('CPS_School_Locations_SY1415.csv',
                               'SY1415'), ('Units2013_14.csv', 'SY1314')]<jupyter_output><empty_output><jupyter_text>Read school locations from each school year<jupyter_code>schools = []
for file, school_year in location_files:
    schools.append(
        load_locations(school_locations_path / file, school_year))<jupyter_output><empty_output><jupyter_text>Append all locations together<jupyter_code>schools = pd.concat(schools, ignore_index=True, sort=True)<jupyter_output><empty_output><jupyter_text>Harmonize school names Let's merge harmonized school names from routes dataset by school_id.First, load the routes dataset<jupyter_code>with (data_path / 'processed/routes.pkl').open('rb') as f:
    all_routes = pickle.load(f)<jupyter_output><empty_output><jupyter_text>Extract harmonized school names and corresponding school ids<jupyter_code>harmonized_names = all_routes[['school_id',
                               'school_name']].drop_duplicates()<jupyter_output><empty_output><jupyter_text>Create treatment dummy by school and school year<jupyter_code>treated_dummy = all_routes[['school_id', 'school_year']]
assert not any(treated_dummy.duplicated())
del all_routes
treated_dummy['treated'] = 1<jupyter_output><empty_output><jupyter_text>Merge new school names, keep old namesfor never treated schools<jupyter_code>schools['school_name_backup'] = schools['school_name']
schools.drop('school_name', axis='columns', inplace=True)
schools = schools.merge(
    harmonized_names, how='left', on='school_id')<jupyter_output><empty_output><jupyter_text>Takes school name of last school year per school id<jupyter_code>untreated_schools = schools[schools['school_name']
                                  .isnull()].copy()
untreated_schools.sort_values(['school_id', 'school_year'], inplace=True)
untreated_schools = untreated_schools.groupby('school_id').apply(
    lambda x: x.tail(1)['school_name_backup'])
untreated_schools = untreated_schools.reset_index(0).reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Adjust formatting of school names of untreated schools, i.e. capitalize and Hs -> HS and Es -> ES<jupyter_code>untreated_schools['school_name_backup'] = untreated_schools[
    'school_name_backup'].str.title().apply(uppercase_school_categories)<jupyter_output><empty_output><jupyter_text>Drop school_name_backup and merge newly created columnschool_name_backup, which contains (at least for all untreated schools)the school name of the last school year in the datasetas described above<jupyter_code>schools.drop('school_name_backup', axis='columns', inplace=True)
schools = schools.merge(
    untreated_schools, how='left', on='school_id')<jupyter_output><empty_output><jupyter_text>Harmonize school name for all untreated schools<jupyter_code>schools.loc[schools['school_name'].isnull(),
                  'school_name'] = schools.loc[schools[
                      'school_name'].isnull(), 'school_name_backup']
schools.drop('school_name_backup', axis='columns', inplace=True)<jupyter_output><empty_output><jupyter_text>Merge treatment dummy<jupyter_code>schools = schools.merge(
    treated_dummy, how='left', on=['school_id', 'school_year'])
assert any(schools['treated'].isnull())
schools.loc[schools['treated'].isnull(), 'treated'] = 0<jupyter_output><empty_output><jupyter_text>Adjust addresses and coordinates for schools with multiple differing entries<jupyter_code>schools = schools.sort_values(
    ['school_name', 'school_year']).reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Find schools which changed location Check GPS coordinates<jupyter_code>schools = pd.concat(
    [
        schools,
        schools.duplicated(['school_name', 'lat', 'lon'],
                                    keep=False).rename('duplicated')
    ],
    axis='columns')

duplicated_analysis = schools.groupby('school_name').filter(
    lambda x: not x['duplicated'].all() and len(x) > 1).sort_values(
        'school_name').reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Check maximum difference in GPS coordinates<jupyter_code>def mark_problems(x, max_dist_allowed=10):
    """Marks groups where the highest distance is higher
    than max_dist_allowed.
    
    Parameters
    ----------
    x : pd.DataFrame
        Intended for a groupby by school_name
        
    max_dist_allowed : int
        Maximum distance in meters allowed
        
    Returns
    -------
    pd.DataFrame
        Same as x but with additional columns problem
        and max_dist
    """
    x_temp = x[['lat', 'lon']].drop_duplicates()
    combos = list(combinations(x_temp.values, 2))
    distances = map(lambda p: vincenty(p[0], p[1]), combos)
    max_dist = max(distances, default=0)
    x['problem'] = True if max_dist > max_dist_allowed else False
    x['max_dist'] = max_dist
    return x
problems = duplicated_analysis.groupby('school_name').apply(
    mark_problems, max_dist_allowed=10).query(
        'problem == True').sort_values('school_name')
problems.shape
problems_nodup = problems.drop_duplicates('school_name')
len(problems_nodup)
problems_nodup['max_dist'].describe()
problems_nodup['max_dist'].hist();
problems = problems.sort_values(['school_name', 'school_year'])<jupyter_output><empty_output><jupyter_text>Create dataset of schools which moved.<jupyter_code>all_schools_with_diff = duplicated_analysis.groupby('school_name').apply(
    mark_problems, max_dist_allowed=0).query('problem == True').sort_values(
        'school_name').drop(
            ['problem', 'duplicated'], axis='columns')
high_diff_schools = all_schools_with_diff.loc[
    all_schools_with_diff['max_dist'] >= 500, 'school_name'].unique().tolist()

high_diff_schools
schools = schools.sort_values(['school_name', 'school_year'])<jupyter_output><empty_output><jupyter_text>There are 11 schools which seemed to have moved in the period of interest. The following presents a dictionary, where the value is the number of the first oberversation number form where on the schools seemed to have changed (zero-indexed).NOTE: This would need to be automated if school years where to be extended.<jupyter_code>moved_schools = {
    'Chi Arts': 1,
    'Fr<NAME>': 1,
    'Intrinsic HS': 1,
    '<NAME> - Roseland HS': 1,
    'Noble - Academy HS': 1,
    'Ombudsman - Northwest HS': 1,
    'Ombudsman - South HS': 1,
    'Ombudsman - West HS': 1,
    'Urban Prep Chtr Bronzeville': 1,
    'YCCS - Houston': 2,
    'YCCS - Innovations': 1
}<jupyter_output><empty_output><jupyter_text>Split schools which moved into old and new entries by date of move<jupyter_code>schools = schools.groupby('school_name').apply(
    split_school, moved_schools=moved_schools)<jupyter_output><empty_output><jupyter_text>Take last values for coordinates and address<jupyter_code>schools = schools.groupby('school_name').apply(
    take_last_values)<jupyter_output><empty_output><jupyter_text>Convert to geopandas dataframe Coordinatesystem to use<jupyter_code>crs = {'init': 'epsg:4326'}<jupyter_output><empty_output><jupyter_text>Convert dataframe<jupyter_code>loc_shapely = [
    Point(lon, lat)
    for lon, lat in zip(schools['lon'], schools['lat'])
]
schools = gpd.GeoDataFrame(
    data=schools, geometry=loc_shapely, crs=crs)<jupyter_output><empty_output><jupyter_text>Save Make sure that only one school id corresponds to each nameand other way around<jupyter_code>check_school_name_id_unique(schools)<jupyter_output><empty_output><jupyter_text>Save school locations<jupyter_code>schools = schools.sort_values(['school_name', 'school_year'])
schools = schools.reset_index(drop=True)
if not (data_path / 'interim').is_dir():
        (data_path / 'interim').mkdir()
with (data_path / 'interim/schools.pkl').open('wb') as f:
    pickle.dump(schools, f)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11344446849967525, 'fraction_numerical': 0.007685646243775709, 'mean_word_length': 5.42043085476025, 'pattern_counts': {'":': 0, '<': 99, '<?xml version=': 0, '>': 104, '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': '597451', 'license': 'MIT', 'n_tokens_mistral': 3016, 'n_tokens_neox': 2905, 'n_words': 609, 'path': 'notebooks/1_prepare_data/2.0-binste-school-locations.ipynb', 'repo_name': 'binste/chicago_safepassage_evaluation', 'size': '9252'}