def add_solar_features(df):
return \
(df
.withColumn('declination_angle',
radians(-23.45
* cos(((2 * pi)/365) * (dayofyear('date') + 10))))
.withColumn('diff_local_time_UTC', timezone_from_date('date'))
.withColumn('d', (2 * pi * dayofyear('date')) / 365)
.withColumn('equation_of_time',
-7.655 * sin(col('d'))
+ 9.873 * sin(2 * col('d') + 3.588))
.drop('d')
.withColumn('time_correction',
4 * (col('loc_long') - (15 * col('diff_local_time_UTC')))
+ col('equation_of_time'))
.withColumn('local_solar_hour',
col('hour') + 0.5 + col('time_correction') / 60)
.withColumn('hour_angle', 0.2618 * (col('local_solar_hour') - 12))
.drop('diff_local_time_UTC', 'equation_of_time', 'time_correction',
'local_solar_hour')
.withColumn('solar_elevation',
degrees(asin(sin('declination_angle')
* sin(radians('loc_lat'))
+ cos('declination_angle')
* cos(radians('loc_lat'))
* cos('hour_angle'))))
.drop('declination_angle', 'hour_angle'))
def add_solar_features(df):
return (df.withColumn(
"declination_angle",
radians(-23.45 * cos(((2 * pi) / 365) * (dayofyear("date") + 10))),
).withColumn("diff_local_time_UTC", timezone_from_date("date")).withColumn(
"d", (2 * pi * dayofyear("date")) / 365).withColumn(
"equation_of_time",
-7.655 * sin(col("d")) + 9.873 * sin(2 * col("d") + 3.588),
).drop("d").withColumn(
"time_correction",
4 * (col("loc_long") - (15 * col("diff_local_time_UTC"))) +
col("equation_of_time"),
).withColumn(
"local_solar_hour",
col("hour") + 0.5 + col("time_correction") / 60).withColumn(
"hour_angle", 0.2618 * (col("local_solar_hour") - 12)).drop(
"diff_local_time_UTC",
"equation_of_time",
"time_correction",
"local_solar_hour",
).withColumn(
"solar_elevation",
degrees(
asin(
sin("declination_angle") *
sin(radians("loc_lat")) +
cos("declination_angle") *
cos(radians("loc_lat")) * cos("hour_angle"))),
).drop("declination_angle", "hour_angle"))
def add_haversine_distances(df, lat_a, lon_a, lat_b, lon_b):
"""
Although the Haversine distance is readily available from sklearn.metrics.pairwise.haversine_distances, the
implementation below makes use only of in-built PySpark functions, so should be more efficient than passing the
sklearn function in as a UDF.
:param df:
:param lat_a:
:param lon_a:
:param lat_b:
:param lon_b:
:return:
"""
earth_radius_km = 6371.0
return \
df \
.withColumn('dist_lat', F.radians(lat_a) - F.radians(lat_b)) \
.withColumn('dist_lon', F.radians(lon_a) - F.radians(lon_b)) \
.withColumn('area',
(F.sin(F.col('dist_lat') / 2) ** 2)
+ (F.cos(F.radians(lat_a))
* F.cos(F.radians(lat_b))
* (F.sin(F.col('dist_lon') / 2) ** 2)
)
) \
.withColumn('central_angle', 2 * F.asin(F.sqrt(F.col('area')))) \
.withColumn('distance_km', F.col('central_angle') * F.lit(earth_radius_km)) \
.drop('dist_lat', 'dist_lon', 'area', 'central_angle')
def computeDistances(spark, dataframe):
df = dataframe.withColumn("lat1",F.radians(F.col("start_station_latitude"))).withColumn("lat2",F.radians(F.col("end_station_latitude")))\
.withColumn("lon1",F.radians(F.col("start_station_longitude"))).withColumn("lon2",F.radians(F.col("end_station_longitude")))\
.withColumn("distance",F.round(F.asin(F.sqrt(
(-F.cos(F.col("lat2") - F.col("lat1"))*0.5 + 0.5) +
F.cos(F.col("lat1"))*
F.cos(F.col("lat2"))*
(-F.cos(F.col("lon2") - F.col("lon1"))*0.5 + 0.5)))
*(2*earth_radius_in_meters/meters_per_mile),2))
df = df.drop(F.col("lat1")).drop(F.col("lat2")).drop(F.col("lon1")).drop(
F.col("lon2"))
return df
def haversine(lon1, lat1, lon2, lat2):
lon1, lat1, lon2, lat2 = map(toRadians, [
lon1.cast("float"),
lat1.cast("float"),
lon2.cast("float"),
lat2.cast("float")
])
dlon = lon2 - lon1
dlat = lat2 - lat1
a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
c = 2 * asin(sqrt(a))
m = 6367000 * c
return m.cast("decimal(10,2)") # meters
def haversine(lng1: Column, lat1: Column, lng2: Column, lat2: Column):
radius = 6378137
#将度数转成弧度
radLng1 = f.radians(lng1)
radLat1 = f.radians(lat1)
radLng2 = f.radians(lng2)
radLat2 = f.radians(lat2)
result = f.asin(
f.sqrt(
f.pow(f.sin((radLat1 - radLat2) / 2.0), 2) +
f.cos(radLat1) * f.cos(radLat2) *
f.pow(f.sin((radLng1 - radLng2) / 2.0), 2))) * 2.0 * radius
return result
def dist(lat1, long1, lat2, long2):
"""
Calculate the great circle distance between two points
on the earth (specified in decimal degrees)
"""
# convert decimal degrees to radians
lat1, long1, lat2, long2 = map(f.toRadians, [lat1, long1, lat2, long2])
# haversine formula
dlon = long2 - long1
dlat = lat2 - lat1
a = f.sin(dlat/2)**2 + f.cos(lat1) * f.cos(lat2) * f.sin(dlon/2)**2
c = 2 * f.asin(f.sqrt(a))
# Radius of earth in kilometers is 6371
km = 6371* c
return f.round(km, 3)
def add_distance_column(dfs, order_column='timestamp'):
# Radians lat/lon
dfs = dfs.withColumn('latitude2', F.radians('latitude')).withColumn(
'longitude2', F.radians('longitude'))
# Groups GPS locations into chucks. A chunk is formed by groups of points that are distant no more than roam_dist
w = Window.partitionBy(['userID']).orderBy(order_column)
dfs = dfs.withColumn('next_lat', F.lead('latitude2', 1).over(w))
dfs = dfs.withColumn('next_lon', F.lead('longitude2', 1).over(w))
# Haversine distance
dfs = dfs.withColumn('distance_next', EARTH_RADIUS * 2 * F.asin(F.sqrt(
F.pow(F.sin((col('next_lat') - col('latitude2')) / 2.0), 2) + F.cos('latitude2') * F.cos('next_lat') * F.pow(
F.sin((col('next_lon') - col('longitude2')) / 2.0), 2))))
dfs = dfs.withColumn('distance_prev', F.lag('distance_next', default=0).over(w)).drop(
'latitude2').drop('longitude2').drop('next_lon').drop('next_lat').drop('distance_next')
return dfs
def distance(lat1, lon1, lat2, lon2, unit='miles'):
'''
Measure simple haversine distance between two points. Default unit = miles.
'''
units = {
'miles': 3963.19,
'kilometers': 6378.137,
'meters': 6378137,
'feet': 20902464
}
phi_1 = py.radians(lat1)
phi_2 = py.radians(lat2)
delta_phi = py.radians(lat2 - lat1)
delta_lambda = py.radians(lon2 - lon1)
area = py.sin(delta_phi/2.0) ** 2 \
+ py.cos(phi_1) * py.cos(phi_2) * \
py.sin(delta_lambda / 2.0) ** 2
central_angle = 2 * py.asin((area**0.5))
radius = units[unit.lower()]
return py.abs(py.round((central_angle * radius), 4))
def main():
# Parameters for the algorithm
roam_dist = 100 # meters
min_stay = 10 # minutes
# Parameters for the paths
input_path = '/path_to_parquet' # parquet file
output_path = '/path_to_parquet_out' # parquet file
spark = SparkSession \
.builder \
.appName("Stop locations") \
.getOrCreate()
# Read data
source_df = spark.read.parquet(input_path)
source_df = source_df.select(
'user_id', 'timestamp',
F.radians('latitude').alias('lat'),
F.radians('longitude').alias("lon")).orderBy('timestamp')
source_df.cache()
# Filter out all the data that is not necessary (e.g. positions equals to others in a time-distance less than min_stay
w = Window.partitionBy(['user_id']).orderBy('timestamp')
source_df = source_df.select("user_id", "timestamp", "lat", "lon",
F.lead("lat", 1).over(w).alias("next_lat"),
F.lead("lon", 1).over(w).alias("next_lon"))
dist_df = source_df.withColumn(
"distance_next", EARTH_RADIUS * 2 * F.asin(
F.sqrt(
F.pow(F.sin((col("next_lat") - col("lat")) / 2.0), 2) +
F.cos("lat") * F.cos("next_lat") *
F.pow(F.sin((col("next_lon") - col("lon")) / 2.0), 2))))
dist_df = dist_df.withColumn("distance_prev",
F.lag("distance_next").over(w))
exclude_df = dist_df.where((
(col("distance_next") < 5) & (col("distance_prev") < 5))
| ((col("distance_next") > roam_dist)
& (col("distance_prev") > roam_dist)))
df = source_df.join(exclude_df, ['user_id', 'timestamp'],
"left_anti").select("user_id", "timestamp", "lat",
"lon")
# Transform to RDD, in order to apply the function get_stop_location
# RDD that contains: (user_id, [timestamp, lat, lon, lat_degrees, lon_degrees])
df_rdd = df.orderBy(['user_id', 'timestamp']).rdd.map(tuple)
df_rdd = df_rdd.map(lambda x: (x[0], [[x[1], x[2], x[3]]]))
# RDD that contains: (user_id, [[timestamp, lat, lon], ..., [timestamp, lat, lon]]), sorted by timestamp
grouped_rdd = df_rdd.reduceByKey(lambda x, y: x + y)
stop_locations_rdd = grouped_rdd.map(lambda x: (
x[0],
get_stop_location(
x[1], min_stay_duration=min_stay, roaming_distance=roam_dist)))
stop_locations_rdd = stop_locations_rdd.flatMapValues(lambda x: x).map(
lambda x: (x[0], x[1][0], x[1][1], x[1][2], x[1][3]))
# Output schema
schema = StructType([
StructField('user_id', StringType(), False),
StructField('lat', DoubleType(), False),
StructField('lon', DoubleType(), False),
StructField('from', TimestampType(), False),
StructField('to', TimestampType(), False)
])
result_df = spark.createDataFrame(stop_locations_rdd, schema)
result_df = result_df.withColumn('lat', F.degrees('lat'))
result_df = result_df.withColumn('lon', F.degrees('lon'))
result_df.write.save(output_path)
def geodistance(df, target_name,lng1, lat1, lng2, lat2):
result = df.withColumn("dlon", radians(col(lng1)) - radians(col(lng2))) \
.withColumn("dlat", radians(col(lat1)) - radians(col(lat2))) \
.withColumn(target_name, asin(sqrt(sin(col("dlat") / 2) ** 2 + cos(radians(col(lat2)))* cos(radians(col(lat1))) * sin(col("dlon") / 2) ** 2)) * 2 * 3963 * 5280) \
.drop("dlon", "dlat")
return result
def distance(CLat, CLon, data, col_name):
return data.withColumn('CLon', f.lit(CLon)).withColumn('CLat',f.lit(CLat)).withColumn("dlon", f.radians(f.col("CLon")) - f.radians(f.col("longitude"))).withColumn("dlat", f.radians(f.col("CLat")) - f.radians(f.col("latitude"))).withColumn(col_name, f.asin(f.sqrt(
f.sin(f.col("dlat") / 2) ** 2 + f.cos(f.radians(f.col("latitude")))
* f.cos(f.radians(f.col("CLat"))) * f.sin(f.col("dlon") / 2) ** 2
)
) * 2 * 6371 * 1000) \
.drop("dlon", "dlat",'CLon', 'CLat')
lat1 = 0.9345569159727344
lon1 = -1.9806997123424743
lat2 = 0.7945023069213337
lon2 = -1.2839693364011688
lat3 = 0.7893221871547071
lon3 = -1.1036193160713015
dlat1 = lat1 - lat0
dlon1 = lon1 - lon0
dlat2 = lat2 - lat0
dlon2 = lon2 - lon0
dlat3 = lat3 - lat0
dlon3 = lon3 - lon0
a1 = F.sin(dlat1 / 2)**2 + F.cos(lat0) * F.cos(lat0) * F.sin(dlon1 / 2)**2
a2 = F.sin(dlat2 / 2)**2 + F.cos(lat0) * F.cos(lat0) * F.sin(dlon2 / 2)**2
a3 = F.sin(dlat3 / 2)**2 + F.cos(lat0) * F.cos(lat0) * F.sin(dlon3 / 2)**2
c1 = F.lit(2) * F.asin(F.sqrt(a1))
c2 = F.lit(2) * F.asin(F.sqrt(a2))
c3 = F.lit(2) * F.asin(F.sqrt(a3))
r = F.lit(6371)
dist1 = (c1 * r).alias('dist1')
dist2 = (c2 * r).alias('dist2')
dist3 = (c3 * r).alias('dist3')
distances = clean2.select("_ID", "TimeSt", "City", "Province", "Latitude",
"Longitude", dist1, dist2, dist3)
distances.registerTempTable("dist0")
# POI assignation and minimal distance to poi
query = """SELECT _ID, TimeSt, City, Province, dist1, dist2, dist3,
CASE WHEN (dist1 < dist2) AND (dist1 < dist3) THEN "POI1 - EDMONTON"
def main():
"""Main function"""
# Get args
args = get_args()
# Azure credentials
sas_token = args.sas
storage_account_name = args.storage
container_in = args.container_in
container_out = args.container_out
azure_accounts = list()
azure_accounts.append({
"storage": storage_account_name,
"sas": sas_token,
"container": container_in
})
azure_accounts.append({
"storage": storage_account_name,
"sas": sas_token,
"container": container_out
})
# VM
cores = args.vm_cores
ram = args.vm_ram
shuffle_partitions = args.shuffle_partitions
# Geohash file path
geohash_path = args.geohashpath
# Date, country, prefix
country = args.country
date_string = args.date
prefix = args.prefix
# Set date variables
day_time = datetime.strptime(date_string, "%Y-%m-%d")
year = day_time.year
month = day_time.month
day = day_time.day
# stop config
seconds = 60
accuracy = args.accuracy
roam_dist = args.roam_dist
min_stay = args.min_stay
overlap_hours = args.overlap_hours
# Path in - path out
blob_in = f"wasbs://{container_in}@{storage_account_name}.blob.core.windows.net/preprocessed/{country}/"
path_out = f"stoplocation-v{VERSION}_r{roam_dist}-s{min_stay}-a{accuracy}-h{overlap_hours}/{country}"
if prefix:
path_out = f"stoplocation-v{VERSION}_prefix_r{roam_dist}-s{min_stay}-a{accuracy}-h{overlap_hours}/{country}"
# config spark
conf = getSparkConfig(cores, ram, shuffle_partitions, azure_accounts)
# Create spark session
sc = SparkContext(conf=conf).getOrCreate()
sqlContext = SQLContext(sc)
spark = sqlContext.sparkSession
# Init azure client
blob_service_client = BlobServiceClient.from_connection_string(
CONN_STRING.format(storage_account_name, sas_token))
# build keys, date is mandatory, prefix opt
partition_key = "year={}/month={}/day={}".format(year, month, day)
if prefix:
partition_key = "year={}/month={}/day={}/prefix={}".format(
year, month, day, prefix)
blob_base = "{}/{}".format(path_out, partition_key)
#
# check for skip
# TODO
#
skip = False
print("process " + partition_key + " to " + blob_base)
start_time = time.time()
local_dir = LOCAL_PATH + partition_key
print("write temp to " + local_dir)
# cleanup local if exists
if (os.path.isdir(local_dir)):
map(os.unlink,
(os.path.join(local_dir, f) for f in os.listdir(local_dir)))
# TODO cleanup remote if exists
# Output schema
schema = ArrayType(
StructType([
#StructField('device_type', IntegerType(), False),
StructField('serial', IntegerType(), False),
StructField('latitude', DoubleType(), False),
StructField('longitude', DoubleType(), False),
StructField('begin', TimestampType(), False),
StructField('end', TimestampType(), False),
StructField('personal_area', BooleanType(), False),
StructField('distance', DoubleType(), False),
StructField('geohash6', StringType(), False),
StructField('after_stop_distance', DoubleType(), False)
]))
spark_get_stop_location = udf(
lambda z: get_stop_location(z, roam_dist, min_stay), schema)
# Geohash file
print("read geohash parquet")
csv_time = time.time()
dfs_us_states = spark.read.format("parquet").load(geohash_path)
# states = [s.STUSPS for s in dfs_us_states.select(
# 'STUSPS').distinct().collect()]
dfs_us_states = dfs_us_states.select(
col('STUSPS').alias('state'),
col('geohash').alias('geohash5'))
dfs_us_states = dfs_us_states.drop_duplicates(subset=['geohash5'])
# Input dataset
print("read dataset table")
read_time = time.time()
# dfs = spark.read.format("parquet").load(blob_in)
# # apply partition filter
# dfs_partition = dfs.where(
# f"(year = {year} AND month = {month} AND day = {day} AND prefix = '{prefix}')")
# read only partition to reduce browse time
dfs_cur_partition = spark.read.format("parquet").load(
f"{blob_in}/{partition_key}")
# lit partition filters as data
dfs_cur_partition = dfs_cur_partition.withColumn('year', F.lit(year))
dfs_cur_partition = dfs_cur_partition.withColumn('month', F.lit(month))
dfs_cur_partition = dfs_cur_partition.withColumn('day', F.lit(day))
if prefix:
dfs_cur_partition = dfs_cur_partition.withColumn(
'prefix', F.lit(prefix))
# read next day for overlap
next_day = day_time + timedelta(days=1)
next_partition_key = "year={}/month={}/day={}".format(
next_day.year, next_day.month, next_day.day)
if prefix:
next_partition_key = "year={}/month={}/day={}/prefix={}".format(
next_day.year, next_day.month, next_day.day, prefix)
dfs_next_partition = spark.read.format("parquet").load(
f"{blob_in}/{next_partition_key}")
dfs_next_partition = dfs_next_partition.where(
F.hour("timestamp") <= (overlap_hours - 1))
# lit partition filters as data
dfs_next_partition = dfs_next_partition.withColumn('year',
F.lit(next_day.year))
dfs_next_partition = dfs_next_partition.withColumn('month',
F.lit(next_day.month))
dfs_next_partition = dfs_next_partition.withColumn('day',
F.lit(next_day.day))
if prefix:
dfs_next_partition = dfs_next_partition.withColumn(
'prefix', F.lit(prefix))
# union with overlap
dfs_partition = dfs_cur_partition.unionAll(dfs_next_partition)
print("process with spark")
spark_time = time.time()
# select columns
dfs_partition = dfs_partition.select(
'prefix', 'userID', 'timestamp', 'latitude', 'longitude',
(F.when(col('opt1') == 'PERSONAL_AREA',
True).otherwise(False)).alias('personal_area'), 'accuracy')
# keep only data with required accuracy
dfs_partition = dfs_partition.where((col('accuracy') <= accuracy)
& (col('accuracy') >= 0))
# stats - enable only for debug!
# num_inputs = dfs_partition.count()
# print(f"read {num_inputs} rows from "+partition_key)
# Lowering the granularity to 1 minutes
# explicitely convert to timestamp
#dfs_partition = dfs_partition.withColumn('timestamp', col('timestamp').cast('timestamp'))
seconds_window = F.unix_timestamp(
'timestamp') - F.unix_timestamp('timestamp') % seconds
w = Window().partitionBy('userID', seconds_window).orderBy('accuracy')
dfs_partition = dfs_partition.withColumn(
'rn',
F.row_number().over(w).cast('int')).where(col('rn') == 1).drop('rn')
# Radians lat/lon
dfs_partition = dfs_partition.withColumn('latitude',
F.radians('latitude')).withColumn(
'longitude',
F.radians('longitude'))
# Groups GPS locations into chucks. A chunk is formed by groups of points that are distant no more than roam_dist
w = Window.partitionBy(['prefix', 'userID']).orderBy('timestamp')
dfs_partition = dfs_partition.withColumn('next_lat',
F.lead('latitude', 1).over(w))
dfs_partition = dfs_partition.withColumn('next_lon',
F.lead('longitude', 1).over(w))
# Haversine distance
dfs_partition = dfs_partition.withColumn(
'distance_next', EARTH_RADIUS * 2 * F.asin(
F.sqrt(
F.pow(F.sin((col('next_lat') - col('latitude')) / 2.0), 2) +
F.cos('latitude') * F.cos('next_lat') *
F.pow(F.sin((col('next_lon') - col('longitude')) / 2.0), 2))))
dfs_partition = dfs_partition.withColumn(
'distance_prev',
F.lag('distance_next', default=0).over(w))
# Chunks
dfs_partition = dfs_partition.withColumn(
'chunk',
F.when(col('distance_prev') > roam_dist, 1).otherwise(0))
windowval = (Window.partitionBy(
'prefix',
'userID').orderBy('timestamp').rangeBetween(Window.unboundedPreceding,
0))
dfs_partition = dfs_partition.withColumn(
'chunk',
F.sum('chunk').over(windowval).cast('int'))
# Remove chunks of the next day
w = Window.partitionBy(['prefix', 'userID', 'chunk'])
dfs_partition = dfs_partition.withColumn(
'min_timestamp', F.dayofmonth(F.min('timestamp').over(w)))
dfs_partition = dfs_partition.where(
col('min_timestamp') == day).drop('min_timestamp')
# Get the stops
result_df = dfs_partition.groupBy('prefix', 'userID', 'chunk').agg(
F.array_sort(
F.collect_list(
F.struct('timestamp', 'latitude', 'longitude', 'distance_prev',
'personal_area'))).alias('gpsdata'),
F.sum('distance_prev').alias('dist_sum'))
result_df = result_df.withColumn('gpsdata',
spark_get_stop_location('gpsdata'))
result_df = result_df.select('userID', 'chunk',
F.explode_outer('gpsdata').alias('e'),
'dist_sum')
result_df = result_df.select(
'userID', 'chunk',
col('e.latitude').alias('latitude'),
col('e.longitude').alias('longitude'),
col('e.begin').alias('begin'),
col('e.end').alias('end'),
col('e.personal_area').alias('personal_area'),
col('e.geohash6').alias('geohash6'),
col('e.serial').alias('serial'),
col('e.distance').alias('stop_distance'),
col('e.after_stop_distance').alias('after_stop_distance'), 'dist_sum')
result_df = result_df.fillna(0, subset=['after_stop_distance'])
# Remove all those stop that start the next day
result_df = result_df.where((col('begin').isNull())
| (F.dayofmonth('begin') != next_day.day))
result_df = result_df.withColumn(
'isStop',
F.when(col('serial').isNotNull(), 1).otherwise(0))
result_df = result_df.withColumn(
'dist_sum',
F.when(col('isStop') == 1,
col('stop_distance')).otherwise(col('dist_sum')))
windowval = (Window.partitionBy('userId').orderBy(
'chunk', 'serial').rowsBetween(Window.currentRow,
Window.unboundedFollowing))
result_df = result_df.withColumn('isStop_cum',
F.sum('isStop').over(windowval))
result_df = result_df.groupBy('userId', 'isStop_cum').agg(
F.first('latitude', ignorenulls=True).alias('latitude'),
F.first('longitude', ignorenulls=True).alias('longitude'),
F.first('begin', ignorenulls=True).alias('begin'),
F.first('end', ignorenulls=True).alias('end'),
F.first('personal_area', ignorenulls=True).alias('personal_area'),
F.first('geohash6', ignorenulls=True).alias('geohash6'),
F.sum('dist_sum').alias('prev_travelled_distance'),
F.sum('after_stop_distance').alias('after_stop_distance'))
# compute next distance, which is null if it's the last
windowval = Window.partitionBy('userId').orderBy(F.desc('isStop_cum'))
result_df = result_df.withColumn(
'next_travelled_distance',
F.lead('prev_travelled_distance').over(windowval))
result_df = result_df.withColumn(
'next_travelled_distance',
F.when((col('next_travelled_distance').isNull()) &
(col('after_stop_distance') > 0),
col('after_stop_distance')).otherwise(
col('next_travelled_distance')))
# Drop nulls
result_df = result_df.dropna(subset=['latitude']).drop('isStop_cum')
# Transform latitude and longitude back to degrees
result_df = result_df.withColumn('latitude', F.degrees('latitude'))
result_df = result_df.withColumn('longitude', F.degrees('longitude'))
# US states
result_df = result_df.withColumn(
"geohash5", F.expr("substring(geohash6, 1, length(geohash6)-1)"))
result_df = result_df.join(F.broadcast(dfs_us_states),
on="geohash5",
how="inner").drop('geohash5')
# lit partition data - enable only if added to partitionBy
# result_df = result_df.withColumn('year', F.lit(year))
# result_df = result_df.withColumn('month', F.lit(month))
# result_df = result_df.withColumn('day', F.lit(day))
# write
out_partitions = len(US_STATES)
result_df.repartition(out_partitions, "state").write.partitionBy(
"state").format('parquet').mode("overwrite").save(local_dir + "/")
# stats - enable only for debug!
# num_records = result_df.count()
# print(f"written {num_records} rows to "+local_dir)
# if num_records == 0:
# raise Exception("Zero rows output")
print("upload local data to azure")
upload_time = time.time()
# upload parts over states
for state in US_STATES:
print(f"upload files for {state}")
state_dir = local_dir + "/state=" + state
state_key = f"{partition_key}/state={state}/"
if (os.path.isdir(state_dir)):
files = [
filename for filename in os.listdir(state_dir)
if filename.startswith("part-")
]
if len(files) > 0:
for file_local in files:
file_path = state_dir + "/" + file_local
part_num = int(file_local.split('-')[1])
part_key = '{:05d}'.format(part_num)
# fix name as static hash to be reproducible
filename_hash = hashlib.sha1(
str.encode(state_key + part_key)).hexdigest()
blob_key = "{}/state={}/part-{}-{}.snappy.parquet".format(
blob_base, state, part_key, filename_hash)
print("upload " + file_path + " to " + container_out +
":" + blob_key)
blob_client = blob_service_client.get_blob_client(
container_out, blob_key)
with open(file_path, "rb") as data:
blob_client.upload_blob(data, overwrite=True)
# cleanup
os.remove(file_path)
else:
print(f"no files to upload for {state}")
else:
print(f"missing partition for {state}")
print("--- {} seconds elapsed ---".format(int(time.time() - start_time)))
print()
stop_time = time.time()
spark.stop()
end_time = time.time()
print("Done in {} seconds (csv:{} read:{} spark:{} upload:{} stop:{})".
format(int(end_time - start_time), int(read_time - csv_time),
int(spark_time - read_time), int(upload_time - spark_time),
int(stop_time - upload_time), int(end_time - stop_time)))
print('Done.')
# explodes all array columns
d1 = d.withColumn("new", F.arrays_zip("heart_rate","timestamp","latitude","longitude","lat2","long2","time2"))\
.withColumn("new", F.explode("new"))\
.select("userId","id",
F.col("new.heart_rate").alias("heart_rate"),
F.col("new.timestamp").alias("timestamp"),
F.col("new.latitude").alias("lat"),
F.col("new.longitude").alias("long"),
F.col("new.lat2").alias("lat2"),
F.col("new.long2").alias("long2"),
F.col("new.time2").alias("time2"))
# haversine formula, calculates distance and speed between two points
d2 = d1.withColumn("distance", 3956 *(2 * F.asin(F.sqrt(F.sin((F.radians("lat") - F.radians("lat2"))/2)**2
+ F.cos(F.radians("lat")) * F.cos(F.radians("lat2")) * F.sin((F.radians("long")
- F.radians("long2"))/2)**2))))\
.withColumn("speed", F.col("distance")/((F.col("timestamp") - F.col("time2"))/3600))
d2 = d2.fillna({"speed": "0"})
# aggregations that compute metrics related to an individual bike trip
query = d2.groupBy("id", "userid").agg(
F.round(F.mean("speed"), 2).alias("avgspeed"),
F.round(F.max("speed"), 2).alias("max_speed"),
F.round(F.mean("heart_rate")).cast("integer").alias("avg_heart_rate"),
F.max("heart_rate").alias("max_heart_rate"),
F.round(F.sum("distance"), 2).alias("distance"),
conv_sec_udf(F.last("timestamp") -
F.first("timestamp")).alias("duration"),
conv_sec_udf((F.last("timestamp") - F.first("timestamp")) /
def tocolumns(df, expr):
import pyspark.sql.functions as fcns
if isinstance(expr, histbook.expr.Const):
return fcns.lit(expr.value)
elif isinstance(expr, (histbook.expr.Name, histbook.expr.Predicate)):
return df[expr.value]
elif isinstance(expr, histbook.expr.Call):
if expr.fcn == "abs" or expr.fcn == "fabs":
return fcns.abs(tocolumns(df, expr.args[0]))
elif expr.fcn == "max" or expr.fcn == "fmax":
return fcns.greatest(*[tocolumns(df, x) for x in expr.args])
elif expr.fcn == "min" or expr.fcn == "fmin":
return fcns.least(*[tocolumns(df, x) for x in expr.args])
elif expr.fcn == "arccos":
return fcns.acos(tocolumns(df, expr.args[0]))
elif expr.fcn == "arccosh":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "arcsin":
return fcns.asin(tocolumns(df, expr.args[0]))
elif expr.fcn == "arcsinh":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "arctan2":
return fcns.atan2(tocolumns(df, expr.args[0]),
tocolumns(df, expr.args[1]))
elif expr.fcn == "arctan":
return fcns.atan(tocolumns(df, expr.args[0]))
elif expr.fcn == "arctanh":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "ceil":
return fcns.ceil(tocolumns(df, expr.args[0]))
elif expr.fcn == "copysign":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "cos":
return fcns.cos(tocolumns(df, expr.args[0]))
elif expr.fcn == "cosh":
return fcns.cosh(tocolumns(df, expr.args[0]))
elif expr.fcn == "rad2deg":
return tocolumns(df, expr.args[0]) * (180.0 / math.pi)
elif expr.fcn == "erfc":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "erf":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "exp":
return fcns.exp(tocolumns(df, expr.args[0]))
elif expr.fcn == "expm1":
return fcns.expm1(tocolumns(df, expr.args[0]))
elif expr.fcn == "factorial":
return fcns.factorial(tocolumns(df, expr.args[0]))
elif expr.fcn == "floor":
return fcns.floor(tocolumns(df, expr.args[0]))
elif expr.fcn == "fmod":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "gamma":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "hypot":
return fcns.hypot(tocolumns(df, expr.args[0]),
tocolumns(df, expr.args[1]))
elif expr.fcn == "isinf":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "isnan":
return fcns.isnan(tocolumns(df, expr.args[0]))
elif expr.fcn == "lgamma":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "log10":
return fcns.log10(tocolumns(df, expr.args[0]))
elif expr.fcn == "log1p":
return fcns.log1p(tocolumns(df, expr.args[0]))
elif expr.fcn == "log":
return fcns.log(tocolumns(df, expr.args[0]))
elif expr.fcn == "pow":
return fcns.pow(tocolumns(df, expr.args[0]),
tocolumns(df, expr.args[1]))
elif expr.fcn == "deg2rad":
return tocolumns(df, expr.args[0]) * (math.pi / 180.0)
elif expr.fcn == "sinh":
return fcns.sinh(tocolumns(df, expr.args[0]))
elif expr.fcn == "sin":
return fcns.sin(tocolumns(df, expr.args[0]))
elif expr.fcn == "sqrt":
return fcns.sqrt(tocolumns(df, expr.args[0]))
elif expr.fcn == "tanh":
return fcns.tanh(tocolumns(df, expr.args[0]))
elif expr.fcn == "tan":
return fcns.tan(tocolumns(df, expr.args[0]))
elif expr.fcn == "trunc":
raise NotImplementedError(
expr.fcn) # FIXME (fcns.trunc is for dates)
elif expr.fcn == "xor":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "conjugate":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "exp2":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "heaviside":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "isfinite":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "left_shift" and isinstance(expr.args[1],
histbook.expr.Const):
return fcns.shiftLeft(tocolumns(df, expr.args[0]),
expr.args[1].value)
elif expr.fcn == "log2":
return fcns.log2(tocolumns(df, expr.args[0]))
elif expr.fcn == "logaddexp2":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "logaddexp":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "mod" or expr.fcn == "fmod":
return tocolumns(df, expr.args[0]) % tocolumns(df, expr.args[1])
elif expr.fcn == "right_shift" and isinstance(expr.args[1],
histbook.expr.Const):
return fcns.shiftRight(tocolumns(df, expr.args[0]),
expr.args[1].value)
elif expr.fcn == "rint":
return fcns.rint(tocolumns(df, expr.args[0]))
elif expr.fcn == "sign":
raise NotImplementedError(expr.fcn) # FIXME
elif expr.fcn == "where":
return fcns.when(tocolumns(df, expr.args[0]),
tocolumns(df, expr.args[1])).otherwise(
tocolumns(df, expr.args[2]))
elif expr.fcn == "numpy.equal":
return tocolumns(df, expr.args[0]) == tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.not_equal":
return tocolumns(df, expr.args[0]) != tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.less":
return tocolumns(df, expr.args[0]) < tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.less_equal":
return tocolumns(df, expr.args[0]) <= tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.isin":
return tocolumns(df, expr.args[0]) in tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.logical_not":
return ~tocolumns(df, expr.args[0])
elif expr.fcn == "numpy.add":
return tocolumns(df, expr.args[0]) + tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.subtract":
return tocolumns(df, expr.args[0]) - tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.multiply":
return tocolumns(df, expr.args[0]) * tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.true_divide":
return tocolumns(df, expr.args[0]) / tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.logical_or":
return tocolumns(df, expr.args[0]) | tocolumns(df, expr.args[1])
elif expr.fcn == "numpy.logical_and":
return tocolumns(df, expr.args[0]) & tocolumns(df, expr.args[1])
else:
raise NotImplementedError(expr.fcn)
else:
raise AssertionError(expr)
"z", F.cos(df["theta"])).drop("theta", "phi")
df = df.withColumn("xc",
F.sin(df["theta_c"]) * F.cos(df["phi_c"])).withColumn(
"yc",
F.sin(df["theta_c"]) * F.sin(df["phi_c"])).withColumn(
"zc",
F.cos(df["theta_c"])).drop("theta_c", "phi_c")
df = df.withColumn("rr",
F.hypot(df.x - df.xc,
F.hypot(df.y - df.yc, df.z - df.zc))).drop(
"x", "y", "z", "xc", "yc", "zc")
df = df.withColumn("rx", F.degrees(df.rr) * 60)
df = df.withColumn("angdist", F.degrees(2 * F.asin(df.rr / 2)) * 60)
df.cache().count()
maxr = df.select(F.max(df.angdist)).take(1)[0][0]
p = df_histplot(df, "angdist")
xlabel(r"radius [arcmin]")
text(0.8,
0.8,
r"$\theta_u={:.2f}^\prime$".format(maxr),
transform=gca().transAxes)
title("nside={}".format(nside))
savefig("nside{}_1d.png".format(nside))
x, y, m = df_histplot2(df,
for poi, (lat, lon) in poi_map.items():
print(poi, lat, lon)
df = df.withColumn(
"dist_to_{0:s}".format(poi),
F.array([
F.sqrt((F.col("Longitude") - F.lit(lat))**2 +
(F.col("Latitude") - F.lit(lon))**2),
F.lit(poi)
]))
df = df.withColumn(
"h_dist_to_{0:s}".format(poi),
F.array([
2.0 * r * F.asin(
F.sqrt(
F.sin(0.5 * (F.radians(F.col("Latitude")) -
F.radians(F.lit(lat))))**2 +
F.cos(F.radians(F.col("Latitude"))) *
F.cos(F.radians(F.lit(lat))) *
F.sin(0.5 * (F.radians(F.col("Longitude")) -
F.radians(F.lit(lon))))**2)),
F.lit(poi)
]))
# Find the nearest POI
df = (df.withColumn(
"label",
F.least(*[F.col("dist_to_{0:s}".format(poi))
for poi in poi_map])).drop(
*["dist_to_{0:s}".format(poi) for poi in poi_map]))
df = (df.withColumn(
"h_label",
