代码之家 › 专栏 › 技术社区 › Hassan Jalil

根据条件为列赋值

condition pyspark dataframe apache-spark pandas

Hassan Jalil · 技术社区 · 8 年前

我正在使用pyspark解析大量数据。我有一个dataframe,它有以下列

ip_address device_id location device_type

我想创建一个名为 id 并指定相同的 身份证件 满足下列条件之一的列

和 ip地址

2) 他们有相同的 设备id 位置 和 设备类型

3) 他们有相同的 ip地址 位置 设备类型

基本上,我想根据上述条件找到代表同一设备的所有行,并为它们提供相同的id

假设我有以下几列

因为每行满足三个条件中的一个。

第1行和第2行满足条件1

所以输出应该是

+--------+-----------+------------+-----------+-------------+----+ | number | device_id | ip_address | location | device_type | id | +--------+-----------+------------+-----------+-------------+----+ | 1 | device1 | ip1 | location1 | type1 | 1 | | 2 | device1 | ip1 | location1 | type1 | 1 | | 3 | device1 | ip2 | location1 | type1 | 1 | | 4 | device2 | ip1 | location1 | type1 | 1 | | 5 | device3 | ip3 | location2 | type2 | 2 | +--------+-----------+------------+-----------+-------------+----+

这有可能实现吗?如果是,我该怎么办?

1 回复 | 直到 8 年前

gaw 8 年前

你能做到的。不确定这是否是理想的方式,但它是有效的:

df = spark.createDataFrame([
("1" ,   "device1"   ,   "ip1"        ,  "location1" ,   "type1"),
("2" ,   "device1"   ,   "ip1"        ,  "location1" ,   "type1"),
("3" ,   "device1"   ,   "ip2"        ,  "location1" ,   "type1"),
("4" ,   "device2"   ,   "ip1"        ,  "location1" ,   "type1"),
("5" ,   "device3"   ,   "ip3"        ,  "location2" ,   "type2")
], ("ip_address", "device_id", "location", "device_type"))

df1 = df.groupBy("device_id","ip_address").agg(min(col("number"))).select(col("device_id").alias("d_id"), col("ip_address").alias("ip"), col("min(number)").alias("id1"))
df2 = df.groupBy("device_id","location","device_type").agg(min(col("number"))).select(col("device_id").alias("d_id"), col("location").alias("l"), col("device_type").alias("d_type"), col("min(number)").alias("id2"))
df3 = df.groupBy("ip_address","location","device_type").agg(min(col("number"))).select(col("ip_address").alias("ip"), col("location").alias("l"), col("device_type").alias("d_type"), col("min(number)").alias("id3"))

df.join(df1, [(df1.d_id == df.device_id) & (df1.ip == df.ip_address)], how="inner").select("number","device_id","ip_address","location","device_type","id1").join(df2, [(df2.d_id == df.device_id) & (df2.l == df.location) & (df2.d_type == df.device_type)], how="inner").select("number","device_id","ip_address","location","device_type","id1","id2").join(df3, [(df3.ip == df.ip_address) & (df3.l == df.location) & (df3.d_type == df.device_type)], how="inner").select("number","device_id","ip_address","location","device_type","id1","id2","id3").withColumn("id",least(col("id1"),col("id2"),col("id3"))).show()

id 列,看起来像:

+------+---------+----------+---------+-----------+---+---+---+---+  
|number|device_id|ip_address| location|device_type|id1|id2|id3| id| 
+------+---------+----------+---------+-----------+---+---+---+---+ 
| 5    | device3 | ip3      |location2| type2     | 5 | 5 | 5 | 5 | 
| 3    | device1 | ip2      |location1| type1     | 3 | 1 | 3 | 1 | 
| 4    | device2 | ip1      |location1| type1     | 4 | 4 | 1 | 1 | 
| 1    | device1 | ip1      |location1| type1     | 1 | 1 | 1 | 1 | 
| 2    | device1 | ip1      |location1| type1     | 1 | 1 | 1 | 1 | 
+------+---------+----------+---------+-----------+---+---+---+---+