英文:
How to filter rows in pandas dataframed
问题
以下是已翻译的内容：
df = pd.read_excel("LAPE_Statistical_Tables_for_England_2021.xlsx", sheet_name="1.3", skiprows=5, skipfooter=24)df = (df.dropna(how="all", axis="columns"),df.dropna(how="all", axis="rows"),# 使用 pd.query() 过滤包含全部大写字符的 "Unnamed: 1" 列的行df.dropna(subset=["Unnamed: 1"], how="any"))df = pd.concat(df)# 如果需要的话，重置索引df = df.reset_index(drop=True)df
为什么它不会删除包含 NaN 的列 1，以及为什么它不会删除列 1 包含全大写字符的行？为什么它不起作用？
英文:
Confused.
Here's a dataset.
Unnamed: 0 Unnamed: 1 Admissions Number of admissions per 100,000 population6 Unnamed: 4 Admissions.1 Number of admissions per 100,000 population6.1 Unnamed: 7 Admissions.2 Number of admissions per 100,000 population6.2 ... Unnamed: 28 Admissions.9 Number of admissions per 100,000 population6.9 Unnamed: 31 Admissions.10 Number of admissions per 100,000 population6.10 Unnamed: 34 Admissions.11 Number of admissions per 100,000 population6.11 Unnamed: 370 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN1 E92000001 ENGLAND7 976420.0 1810.0 NaN 713550.0 2810.0 NaN 262870.0 940.0 ... NaN 841760 1620 NaN 614050 2530 NaN 227710 840 NaN2 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN3 NaN Unknown 5100.0 0.0 NaN 4360.0 0.0 NaN 730.0 0.0 ... NaN 5220 0 NaN 4460 0 NaN 760 0 NaN4 NaN 1 2.0 3.0 4.0 5.0 6.0 7.0
My code to transform it is:
df = pd.read_excel("LAPE_Statistical_Tables_for_England_2021.xlsx", sheet_name="1.3", skiprows=5, skipfooter=24)df = (df.dropna(how="all", axis="columns"),df.dropna(how="all", axis="rows"),# Filter rows where "Unnamed: 1" column contains all uppercase characters using pd.query()df.dropna(subset=["Unnamed: 1"], how="any"))df = pd.concat(df)# Reset the index if neededdf = df.reset_index(drop=True)df
But why does it not remove the NaNs where column 1 is clearly contains NaN. I also want to remove the rows where column 1 is all uppercase.
Why does this not work?
答案1
得分: 1
在代码中存在一些问题。括号创建了一个元组，而不是修改原始的 DataFrame df，而且不是将一个元组 (df) 连接起来，而是连接了前面操作生成的各个 DataFrame 来解决错误。
我考虑了前两列，以下是已经更正的代码：
df = pd.read_excel("test1.xlsx")df = df.dropna(how="all", axis="columns") # 移除所有 NaN 值的列df = df.dropna(how="all", axis="rows") # 移除所有 NaN 值的行# 使用 pd.query() 过滤 "Unnamed: 1" 列中包含全部大写字符的行df = df.dropna(subset=["Unnamed: 1"], how="any")# 如果需要，重置索引df = df.reset_index(drop=True)
使用你的代码输出：
dfUnnamed: 0 Unnamed: 10 NaN1 E920000012 NaN3 NaN4 NaN0 NaN1 E920000012 NaN3 NaN4 NaN1 E92000001
应用我的修改后的输出：
Unnamed: 0 Unnamed: 11 E92000001
英文:
There are couple of issues in the code. The parentheses create a tuple instead of modifying the original DataFrame df and instead of concatenating a tuple (df), concatenate the individual DataFrames resulting from the previous operations to resolve the error.
I have considered first two columns and Here's the corrected code:
df = pd.read_excel("test1.xlsx")df = df.dropna(how="all", axis="columns") # Remove columns with all NaN valuesdf = df.dropna(how="all", axis="rows") # Remove rows with all NaN values# Filter rows where "Unnamed: 1" column contains all uppercase characters using pd.query()df = df.dropna(subset=["Unnamed: 1"], how="any")# Reset the index if neededdf = df.reset_index(drop=True)
Output using your code:
dfUnnamed: 0 Unnamed: 10 NaN1 E920000012 NaN3 NaN4 NaN0 NaN1 E920000012 NaN3 NaN4 NaN1 E92000001
Output after applying my modifications:
Unnamed: 0 Unnamed: 11 E92000001
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论