<jupyter_start><jupyter_text>Settings<jupyter_code>folder = "data\\stevci\\"
folder_ring = "data\\stevci_obvoznica\\"
use_external_weather = True<jupyter_output><empty_output><jupyter_text>File import<jupyter_code>location_id = get_files_counters.get_ids(folder)
id_location = {location_id[x]:x for x in location_id}
columns = ["STM", 
           "date" ,
           "time", 
           "motorbike1", 
           "car1", 
           "bus1", 
           "LT1", 
           "ST1", 
           "TT1", 
           "TP1", 
           "TPP1", 
           "count1", 
           "motorbike2", 
           "car2", 
           "bus2", 
           "LT2", 
           "ST2", 
           "TT2", 
           "TP2", 
           "TPP2", 
           "count2", 
           "count"]
"""
file = "STEP_MOL_1001-156_2020_09_01__00_00-2020_09_30__23_50.S_U"
df = pd.read_csv(folder+file, 
                 encoding="cp1250", 
                 skiprows=12,
                 header=None, 
                 sep='\s{3,}', 
                 engine='python', 
                 usecols=np.arange(len(columns)))
"""<jupyter_output><empty_output><jupyter_text>Parsing  From CSV files<jupyter_code>i = location_id["Dunajska zel. podvoz > Center"]
del location_id["Dunajska zel. podvoz > Center"]
location_id["Dunajska zel. podvoz <obe smeri> Center"] = i
for i1,name in enumerate(location_id):
    files = get_files_counters.get_file_names(name, location_id, folder)      
    
    for i2,f in enumerate(files):
        df22 = pd.read_csv(folder+f, encoding="cp1250", skiprows=12,sep='\s{1,}', header=None, engine='python', usecols=np.arange(len(columns)))
        #df = pd.read_csv(folder+f, encoding="cp1250", skiprows=11,sep='\s{1,}', engine='python')
        if len(df22.iloc[0,1])<8: # pri letališki je oznaka 1063 Let in je treba en stolpec izpustiti
            df22 = pd.read_csv(folder+f, encoding="cp1250", skiprows=12,sep='\s{1,}', header=None, engine='python', usecols=np.arange(len(columns)+1))
            df22 = df22.drop(columns=df22.columns[1])    
        
        df22 = df22.dropna()
        df22.columns = columns
        if "obe smeri" not in name: 
            df22 = df22[['date', 'time', 'count']]
        else:
            df22 = df22[['date', 'time', 'count1', 'count2']]
            
        if i2 == 0:
            df21 = df22
        else:
            df21 = pd.concat([df21, df22], ignore_index=True, sort=False)
   
    if "obe smeri" not in name:    
        #df21.to_csv(folder+"obdelano\\"+name.replace(">","").replace("<","")+".csv")
        
        df21['location'] = name
        df21['counter_id'] = location_id[name]
        df21['direction'] = 0
        if i1 == 0:
            df = df21
        else:
            df = pd.concat([df, df21], ignore_index=True, sort=False)
    else:
        name_smer1 = name.replace("<obe smeri>","SMER 0")
        name_smer2 = name.replace("<obe smeri>","SMER 1")
            
        df_smer1 = df21[['date', 'time', 'count1']].copy()
        df_smer2 = df21[['date', 'time', 'count2']].copy()
        
        df_smer1.columns = ['date', 'time', 'count']
        df_smer2.columns = ['date', 'time', 'count']
        
        #df_smer1.to_csv(folder+"obdelano\\"+name_smer1+".csv")
        #df_smer2.to_csv(folder+"obdelano\\"+name_smer2+".csv")
    
        df_smer1['location'] = name_smer1
        df_smer1['counter_id'] = location_id[name]
        df_smer1['direction'] = 0
        
        df_smer2['location'] = name_smer2
        df_smer2['counter_id'] = location_id[name]
        df_smer2['direction'] = 1
                
        if i1 == 0:
            df = pd.concat([df_smer1, df_smer2], ignore_index=True, sort=False)
        else:
            df = pd.concat([df, df_smer1, df_smer2], ignore_index=True, sort=False)<jupyter_output><empty_output><jupyter_text>From additional CSV files<jupyter_code>folder_additional = "data\\stevci_dodatni\\"
