# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: 'Python 3.7.3 64-bit (''base'': conda)'
#     language: python
#     name: python37364bitbasecondacd385dda59854559b44e3f82ede14f15
# ---

# +
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt

from IPython.display import display

from datetime import date
import datetime
# -

locations = {'Slovenska': '9000000619',
             'Dunajska1': '9000001783',
             'Dunajska2': '9000000656',
             'Dunajska3': '9000000655',
             'Izanska1': '9000000820',
             'Izanska2': '9000001506',
             'Celovska': '9000001950',
             'Gosposvetska': '9000000529',
             'Koper': '9000001092'}


# ## Settings

# +
separate_directions = True
remove_outliers = True

pct_up_threshold = 0.5
# -

# ## Read the counters

# +
df = pd.DataFrame()

for label, loc_id in locations.items():
    file_name = 'data\\'+label+'_telraam.csv'
    df2 = pd.read_csv(file_name)
    df2['location'] = label
    df2['counter_id'] = loc_id
    print(label+"; from:", df2['date'].min()+"; to:", df2['date'].max())
    
    df = df.append(df2, ignore_index=True)

df = df[df['pct_up'] != 0]
if pct_up_threshold:
    df = df[df['pct_up'] >= pct_up_threshold]

df.reset_index(drop=True, inplace=True)

# +
#df['pedestrian']*df['pct_up']
# -

# ## Dates
# * Convert from UTC to local time
# * get dates and times
# * get workday data

df['utc'] = df['date'].map(lambda d:datetime.datetime.strptime(d,'%Y-%m-%dT%H:%M:%S.%fZ'))
df['local'] = df['utc'].map(lambda x:datetime.datetime.fromtimestamp(x.timestamp()))

#df['day'] = df['local'].map(date.weekday)
#df['hour'] = pd.DatetimeIndex(df['local']).hour
df['date'] = pd.DatetimeIndex(df['local']).date
df['time'] = pd.DatetimeIndex(df['local']).hour + pd.DatetimeIndex(df['local']).minute/60 + pd.DatetimeIndex(df['local']).second/3600

df['weekday'] = df['local'].dt.weekday
df['workday'] = df['weekday'].map(lambda x: 1 if x < 5 else 0)
df = df.drop(columns = "weekday")
df['workday'].describe()

# ## Weather data

df_weather = pd.read_csv('data\\weather.csv')
#df_weather['date'] = pd.to_datetime(df_weather['date'])
for date in df['date'].unique():
    try:
        weather = df_weather.loc[df_weather['date'] == str(date), 'weather'].iloc[0]
    except:
        weather = np.nan
    locs = date == df['date']
    df.loc[locs, "weather"] = weather

# ## Editing the columns

df = df.drop(columns=['utc', 'local'])

df = df[['counter_id', 'date', 'time', 'pedestrian', 'bike', 'car', 'lorry', 'pedestrian_lft',
       'bike_lft', 'car_lft', 'lorry_lft', 'pedestrian_rgt', 'bike_rgt',
       'car_rgt', 'lorry_rgt', 'location', 'pct_up', 'workday', 'weather']]

# ## Saving

df.to_csv("data\\telraam_full.csv", index=False)

df

# ### Smaller dataset

df['count'] = df['car'] + df['lorry']
df['count_lft'] = df['car_lft'] + df['lorry_lft']
df['count_rgt'] = df['car_rgt'] + df['lorry_rgt']

# +
df_small = pd.DataFrame(columns = ['counter_id','date','time','count','workday','weather'])
counter_ids = df.counter_id.unique()

for counter_id in counter_ids:
    #df1 = df[df['counter_id'] == counter_id]
    df1 = df[df['counter_id'].str.startswith(counter_id)]

    
    if separate_directions:
        df11 = df1[['counter_id','date','time','count_lft','workday','weather']].copy()
        df11['counter_id'] = df11['counter_id'] + '-1'
        df12 = df1[['counter_id','date','time','count_rgt','workday','weather']].copy()
        df12['counter_id'] = df12['counter_id'] + '-2'
    
        df11.columns = ['counter_id','date','time','count','workday','weather']
        df12.columns = ['counter_id','date','time','count','workday','weather']

        df_small= pd.concat((df_small, df11), ignore_index=True)
        df_small= pd.concat((df_small, df12), ignore_index=True)
    else:
        df1 = df1[['counter_id','date','time','count','workday','weather']]
        df_small= pd.concat((df_small, df1), ignore_index=True)
        
# -

df_small.shape

# ### Remove outliers

# +
if remove_outliers:
    df_small = df_small[df_small['count'] != 0]        
    df = pd.DataFrame()
    for counter_id in counter_ids:
        #df2 = df_small[df_small['counter_id'] == counter_id]   
        df2 = df_small[df_small['counter_id'].str.startswith(counter_id)]

        m, s = np.mean(df2['count']), np.std(df2['count'])
    
        idxs = np.logical_not(np.logical_or(df2['count'] > m + 3 * s, df2['count'] < m - 3 * s))
        df2 = df2[idxs]
        
        df = pd.concat([df,df2], ignore_index=True, sort=False)
        
        
else:
    df = df_small
    
# -

df.shape

# ## Saving

df.to_csv("data\\telraam.csv", index=False)

df



StarcoderdataJupyterScriptsDedupFiltered