时间戳

首页 > 解决方案 > Python按2列分组，但获取记录因不同列而异

python - Python按2列分组，但获取记录因不同列而异

问题描述

我有一个数据框，它有 3 列，ZIP_CODE、TERR_NAME、STATE。对于给定的 ZIP_CODE 和 TERR_NAME，只能有一个州代码。可能存在重复记录，但不是具有相同 ZIP_CODE/TERR_NAME 和 2 个不同状态的记录？请问如何获取错误记录。

我尝试按 ZIP_CODE/TERR_NAME/STATE 进行分组，但对获取这些错误记录一无所知。

df1= sqlContext.createDataFrame([("81A01","TERR NAME 01","NJ"),("81A01","TERR NAME 01","CA"),("81A02","TERR NAME 02","NY"),("81A03","TERR NAME 03","NY"), ("81A03","TERR NAME 03","CA"), ("81A04","TERR NAME 04","FL"), ("81A05","TERR NAME 05","NJ"), ("81A06","TERR NAME 06","CA"), ("81A06","TERR NAME 06","CA")], ["zip_code","territory_name","state"])

df1.createOrReplaceTempView("df1_temp")
+--------+--------------+-----+ 
|zip_code|territory_name|state| 
+--------+--------------+-----+ 
| 81A01| TERR NAME 01| NJ| 
| 81A01| TERR NAME 01| CA| 
| 81A02| TERR NAME 02| NY| 
| 81A03| TERR NAME 03| NY| 
| 81A03| TERR NAME 03| CA| 
| 81A04| TERR NAME 04| FL| 
| 81A05| TERR NAME 05| NJ| 
| 81A06| TERR NAME 06| CA| 
| 81A06| TERR NAME 06| CA|
+--------+--------------+-----+

我需要一个没有这些代码的数据框，即 81A01、81A03，它们具有相同的 zip_code、地区名称，但使用 spark.sql() 具有不同的州代码。

预期的新DF：

+--------+--------------+-----+ 
|zip_code|territory_name|state| 
+--------+--------------+-----+ 
| 81A02| TERR NAME 02| NY| 
| 81A04| TERR NAME 04| FL| 
| 81A05| TERR NAME 05| NJ| 
| 81A06| TERR NAME 06| CA| 
| 81A06| TERR NAME 06| CA|
+--------+--------------+-----+

排除的邮政编码：

+--------+--------------+-----+ 
|zip_code|territory_name|state| 
+--------+--------------+-----+ 
| 81A01| TERR NAME 01| NJ| 
| 81A01| TERR NAME 01| CA| 
| 81A03| TERR NAME 03| NY| 
| 81A03| TERR NAME 03| CA| 
+--------+--------------+-----+

提前致谢。

标签： pythonpython-2.7apache-spark-sqlpyspark-sql

解决方案

import pandas as pd 
data = {
    "zip_code":["81A01", "81A01", "81A02", "81A03", "81A03", "81A04", "81A05", 
    "81A06", "81A06"],
    "territory_name": ["TERR NAME 01", "TERR NAME 01", "TERR NAME 02", 
    "TERR NAME 03", "TERR NAME 03", "TERR NAME 04", "TERR NAME 05", 
    "TERR NAME 06", "TERR NAME 06"], 
    "state": ["NJ", "CA", "NY", "NY", "CA", "FL", "NJ", "CA", "CA"]  
}
df = pd.DataFrame(data)


duplicate = list(set([tuple(df[(df["zip_code"] == df["zip_code"][i]) & 
           (df["territory_name"] == df["territory_name"][i])].index) for i in 
           range(len(df))]))


for i in duplicate:
    if len(i) > 1:
        if not df["state"][i[0]] == df["state"][i[1]]:
            df = df.drop(i[0])
            df = df.drop(i[1])
print(df)

推荐阅读