l = listdir(folder_additional)

name = l[0]

file_name = folder_additional + name
    
f = open(file_name, encoding="cp1250")
f = f.readlines()
label = f[2].split(":")[1].split("/")[0].strip()
cols = f[22].strip().split()

location = label.split()[1:]
counter_id = name.split("_")[0]


folder_additional = "data\\stevci_dodatni\\"
l = listdir(folder_additional)

for i1,name in enumerate(l):
    file_name = folder_additional + name
    
    f = open(file_name, encoding="cp1250")
    f = f.readlines()
    label = f[2].split(":")[1].split("/")[0].strip()
    cols = f[22].strip().split()

    location = label.split()[1:]
    counter_id = name.split("_")[0]
    
    df2 = pd.read_csv(file_name, encoding="cp1250", skiprows=24,sep='\s{1,}', header=None, engine='python', usecols=np.arange(len(cols)))
    df2 =df2.iloc[:,[1,2,11,20,21]]
    df2.columns = ["date", "time", "count1", "count2", "count"]
    df2 = df2.dropna()
    
    if "obe_smeri" not in name:    
        df2 = df2[['date', 'time', 'count']]
        df2['location'] = location
        df2['counter_id'] = counter_id
        df2['direction'] = 0
        df = pd.concat([df, df2], ignore_index=True, sort=False)
    
    else:
        df2 = df2[['date', 'time', 'count1', 'count2']]
        
        name_smer1 = name + " SMER 0"
        name_smer2 = name + " SMER 1"
            
        df_smer1 = df2[['date', 'time', 'count1']].copy()
        df_smer2 = df2[['date', 'time', 'count2']].copy()
        
        df_smer1.columns = ['date', 'time', 'count']
        df_smer2.columns = ['date', 'time', 'count']
        
        #df_smer1.to_csv(folder+"obdelano\\"+name_smer1+".csv")
        #df_smer2.to_csv(folder+"obdelano\\"+name_smer2+".csv")
    
        df_smer1['location'] = name_smer1
        df_smer1['counter_id'] = counter_id
        df_smer1['direction'] = 0
        
        df_smer2['location'] = name_smer2
        df_smer2['counter_id'] = counter_id
        df_smer2['direction'] = 1
                
        df = pd.concat([df, df_smer1, df_smer2], ignore_index=True, sort=False)
        
        
df = df[df["date"]!="skupaj"].copy()
df['date'] = df['date'].map(lambda x: "20"+x.split('.')[2]) + "-" + df['date'].map(lambda x: x.split('.')[1]) + "-" + df['date'].map(lambda x: x.split('.')[0])<jupyter_output><empty_output><jupyter_text>From xlsx files<jupyter_code>l = listdir(folder)
excel_files = []
for f in l:
    if splitext(f)[1] == ".xlsx":
        excel_files.append(f)
