2. 01-geotagging

• This notebooks creates gpkgs for each day with all of its road closures

• Step 19 of methodology

import pandas as pd
import geopandas as gpd
import os
import numpy as np

3. functions

def update_km(closures):
    for index, row in closures.iterrows():
        #update the comma-separated km markings to be a list
        km = row['KM']
        if isinstance(km, str):
            km = km.split(',')
        col = closures.columns.get_loc('KM')
        closures.iat[index, col] = km
    return closures

def add_closures(closures):
    road_sample = gpd.read_file('/home/jovyan/work/data/b-roads/road-split/54.gpkg') #grab desired column names
    date_gpkg = road_sample[0:0]
    
    #manually update the closures
    for index, row in closures.iterrows():
        road_id = row['Road ID']
        km = row['KM']

        #find the road and date files, update
        road_path = '/home/jovyan/work/data/b-roads/road-split/' + str(road_id) + '.gpkg'
        road_gpkg = gpd.read_file(road_path)
        status_col = road_gpkg.columns.get_loc('status')

        #only 1 closure
        if isinstance(km, int):
            try:
                row_ind = road_gpkg.index[road_gpkg['segment_id'] == km].tolist()
                road_gpkg.at[row_ind, 'status'] = 1
                date_gpkg = date_gpkg.append(road_gpkg)

            except IndexError:
                print("Not a valid road closure - road does not have this km marking")
                continue
        
        #multiple closures
        else:
            for i in range(0, len(km)):
                try:
                    row_ind = road_gpkg.index[road_gpkg['segment_id'] == int(km[i])].tolist()
                    road_gpkg.at[row_ind, 'status'] = 1
                except IndexError:
                    print("Not a valid road closure - road does not have this km marking")
            date_gpkg = date_gpkg.append(road_gpkg)
    
    date = closures['Date'][0].date().strftime('%Y-%m-%d')
    date_gpkg.loc[:, 'date'] = date
    
    return date_gpkg

def make_closure_file(filename):
    closures = pd.read_excel(filename) #list of road closures on a date
    closures.astype(object)

    date = closures['Date'][0].date().strftime('%Y-%m-%d')
    date_path = '/home/jovyan/work/data/a-closures/closures-agg/' + date + '.gpkg'

    closures = update_km(closures)

    date_gpkg = add_closures(closures)
    date_gpkg.to_file(date_path, driver='GPKG')
    print('done!')
    return date_gpkg

4. START HERE

• update the filepath with each new closure xlsx that needs to be read, then run straight through

filepath = '/home/jovyan/work/data/a-closures/daily/2021-07-02.xlsx'
data = make_closure_file(filepath)

done!

data.loc[data['status']==1]

	road_id	date	status	segment_id	geometry
56	54	2021-07-02	1	57	MULTILINESTRING ((79.07799 30.17550, 79.07768 ...