def test_window_functions_cumulative_sum(self):
df = self.spark.createDataFrame([("one", 1), ("two", 2)], ["key", "value"])
from pyspark.sql import functions as F
# Test cumulative sum
sel = df.select(
df.key,
F.sum(df.value).over(Window.rowsBetween(Window.unboundedPreceding, 0)))
rs = sorted(sel.collect())
expected = [("one", 1), ("two", 3)]
for r, ex in zip(rs, expected):
self.assertEqual(tuple(r), ex[:len(r)])
# Test boundary values less than JVM's Long.MinValue and make sure we don't overflow
sel = df.select(
df.key,
F.sum(df.value).over(Window.rowsBetween(Window.unboundedPreceding - 1, 0)))
rs = sorted(sel.collect())
expected = [("one", 1), ("two", 3)]
for r, ex in zip(rs, expected):
self.assertEqual(tuple(r), ex[:len(r)])
# Test boundary values greater than JVM's Long.MaxValue and make sure we don't overflow
frame_end = Window.unboundedFollowing + 1
sel = df.select(
df.key,
F.sum(df.value).over(Window.rowsBetween(Window.currentRow, frame_end)))
rs = sorted(sel.collect())
expected = [("one", 3), ("two", 2)]
for r, ex in zip(rs, expected):
self.assertEqual(tuple(r), ex[:len(r)])
def test_groupedData(self):
from pyspark.sql import DataFrame
from pyspark.sql.functions import sum, pandas_udf, PandasUDFType
from ts.flint import TimeSeriesGroupedData
price = self.price()
assert(type(price.groupBy('time')) is TimeSeriesGroupedData)
assert(type(price.groupby('time')) is TimeSeriesGroupedData)
result1 = price.groupBy('time').agg(sum(price['price'])).sort('time').toPandas()
expected1 = DataFrame.groupBy(price, 'time').agg(sum(price['price'])).sort('time').toPandas()
assert_same(result1, expected1)
result2 = price.groupBy('time').pivot('id').sum('price').toPandas()
expected2 = DataFrame.groupBy(price, 'time').pivot('id').sum('price').toPandas()
assert_same(result2, expected2)
@pandas_udf(price.schema, PandasUDFType.GROUPED_MAP)
def foo(df):
return df
result3 = price.groupby('time').apply(foo).toPandas()
expected3 = DataFrame.groupBy(price, 'time').apply(foo).toPandas()
assert_same(result3, expected3)
result4 = price.groupby('time').count().toPandas()
expected4 = DataFrame.groupBy(price, 'time').count().toPandas()
assert_same(result4, expected4)
result5 = price.groupby('time').mean('price').toPandas()
expected5 = DataFrame.groupBy(price, 'time').mean('price').toPandas()
assert_same(result5, expected5)
def test_mixed_sql(self):
"""
Test mixing group aggregate pandas UDF with sql expression.
"""
df = self.data
sum_udf = self.pandas_agg_sum_udf
# Mix group aggregate pandas UDF with sql expression
result1 = (df.groupby('id')
.agg(sum_udf(df.v) + 1)
.sort('id'))
expected1 = (df.groupby('id')
.agg(sum(df.v) + 1)
.sort('id'))
# Mix group aggregate pandas UDF with sql expression (order swapped)
result2 = (df.groupby('id')
.agg(sum_udf(df.v + 1))
.sort('id'))
expected2 = (df.groupby('id')
.agg(sum(df.v + 1))
.sort('id'))
# Wrap group aggregate pandas UDF with two sql expressions
result3 = (df.groupby('id')
.agg(sum_udf(df.v + 1) + 2)
.sort('id'))
expected3 = (df.groupby('id')
.agg(sum(df.v + 1) + 2)
.sort('id'))
self.assertPandasEqual(expected1.toPandas(), result1.toPandas())
self.assertPandasEqual(expected2.toPandas(), result2.toPandas())
self.assertPandasEqual(expected3.toPandas(), result3.toPandas())
def test_nondeterministic_vectorized_udf_in_aggregate(self):
df = self.spark.range(10)
random_udf = self.nondeterministic_vectorized_udf
with QuietTest(self.sc):
with self.assertRaisesRegexp(AnalysisException, 'nondeterministic'):
df.groupby(df.id).agg(sum(random_udf(df.id))).collect()
with self.assertRaisesRegexp(AnalysisException, 'nondeterministic'):
df.agg(sum(random_udf(df.id))).collect()
def test_nondeterministic_udf_in_aggregate(self):
from pyspark.sql.functions import udf, sum
import random
udf_random_col = udf(lambda: int(100 * random.random()), 'int').asNondeterministic()
df = self.spark.range(10)
with QuietTest(self.sc):
with self.assertRaisesRegexp(AnalysisException, "nondeterministic"):
df.groupby('id').agg(sum(udf_random_col())).collect()
with self.assertRaisesRegexp(AnalysisException, "nondeterministic"):
df.agg(sum(udf_random_col())).collect()
def gen_report_table(hc,curUnixDay):
rows_indoor=sc.textFile("/data/indoor/*/*").map(lambda r: r.split(",")).map(lambda p: Row(clientmac=p[0], entityid=int(p[1]),etime=int(p[2]),ltime=int(p[3]),seconds=int(p[4]),utoday=int(p[5]),ufirstday=int(p[6])))
HiveContext.createDataFrame(hc,rows_indoor).registerTempTable("df_indoor")
#ClientMac|etime|ltime|seconds|utoday|ENTITYID|UFIRSTDAY
sql="select entityid,clientmac,utoday,UFIRSTDAY,seconds,"
sql=sql+"count(1) over(partition by entityid,clientmac) as total_cnt,"
sql=sql+"count(1) over (partition by entityid,clientmac order by utoday range 2505600 preceding) as day_30," # 2505600 is 29 days
sql=sql+"count(1) over (partition by entityid,clientmac order by utoday range 518400 preceding) as day_7," #518400 is 6 days
sql=sql+"count(1) over (partition by entityid,clientmac,UFIRSTDAY order by UFIRSTDAY range 1 preceding) as pre_mon "
sql=sql+"from df_indoor order by entityid,clientmac,utoday"
df_id_stat=hc.sql(sql)
df_id_mm=df_id_stat.withColumn("min", func.min("utoday").over(Window.partitionBy("entityid","clientmac"))).withColumn("max", func.max("utoday").over(Window.partitionBy("entityid","clientmac")))
#df_id_mm df_min_max ,to caculate firtarrival and last arrival
df_id_stat_distinct=df_id_stat.drop("seconds").drop("day_30").drop("day_7").drop("utoday").drop("total_cnt").distinct()
#distinct df is for lag function to work
df_id_prepremon=df_id_stat_distinct.withColumn("prepre_mon",func.lag("pre_mon").over(Window.partitionBy("entityid","clientmac").orderBy("entityid","clientmac","UFIRSTDAY"))).drop("pre_mon").na.fill(0)
cond_id = [df_id_mm.clientmac == df_id_prepremon.clientmac, df_id_mm.entityid == df_id_prepremon.entityid, df_id_mm.UFIRSTDAY==df_id_prepremon.UFIRSTDAY]
df_indoor_fin_tmp=df_id_mm.join(df_id_prepremon, cond_id, 'outer').select(df_id_mm.entityid,df_id_mm.clientmac,df_id_mm.utoday,df_id_mm.UFIRSTDAY,df_id_mm.seconds,df_id_mm.day_30,df_id_mm.day_7,df_id_mm.min,df_id_mm.max,df_id_mm.total_cnt,df_id_prepremon.prepre_mon)
df_indoor_fin_tmp=df_indoor_fin_tmp.selectExpr("entityid as entityid","clientmac as clientmac","utoday as utoday","UFIRSTDAY as ufirstday","seconds as secondsbyday","day_30 as indoors30","day_7 as indoors7","min as FirstIndoor","max as LastIndoor","total_cnt as indoors","prepre_mon as indoorsPrevMonth")
#newly added part for indoors7 and indoors30 based on current date
df_indoor_fin_tmp1= df_indoor_fin_tmp.withColumn("r_day_7", func.when((curUnixDay- df_indoor_fin_tmp.utoday)/86400<7 , 1).otherwise(0))
df_indoor_fin_tmp2=df_indoor_fin_tmp1.withColumn("r_day_30", func.when((curUnixDay- df_indoor_fin_tmp1.utoday)/86400<30 , 1).otherwise(0))
df_indoor_fin_tmp3=df_indoor_fin_tmp2.withColumn("r_indoors7",func.sum("r_day_7").over(Window.partitionBy("entityid","clientmac")))
df_indoor_fin_tmp4=df_indoor_fin_tmp3.withColumn("r_indoors30",func.sum("r_day_30").over(Window.partitionBy("entityid","clientmac")))
df_indoor_fin=df_indoor_fin_tmp4.drop("r_day_7").drop("r_day_30")
hc.sql("drop table if exists df_indoor_fin")
df_indoor_fin.write.saveAsTable("df_indoor_fin")
rows_flow=sc.textFile("/data/flow/*/*").map(lambda r: r.split(",")).map(lambda p: Row(clientmac=p[0], entityid=int(p[1]),etime=int(p[2]),ltime=int(p[3]),utoday=int(p[4]),ufirstday=int(p[5])))
HiveContext.createDataFrame(hc,rows_flow).registerTempTable("df_flow")
# ClientMac|ENTITYID|UFIRSTDAY|etime|ltime|utoday
sql="select entityid,clientmac,utoday,UFIRSTDAY,"
sql=sql+"count(1) over(partition by entityid,clientmac) as total_cnt,"
sql=sql+"count(1) over (partition by entityid,clientmac order by utoday range 2505600 preceding) as day_30," # 2505600 is 29 days
sql=sql+"count(1) over (partition by entityid,clientmac order by utoday range 518400 preceding) as day_7," #518400 is 6 days
sql=sql+"count(1) over (partition by entityid,clientmac,UFIRSTDAY order by UFIRSTDAY range 1 preceding) as pre_mon "
sql=sql+"from df_flow order by entityid,clientmac,utoday"
df_fl_stat=hc.sql(sql)
df_fl_mm=df_fl_stat.withColumn("min", func.min("utoday").over(Window.partitionBy("entityid","clientmac"))).withColumn("max", func.max("utoday").over(Window.partitionBy("entityid","clientmac")))
#df_fl_mm df_min_max ,to caculate firtarrival and last arrival
df_fl_stat_distinct=df_fl_stat.drop("day_30").drop("day_7").drop("utoday").drop("total_cnt").distinct()
#distinct df is for lag function to work
df_fl_prepremon=df_fl_stat_distinct.withColumn("prepre_mon",func.lag("pre_mon").over(Window.partitionBy("entityid","clientmac").orderBy("entityid","clientmac","UFIRSTDAY"))).drop("pre_mon").na.fill(0)
cond_fl = [df_fl_mm.clientmac == df_fl_prepremon.clientmac, df_fl_mm.entityid == df_fl_prepremon.entityid, df_fl_mm.UFIRSTDAY==df_fl_prepremon.UFIRSTDAY]
df_flow_fin=df_fl_mm.join(df_fl_prepremon, cond_fl, 'outer').select(df_fl_mm.entityid,df_fl_mm.clientmac,df_fl_mm.utoday,df_fl_mm.UFIRSTDAY,df_fl_mm.day_30,df_fl_mm.day_7,df_fl_mm.min,df_fl_mm.max,df_fl_mm.total_cnt,df_fl_prepremon.prepre_mon)
df_flow_fin=df_flow_fin.selectExpr("entityid as entityid","clientmac as clientmac","utoday as utoday","UFIRSTDAY as ufirstday","day_30 as visits30","day_7 as visits7","min as FirstVisit","max as LastVisit","total_cnt as visits","prepre_mon as visitsPrevMonth")
hc.sql("drop table if exists df_flow_fin")
df_flow_fin.write.saveAsTable("df_flow_fin")
def run_benchmarks(base_path):
print("=========================================================================================")
print("Loading data for: ")
print(base_path)
print("=========================================================================================")
start=time.time()
df=hive_context.read.format("com.databricks.spark.csv").option("header", "true").option("inferSchema", "true").load(base_path)
#print(df)
#print(df.printSchema())
print(df.count())
df.cache()
print("Time taken for groupBy on DataFrame column C followed by sum aggregate: ")
start_task=time.time()
df_groupby_C=df.groupBy('C').agg(F.sum(df.id))
print(df_groupby_C.count())
end_task=time.time()
end=time.time()
x=[base_path, end-start, end_task-start_task]
print("=========================================================================================")
print("OUTPUT")
print(x)
print("=========================================================================================")
return x
def getValueFieldValueLists(self, handlerId, keyFields, valueFields):
df = self.entity.groupBy(keyFields)
agg = self.options.get("aggregation",self.getDefaultAggregation(handlerId))
maxRows = int(self.options.get("rowCount","100"))
numRows = min(maxRows,df.count())
valueLists = []
for valueField in valueFields:
valueDf = None
if agg == "SUM":
valueDf = df.agg(F.sum(valueField).alias("agg"))
elif agg == "AVG":
valueDf = df.agg(F.avg(valueField).alias("agg"))
elif agg == "MIN":
valueDf = df.agg(F.min(valueField).alias("agg"))
elif agg == "MAX":
valueDf = df.agg(F.max(valueField).alias("agg"))
else:
valueDf = df.agg(F.count(valueField).alias("agg"))
for keyField in keyFields:
valueDf = valueDf.sort(F.col(keyField).asc())
valueDf = valueDf.dropna()
rows = valueDf.select("agg").take(numRows)
valueList = []
for row in rows:
valueList.append(row["agg"])
valueLists.append(valueList)
return valueLists
def test_multiple_udfs(self):
"""
Test multiple group aggregate pandas UDFs in one agg function.
"""
from pyspark.sql.functions import sum, mean
df = self.data
mean_udf = self.pandas_agg_mean_udf
sum_udf = self.pandas_agg_sum_udf
weighted_mean_udf = self.pandas_agg_weighted_mean_udf
result1 = (df.groupBy('id')
.agg(mean_udf(df.v),
sum_udf(df.v),
weighted_mean_udf(df.v, df.w))
.sort('id')
.toPandas())
expected1 = (df.groupBy('id')
.agg(mean(df.v),
sum(df.v),
mean(df.v).alias('weighted_mean(v, w)'))
.sort('id')
.toPandas())
self.assertPandasEqual(expected1, result1)
def sum_aggregations(category, hours=None):
actual_suffix = ''
new_suffix = '_%s' % category
if hours:
actual_suffix = '_%s' % category
new_suffix += '_%sh' % hours
return [func.sum(column + actual_suffix).alias(column + new_suffix) for column in ['Pickup_Count', 'Dropoff_Count']]
def test_retain_group_columns(self):
with self.sql_conf({"spark.sql.retainGroupColumns": False}):
df = self.data
sum_udf = self.pandas_agg_sum_udf
result1 = df.groupby(df.id).agg(sum_udf(df.v))
expected1 = df.groupby(df.id).agg(sum(df.v))
self.assertPandasEqual(expected1.toPandas(), result1.toPandas())
def formatItens(firstTime):
#format itenary data
global itens
itens = itens.withColumn("ORIGIN_AIRPORT_ID",toInt("ORIGIN_AIRPORT_ID"))
itens = itens.withColumn("DEST_AIRPORT_ID",toInt("DEST_AIRPORT_ID"))
itens = itens.withColumn("MARKET_MILES_FLOWN",toKm("MARKET_MILES_FLOWN"))
itens = itens.withColumn("PASSENGERS",toInt("PASSENGERS"))
if firstTime:
aggArg = sum("PASSENGERS").alias("PASSENGERS"),mean("MARKET_MILES_FLOWN").alias("MARKET_KMS_FLOWN")
itens = itens.groupBy("ORIGIN_AIRPORT_ID","DEST_AIRPORT_ID").agg(*aggArg).cache()
def runBPwithGraphFrames(cls, g, numIter):
"""Run Belief Propagation using GraphFrame.
This implementation of BP shows how to use GraphFrame's aggregateMessages method.
"""
# choose colors for vertices for BP scheduling
colorG = cls._colorGraph(g)
numColors = colorG.vertices.select('color').distinct().count()
# TODO: handle vertices without any edges
# initialize vertex beliefs at 0.0
gx = GraphFrame(colorG.vertices.withColumn('belief', sqlfunctions.lit(0.0)), colorG.edges)
# run BP for numIter iterations
for iter_ in range(numIter):
# for each color, have that color receive messages from neighbors
for color in range(numColors):
# Send messages to vertices of the current color.
# We may send to source or destination since edges are treated as undirected.
msgForSrc = sqlfunctions.when(
AM.src['color'] == color,
AM.edge['b'] * AM.dst['belief'])
msgForDst = sqlfunctions.when(
AM.dst['color'] == color,
AM.edge['b'] * AM.src['belief'])
# numerically stable sigmoid
logistic = sqlfunctions.udf(cls._sigmoid, returnType=types.DoubleType())
aggregates = gx.aggregateMessages(
sqlfunctions.sum(AM.msg).alias("aggMess"),
sendToSrc=msgForSrc,
sendToDst=msgForDst)
v = gx.vertices
# receive messages and update beliefs for vertices of the current color
newBeliefCol = sqlfunctions.when(
(v['color'] == color) & (aggregates['aggMess'].isNotNull()),
logistic(aggregates['aggMess'] + v['a'])
).otherwise(v['belief']) # keep old beliefs for other colors
newVertices = (v
.join(aggregates, on=(v['id'] == aggregates['id']), how='left_outer')
.drop(aggregates['id']) # drop duplicate ID column (from outer join)
.withColumn('newBelief', newBeliefCol) # compute new beliefs
.drop('aggMess') # drop messages
.drop('belief') # drop old beliefs
.withColumnRenamed('newBelief', 'belief')
)
# cache new vertices using workaround for SPARK-1334
cachedNewVertices = AM.getCachedDataFrame(newVertices)
gx = GraphFrame(cachedNewVertices, gx.edges)
# Drop the "color" column from vertices
return GraphFrame(gx.vertices.drop('color'), gx.edges)
def compute(day):
# On veut les jours day-30 à day-1
sums = wikipediadata.where(
(wikipediadata.day >= day-30) & (wikipediadata.day <= day-1))
# Sous-ensemble de test
#sums = sums.where((sums.page == 'Cadillac_Brougham') | ((sums.page == 'Roald_Dahl') & (sums.projectcode == 'fr')))
# On somme les heures de la journées
sums = sums.groupby('projectcode', 'page', 'day').sum('views')
# On cache pour plus tard
sums.cache()
# on définit une windows := jour precedent
window_spec = Window.partitionBy(sums.projectcode, sums.page) \
.orderBy(sums.day.asc()).rowsBetween(-1, -1)
# on calcule la différence entre views(d) - views(d-1)
diffs = sums.withColumn('diff', sums.views - F.sum(sums.views) \
.over(window_spec))
# on calcule les coefs à appliquer à chaque jour
coefs = pd.DataFrame({'day': range(day-30, day)})
coefs['coef'] = 1. / (day - coefs.day)
coefs = hc.createDataFrame(coefs)
diffs = diffs.join(coefs, 'day')
# on calcul le score de chaque jour
diffs = diffs.withColumn('sub_score', diffs.diff * diffs.coef)
totals = diffs.groupby('projectcode', 'page').sum('views', 'sub_score')
# on normalise par la racine de la somme des views
totals = totals.withColumn('score',
totals['SUM(sub_score)'] / F.sqrt(totals['SUM(views)'])) \
.orderBy(F.desc('score')) \
.withColumnRenamed('SUM(views)', 'total_views') \
.limit(10)
views = sums.select('projectcode', 'page', 'day', 'views') \
.join(totals.select('projectcode', 'page', 'total_views', 'score'),
(totals.projectcode == sums.projectcode) & (totals.page == sums.page), 'right_outer')
df = totals.select('projectcode', 'page', 'total_views', 'score').toPandas()
df2 = views.toPandas()
df2 = df2.iloc[:, 2:]
df2 = df2.pivot_table(values='views', columns=['day'], index=['projectcode', 'page'], fill_value=0)
df = df.merge(df2, left_on=['projectcode', 'page'], right_index=True)
df.to_csv(filename(day), index=False)
# on vide le cache
hc.clearCache()
def makeMapping(firstTime):
global routes
grpString = "ORIGIN_AIRPORT_ID","ORIGIN_CITY_NAME","ORIGIN","DEST_AIRPORT_ID","DEST_CITY_NAME","DEST","UNIQUE_CARRIER_NAME"
if firstTime:
routes = routes.groupBy(*grpString).agg(sum("PASSENGERS").alias("PASSENGERS"),sum("DEPARTURES_PERFORMED").alias("DEPARTURES_PERFORMED"),mean("RAMP_TO_RAMP").alias("RAMP_TO_RAMP"))
for i in routes.collect():
if not dictAir.get("Airport{}".format(i[0])):
initNode(i[0],(i[1],i[2]),i[8])
if not dictAir.get("Airport{}".format(i[3])):
initNode(i[3],(i[4],i[5]),0)
if (i[9]!=9876543.21):
tripTime =i[9]
getApt(i[0])['depts'] += i[8]
sourceCNX = getApt(i[0])['cnx']
sourceCNX.append((int(i[3]),tripTime,i[6]))
def test_udf_with_aggregate_function(self):
df = self.spark.createDataFrame([(1, "1"), (2, "2"), (1, "2"), (1, "2")], ["key", "value"])
from pyspark.sql.functions import udf, col, sum
from pyspark.sql.types import BooleanType
my_filter = udf(lambda a: a == 1, BooleanType())
sel = df.select(col("key")).distinct().filter(my_filter(col("key")))
self.assertEqual(sel.collect(), [Row(key=1)])
my_copy = udf(lambda x: x, IntegerType())
my_add = udf(lambda a, b: int(a + b), IntegerType())
my_strlen = udf(lambda x: len(x), IntegerType())
sel = df.groupBy(my_copy(col("key")).alias("k"))\
.agg(sum(my_strlen(col("value"))).alias("s"))\
.select(my_add(col("k"), col("s")).alias("t"))
self.assertEqual(sel.collect(), [Row(t=4), Row(t=3)])
def handleUIOptions(self, displayColName):
agg = self.options.get("aggregation")
valFields = self.options.get("valueFields")
if agg == 'COUNT':
return self.entity.groupBy(displayColName).agg(F.count(displayColName).alias("agg")).toPandas()
elif agg == 'SUM':
return self.entity.groupBy(displayColName).agg(F.sum(valFields).alias("agg")).toPandas()
elif agg == 'AVG':
return self.entity.groupBy(displayColName).agg(F.avg(valFields).alias("agg")).toPandas()
elif agg == 'MIN':
return self.entity.groupBy(displayColName).agg(F.min(valFields).alias("agg")).toPandas()
elif agg == 'MAX':
return self.entity.groupBy(displayColName).agg(F.max(valFields).alias("agg")).toPandas()
elif agg == 'MEAN':
return self.entity.groupBy(displayColName).agg(F.mean(valFields).alias("agg")).toPandas()
else:
return self.entity.groupBy(displayColName).agg(F.count(displayColName).alias("agg")).toPandas()
def test_aggregate_messages(self):
g = self._graph("friends")
# For each user, sum the ages of the adjacent users,
# plus 1 for the src's sum if the edge is "friend".
sendToSrc = (
AM.dst['age'] +
sqlfunctions.when(
AM.edge['relationship'] == 'friend',
sqlfunctions.lit(1)
).otherwise(0))
sendToDst = AM.src['age']
agg = g.aggregateMessages(
sqlfunctions.sum(AM.msg).alias('summedAges'),
sendToSrc=sendToSrc,
sendToDst=sendToDst)
# Run the aggregation again providing SQL expressions as String instead.
agg2 = g.aggregateMessages(
"sum(MSG) AS `summedAges`",
sendToSrc="(dst['age'] + CASE WHEN (edge['relationship'] = 'friend') THEN 1 ELSE 0 END)",
sendToDst="src['age']")
# Convert agg and agg2 to a mapping from id to the aggregated message.
aggMap = {id_: s for id_, s in agg.select('id', 'summedAges').collect()}
agg2Map = {id_: s for id_, s in agg2.select('id', 'summedAges').collect()}
# Compute the truth via brute force.
user2age = {id_: age for id_, age in g.vertices.select('id', 'age').collect()}
trueAgg = {}
for src, dst, rel in g.edges.select("src", "dst", "relationship").collect():
trueAgg[src] = trueAgg.get(src, 0) + user2age[dst] + (1 if rel == 'friend' else 0)
trueAgg[dst] = trueAgg.get(dst, 0) + user2age[src]
# Compare if the agg mappings match the brute force mapping
self.assertEqual(aggMap, trueAgg)
self.assertEqual(agg2Map, trueAgg)
# Check that TypeError is raises with messages of wrong type
with self.assertRaises(TypeError):
g.aggregateMessages(
"sum(MSG) AS `summedAges`",
sendToSrc=object(),
sendToDst="src['age']")
with self.assertRaises(TypeError):
g.aggregateMessages(
"sum(MSG) AS `summedAges`",
sendToSrc=dst['age'],
sendToDst=object())
def test_wrong_args(self):
df = self.data
with QuietTest(self.sc):
with self.assertRaisesRegexp(ValueError, 'Invalid udf'):
df.groupby('id').apply(lambda x: x)
with self.assertRaisesRegexp(ValueError, 'Invalid udf'):
df.groupby('id').apply(udf(lambda x: x, DoubleType()))
with self.assertRaisesRegexp(ValueError, 'Invalid udf'):
df.groupby('id').apply(sum(df.v))
with self.assertRaisesRegexp(ValueError, 'Invalid udf'):
df.groupby('id').apply(df.v + 1)
with self.assertRaisesRegexp(ValueError, 'Invalid function'):
df.groupby('id').apply(
pandas_udf(lambda: 1, StructType([StructField("d", DoubleType())])))
with self.assertRaisesRegexp(ValueError, 'Invalid udf'):
df.groupby('id').apply(pandas_udf(lambda x, y: x, DoubleType()))
with self.assertRaisesRegexp(ValueError, 'Invalid udf.*GROUPED_MAP'):
df.groupby('id').apply(
pandas_udf(lambda x, y: x, DoubleType(), PandasUDFType.SCALAR))
def test_complex_groupby(self):
from pyspark.sql.functions import sum
df = self.data
sum_udf = self.pandas_agg_sum_udf
plus_one = self.python_plus_one
plus_two = self.pandas_scalar_plus_two
# groupby one expression
result1 = df.groupby(df.v % 2).agg(sum_udf(df.v))
expected1 = df.groupby(df.v % 2).agg(sum(df.v))
# empty groupby
result2 = df.groupby().agg(sum_udf(df.v))
expected2 = df.groupby().agg(sum(df.v))
# groupby one column and one sql expression
result3 = df.groupby(df.id, df.v % 2).agg(sum_udf(df.v)).orderBy(df.id, df.v % 2)
expected3 = df.groupby(df.id, df.v % 2).agg(sum(df.v)).orderBy(df.id, df.v % 2)
# groupby one python UDF
result4 = df.groupby(plus_one(df.id)).agg(sum_udf(df.v))
expected4 = df.groupby(plus_one(df.id)).agg(sum(df.v))
# groupby one scalar pandas UDF
result5 = df.groupby(plus_two(df.id)).agg(sum_udf(df.v))
expected5 = df.groupby(plus_two(df.id)).agg(sum(df.v))
# groupby one expression and one python UDF
result6 = df.groupby(df.v % 2, plus_one(df.id)).agg(sum_udf(df.v))
expected6 = df.groupby(df.v % 2, plus_one(df.id)).agg(sum(df.v))
# groupby one expression and one scalar pandas UDF
result7 = df.groupby(df.v % 2, plus_two(df.id)).agg(sum_udf(df.v)).sort('sum(v)')
expected7 = df.groupby(df.v % 2, plus_two(df.id)).agg(sum(df.v)).sort('sum(v)')
self.assertPandasEqual(expected1.toPandas(), result1.toPandas())
self.assertPandasEqual(expected2.toPandas(), result2.toPandas())
self.assertPandasEqual(expected3.toPandas(), result3.toPandas())
self.assertPandasEqual(expected4.toPandas(), result4.toPandas())
self.assertPandasEqual(expected5.toPandas(), result5.toPandas())
self.assertPandasEqual(expected6.toPandas(), result6.toPandas())
self.assertPandasEqual(expected7.toPandas(), result7.toPandas())
def test_smvTimePanelAgg_with_Week(self):
df = self.createDF("k:Integer; ts:String; v:Double",
"1,20120301,1.5;" +
"1,20120304,4.5;" +
"1,20120308,7.5;" +
"1,20120309,2.45"
).withColumn("ts", col('ts').smvStrToTimestamp("yyyyMMdd"))
import smv.panel as p
res = df.smvGroupBy('k').smvTimePanelAgg(
'ts', p.Week(2012, 3, 1), p.Week(2012, 3, 10)
)(
sum('v').alias('v')
)
expect = self.createDF("k: Integer;smvTime: String;v: Double",
"""1,W20120305,9.95;
1,W20120227,6.0""")
self.should_be_same(res, expect)
def test_smvTimePanelAgg(self):
df = self.createDF("k:Integer; ts:String; v:Double",
"""1,20120101,1.5;
1,20120301,4.5;
1,20120701,7.5;
1,20120501,2.45"""
).withColumn("ts", col('ts').smvStrToTimestamp("yyyyMMdd"))
import smv.panel as p
res = df.smvGroupBy('k').smvTimePanelAgg(
'ts', p.Quarter(2012,1), p.Quarter(2012,2)
)(
sum('v').alias('v')
)
expect = self.createDF("k: Integer;smvTime: String;v: Double",
"""1,Q201201,6.0;
1,Q201202,2.45""")
self.should_be_same(expect, res)
def test_complex_expressions(self):
df = self.data
plus_one = self.python_plus_one
plus_two = self.pandas_scalar_plus_two
sum_udf = self.pandas_agg_sum_udf
# Test complex expressions with sql expression, python UDF and
# group aggregate pandas UDF
result1 = (df.withColumn('v1', plus_one(df.v))
.withColumn('v2', df.v + 2)
.groupby(df.id, df.v % 2)
.agg(sum_udf(col('v')),
sum_udf(col('v1') + 3),
sum_udf(col('v2')) + 5,
plus_one(sum_udf(col('v1'))),
sum_udf(plus_one(col('v2'))))
.sort('id')
.toPandas())
expected1 = (df.withColumn('v1', df.v + 1)
.withColumn('v2', df.v + 2)
.groupby(df.id, df.v % 2)
.agg(sum(col('v')),
sum(col('v1') + 3),
sum(col('v2')) + 5,
plus_one(sum(col('v1'))),
sum(plus_one(col('v2'))))
.sort('id')
.toPandas())
# Test complex expressions with sql expression, scala pandas UDF and
# group aggregate pandas UDF
result2 = (df.withColumn('v1', plus_one(df.v))
.withColumn('v2', df.v + 2)
.groupby(df.id, df.v % 2)
.agg(sum_udf(col('v')),
sum_udf(col('v1') + 3),
sum_udf(col('v2')) + 5,
plus_two(sum_udf(col('v1'))),
sum_udf(plus_two(col('v2'))))
.sort('id')
.toPandas())
expected2 = (df.withColumn('v1', df.v + 1)
.withColumn('v2', df.v + 2)
.groupby(df.id, df.v % 2)
.agg(sum(col('v')),
sum(col('v1') + 3),
sum(col('v2')) + 5,
plus_two(sum(col('v1'))),
sum(plus_two(col('v2'))))
.sort('id')
.toPandas())
# Test sequential groupby aggregate
result3 = (df.groupby('id')
.agg(sum_udf(df.v).alias('v'))
.groupby('id')
.agg(sum_udf(col('v')))
.sort('id')
.toPandas())
expected3 = (df.groupby('id')
.agg(sum(df.v).alias('v'))
.groupby('id')
.agg(sum(col('v')))
.sort('id')
.toPandas())
self.assertPandasEqual(expected1, result1)
self.assertPandasEqual(expected2, result2)
self.assertPandasEqual(expected3, result3)
def run(self, i):
df = i[_DEP_NAME_]
return df.groupBy(F.col("ST")).agg(F.sum(F.col("EMP")).alias("EMP"))
hbase_rdd = sc.newAPIHadoopRDD(HBaseUtil.inputFormatClass,
HBaseUtil.keyClass,
HBaseUtil.valueClass,
keyConverter=HBaseUtil.keyConv,
valueConverter=HBaseUtil.valueConv,
conf=HBaseUtil.conf)
values = hbase_rdd.values()
init_rdd = values.flatMap(lambda x: x.split("\n")).map(lambda x: json.loads(x)) \
.map(lambda x: dp.dict_del(x))
data_frame = sqlContext.read.json(init_rdd)
# data_frame.show()
# data_frame.printSchema()
result = data_frame.groupBy('qualifier').agg(
F.min(data_frame.value), F.max(data_frame.value),
F.avg(data_frame.value), F.sum(data_frame.value),
F.count(data_frame.value)).collect()
for x in result:
print(x)
# valid_rdd = init_rdd.filter(
# lambda x: x.get('A1')[0:4] == '0110' and int(x.get('A6')) == 0).cache()
# roll_filter_rdd = valid_rdd.filter(lambda x: not x.get('A2') is None).map(lambda x: float(x.get('A2'))).cache()
# roll_mean = roll_filter_rdd.mean()
# roll_count = roll_filter_rdd.count()
# roll_max = roll_filter_rdd.max()
# roll_min = roll_filter_rdd.min()
#
# pitch_filter_rdd = valid_rdd.filter(lambda x: not x.get('A3') is None).map(lambda x: float(x.get('A3'))).cache()
# pitch_mean = pitch_filter_rdd.mean()
# pitch_count = pitch_filter_rdd.count()
.getOrCreate()
#part 1 question 1
df = spark.read.csv(r"C:\Users\pallavi\PycharmProjects\BDP_ICP10\survey.csv",
header=True)
df.createOrReplaceTempView("Survey")
#part 1 question 2
df.write.option("header", "true").csv("spark_survey3.csv")
#part 1 question 3
print(df.dropDuplicates().count())
df.groupBy(df.columns)\
.count()\
.where(f.col('count') > 1)\
.select(f.sum('count'))\
.show()
#part 1 question 4
spark.sql(
"select * from Survey where Gender = 'Male' or Gender = 'M' or Gender='male'"
).createTempView("Table_Male")
spark.sql("select * from Survey where Gender = 'Female' or Gender = 'female'"
).createTempView("Table_Female")
spark.sql(
"select * from Table_Male union select * from Table_Female order by Country "
).show(50)
#part 1 question 5
spark.sql("select treatment,count(*) as count from Survey group by treatment"
).show()
def test_complex_expressions(self):
df = self.data
plus_one = self.python_plus_one
plus_two = self.pandas_scalar_plus_two
sum_udf = self.pandas_agg_sum_udf
# Test complex expressions with sql expression, python UDF and
# group aggregate pandas UDF
result1 = (df.withColumn('v1', plus_one(df.v))
.withColumn('v2', df.v + 2)
.groupby(df.id, df.v % 2)
.agg(sum_udf(col('v')),
sum_udf(col('v1') + 3),
sum_udf(col('v2')) + 5,
plus_one(sum_udf(col('v1'))),
sum_udf(plus_one(col('v2'))))
.sort(['id', '(v % 2)'])
.toPandas().sort_values(by=['id', '(v % 2)']))
expected1 = (df.withColumn('v1', df.v + 1)
.withColumn('v2', df.v + 2)
.groupby(df.id, df.v % 2)
.agg(sum(col('v')),
sum(col('v1') + 3),
sum(col('v2')) + 5,
plus_one(sum(col('v1'))),
sum(plus_one(col('v2'))))
.sort(['id', '(v % 2)'])
.toPandas().sort_values(by=['id', '(v % 2)']))
# Test complex expressions with sql expression, scala pandas UDF and
# group aggregate pandas UDF
result2 = (df.withColumn('v1', plus_one(df.v))
.withColumn('v2', df.v + 2)
.groupby(df.id, df.v % 2)
.agg(sum_udf(col('v')),
sum_udf(col('v1') + 3),
sum_udf(col('v2')) + 5,
plus_two(sum_udf(col('v1'))),
sum_udf(plus_two(col('v2'))))
.sort(['id', '(v % 2)'])
.toPandas().sort_values(by=['id', '(v % 2)']))
expected2 = (df.withColumn('v1', df.v + 1)
.withColumn('v2', df.v + 2)
.groupby(df.id, df.v % 2)
.agg(sum(col('v')),
sum(col('v1') + 3),
sum(col('v2')) + 5,
plus_two(sum(col('v1'))),
sum(plus_two(col('v2'))))
.sort(['id', '(v % 2)'])
.toPandas().sort_values(by=['id', '(v % 2)']))
# Test sequential groupby aggregate
result3 = (df.groupby('id')
.agg(sum_udf(df.v).alias('v'))
.groupby('id')
.agg(sum_udf(col('v')))
.sort('id')
.toPandas())
expected3 = (df.groupby('id')
.agg(sum(df.v).alias('v'))
.groupby('id')
.agg(sum(col('v')))
.sort('id')
.toPandas())
assert_frame_equal(expected1, result1)
assert_frame_equal(expected2, result2)
assert_frame_equal(expected3, result3)
from pyspark.sql import SparkSession
from pyspark import SparkConf, SparkContext
my_spark = SparkSession.builder.getOrCreate()
df = my_spark.read.format("com.mongodb.spark.sql.DefaultSource").option(
"uri",
"mongodb://*****:*****@ec2-54-210-44-189.compute-1.amazonaws.com/test.reviews"
).load()
df1 = my_spark.read.format("com.mongodb.spark.sql.DefaultSource").option(
"uri",
"mongodb://*****:*****@ec2-54-210-44-189.compute-1.amazonaws.com/test.metadata"
).load()
df3 = df.groupBy("asin").agg(
func.sum("overall").alias("item_sum"),
func.count(func.lit(1)).alias("item_counts"))
df3 = df3.filter(df3["item_counts"] >= 100)
df3 = df3.withColumn("item_avg",
func.col("item_sum") /
func.col("item_counts")).drop('item_sum')
#select from metadata
df4 = df1.select("asin", "categories", "title")
df5 = df4.join(df3, "asin", "inner").drop("asin")
df6 = df5.select("categories", "title", "item_counts", "item_avg",
func.explode_outer("categories"))
df7 = df6.drop("categories").withColumnRenamed("col", "categories")
df9 = df7.select("categories", "title", "item_counts",
"item_avg").sort("categories")
#master_regex_one_df = master_regex_one_df.repartition(160)
mid_time1 = time.time()
print("Number of partitions of master_regex_one_df: {}".format(
master_regex_one_df.rdd.getNumPartitions()))
master_shrunken_df = master_regex_one_df.where(
'regexes_diff_bool == 1 or core_diff_bool == 1')
print("--- %s seconds ---" % (time.time() - mid_time1))
print(
'these two should be these same, the summing of regexes_diff_bool and core_diff_bool:'
)
# TEST - these two should be the same (if not 0):
print("master:")
master_regex_one_df.groupBy("year", "month").agg(
f.sum("regexes_diff_bool").alias("num_revs_with_regex_diff"),
f.sum("core_diff_bool").alias("num_revs_with_core_diff")).orderBy(
master_regex_one_df.year, master_regex_one_df.month).show(n=30)
print("filtered:")
master_shrunken_df.groupBy("year", "month").agg(
f.sum("regexes_diff_bool").alias("num_revs_with_regex_diff"),
f.sum("core_diff_bool").alias("num_revs_with_core_diff")).orderBy(
master_shrunken_df.year, master_shrunken_df.month).show(n=30)
print('\n\n\n')
# MASTER
#TODO See if we can export the master_regex_one_df file actually
#print("Preview master_regex_one_df: ")
#master_regex_one_df.orderBy(master_regex_one_df.articleid.asc_nulls_first(), master_regex_one_df.year, master_regex_one_df.month, master_regex_one_df.date_time).show(n=10)
#out_filepath_master = "{}/{}_master_{}.tsv".format(args.output_directory,args.output_filename,datetime.utcnow().strftime("%Y-%m-%d_%H-%M-%S"))
# MAGIC - Explore the data to see whether we can use it to produce energy consumption forecasts, and if yes, write code to prepare the data to fit a model on
# MAGIC
# MAGIC We can start by calculating energy consumption at daily and monthly levels.
# MAGIC We can do that by doing separate aggregations and save the datasets separately. Alternatively, we can do roll up aggregations, where we calculate hiearchical subtotals
# MAGIC (from left to right).
# COMMAND ----------
consumption_df = (consumption_df.selectExpr('to_date(DateTime) AS Date', '*')
.selectExpr('year(date) AS Year', 'month(Date) as Month', '*')
)
# COMMAND ----------
consumption_rollup_df = (consumption_df.rollup(['Year', 'Month', 'Date', 'LCLid']) # pay attention to the ordering of cols, as aggregations goes left-to-right
.agg(f.sum('KWH_half_hour').alias('KWH'),
f.countDistinct('LCLid').alias('n_households')
)
.orderBy(['Year', 'Month', 'Date', 'LCLid'])
).cache()
# COMMAND ----------
# triggering the computation
display(consumption_rollup_df)
# COMMAND ----------
# we cached the object, so this will be instantaneous:
display(consumption_rollup_df)
StructField("name", StringType(), True)
])
names = spark.read.schema(schema).option("sep", " ").csv("./Marvel-Names.txt")
lines = spark.read.text("./Marvel-Graph.txt")
connections = (
# the first column of each row is the id of a superhero we want to check
lines.withColumn("id",
func.split(func.col("value"), " ")[0])
# - 1 means exclude the superhero him/herself
.withColumn("connections",
func.size(func.split(func.col("value"), " ")) -
1).groupBy("id").agg(
func.sum("connections").alias("connections")))
minConnectionCount = connections.agg(func.min("connections")).first()[0]
# list all heroes who has the least amount of connections
mostObscureHeroes = connections.filter(
func.col("connections") == minConnectionCount)
mostObscureHeroNames = names.join(mostObscureHeroes, "id", "inner")
print(
f"The following characters have only {minConnectionCount} connection{ '' if minConnectionCount <=1 else 's' }"
)
mostObscureHeroNames.select("name").show()
# Stop the session
target, F.log1p(F.col(target))))
fitted = gbt.fit(X_train)
yhat = (fitted.transform(X_test).withColumn(
"prediction", F.expm1(F.col("prediction"))).withColumn(
target, F.expm1(F.col(target))).withColumn(
'fiability', 1 - F.abs(F.col(target) - F.col("prediction")) /
F.col(target)).withColumn(
'fiability',
F.when(F.col("fiability") < 0,
0).otherwise(F.col("fiability"))))
print(
yhat.select(
F.sum(F.col(target) * F.col("fiability")) /
F.sum(F.col(target))).show())
eval_ = RegressionEvaluator(labelCol=target,
predictionCol="prediction",
metricName="rmse")
rmse = eval_.evaluate(yhat)
print('rmse is %.2f' % rmse)
mae = eval_.evaluate(yhat, {eval_.metricName: "mae"})
print('mae is %.2f' % mae)
r2 = eval_.evaluate(yhat, {eval_.metricName: "r2"})
print('r2 is %.2f' % r2)
df.createTempView('orders_VW')
spark.sql('select * from orders_VW order by order_status desc').show()
#--------------------------------------------
#-------------------AGGREGATE----------------
#--------------------------------------------
# list all availale functions , spark-shell can be used for this to print all available apis'
# spark-shell
# org.apache.spark.sql.functions.
from pyspark.sql.functions import sum # check if required
df.select(round(sum('order_price'),2)).show()
#GROUPBY
df.groupBy('Order_id').sum('order_price').show()
df.groupBy('Order_id').agg(sum('order_price')).show()
df.groupBy('Order_id').agg(sum('order_price').alias('sum_price')).show() # alias not allowed without agg
df.groupBy('Order_id').count().show()
df.groupBy("department","state").sum("salary","bonus")
# to have alias and every function inside agg reuqires column name`
df_orderItems.groupBy('Order_id').agg(count('order_id').alias('price_count')).show()
# COMMAND ----------
# TEST
from test_helper import Test
Test.assertEquals(sizedFirst[0], len(sizedFirst[1]), 'incorrect implementation for sized')
# COMMAND ----------
# MAGIC %md
# MAGIC Next, you'll need to aggregate the counts. You can do this using `func.sum` in either a `.select` or `.agg` method call on the `DataFrame`. Make sure to give your `Column` the alias `numberOfWords`. There are some examples in [Python](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.GroupedData.agg) and [Scala](https://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.DataFrame) in the APIs.
# COMMAND ----------
# ANSWER
numberOfWords = sized.agg(func.sum('size').alias('numberOfWords'))
wordCount = numberOfWords.first()[0]
print wordCount
# COMMAND ----------
# TEST
Test.assertEquals(wordCount, 1903220, 'incorrect word count')
# COMMAND ----------
# MAGIC %md
# MAGIC Next, we'll compute the word count using `select`, the function `func.explode()`, and then taking a `count()` on the `DataFrame`. Make sure to name the column returned by the `explode` function 'word'.
# COMMAND ----------
# - daily_usage_aggs
# as health:
# - as_health_aggs
# as sessions:
# - as_session_aggs
# as clicks:
# - as_clicks_aggs
# as snippets:
# - as_snippets_aggs = get_as_snippets_aggs(message_id=None)
# agg fields: main summary usage ------------------
days_used = F.countDistinct(F.col('activity_dt')).alias('days_used')
active_hours = F.sum(
F.coalesce(F.col('active_ticks'), F.lit(0)) * F.lit(5) /
F.lit(3600)).alias('active_hours')
uris = F.sum(
F.coalesce(F.col('scalar_parent_browser_engagement_total_uri_count'),
F.lit(0))).alias('uris')
tabs_opened = F.sum(
F.coalesce(F.col('scalar_parent_browser_engagement_tab_open_event_count'),
F.lit(0))).alias('tabs_opened')
windows_opened = F.sum(
F.coalesce(
F.col('scalar_parent_browser_engagement_window_open_event_count'),
F.lit(0))).alias('windows_opened')
# data_c = spark.read.json('channel_base/part*', schema = channel_sch)
convertTime = functions.udf(timeToFrame)
data_s = spark.read.json('stream_info.json', schema = stream_sch)
data_c = spark.read.json('channel_info.json', schema = channel_sch).cache()
data_s = data_s.withColumn('time_frame', convertTime(data_s.created_at)).cache()
data_s.createOrReplaceTempView('data_s')
data_c.createOrReplaceTempView('data_c')
game_count_by_time = data_s.groupBy('time_frame', 'game_name').count()
game_count_by_time = game_count_by_time.orderBy(game_count_by_time['count'].desc())
view_count_by_time = data_s.groupBy('time_frame', 'game_name').agg(functions.sum('viewers').alias('total_view'))
view_count_by_time = view_count_by_time.orderBy(view_count_by_time['total_view'].desc())
game_count_by_time.coalesce(1).write.json('game_count_by_time', mode = 'overwrite')
view_count_by_time.coalesce(1).write.json('view_count_by_time', mode = 'overwrite')
view_num_by_game = data_c.groupby(data_c['game']).agg(functions.sum(data_c['views']),functions.sum(data_c['followers']))
view_num_by_streamer = data_c\
.select('stream_id','channel_id','game','name','views','followers','created_at','updated_at','partner')\
.orderBy(functions.desc('views'),'game')
print(view_num_by_streamer.show(5))
viewcount_by_game = view_num_by_game\
df_vuelos_retraso = df_vuelos_retraso.join(df_pais, df_pais["cod_pais"] == df_vuelos_retraso["origen"], 'inner')
df_vuelos_retraso = df_vuelos_retraso.select("vuelo", f.col("pais").alias("origen"), "destino","dias_retraso")
df_vuelos_retraso = df_vuelos_retraso.join(df_pais, df_pais["cod_pais"] == df_vuelos_retraso["destino"], 'inner')
df_vuelos_retraso = df_vuelos_retraso.select("vuelo", "origen", f.col("pais").alias("destino"),"dias_retraso")
print("Retraso Vuelos")
print(df_vuelos_retraso.show())
#df_top_dia_retrasos.write.mode("overwrite").saveAsTable("top_dia_retrasos")
df_retraso_acumulado = df_join.join(df_vuelos, "vuelo", 'inner')
df_retraso_acumulado = df_retraso_acumulado.groupBy('origen', 'dia').sum('dias_retraso')
df_retraso_acumulado = df_retraso_acumulado.select("origen","dia",f.col("sum(dias_retraso)").alias("retraso"))
window = w.Window.partitionBy(f.col("origen")).orderBy(f.col("retraso"))
df_retraso_acumulado = df_retraso_acumulado.withColumn("retraso_acumulado", f.sum("retraso").over(window))
df2 = df_retraso_acumulado.repartition(4)
##testing
"""
pais_vip = ["Peru", "España", "Mexico"]
udf_pais_vip = f.udf(lambda x : "VIP" if x in pais_vip else "NO VIP")
df = df_salidas.select(f.col("pais"),f.col("top_salidas"), udf_pais_vip(f.col("pais")).alias("vip"))
columns = ["Seqno","Name"]
data = [("1", "john jones"),
("2", "tracey smith"),
("3", "amy sanders")]
df = spark.createDataFrame(data=data,schema=columns)
duration_udf = udf(lambda start, end: getDuration(start, end))
length_udf = udf(lambda length: convertLength(length))
count_active_udf = udf(
lambda duration, length: countForActive(duration, length))
percentage_udf = udf(
lambda numActive, numTotal: percentageActive(numActive, numTotal))
content_map = spark.read.csv('../content_mapping.csv',
header='true').select('title', 'length')
df = spark.read.parquet(
'./DLT_03_users_device_logs_shifted/*').dropDuplicates().na.drop()
df = df.join(content_map, [df.item_name == content_map.title])
df = df.withColumn('duration', duration_udf(df['start_time'], df['end_time']))
df = df.withColumn('length', length_udf(df['length']))
df = df.withColumn('counts_for_active',
count_active_udf(df['duration'], df['length']))
df = df.groupBy('device_id',
'month').agg(F.sum('counts_for_active').alias('count'))
active = df.filter(df['count'] >= 20)
non_active = df.filter(df['count'] < 20)
active = active.groupBy('month').agg(
F.count('device_id').alias('active')).orderBy('month')
non_active = non_active.groupBy('month').agg(
F.count('device_id').alias('user_counts')).orderBy('month').selectExpr(
'month as month2', 'user_counts as non_active')
all_users = active.join(
non_active,
[active.month == non_active.month2]).drop('month2').orderBy('month')
all_users.coalesce(1).write.parquet('DTL_04_users_by_month')
def test_select_aggregate_dont_preserve_order(self):
from pyspark.sql.functions import sum
self.shared_test_partition_preserving(lambda df: df.select(sum('forecast')), False)
def count_not_null(c):
return sum(col(c).isNotNull().cast("integer")).alias(c)
def count_null(col_name):
""" Build up a list of column expressions, one per column. """
return sum(col(col_name).isNull().cast("integer")).alias(col_name)
.master("local[4]")\
.appName("NASA Kennedy Space Center WWW server")\
.config("spark.sql.execution.arrow.enabled", "true")\
.config("spark.memory.fraction", 0.8)\
.config("spark.executor.memory", "1g")\
.config("spark.driver.memory", "1g")\
.getOrCreate()
nasaSchema = StructType([StructField("host",StringType(),True),\
StructField("timestamp",StringType(),True),\
StructField("requisicao",StringType(),True),\
StructField("code_http",IntegerType(),True),\
StructField("total_bytes",StringType(),True)])
nasa = spark.createDataFrame(df_nasa, schema=nasaSchema)
nasa.select('host').distinct().count().show()
nasa.filter(f.col('code_http') == 404).count().show()
nasa.groupBy("requisicao").agg(f.count('requisicao').alias('qtd')).sort(
f.desc(qtd)).limit(5).show()
nasa.withColumn('data', nasa['timestamp'].substr(1, 10)).show()
nasa.filter(f.col('code_http') == 404).groupBy('data').agg(
f.count('code_http')).show()
nasa.filter(f.col('code_http') == 404).groupBy('data').agg(
f.sum('total_bytes')).show()
def trioAnalysis(nMax):
routed = itens.map(lambda x: (x[0],x[1],(parseResults(closestRoute(x[0],x[1],0,0),"trios")),x[2],x[3]))
routed = routed.map(lambda x: (x[0],x[1],x[2],(x[4]-tripDistance(x[2]))*x[3]))
print("Calculating optimal routes... (this could take a minute, but let's see you try to give directions to 5.8 million people!)")
schemaString = ['ORIGIN_AIRPORT_ID','Dest','Trip','kmSaved']
kmSavedDF=sqlc.createDataFrame(routed,schemaString).groupBy("ORIGIN_AIRPORT_ID").agg(sum("kmSaved").alias("kmSaved"))
print("Looking up airport names...")
kmSavedDF=kmSavedDF.map(lambda x: Row(Name=getName(x[0]),kmPerDept=(x[1]/getApt(x[0])['depts']),totalDepts=getApt(x[0])['depts'],totalKm = x[1]))
kmSavedDF=sqlc.createDataFrame(kmSavedDF,['Name','kmPerDept','totalDepts','totalKm'])
print("Saving data...")
cwd = os.getcwd()
kmSavedDF.write.parquet(cwd+"/FlightData/FlightOptResults.parquet")
routes.write.parquet(cwd+"/FlightData/FlightOptRoutes.parquet")
itens.write.parquet(cwd+"/FlightData/FlightOptItens.parquet")
return kmSavedDF
.option("kafka.bootstrap.servers", "localhost:9092") \
.option("subscribe", "trades") \
.option("startingOffsets", "earliest") \
.load()
value_df = kafka_df.select(
from_json(col("value").cast("string"), stock_schema).alias("value"))
trade_df = value_df.select("value.*") \
.withColumn("CreatedTime", to_timestamp(col("CreatedTime"), "yyyy-MM-dd HH:mm:ss")) \
.withColumn("Buy", expr("case when Type == 'BUY' then Amount else 0 end")) \
.withColumn("Sell", expr("case when Type == 'SELL' then Amount else 0 end"))
window_agg_df = trade_df \
.withWatermark("CreatedTime", "30 minute") \
.groupBy(window(col("CreatedTime"), "15 minute")) \
.agg(sum("Buy").alias("TotalBuy"),
sum("Sell").alias("TotalSell"))
output_df = window_agg_df.select("window.start", "window.end", "TotalBuy",
"TotalSell")
window_query = output_df.writeStream \
.format("console") \
.outputMode("update") \
.option("checkpointLocation", "chk-point-dir") \
.trigger(processingTime="30 second") \
.start()
logger.info("Waiting for Query")
window_query.awaitTermination()
def test_mixed_udfs(self):
"""
Test mixing group aggregate pandas UDF with python UDF and scalar pandas UDF.
"""
df = self.data
plus_one = self.python_plus_one
plus_two = self.pandas_scalar_plus_two
sum_udf = self.pandas_agg_sum_udf
# Mix group aggregate pandas UDF and python UDF
result1 = (df.groupby('id')
.agg(plus_one(sum_udf(df.v)))
.sort('id'))
expected1 = (df.groupby('id')
.agg(plus_one(sum(df.v)))
.sort('id'))
# Mix group aggregate pandas UDF and python UDF (order swapped)
result2 = (df.groupby('id')
.agg(sum_udf(plus_one(df.v)))
.sort('id'))
expected2 = (df.groupby('id')
.agg(sum(plus_one(df.v)))
.sort('id'))
# Mix group aggregate pandas UDF and scalar pandas UDF
result3 = (df.groupby('id')
.agg(sum_udf(plus_two(df.v)))
.sort('id'))
expected3 = (df.groupby('id')
.agg(sum(plus_two(df.v)))
.sort('id'))
# Mix group aggregate pandas UDF and scalar pandas UDF (order swapped)
result4 = (df.groupby('id')
.agg(plus_two(sum_udf(df.v)))
.sort('id'))
expected4 = (df.groupby('id')
.agg(plus_two(sum(df.v)))
.sort('id'))
# Wrap group aggregate pandas UDF with two python UDFs and use python UDF in groupby
result5 = (df.groupby(plus_one(df.id))
.agg(plus_one(sum_udf(plus_one(df.v))))
.sort('plus_one(id)'))
expected5 = (df.groupby(plus_one(df.id))
.agg(plus_one(sum(plus_one(df.v))))
.sort('plus_one(id)'))
# Wrap group aggregate pandas UDF with two scala pandas UDF and user scala pandas UDF in
# groupby
result6 = (df.groupby(plus_two(df.id))
.agg(plus_two(sum_udf(plus_two(df.v))))
.sort('plus_two(id)'))
expected6 = (df.groupby(plus_two(df.id))
.agg(plus_two(sum(plus_two(df.v))))
.sort('plus_two(id)'))
self.assertPandasEqual(expected1.toPandas(), result1.toPandas())
self.assertPandasEqual(expected2.toPandas(), result2.toPandas())
self.assertPandasEqual(expected3.toPandas(), result3.toPandas())
self.assertPandasEqual(expected4.toPandas(), result4.toPandas())
self.assertPandasEqual(expected5.toPandas(), result5.toPandas())
self.assertPandasEqual(expected6.toPandas(), result6.toPandas())
# +---+--------------------+
# | id| name|
# +---+--------------------+
# | 1|24-HOUR MAN/EMMANUEL|
# | 2|3-D MAN/CHARLES CHAN|
# | 3| 4-D MAN/MERCURIO|
# | 4| 8-BALL/|
# | 5| A|
# +---+--------------------+
lines = spark.read.text('datasets/Marvel-graph.txt')
connections = lines.withColumn('id', F.split(F.col('value'), ' ')[0])\
.withColumn('connections', F.size(F.split(F.col('value'), ' ')) - 1)\
.groupby('id')\
.agg(F.sum('connections').alias('connections'))\
.sort('connections', ascending=True)
# We get the first from a ordered rank on #connections
least_popular = connections.first()
least_popular_conn = connections.filter(F.col('connections') == least_popular.connections)
least_popular_conn.show()
joined = least_popular_conn.join(hero_names, 'id')
joined.show()
# +----+-----------+--------------------+
# | id|connections| name|
# +----+-----------+--------------------+
# 取出特定欄位
statColumn = ['CUSAUNT', 'CARNO', 'STDNO', 'TDATE', 'QTY', 'MILE']
pDf = df.select(statColumn)
# 分離日期欄位的年及月至新欄位
tDf = (pDf.withColumn('TDATEYEAR', pDf['TDATE'].substr(1, 4)).withColumn(
'TDATEMONTH',
pDf['TDATE'].substr(5, 2)).withColumn('TDATEDAY',
pDf['TDATE'].substr(7, 2)))
# 刪除不必要欄位
tDf = tDf.drop('TDATE')
#
groupColumn = [
'CUSAUNT', 'CARNO', 'STDNO', 'TDATEYEAR', 'TDATEMONTH', 'TDATEDAY'
]
sDf = (tDf.groupBy(groupColumn).agg(
sum(tDf.QTY.cast('float')).alias('sQty'),
sum(tDf.MILE.cast('float')).alias('sMile'),
count(tDf.QTY.cast('float')).alias('cTimes')).orderBy(groupColumn))
#
# 目的路徑
outputPath = "/home/cpc/data/resultData"
# 目的資料
outputFile = "cusauntCarnoYMDsQtysMilecTimes"
# 完整路徑和資料
outputFull = outputPath + "/" + outputFile
#
sDf.toJSON().coalesce(1).saveAsTextFile(outputFull)
#
# 年度月油品（汽油/柴油）銷售總量
#
# Subtract startTime from endTime to get lifeTime of each revision
tScoring = perf_counter()
dfScore = dfScore.withColumn(
"LiveSeconds",
func.when(
func.isnull(dfScore.EpochTimestampEnd -
dfScore.EpochTimestampStart),
dateOfDataDumpInEpochSeconds -
dfScore.EpochTimestampStart).otherwise(
dfScore.EpochTimestampEnd - dfScore.EpochTimestampStart))
# dfScore.show()
# print("Table with Lifetime Calcs ^^^")
# Score each contributor based on the average and total life of their revisions
contributors = Window.partitionBy("cleanContributor")
outDf = dfScore.withColumn("Score-Sum", func.sum("LiveSeconds").over(contributors)) \
.withColumn("Score-Avg", func.avg("LiveSeconds").over(contributors)) \
.withColumn("Score-Count", func.count("LiveSeconds").over(contributors)) \
.select("cleanContributor","Score-Sum","Score-Avg","Score-Count","isRegistered").distinct().orderBy("cleanContributor","Score-Sum","Score-Avg","Score-Count",ascending=False)
# numContributors = outDf.count()
tScoringEnd = perf_counter()
# outDf.show(numContributors,truncate=False)
# outDf.show()
# print("Total number contributors: ",numContributors)
# Write output to Postgresql database
dbTableName = "ContributorScores"
tWriteDB = perf_counter()
url = 'jdbc:postgresql://10.0.0.11:5442/cluster_output'
postgresUser = os.environ['POSTGRES_USER']
postgresPass = os.environ['POSTGRES_PASS']
spark = SparkSession.builder.master(
"spark://ec2-34-206-0-125.compute-1.amazonaws.com:7077").appName(
"amazon-insights").config("spark.executor.memory",
"6gb").getOrCreate()
sqlContext = SQLContext(spark.sparkContext)
departments = []
s3 = boto3.client('s3')
response = s3.list_objects_v2(Bucket='amazonreviewsinsight', Delimiter='/')
obj = response.get('CommonPrefixes')
for obj in response.get('CommonPrefixes'):
department = str(obj.get('Prefix')).replace("product_category=", "")
departments.append(department)
reviews = sqlContext.read.parquet(
's3a://amazonreviewsinsight/product_category=Electronics/part-00000-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet'
)
reviews = sqlContext.read.parquet(
's3a://amazonreviewsinsight/product_category=' + departments[0])
reviews = reviews.filter(reviews.marketplace == 'US')
reviews = reviews.drop('market_place', 'product_id', 'customer_id',
'review_id', 'product_parent', 'vine',
'review_headline')
reviews = reviews.groupby('product_title', 'review_date').agg(
f.mean('star_rating').alias('avg_star_rating_daily'),
f.count('product_title').alias('no_of_purchases'),
f.sum('helpful_votes').alias('helpful_votes_in_day'),
f.sum('total_votes').alias('total_votes_in_day'),
f.collect_list('review_body').alias("daily_text_review"))
reviews = reviews.withColumn(
"reviews_no_punc",
lower(trim(regexp_replace('daily_reviews', '[^A-Za-z0-9 ]+', ''))))
def get_aggregared_sum(self, df, dimension_columns):
grouped_data = {}
for col in dimension_columns:
agg_df = df.groupBy(col).agg(FN.sum(col).alias("count")).toPandas()
grouped_data[col] = dict(list(zip(agg_df[col], agg_df["count"])))
return grouped_data
).option('mode', 'DROPMALFORMED').load(
"file:///media/alessandro/storage/big_data-primoProgetto/dataset/X1_historical_stocks.csv"
)
stock_prices = stock_prices.select('ticker', 'close', 'volume',
year("date").alias('year'))
stocks = stocks.select('ticker', 'sector')
joined = stock_prices.join(stocks, on='ticker')
filtered = joined.filter((joined.year <= '2018') & (joined.year >= '2004')
& (joined.sector != 'N/A'))
intermediate1 = filtered.groupBy('sector', 'year').agg(
F.sum(filtered.volume).alias('volCompl'),
F.mean(filtered.close).alias('avg_volume'))
intermediate1 = intermediate1.sort(F.desc('sector'), F.desc('year'))
intermediate2 = filtered.groupBy('sector', 'year').agg(
F.sum(filtered.close).alias('actualQuote'))
intermediate2 = intermediate2.sort(F.desc('sector'), F.desc('year'))
intermediate3 = intermediate2.withColumn(
'previousQuote',
F.lead('actualQuote').over(
Window.partitionBy('sector').orderBy(F.desc('sector'),
F.desc('year'))))
def __call__(self, df, c, by=None, index='_idx', result='_res'):
return dataframe.percentiles(df, c, by, self.p, index, result)
class typeof:
def __call__(self, df, c, by=None, index='_idx', result='_res'):
_gcols = [by] if isinstance(by, str) and by else by or []
t = df.select(c).schema.fields[0].dataType.simpleString()
return df.select(c, *_gcols).groupby(*_gcols).agg(
F.lit(c).alias(index),
F.lit(t).alias(result))
df_functions = (typeof, topn, topn_count, topn_values, percentiles)
null = lambda c: F.sum(c.isNull().cast('int'))
nan = lambda c: F.sum(c.isnan)
integer = lambda c: F.coalesce(F.sum((F.rint(c) == c).cast('int')), F.lit(0))
boolean = lambda c: F.coalesce(
F.sum((c.cast('boolean') == F.rint(c)).cast('int')), F.lit(0))
zero = lambda c: F.sum((c == 0).cast('int'))
empty = lambda c: F.sum((F.length(c) == 0).cast('int'))
pos = lambda c: F.sum((c > 0).cast('int'))
neg = lambda c: F.sum((c < 0).cast('int'))
distinct = lambda c: F.countDistinct(c)
one = lambda c: F.first(c, False).cast(T.StringType())
count = F.count
sum = F.sum
sum_pos = lambda c: F.sum(F.when(c > 0, c))
def main():
glueContext = GlueContext(SparkContext.getOrCreate())
spark = glueContext.spark_session
# date_now = datetime.now()
# preday = date_now + timedelta(days=-1)
# d1 = preday.strftime("%Y%m%d")
# print("d1 =", d1)
#
# now = datetime.now() # current date and time
# year = now.strftime("%Y%m%d")
# print("year:", year)
dyf_mapping_lo_student_history = glueContext.create_dynamic_frame.from_catalog(
database="nvn_knowledge",
table_name="mapping_lo_student_history"
)
print('Count:', dyf_mapping_lo_student_history.count())
# # Filter nhung ban ghi cua ngay hom truoc, filter nhung ban ghi co diem != 0
# dyf_mapping_lo_student_history = Filter.apply(frame=dyf_mapping_lo_student_history, f=lambda x: x['date_id'] is not None)
dyf_mapping_lo_student_history = Filter.apply(frame=dyf_mapping_lo_student_history,
f=lambda x: x['date_id'] is not None and
(x['knowledge'] != 0 or x['comprehension'] != 0 or x[
'application'] != 0 or x['analysis'] != 0 or x[
'synthesis'] != 0 or x['evaluation'] != 0))
if dyf_mapping_lo_student_history.count() > 0:
print('START JOB---------------')
df_mapping_lo_student_history = dyf_mapping_lo_student_history.toDF()
df_mapping_lo_student_history = df_mapping_lo_student_history.groupby('date_id', 'student_id',
'learning_object_id').agg(
f.sum("knowledge").alias("knowledge"),
f.sum("comprehension").alias("comprehension"), f.sum("application").alias("application"),
f.sum("analysis").alias("analysis"), f.sum("synthesis").alias("synthesis"),
f.sum("evaluation").alias("evaluation"))
df_mapping_lo_student_history.printSchema()
df_mapping_lo_student_history.show()
print('END JOB---------------')
dyf_mapping_lo_student_used = DynamicFrame.fromDF(df_mapping_lo_student_history, glueContext,
"dyf_student_lo_init")
# print('COUNT:', dyf_student_lo_init.count())
# dyf_student_lo_init.printSchema()
# dyf_student_lo_init.show()
dyf_mapping_lo_student_used = ApplyMapping.apply(frame=dyf_mapping_lo_student_used,
mappings=[("student_id", "long", "student_id", "long"),
("learning_object_id", "long", "learning_object_id",
"long"),
("date_id", "int", "date_id", "long"),
("knowledge", 'long', 'knowledge', 'long'),
("comprehension", 'long', 'comprehension', 'long'),
("application", 'long', 'application', 'long'),
("analysis", 'long', 'analysis', 'long'),
("synthesis", 'long', 'synthesis', 'long'),
("evaluation", 'long', 'evaluation', 'long')])
dyf_mapping_lo_student_used = ResolveChoice.apply(frame=dyf_mapping_lo_student_used, choice="make_cols",
transformation_ctx="resolvechoice2")
dyf_mapping_lo_student_used = DropNullFields.apply(frame=dyf_mapping_lo_student_used,
transformation_ctx="dyf_mapping_lo_student_used")
datasink5 = glueContext.write_dynamic_frame.from_jdbc_conf(frame=dyf_mapping_lo_student_used,
catalog_connection="glue_redshift",
connection_options={
"dbtable": "mapping_lo_student_used",
"database": "dts_odin",
"postactions": """ call proc_insert_tbhv();
INSERT INTO mapping_lo_student_history SELECT * FROM mapping_lo_student_used;
DROP TABLE IF EXISTS mapping_lo_student_used """
},
redshift_tmp_dir="s3n://dts-odin/temp1/dyf_student_lo_init",
transformation_ctx="datasink5")
def AggFunctions(df, cols):
org_window = Window.partitionBy(F.col("latitude"),
F.col("longitude")).orderBy(
F.col("date").asc())
windowSpec = org_window.rowsBetween(-7, 0)
for idx, col in enumerate(cols):
df = df.withColumn(col + "_mean_7_days",
F.avg(F.col(col)).over(windowSpec))
df = df.withColumn(
col + "_max_7_days",
F.max(F.col(col)).over(windowSpec.rowsBetween(-7, 0)))
df = df.withColumn(
col + "_min_7_days",
F.min(F.col(col)).over(windowSpec.rowsBetween(-7, 0)))
df = df.withColumn(
col + "_cummalitive_7_days",
F.sum(F.col(col)).over(windowSpec.rowsBetween(-7, 0)))
df = df.withColumn(
col + "_std_7_days",
F.stddev(F.col(col)).over(windowSpec.rowsBetween(-7, 0)))
windowSpec = org_window.rowsBetween(-14, 0)
for idx, col in enumerate(cols):
df = df.withColumn(
col + "_mean_14_days",
F.avg(F.col(col)).over(windowSpec.rowsBetween(-14, 0)))
df = df.withColumn(
col + "_max_14_days",
F.max(F.col(col)).over(windowSpec.rowsBetween(-14, 0)))
df = df.withColumn(
col + "_min_14_days",
F.min(F.col(col)).over(windowSpec.rowsBetween(-14, 0)))
df = df.withColumn(
col + "_cummalitive_14_days",
F.sum(F.col(col)).over(windowSpec.rowsBetween(-14, 0)))
df = df.withColumn(
col + "_std_14_days",
F.stddev(F.col(col)).over(windowSpec.rowsBetween(-14, 0)))
windowSpec = org_window.rowsBetween(-30, 0)
for idx, col in enumerate(cols):
df = df.withColumn(
col + "_mean_30_days",
F.avg(F.col(col)).over(windowSpec.rowsBetween(-30, 0)))
df = df.withColumn(
col + "_max_30_days",
F.max(F.col(col)).over(windowSpec.rowsBetween(-30, 0)))
df = df.withColumn(
col + "_min_30_days",
F.min(F.col(col)).over(windowSpec.rowsBetween(-30, 0)))
df = df.withColumn(
col + "_cummalitive_30_days",
F.sum(F.col(col)).over(windowSpec.rowsBetween(-30, 0)))
df = df.withColumn(
col + "_std_30_days",
F.stddev(F.col(col)).over(windowSpec.rowsBetween(-30, 0)))
windowSpec = org_window.rowsBetween(-60, 0)
for idx, col in enumerate(cols):
df = df.withColumn(
col + "_mean_60_days",
F.avg(F.col(col)).over(windowSpec.rowsBetween(-60, 0)))
df = df.withColumn(
col + "_max_60_days",
F.max(F.col(col)).over(windowSpec.rowsBetween(-60, 0)))
df = df.withColumn(
col + "_min_60_days",
F.min(F.col(col)).over(windowSpec.rowsBetween(-60, 0)))
df = df.withColumn(
col + "_cummalitive_60_days",
F.sum(F.col(col)).over(windowSpec.rowsBetween(-60, 0)))
df = df.withColumn(
col + "_std_60_days",
F.stddev(F.col(col)).over(windowSpec.rowsBetween(-60, 0)))
windowSpec = org_window.rowsBetween(-90, 0)
for idx, col in enumerate(cols):
df = df.withColumn(
col + "_mean_90_days",
F.avg(F.col(col)).over(windowSpec.rowsBetween(-90, 0)))
df = df.withColumn(
col + "_max_90_days",
F.max(F.col(col)).over(windowSpec.rowsBetween(-90, 0)))
df = df.withColumn(
col + "_min_90_days",
F.min(F.col(col)).over(windowSpec.rowsBetween(-90, 0)))
df = df.withColumn(
col + "_cummalitive_90_days",
F.sum(F.col(col)).over(windowSpec.rowsBetween(-90, 0)))
df = df.withColumn(
col + "_std_90_days",
F.stddev(F.col(col)).over(windowSpec.rowsBetween(-90, 0)))
windowSpec = org_window.rowsBetween(-180, 0)
for idx, col in enumerate(cols):
df = df.withColumn(
col + "_mean_6_months",
F.avg(F.col(col)).over(windowSpec.rowsBetween(-180, 0)))
df = df.withColumn(
col + "_max_6_months",
F.max(F.col(col)).over(windowSpec.rowsBetween(-180, 0)))
df = df.withColumn(
col + "_min_6_months",
F.min(F.col(col)).over(windowSpec.rowsBetween(-180, 0)))
df = df.withColumn(
col + "_cummalitive_6_months",
F.sum(F.col(col)).over(windowSpec.rowsBetween(-180, 0)))
df = df.withColumn(
col + "_std_6_months",
F.stddev(F.col(col)).over(windowSpec.rowsBetween(-180, 0)))
windowSpec = org_window.rowsBetween(-365, 0)
for idx, col in enumerate(cols):
df = df.withColumn(
col + "_mean_1_year",
F.avg(F.col(col)).over(windowSpec.rowsBetween(-365, 0)))
df = df.withColumn(
col + "_max_1_year",
F.max(F.col(col)).over(windowSpec.rowsBetween(-365, 0)))
df = df.withColumn(
col + "_min_1_year",
F.min(F.col(col)).over(windowSpec.rowsBetween(-365, 0)))
df = df.withColumn(
col + "_cummalitive_1_year",
F.sum(F.col(col)).over(windowSpec.rowsBetween(-365, 0)))
df = df.withColumn(
col + "_std_1_year",
F.stddev(F.col(col)).over(windowSpec.rowsBetween(-365, 0)))
return df
, (F.log10(t_num / words_df.df ) * words_tf.tf ).alias("tf_idf") )
# 6. cache the TFIDF data framework for further usage
tokensWithTfIdf.cache()
return tokensWithTfIdf
def search_words (query , N ,TFIDF, df):
# 1. split the query to words
query_lst = set(query.lower().split())
# 2. calculate the number of words in query
q_n = len(query_lst)
# 3. search for query words in TFIDF and aggregate base on each document
# to summerize tf_idf and calculate the frequency of query words in each document
search = TFIDF.filter(TFIDF.token.isin(query_lst)).groupby(TFIDF._id)\
.agg(F.sum("tf_idf").alias("sum_tf_idf"), F.count("tf_idf").alias('freq'))
# 4. the score is calculated by multiplying sum_tf_idf to frequency of query words in document
# and dividing it to the number of words in query
# in last step, order the results by highest scores and get N top of them as output
search = search.select((search._id).alias("id") , (search.sum_tf_idf * search.freq / q_n).alias('scores'))\
.orderBy("scores", ascending =False).limit(N)
# 5. In the end, join the search output with original data framework to fetch text_entry
# and select _id , rounded score (3 decimal) and text_entry as result of search
search = search.join(df, df._id == search.id).select(search.id, F.bround(search.scores,3), "text_entry").orderBy("scores", ascending =False)
return search.collect()
def print_result (query ,result):
def test_mixed_udfs(self):
"""
Test mixing group aggregate pandas UDF with python UDF and scalar pandas UDF.
"""
df = self.data
plus_one = self.python_plus_one
plus_two = self.pandas_scalar_plus_two
sum_udf = self.pandas_agg_sum_udf
# Mix group aggregate pandas UDF and python UDF
result1 = (df.groupby('id')
.agg(plus_one(sum_udf(df.v)))
.sort('id'))
expected1 = (df.groupby('id')
.agg(plus_one(sum(df.v)))
.sort('id'))
# Mix group aggregate pandas UDF and python UDF (order swapped)
result2 = (df.groupby('id')
.agg(sum_udf(plus_one(df.v)))
.sort('id'))
expected2 = (df.groupby('id')
.agg(sum(plus_one(df.v)))
.sort('id'))
# Mix group aggregate pandas UDF and scalar pandas UDF
result3 = (df.groupby('id')
.agg(sum_udf(plus_two(df.v)))
.sort('id'))
expected3 = (df.groupby('id')
.agg(sum(plus_two(df.v)))
.sort('id'))
# Mix group aggregate pandas UDF and scalar pandas UDF (order swapped)
result4 = (df.groupby('id')
.agg(plus_two(sum_udf(df.v)))
.sort('id'))
expected4 = (df.groupby('id')
.agg(plus_two(sum(df.v)))
.sort('id'))
# Wrap group aggregate pandas UDF with two python UDFs and use python UDF in groupby
result5 = (df.groupby(plus_one(df.id))
.agg(plus_one(sum_udf(plus_one(df.v))))
.sort('plus_one(id)'))
expected5 = (df.groupby(plus_one(df.id))
.agg(plus_one(sum(plus_one(df.v))))
.sort('plus_one(id)'))
# Wrap group aggregate pandas UDF with two scala pandas UDF and user scala pandas UDF in
# groupby
result6 = (df.groupby(plus_two(df.id))
.agg(plus_two(sum_udf(plus_two(df.v))))
.sort('plus_two(id)'))
expected6 = (df.groupby(plus_two(df.id))
.agg(plus_two(sum(plus_two(df.v))))
.sort('plus_two(id)'))
assert_frame_equal(expected1.toPandas(), result1.toPandas())
assert_frame_equal(expected2.toPandas(), result2.toPandas())
assert_frame_equal(expected3.toPandas(), result3.toPandas())
assert_frame_equal(expected4.toPandas(), result4.toPandas())
assert_frame_equal(expected5.toPandas(), result5.toPandas())
assert_frame_equal(expected6.toPandas(), result6.toPandas())
timegroup_pvs=Vectors.sparse(maxInd,[(intervalIndDict[(weekday,hour)],pageviews)])
timegroup_visit=Vectors.sparse(maxInd,[(intervalIndDict[(weekday,hour)],1.)])
return Row(browser=browser,a_user_key=a_user_key,age=age,\
day=day,hour=hour,date=date,weekday=weekday,pv=pageviews,\
pv_nh=pageview_nothome,pv_bet=pageview_betalt,referrer=referrer,\
device=device,gender=gender,days_since_registration=days_since_registration,\
reg_date=reg_date,timegroup_pvs=timegroup_pvs,timegroup_visit=timegroup_visit,\
a_virtual=a_virtual)
if __name__ == "__main__":
print(intervalIndDict)
conf=SparkConf().setAppName('konsumprofiler').setMaster("local[8]").set('spark.app.id','200')
sc=SparkContext(conf=conf)
sqlContext = SQLContext(sc)
period=2. #2 weeks of data
konsum=sc.textFile('/home/erlenda/data/konsum/amedia_mainsite_20151124-20151207_15145.tsv').map(parseEntry)
konsum_reg_user=konsum.filter(lambda x:(x.a_user_key!='NAN') and (x.a_user_key!='') )
konsum_user=sqlContext.createDataFrame(konsum_reg_user)
pprint(konsum_user.take(5))
tt=konsum_user.groupBy('a_user_key').agg(sqlfuncs.sum('timegroup_pvs'))
pprint(tt.take(5))
numPartitions = 32
rdd1 = RandomRDDs.normalVectorRDD(spark, nRow, nCol, numPartitions, seed)
seed = 3
rdd2 = RandomRDDs.normalVectorRDD(spark, nRow, nCol, numPartitions, seed)
sc = spark.sparkContext
# convert each tuple in the rdd to a row
randomNumberRdd1 = rdd1.map(
lambda x: Row(A=float(x[0]), B=float(x[1]), C=float(x[2]), D=float(x[3])))
randomNumberRdd2 = rdd2.map(
lambda x: Row(E=float(x[0]), F=float(x[1]), G=float(x[2]), H=float(x[3])))
# create dataframe from rdd
schemaRandomNumberDF1 = spark.createDataFrame(randomNumberRdd1)
schemaRandomNumberDF2 = spark.createDataFrame(randomNumberRdd2)
# cache the dataframe
#schemaRandomNumberDF.cache()
cross_df = schemaRandomNumberDF1.crossJoin(schemaRandomNumberDF2)
# cache the dataframe
cross_df.cache()
# aggregate
results = cross_df.groupBy("A").agg(func.max("B"), func.sum("C"))
results.show(n=100)
print "----------Count in cross-join--------------- {0}".format(
cross_df.count())
def count_null(col_name):
return sum(col(col_name).isNull().cast('integer')).alias(col_name)
df.show()
"""# Aggregate each <window_minutes> window to compute:
- average number of people detected
- average group size
- average velocity
"""
# seconds = window_minutes * 60
window_str = '{} minutes'.format(window_minutes)
agg_df = (
df.groupBy(
window('timestamp',
windowDuration=window_str,
slideDuration=window_str)).agg(
F.sum('num_people'), F.sum('num_groups'),
F.sum('sum_velocities'), F.sum('num_velocities'),
avg('num_people'), collect_list('x_centers'),
collect_list('y_centers')).withColumn(
'avg_group_size',
avg_udf(struct('sum(num_people)',
'sum(num_groups)'))).withColumn(
'avg_velocity',
avg_udf(
struct('sum(sum_velocities)',
'sum(num_velocities)'))).
withColumnRenamed(
'avg(num_people)',
'avg_num_people').withColumn(
'x_centers', flattenUdf('collect_list(x_centers)')).withColumn(
'y_centers', flattenUdf('collect_list(y_centers)')).drop(