excel_files
for name in excel_files:
    xls_file = pd.ExcelFile(folder+name)
    df2 = xls_file.parse(xls_file.sheet_names[1], header=None, skiprows=12)
    df2.columns = columns[1:]
    df2 = df2.dropna()
    
    counter_id = name.split()[0]
    if counter_id in id_location:
        location = id_location[counter_id]
    else:
        location = " ".join(name.split()[1:]).split("-")[0].strip()
        
    
    if "obe smeri" not in name: 
        df2 = df2[['date', 'time', 'count']]       
        df2['location'] = location
        df2['counter_id'] = counter_id
        df2['direction'] = 0
        
        df2['date'] = df2['date'].map(lambda x: x.split(".")[2]+"-"+x.split(".")[1]+"-"+x.split(".")[0])
        
        df = pd.concat([df, df2], ignore_index=True, sort=False)
    else:
        df2 = df2[['date', 'time', 'count1', 'count2']]
        
        df_smer1 = df2[['date', 'time', 'count1']].copy()
        df_smer2 = df2[['date', 'time', 'count2']].copy()
        
               
        df_smer1.columns = ['date', 'time', 'count']
        df_smer2.columns = ['date', 'time', 'count']
        
        if counter_id in id_location:
            name_smer1 = location.replace("<obe smeri>","SMER 0")
            name_smer2 = location.replace("<obe smeri>","SMER 1")
        else:
            name_smer1 = location.replace("(obe smeri)","SMER 0")
            name_smer2 = location.replace("(obe smeri)","SMER 1")
        
        
        df_smer1['location'] = name_smer1
        df_smer1['counter_id'] = counter_id
        df_smer1['direction'] = 0
        
        df_smer2['location'] = name_smer2
        df_smer2['counter_id'] = counter_id
        df_smer2['direction'] = 1
        
        df_smer1['date'] = df_smer1['date'].map(lambda x: x.split(".")[2]+"-"+x.split(".")[1]+"-"+x.split(".")[0])
        df_smer2['date'] = df_smer2['date'].map(lambda x: x.split(".")[2]+"-"+x.split(".")[1]+"-"+x.split(".")[0])
        
        df = pd.concat([df,df_smer1, df_smer2], ignore_index=True, sort=False)<jupyter_output><empty_output><jupyter_text>Additional filter - using `counter_ids.txt`<jupyter_code>s=[]
f = open("data\\stevci\\counter_ids.txt", encoding="utf8")
for x in f:
    x=x.strip()
    s.extend(x.split(";"))
f.close()
print(s)
df = df[df["counter_id"].isin(s)]
df["counter_id"].unique()<jupyter_output><empty_output><jupyter_text>Label directions<jupyter_code>df["counter_id_direction"] = df["counter_id"]
df.loc[df["direction"]==1, "counter_id_direction"] = df["counter_id"]+"-1"<jupyter_output><empty_output><jupyter_text>Dates<jupyter_code>df['date'] = pd.to_datetime(df['date'])<jupyter_output><empty_output><jupyter_text>Times<jupyter_code>df["time"] = df["time"].map(lambda x:int(x.split(":")[0]))<jupyter_output><empty_output><jupyter_text>From highway (ring) counters<jupyter_code>"""
df2 = pd.read_csv(folder_ring+"ring_counters.csv")
df_description = df2[["title", "id", "stevci_lokacijaOpis"]]
df_description = df_description.drop_duplicates()
df_description.to_excel(folder_ring+"ring_counters_description.xlsx", index=False)
df_description.to_csv(folder_ring+"ring_counters_description.csv", index=False)
df2 = df2[["title", "stevci_datum", "stevci_ura", "stevci_stev"]]
df2.columns = ['counter_id', 'date', 'time', 'count']
"""<jupyter_output><empty_output><jupyter_text>Merge all lanes in the same direction and sum the counts within an hour<jupyter_code>"""
df2['counter_id'] = df2['counter_id'].map(lambda x: x.replace(' (v)','').replace(' (p)','').replace(' (po)',''))
df2 = df2.groupby(["counter_id", "date", "time"], as_index=False).sum()

df2["counter_id_direction"] = df2["counter_id"]
df2["location"] = df2["counter_id"]
df2["direction"] = 0

df2['date'] = df2['date'].map(lambda x: x.split("/")[2]+"-"+x.split("/")[1]+"-"+x.split("/")[0])
df2['date'] = pd.to_datetime(df2['date'])

df2 = df2[['date', 'time', 'count', 'location', 'counter_id', 'direction' ,'counter_id_direction']]
"""<jupyter_output><empty_output><jupyter_text>Rounding<jupyter_code>#df2['time'] = df2['time'].map(lambda t: round(float(t.split(":")[0]) + float(t.split(":")[0])/60))<jupyter_output><empty_output><jupyter_text>Duplicate removal<jupyter_code>#df2 = df2.groupby(['date', 'time', 'location', 'counter_id', 'direction' ,'counter_id_direction'], as_index=False).max()<jupyter_output><empty_output><jupyter_text>Filter<jupyter_code>#df2 = df2[df2["counter_id"]=="HC-H3, LJ (S obvoznica) : LJ (Celovška - Dunajska)"]<jupyter_output><empty_output><jupyter_text>Concat with other counters<jupyter_code>df = pd.concat([df,df2], ignore_index=True, sort=False)<jupyter_output><empty_output><jupyter_text>Export the counter locations<jupyter_code>f = open("data\\counter_locations.txt", 'w', encoding="utf8")
l = list(set(zip(df["location"], df["counter_id"])))
l.sort()
for counter_loc_id in l:
    print(counter_loc_id, file=f)
