python dataframe合并行后保留最新日期_合并spark datafram中的日期范围

我们可以使用Window和lag函数来计算连续的组，然后以与您共享的Pandas函数类似的方式聚合这些组。下面给出了一个工作实例，希望对您有所帮助！在import pandas as pd

from dateutil.parser import parse

from pyspark.sql.window import Window

import pyspark.sql.functions as F

# EXAMPLE DATA -

pdf = pd.DataFrame.from_items([('FruitID', [1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4]),

('FruitType', ['Apple', 'Apple', 'Apple', 'Orange', 'Orange', 'Orange', 'Banana', 'Banana', 'Blueberry', 'Mango', 'Kiwi', 'Mango']),

('StartDate', [parse(x) for x in ['2015-01-01', '2016-01-01', '2017-01-01', '2015-01-01', '2016-05-31',

'2017-01-01', '2015-01-01', '2016-01-01', '2017-01-01', '2015-01-01', '2016-09-15', '2017-01-01']]),

('EndDate', [parse(x) for x in ['2016-01-01', '2017-01-01', '2018-01-01', '2016-01-01', '2017-01-01',

'2018-01-01', '2016-01-01', '2017-01-01', '2018-01-01', '2016-01-01', '2017-01-01', '2018-01-01']])

])

pdf.sort_values(['FruitID', 'StartDate'])

df = sqlContext.createDataFrame(pdf)

# FIND CONTIGUOUS GROUPS AND AGGREGATE -

w = Window.partitionBy("FruitType").orderBy("StartDate")

contiguous = F.when(F.datediff(F.lag("EndDate", 1).over(w),F.col("StartDate"))!=0,F.lit(1)).otherwise(F.lit(0))

df = (df

.withColumn('contiguous_grp', F.sum(contiguous).over(w))

.groupBy('FruitType','contiguous_grp')

.agg(F.first('StartDate').alias('StartDate'),F.last('EndDate').alias('EndDate'))

.drop('contiguous_grp'))

df.show()

输出：

^{pr2}$