<jupyter_start><jupyter_text>Melb PTV Datathon 2018: Data Wrangling (Flinders vers)Intro:Just grab the parent route data from the card data and do some simple visualisationsDate: 25/9/2018Version: 0.11Environment: Python 3.5.2 and Jupyter notebookLibraries used:     pandas, re, string, os, gzip, numpy, re, datetime, seaborn, matplotlibe, geopy  quick links- Import files and libraries- look at frequencies on route by hour of day- Create frequency table for time windows of 30 mins- Normalise frequencies for unity display- Save to file    Import datafiles and python libraries<jupyter_code># Import the libraries needed to read and report on data files
import pandas as pd
import re
import string
import os 
import gzip
import numpy as np
import datetime as dt
# import seaborn as sns
from matplotlib import pyplot as plt
%matplotlib inline
import matplotlib.dates as mdates
import geopy.distance as distance
import matplotlib.pyplot as plt
import matplotlib.cm
import peakutils
from mpl_toolkits.basemap import Basemap
from random import randint
# code is working for this filtering
myYears = ['2018']
# search for files from root directory down
mypath = "./data"
colNames= ('StopID', 'SourceSystem', 'name','location','StopType','Suburb','PostCode','City','Council','Region','Lat','Long','LatTolerance', 'LongTolerance')
dfStops = pd.DataFrame()
with open("new_Stop_Data.txt",'r',encoding = "ISO-8859-1") as f:
    dfStops = pd.read_table(f, delimiter='\t', names = colNames, dtype={'StopID':np.int64,  'Region':'category'})
    print('The dfStops dataframe has ' + str(dfStops.shape) + ' rows and columns with the following column names and data types.')
    dfStops.dtypes
# get lat long of flinders st station
cLat = dfStops[dfStops['StopID']==19854].Lat.unique()
cLong= dfStops[dfStops['StopID']==19854].Long.unique()
cLatLong = [cLat[0],cLong[0]]


# create tuple of lat, long to make manipulation easier
dfStops['LatLong'] = dfStops[['Lat','Long']].apply(tuple,axis=1)
dfStops['cDist']=dfStops.apply(lambda row: distance.distance(cLatLong,row['LatLong']).km,axis=1 )
dfStops.cDist.describe()
# don't run this for flinders
flindersStops = dfStops[dfStops['cDist']<.2].StopID.unique().astype(str)
flindersStops
len(flindersStops)
# Walk through directories to collate all  gz (compressed) filenames

fnames = []
fullnames = []
for root, dirs,files in os.walk(mypath):
    for f in files:
        if "gz" in f:
            fnames.append(os.path.join(root,f).split('/'))
            fullnames.append(os.path.join(root,f)) 

# put files names in a dataframe
allFiles = pd.DataFrame(fnames,columns=['Root','Subdir','Sample','ScanType','Year','Week','fName'])
allFiles['FullName'] = fullnames

allFiles.sample(10)
allFiles[allFiles['Year']=='2018'].describe()
# create list for all weeks in specified year
allWeeks = allFiles[allFiles['Year'].isin(myYears)].Week.unique()
# set type of scan by the filename
allFiles['OnOff'] = allFiles['ScanType'].apply(lambda x: "Off" if x == "ScanOffTransaction" else "On")
# convert week number into an number for easier manipulation
allFiles['WeekNbr'] = allFiles['Week'].apply(lambda x: re.findall(r'\d+',x)[0])
allFiles['lines'] = None
allFiles[allFiles['OnOff']=='On'].describe()
flindersStops = []
allFiles[allFiles['OnOff']!='On'].describe()
# only interested in scanon
allFiles = allFiles[allFiles['OnOff']=='On'].sort_values(by='Week')
# only interested in week10 for now
allWeeks = ['Week10']
# allWeeks
def getSampleForWeek(filename,scan, yr,wk):
    print('get data from file '+filename)
    for line in gzip.open(filename,'rt'):
        line = line.rstrip() + "|" + scan+'|'+yr+'|'+wk
        datLines.append(line.split('|'))
        
    df= pd.DataFrame(datLines)
    df.columns=['Mode','BusinessDate','DateTime','CardID','CardType','VehicleID','ParentRoute','RouteID','StopID','ScanType','Year','Week']

    if len(flindersStops)>0:
        df = df.loc[df['StopID'].isin(flindersStops)]
        print("flinders")
    
    # convert datetime to type datetime (needs to be numpy datetime64 for windowing)
    # created new column as dodgy workaround for problem code
    df['newDateTime']  = pd.to_datetime(df['DateTime'].astype(str), errors='coerce')
    # convert datetime into string for unity parsing
    df['Day']=df['newDateTime'].apply(lambda x: dt.datetime.strftime(x,"%d-%b-%Y"))
    df.set_index('newDateTime',inplace=True)
    scols = ['Day','DateTime']
    df.sort_values(scols, inplace=True)
    # create the time windows wtih groupby
    # todo consider 2nd level group by parentroute
    gcols = ['Day']
    grp = df.groupby(gcols).resample('30min').CardID.count().reset_index()
    grp['Week'] = wk
    grp['Year'] = yr
    print('sample of grp')
    print(grp.sample(2))
    return grp