f.close()

#pd.DataFrame({'location':df.location.unique()}).to_excel("data\\counter_locations.xlsx", index=False)<jupyter_output><empty_output><jupyter_text>Reduce the columns<jupyter_code>df = df[['date', 'time', 'counter_id_direction', 'count']]
df.columns = ['date', 'time', 'counter_id', 'count']
#df['date'] = df['date'].map(lambda x: "20"+x.split('.')[2]) + "-" + df['date'].map(lambda x: x.split('.')[1]) + "-" + df['date'].map(lambda x: x.split('.')[0] if len(x) < 10 else x)<jupyter_output><empty_output><jupyter_text>Unify the ids of the counters with different ids<jupyter_code># read the synonyms
d = {}

f = open("data\\counter_synonyms.txt", encoding="utf8")
for l in f:
    l = l.strip().split(";")
    primary = l[0]
    syns = l[1:]
    d[primary] = syns
for primary, syns in d.items():
    for syn in syns:
        df.loc[df['counter_id']  == syn, 'counter_id'] = primary
        df.loc[df['counter_id']  == syn+'-1', 'counter_id'] = primary+'-1'<jupyter_output><empty_output><jupyter_text>Remove potential duplicates<jupyter_code>df = df.groupby(['counter_id', 'date', 'time'], as_index=False).max()
# zakaj tole meč ven 1016-140;1946-230; 1035-136<jupyter_output><empty_output><jupyter_text>Remove outliers<jupyter_code>cs = df.counter_id.unique()
cs
df_filtered = pd.DataFrame(columns = df.columns)


for c in cs:
    df2 = df[df.counter_id == c]
    
    
    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_filtered = pd.concat([df_filtered,df2], ignore_index=True, sort=False)
df = df_filtered<jupyter_output><empty_output><jupyter_text>Dates<jupyter_code>df['weekday'] = df['date'].dt.weekday
df['workday'] = df['weekday'].map(lambda x: 1 if x < 5 else 0)
df = df.drop(columns = "weekday")
df['workday'].describe()<jupyter_output><empty_output><jupyter_text>Weather data<jupyter_code>df.date.head()
#pd.to_datetime(df_weather['date']).dt.date
df.head()
if not use_external_weather:
    df_weather = pd.read_csv("data\\google_data_filtered.csv", encoding="utf8")
    df_weather = df_weather[["date", "weather"]]
    df_weather = df_weather.drop_duplicates()
    df_weather["date"] = pd.to_datetime(df_weather['date'])
    df = pd.merge(df, df_weather.drop_duplicates(), on="date", how="left")
else:
    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'] == date, 'weather'].iloc[0]
        except:
            weather = np.nan
        locs = date == df['date']
        df.loc[locs, "weather"] = weather<jupyter_output><empty_output><jupyter_text>Export<jupyter_code>df.to_csv("data\\counters.csv", encoding="utf8", index=False)
df.counter_id.unique()
df[df['weather'].isna()].date.unique()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup