covi19_gmaps_crowds_zurich

#!/usr/bin/env python

'''
Post processing the scrape job
requires `pip install pandas gcsfs`
'''

import pandas as pd
import numpy as np
from datetime import datetime

metadata = 'https://docs.google.com/spreadsheets/d/1KDqquW2axaUM9Z62JbyppuPq09IpAZRSIpPLb08nVqQ/gviz/tq?tqx=out:csv&sheet=Sheet1'
md = pd.read_csv(metadata)
md.set_index('url',inplace=True)
# join to data on field "url"

#url = 'all_valid.csv' # only current measurements
url = 'gs://kantonzh-covid-hkfsaqgshw/gmaps_scrape/all_valid.csv'

N = 5 # how many head/tail to print
jetzt = datetime.now()
today = ("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")[jetzt.weekday()]
last_hour = jetzt.hour - 2

df = pd.read_csv(url)

# parse datetimes
#df['scrape_time'] = pd.to_datetime(df.scrape_time, format='%Y%m%d_%H%M%S')

# remove unused columns
del df['place']
del df['scrape_time']

# keep only rows with a valid current time
df = df[df.popularity_percent_normal.notnull()]

df = df[df.day_of_week == today]
df = df[df.hour_of_day == last_hour]
#df = df[df.hour_of_day == 15]

#df = df[df.popularity_percent_current.notnull()]


# truncate scrape timestamp to hours
#df['scrape_time'] = df['scrape_time'].dt.floor('h')

# create groups for each place, day of week, hour of day
df = df.groupby(['url','hour_of_day','day_of_week']).mean()

# calc ration between current and normal popularity
df['popularity_factor'] = (df.popularity_percent_current - df.popularity_percent_normal)/ df.popularity_percent_normal

df['popularity_category'] = np.select(
		[
			df['popularity_factor'].between(-99.0, -0.5, inclusive=True),
			df['popularity_factor'].between(-0.5, -0.25, inclusive=True),
			df['popularity_factor'].between(-0.25, 0.25, inclusive=True),
			df['popularity_factor'].between(0.25, 0.5, inclusive=True),
			df['popularity_factor'].between(0.25, 99.0, inclusive=True),
		],
		[
			'Much less crowded than usual',
			'A little less crowded than usual',
			'About the same',
			'A little more crowded than usual',
			'Much more crowded than usual'
		],
		default='Unknown'
	)

#df.rename(columns=lambda x: x.replace('_',' '), inplace=True)
df.reset_index(inplace=True)
md.reset_index(inplace=True)

# join metadata and data
df = pd.merge(md, df, left_on='url',right_on='url')
df.sort_values(by='popularity_factor', ascending=False, inplace=True)
#df = df[df.popularity_category.notnull()]
#print(df)
df.to_csv('popularity.csv',index=False)

popularity.csv