Excel行范围查找与列参数匹配:筛选符合条件国家的实现方案咨询
问题背景
我有一个超过22000行的xls文件,存储了各个国家每年的数值数据,示例如下:
| country | year | Value |
|---|---|---|
| United States | 1840 | 0 |
| United States | 1841 | 4 |
| ... | ... | ... |
| United States | 2019 | 0 |
| United States | 2020 | 3 |
| Samoa | 1840 | 2 |
| Samoa | 1841 | 2 |
| ... | ... | ... |
| Samoa | 2019 | 1 |
任务要求
筛选出在指定时间段(例如1900-2020年)内,第三列Value值覆盖了全部指定分值范围(例如0-6)的国家,将符合条件的国家数据写入xls文件的独立工作表中;若国家在该时间段内仅覆盖2-3个分值则不纳入结果。
现有代码
import xlrd import xlwt book = xlrd.open_workbook('book.xls') sheet = book.sheet_by_index(0) # Create new xls file and two sheets for two criteria new_book = xlwt.Workbook() ... new_sheet_2 = new_book.add_sheet('Result - second criterion') for row in range(1, sheet.nrows): country_value = sheet.cell_value(row, 0) year_value = sheet.cell_value(row, 3) index_value = sheet.cell_value(row, 10) ... if country_value == sheet.cell_value(row + 1, 0): result2.append([row_value, year_value, lex_index]) ...
目前的疑问是,认为不需要单独用列表存储数据,应该可以直接提取符合要求的国家,但暂未找到更合适的实现方案,求优化思路。
优化思路
- 首先明确无法单遍历完成筛选:你不可能读某一个国家的第一条数据就判断它是否符合覆盖全部分值的要求,必须先统计完该国家指定时间段内的所有Value取值才能判断,所以最少需要两次遍历,完全不需要存储全量数据,仅存统计信息即可。
- 第一次遍历仅做聚合统计:用字典做存储,key为国家名,value为该国家指定年份范围内的Value集合(set类型自动去重),遍历所有行时只要年份符合要求,就把对应Value加入该国家的set中,2万行数据的统计量极小,完全没有性能压力。
- 统计完成后过滤合格国家:判断每个国家的Value集合是否包含指定分值范围的所有值,比如0-6就判断
value_set >= {0,1,2,3,4,5,6},把符合条件的国家名单独存到一个列表里。 - 第二次遍历原表直接写入:只要当前行的国家在合格列表里、年份符合要求,就直接写入新工作表,不需要额外存储全量数据,省内存逻辑也清晰。
- 注意列索引对应:现有代码里取year是第3列、value是第10列,注意和你的实际表结构对应,示例表的year是第2列(索引1)、Value是第3列(索引2),别搞错列索引导致逻辑错误。
如果可以接受用pandas实现,代码会更简洁:
import pandas as pd # 自定义参数 START_YEAR = 1900 END_YEAR = 2020 FULL_SCORE_RANGE = set(range(0,7)) # 对应分值范围0-6 # 读取原始数据 df = pd.read_excel('book.xls') # 筛选指定年份范围内的数据 df_year_filtered = df[(df['year'] >= START_YEAR) & (df['year'] <= END_YEAR)] # 按国家聚合,统计每个国家的Value唯一值集合 country_value_map = df_year_filtered.groupby('country')['Value'].agg(set).reset_index() # 筛选出覆盖全部分值的合格国家 qualified_countries = country_value_map[country_value_map['Value'].apply(lambda x: x >= FULL_SCORE_RANGE)]['country'].tolist() # 提取合格国家的所有数据 df_result = df_year_filtered[df_year_filtered['country'].isin(qualified_countries)] # 写入新文件,每个国家对应一个独立工作表 with pd.ExcelWriter('result.xls') as writer: for country in qualified_countries: df_result[df_result['country'] == country].to_excel(writer, sheet_name=country, index=False)
内容的提问来源于stack exchange,提问作者Octner
相关产品推荐
相关产品推荐