def AgggregateDataWindows(alldf,thisDate, dataType):

        print("been given these values")
        print(alldf.sample(2))
        print("===given")
    
        # group data into 30 minute time windows
        gcols = ['Year','Week','Time','Minutes']
        newgrp = alldf.groupby(gcols).CardID.sum().reset_index()

        newgrp.columns = ['Year','Week','Time','Minutes','Freq']
        newgrp['Day']= thisDate
        gcols=['Year','Week','Day','Time','Minutes','Freq']
        newgrp = newgrp[gcols]

        # normalise the frequency counts for mapping in unity
        maxFreq = newgrp.Freq.max()
        minFreq = newgrp.Freq.min()
        newgrp['normFreq'] = newgrp['Freq'].apply(lambda x: (x-minFreq)/(maxFreq-minFreq))

        # sort into time order
        scols = ['Year','Week','Minutes']    
        newgrp.sort_values(by=scols, inplace=True)

        # identify peaks for audio
        rowindices = peakutils.indexes(newgrp['normFreq'],thres=0.15,min_dist=10)
        print("peaks at "+str(rowindices))
        newgrp['Peak']=False
        colIndex = newgrp.columns.get_loc('Peak')
        newgrp.iloc[rowindices,colIndex]=True
 
        newgrp['DataType']=dataType
    
        # sort into time order
        scols = ['DataType','Year','Week','Day','Minutes']
        # put columns in expected order for unity
        gcols = ['DataType','Year','Week','Day', 'Minutes','Time', 'Freq', 'normFreq', 'Peak']
        newgrp = newgrp[gcols]

        return newgrp.sort_values(scols)
# import all scanon data files for selected year (parameter at the beginning)
# takes about an hour

for w in allWeeks:
    # @filter
    datLines = []
    allGrps = pd.DataFrame()
    for i,row in allFiles[(allFiles['Year'].isin(myYears))&(allFiles['Week']==w)].iterrows():
        
        print(dt.datetime.now().strftime('%H:%M')+ 
          ' Processing '+row['Year']+' week '+ row['Week']+' file: '+ row['FullName'])
        grp = getSampleForWeek(row['FullName'],row['OnOff'],row['Year'],row['Week'])
        allGrps = allGrps.append(grp)
        # todo add mode to show train/tram/bus proportions
    
    day1 = allGrps['Day'].min()
    daysInWeek = allGrps['Day'].unique()

    # convert time into minutes after midnight
    allGrps['Minutes']=allGrps['newDateTime'].apply(lambda x: (x-x.normalize()).total_seconds()/60/30)
    allGrps['Time']=allGrps['newDateTime'].apply(lambda x: dt.datetime.time(x))
    
    print('grouping the weeks data across samples')
    print('includes days '+allGrps['Day'].unique())
    grp = AgggregateDataWindows(allGrps,day1,'Weekly') 

    for d in daysInWeek:
        print('group into daily data for the week: date '+d)
        dayGrp = AgggregateDataWindows(allGrps[allGrps['Day']==d],d,'Daily')
        grp = grp.append(dayGrp,ignore_index=True,sort=True)

    # sort into time order
    scols = ['DataType','Day','Minutes']
    colIndex = grp.columns.get_loc('Day')
    # save week data for visualisation in unity
    fname = "weekly"+str(grp.iloc[1,colIndex])+".csv"
    if len(flindersStops)>0:
        fname = "ecgflinders"+str(grp.iloc[1,colIndex])+".csv"
        print("flinders")
    grp.sort_values(by=scols).to_csv(fname,index=False)
    
print(dt.datetime.now().strftime('%H:%M'))      
print('------Finished all weeks ------')

grp.describe()
    
grp['Day'].unique()<jupyter_output><empty_output><jupyter_text>todo - try to pickup school time peak identify peaks for audiorowindices = peakutils.indexes(grp['normFreq'],thres=0.5,min_dist=.5)print("peaks at "+str(rowindices))grp['Peak']=FalsecolIndex = grp.columns.get_loc('Peak')grp.iloc[rowindices,colIndex]=True<jupyter_code>str(rowindices)
grp[grp['Peak']==True]
grp.iloc[1,3].astype(str)
# look at pattern of cards scan on and off by hourly window
myPlt = grp['Freq'].plot()
plt.title('scanon frequencies by 30 min window')
plt.ylabel('freq'), plt.xlabel('time of day');
grp.columns
grp.sample(10)
allGrps['Day'].min()
allGrps.sample(10)
# todo add in sunrise/sunset

grp.sample(10)
grp.columns<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15471183357962942, 'fraction_numerical': 0.010571785836080483, 'mean_word_length': 4.39423666462293, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 11, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '58246', 'license': 'MIT', 'n_tokens_mistral': 3008, 'n_tokens_neox': 2928, 'n_words': 679, 'path': 'daily ecg.ipynb', 'repo_name': 'mazinmelb/PTVDataasHeartbeat', 'size': '8797'}