从 Pandas DataFrame 中删除包含空单元格的行 [英] Drop rows containing empty cells from a pandas DataFrame

问题描述

我有一个 pd.DataFrame，它是通过解析一些 excel 电子表格创建的.其中一列有空单元格.例如，下面是该列频率的输出，32320 条记录缺少 Tenant 的值.

<预><代码>>>>value_counts(租户，规范化=假)32320雷霆8170大数据 其他 5700云巡洋舰 5700伙伴百科 5700康卡斯特 5700SDP 5700集市 5700数据类型:int64

我试图删除缺少租户的行，但是 .isnull() 选项无法识别缺失值.

<预><代码>>>>df['租户'].isnull().sum()0

该列的数据类型为Object".在这种情况下发生了什么?如何删除租户丢失的记录?

解决方案

如果一个值是 np.nan 对象，Pandas 会将其识别为 null，打印为 NaN在数据框中.您的缺失值可能是空字符串，Pandas 无法将其识别为 null.要解决此问题，您可以使用 replace() 将空字符串(或空单元格中的任何内容)转换为 np.nan 对象，然后调用 dropna() 在您的 DataFrame 上删除具有空租户的行.

为了演示，我们在 Tenants 列中创建了一个带有一些随机值和一些空字符串的 DataFrame:

<预><代码>>>>将熊猫导入为 pd>>>将 numpy 导入为 np>>>>>>df = pd.DataFrame(np.random.randn(10, 2), columns=list('AB'))>>>df['租户'] = np.random.choice(['Babar', 'Rataxes', ''], 10)>>>打印文件A B 租户0 -0.588412 -1.179306 巴巴1 -0.008562 0.7252392 0.282146 0.421721 税3 0.627611 -0.661126 巴巴4 0.805304 -0.8342145 -0.514568 1.890647 巴巴尔6 -1.188436 0.294792 税7 1.471766 -0.267807 巴巴尔8 -1.730745 1.358165 税9 0.066946 0.375640

现在我们用 np.nan 对象替换 Tenants 列中的任何空字符串，如下所示:

<预><代码>>>>df['租户'].replace('', np.nan, inplace=True)>>>打印文件A B 租户0 -0.588412 -1.179306 巴巴1 -0.008562 0.725239 NaN2 0.282146 0.421721 税3 0.627611 -0.661126 巴巴4 0.805304 -0.834214 NaN5 -0.514568 1.890647 巴巴尔6 -1.188436 0.294792 税7 1.471766 -0.267807 巴巴尔8 -1.730745 1.358165 税9 0.066946 0.375640 南

现在我们可以删除空值:

<预><代码>>>>df.dropna(subset=['租户'],就地=真)>>>打印文件A B 租户0 -0.588412 -1.179306 巴巴2 0.282146 0.421721 税3 0.627611 -0.661126 巴巴5 -0.514568 1.890647 巴巴尔6 -1.188436 0.294792 税7 1.471766 -0.267807 巴巴尔8 -1.730745 1.358165 税

I have a pd.DataFrame that was created by parsing some excel spreadsheets. A column of which has empty cells. For example, below is the output for the frequency of that column, 32320 records have missing values for Tenant.

>>> value_counts(Tenant, normalize=False)
                              32320
    Thunderhead                8170
    Big Data Others            5700
    Cloud Cruiser              5700
    Partnerpedia               5700
    Comcast                    5700
    SDP                        5700
    Agora                      5700
    dtype: int64

I am trying to drop rows where Tenant is missing, however .isnull() option does not recognize the missing values.

>>> df['Tenant'].isnull().sum()
    0

The column has data type "Object". What is happening in this case? How can I drop records where Tenant is missing?

解决方案

Pandas will recognise a value as null if it is a np.nan object, which will print as NaN in the DataFrame. Your missing values are probably empty strings, which Pandas doesn't recognise as null. To fix this, you can convert the empty stings (or whatever is in your empty cells) to np.nan objects using replace(), and then call dropna()on your DataFrame to delete rows with null tenants.

To demonstrate, we create a DataFrame with some random values and some empty strings in a Tenants column:

>>> import pandas as pd
>>> import numpy as np
>>> 
>>> df = pd.DataFrame(np.random.randn(10, 2), columns=list('AB'))
>>> df['Tenant'] = np.random.choice(['Babar', 'Rataxes', ''], 10)
>>> print df

          A         B   Tenant
0 -0.588412 -1.179306    Babar
1 -0.008562  0.725239         
2  0.282146  0.421721  Rataxes
3  0.627611 -0.661126    Babar
4  0.805304 -0.834214         
5 -0.514568  1.890647    Babar
6 -1.188436  0.294792  Rataxes
7  1.471766 -0.267807    Babar
8 -1.730745  1.358165  Rataxes
9  0.066946  0.375640         

Now we replace any empty strings in the Tenants column with np.nan objects, like so:

>>> df['Tenant'].replace('', np.nan, inplace=True)
>>> print df

          A         B   Tenant
0 -0.588412 -1.179306    Babar
1 -0.008562  0.725239      NaN
2  0.282146  0.421721  Rataxes
3  0.627611 -0.661126    Babar
4  0.805304 -0.834214      NaN
5 -0.514568  1.890647    Babar
6 -1.188436  0.294792  Rataxes
7  1.471766 -0.267807    Babar
8 -1.730745  1.358165  Rataxes
9  0.066946  0.375640      NaN

Now we can drop the null values:

>>> df.dropna(subset=['Tenant'], inplace=True)
>>> print df

          A         B   Tenant
0 -0.588412 -1.179306    Babar
2  0.282146  0.421721  Rataxes
3  0.627611 -0.661126    Babar
5 -0.514568  1.890647    Babar
6 -1.188436  0.294792  Rataxes
7  1.471766 -0.267807    Babar
8 -1.730745  1.358165  Rataxes

这篇关于从 Pandas DataFrame 中删除包含空单元格的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！