数据集重复项剔除与指定国家筛选需求

AI 小北

2026-7-19

问题描述

我有如下数据集：

ID	Country	Count
1	Austria	3
1	Austria	3
1	Austria	3
1	Belgium	3
1	Belgium	3
1	Belgium	3
2	Croatia	5
2	Croatia	5
2	Germany	5
2	US	5
2	US	5

想要得到的结果：

ID	Country	Count
1	Austria	3
1	Belgium	3
2	Croatia	5
2	Germany	5

核心需求：

只保留Austria、Belgium、Croatia、Germany，剔除US
按ID分组，每个ID下的同Country记录只留一条

常用工具实现方案

Python Pandas

先筛选允许的国家，再按ID+Country去重：

import pandas as pd

# 构造原始数据（实际场景可替换为pd.read_csv等读取方式）
df = pd.DataFrame({
    'ID': [1,1,1,1,1,1,2,2,2,2,2],
    'Country': ['Austria','Austria','Austria','Belgium','Belgium','Belgium','Croatia','Croatia','Germany','US','US'],
    'Count': [3,3,3,3,3,3,5,5,5,5,5]
})

# 筛选指定国家
allowed_countries = {'Austria', 'Belgium', 'Croatia', 'Germany'}
df_filtered = df[df['Country'].isin(allowed_countries)]

# 按ID和Country去重，保留第一条记录
df_result = df_filtered.drop_duplicates(subset=['ID', 'Country'], keep='first')

print(df_result)

SQL

假设表名为your_table，用DISTINCT直接去重+筛选：

SELECT DISTINCT ID, Country, Count
FROM your_table
WHERE Country IN ('Austria', 'Belgium', 'Croatia', 'Germany')
ORDER BY ID, Country;

如果同一ID+Country的Count值固定，也可以用GROUP BY：

SELECT ID, Country, Count
FROM your_table
WHERE Country IN ('Austria', 'Belgium', 'Croatia', 'Germany')
GROUP BY ID, Country, Count
ORDER BY ID, Country;