自加入带有时间戳的pyspark数据帧 [英] self join in pyspark dataframe with timestamp

问题描述

我有一个火花数据框如下

I have a spark dataframe as follows

+--+--------+-----------+
|id| account|       time|     
+--+--------+-----------+
| 4|      aa| 01/01/2017|    
| 2|      bb| 03/01/2017|    
| 6|      cc| 04/01/2017|    
| 1|      bb| 05/01/2017|      
| 5|      bb| 09/01/2017|    
| 3|      aa| 02/01/2017|
+--+--------+-----------+

我想得到如下数据

+---+---+-------+
|id1|id2|account|
+---+---+-------+
|  4|  3|     aa|
|  2|  5|     bb|
|  1|  5|     bb|
|  2|  1|     bb|
+---+---+-------+

所以我需要在帐户中找到任何可能的对，id1 将是较早时间的 id，而 id2 将是较晚时间的 id.我对 pyspark 很陌生，我认为 self join 可能是一个好的开始.
任何人都可以帮助我吗?

so I need find any possible pair within an account, and id1 would be the id with the earlier time and id2 would be the id with later time. I'm very new to pyspark, I think self join maybe a good start.
Anyone can help me with it?

推荐答案

IIUC，您可以使用自连接来实现:

IIUC, you can achieve this using a self join:

import pyspark.sql.functions as f
df.alias('l').join(df.alias('r'), on='account')\
    .where('r.time > l.time')\
    .select(f.col('l.id').alias('id1'), f.col('r.id').alias('id2'), 'l.account')\
    .show()
#+---+---+-------+
#|id1|id2|account|
#+---+---+-------+
#|  1|  5|     bb|
#|  2|  1|     bb|
#|  2|  5|     bb|
#|  4|  3|     aa|
#+---+---+-------+

• 在 account 上将 DataFrame (df) 加入自身.(我们分别将左右 DataFrame 别名为 'l' 和 'r'.)
• 下一个过滤器使用 where 只保留 r.time > 的行.l.time.
• 剩下的将是相同account的id对，其中l.id出现在r.id.
• Join the DataFrame (df) to itself on the account. (We alias the left and right DataFrames as 'l' and 'r' respectively.)
• Next filter using where to keep only the rows where r.time > l.time.
• Everything left will be pairs of ids for the same account where l.id occurs before r.id.

这篇关于自加入带有时间戳的pyspark数据帧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！