在spark python中使用两列作为键

Question

我的csv文件中有4列和许多行。你知道吗

Date(MM/DD/YY)  Arr_Dep     Dom_Int             Num_Fl
01/01/15 0:00   Arrival     Domestic            357
03/01/15 0:00   Arrival     International       269
06/01/15 0:00   Departure   Domestic            82
08/01/15 0:00   Departure   International        5
05/01/16 0:00   Arrival     Domestic            44
06/01/16 0:00   Arrival     Domestic            57
07/01/16 0:00   Departure   International       51
08/01/16 0:00   Departure   International       40
08/01/17 0:00   Arrival     Domestic            1996
10/01/17 0:00   Departure   International       21

我必须根据航班是到达还是起飞来计算某一年每月的平均航班数。所以我期望上面输入的输出是：

2015, arrival, 313
2015, departure, 44
2016, arrival, 51
2016, departure, 46
2017, arrival, 1996
2017, departure, 21

我面临的问题是，我应该如何在我的键中包含两列，即Arr\u Dep和Date列，以便最终将其减少到平均值。 到目前为止，我已经写了以下脚本。不知道如何进行

from pyspark import SparkContext
from operator import add
import sys

sc = SparkContext(appName="example")
input_file = sys.argv[1]
lines = sc.textFile(input_file)
first = lines.map(lambda x : ((x.split(",")[0].split(" ")[0][5:]).encode('ascii','ignore'), int(x.split(",")[-1]), x.split(",")[1]))
second = first.filter(lambda x : "Arrival" in x[1] or "Departure" in x[1])
third = second.map(lambda x : (x[0],x[1]))
result = third.reduceByKey("Not sure how to calculate average")
output = result.collect()
for v in sorted(output, key = lambda x:x[0]):
    print '%s, %s' % (v[0], v[1])

我不太清楚上面的剧本。我对spark和python还不熟悉。你知道怎么继续吗？你知道吗

Answer 1

最好使用SQLAPI：

from pyspark.sql.functions import *

df = spark.read.options(inferSchema=True, header=True).csv(input_file)
df\
  .groupBy(year(to_date("Date(MM/DD/YY)", "MM/dd/yyH:mm")).alias("year"), "Arr_Dep")\
  .avg("Num_Fl")