问题:将包含NaN的Pandas列转换为dtype`int`

我将数据从.csv文件读取到Pandas数据框,如下所示。对于其中一列,id我想将列类型指定为int。问题在于该id系列的值缺失/为空。

当我尝试id在读取.csv时将列转换为整数时,得到:

df= pd.read_csv("data.csv", dtype={'id': int}) 
error: Integer column has NA values

或者,我尝试在阅读以下内容后转换列类型,但是这次我得到:

df= pd.read_csv("data.csv") 
df[['id']] = df[['id']].astype(int)
error: Cannot convert NA to integer

我该如何解决?

I read data from a .csv file to a Pandas dataframe as below. For one of the columns, namely id, I want to specify the column type as int. The problem is the id series has missing/empty values.

When I try to cast the id column to integer while reading the .csv, I get:

df= pd.read_csv("data.csv", dtype={'id': int}) 
error: Integer column has NA values

Alternatively, I tried to convert the column type after reading as below, but this time I get:

df= pd.read_csv("data.csv") 
df[['id']] = df[['id']].astype(int)
error: Cannot convert NA to integer

How can I tackle this?


回答 0

整数列中缺少NaN rep是熊猫的“陷阱”

通常的解决方法是仅使用浮点数。

The lack of NaN rep in integer columns is a pandas “gotcha”.

The usual workaround is to simply use floats.


回答 1

在0.24。+版本中,pandas获得了保留具有缺失值的整数dtypes的功能。

可空整数数据类型

大熊猫可以使用来表示可能缺少值的整数数据。这是在熊猫中实现的扩展类型。它不是整数的默认dtype,因此不会被推断。您必须将dtype明确传递给Series

arr = pd.array([1, 2, np.nan], dtype=pd.Int64Dtype())
pd.Series(arr)

0      1
1      2
2    NaN
dtype: Int64

要将列转换为可为空的整数,请使用:

df['myCol'] = df['myCol'].astype('Int64')

In version 0.24.+ pandas has gained the ability to hold integer dtypes with missing values.

Nullable Integer Data Type.

Pandas can represent integer data with possibly missing values using . This is an extension types implemented within pandas. It is not the default dtype for integers, and will not be inferred; you must explicitly pass the dtype into or Series:

arr = pd.array([1, 2, np.nan], dtype=pd.Int64Dtype())
pd.Series(arr)

0      1
1      2
2    NaN
dtype: Int64

For convert column to nullable integers use:

df['myCol'] = df['myCol'].astype('Int64')

回答 2

我的用例是在装入数据库表之前先整理数据:

df[col] = df[col].fillna(-1)
df[col] = df[col].astype(int)
df[col] = df[col].astype(str)
df[col] = df[col].replace('-1', np.nan)

删除NaN,转换为int,转换为str,然后重新插入NAN。

它虽然不漂亮,但可以完成工作!

My use case is munging data prior to loading into a DB table:

df[col] = df[col].fillna(-1)
df[col] = df[col].astype(int)
df[col] = df[col].astype(str)
df[col] = df[col].replace('-1', np.nan)

Remove NaNs, convert to int, convert to str and then reinsert NANs.

It’s not pretty but it gets the job done!


回答 3

现在可以创建一个包含NaNs作为intdtype 的熊猫列,因为它现在已正式添加到熊猫0.24.0中。

pandas 0.24.x发行说明 Quote:“ Pandas已经拥有了持有缺失值的整数dtypes的能力

It is now possible to create a pandas column containing NaNs as dtype int, since it is now officially added on pandas 0.24.0

pandas 0.24.x release notes Quote: “Pandas has gained the ability to hold integer dtypes with missing values


回答 4

如果绝对要在列中组合整数和NaN,则可以使用“对象”数据类型:

df['col'] = (
    df['col'].fillna(0)
    .astype(int)
    .astype(object)
    .where(df['col'].notnull())
)

这将用整数替换NaN(无关紧要),将其转换为int,转换为对象,最后重新插入NaN。

If you absolutely want to combine integers and NaNs in a column, you can use the ‘object’ data type:

df['col'] = (
    df['col'].fillna(0)
    .astype(int)
    .astype(object)
    .where(df['col'].notnull())
)

This will replace NaNs with an integer (doesn’t matter which), convert to int, convert to object and finally reinsert NaNs.


回答 5

如果您可以修改存储的数据,请使用缺少的哨兵值id。由列名推断出的一个常见用例id是一个严格大于零的整数,您可以将其0用作前哨值,以便编写

if row['id']:
   regular_process(row)
else:
   special_process(row)

If you can modify your stored data, use a sentinel value for missing id. A common use case, inferred by the column name, being that id is an integer, strictly greater than zero, you could use 0 as a sentinel value so that you can write

if row['id']:
   regular_process(row)
else:
   special_process(row)

回答 6

您可以使用.dropna()是否可以删除带有NaN值的行。

df = df.dropna(subset=['id'])

或者,使用.fillna().astype()将NaN替换为值,并将其转换为int。

在处理带有大整数的CSV文件时,我遇到了这个问题,而其中一些缺失(NaN)。不能使用float作为类型,因为我可能会降低精度。

我的解决方案是使用str作为中间类型。然后,您可以在稍后的代码中将字符串转换为int。我将NaN替换为0,但是您可以选择任何值。

df = pd.read_csv(filename, dtype={'id':str})
df["id"] = df["id"].fillna("0").astype(int)

为了进行说明,这是一个示例,说明浮点数可能会降低精度:

s = "12345678901234567890"
f = float(s)
i = int(f)
i2 = int(s)
print (f, i, i2)

输出为:

1.2345678901234567e+19 12345678901234567168 12345678901234567890

You could use .dropna() if it is OK to drop the rows with the NaN values.

df = df.dropna(subset=['id'])

Alternatively, use .fillna() and .astype() to replace the NaN with values and convert them to int.

I ran into this problem when processing a CSV file with large integers, while some of them were missing (NaN). Using float as the type was not an option, because I might loose the precision.

My solution was to use str as the intermediate type. Then you can convert the string to int as you please later in the code. I replaced NaN with 0, but you could choose any value.

df = pd.read_csv(filename, dtype={'id':str})
df["id"] = df["id"].fillna("0").astype(int)

For the illustration, here is an example how floats may loose the precision:

s = "12345678901234567890"
f = float(s)
i = int(f)
i2 = int(s)
print (f, i, i2)

And the output is:

1.2345678901234567e+19 12345678901234567168 12345678901234567890

回答 7

这里的大多数解决方案都告诉您如何使用占位符整数表示空值。如果不确定整数是否会显示在源数据中,则该方法无济于事。我的方法将格式化不包含其十进制值的浮点数,并将空值转换为无值。结果是一个对象数据类型,当加载到CSV中时,它将看起来像一个带有空值的整数字段。

keep_df[col] = keep_df[col].apply(lambda x: None if pandas.isnull(x) else '{0:.0f}'.format(pandas.to_numeric(x)))

Most solutions here tell you how to use a placeholder integer to represent nulls. That approach isn’t helpful if you’re uncertain that integer won’t show up in your source data though. My method with will format floats without their decimal values and convert nulls to None’s. The result is an object datatype that will look like an integer field with null values when loaded into a CSV.

keep_df[col] = keep_df[col].apply(lambda x: None if pandas.isnull(x) else '{0:.0f}'.format(pandas.to_numeric(x)))

回答 8

我在使用pyspark时遇到了这个问题。由于这是在jvm上运行的代码的python前端,因此它需要类型安全,并且不能选择使用float而不是int。我通过将熊猫包装pd.read_csv在一个函数中来解决此问题,该函数将使用用户定义的填充值填充用户定义的列,然后再将其转换为所需的类型。这是我最终使用的内容:

def custom_read_csv(file_path, custom_dtype = None, fill_values = None, **kwargs):
    if custom_dtype is None:
        return pd.read_csv(file_path, **kwargs)
    else:
        assert 'dtype' not in kwargs.keys()
        df = pd.read_csv(file_path, dtype = {}, **kwargs)
        for col, typ in custom_dtype.items():
            if fill_values is None or col not in fill_values.keys():
                fill_val = -1
            else:
                fill_val = fill_values[col]
            df[col] = df[col].fillna(fill_val).astype(typ)
    return df

I ran into this issue working with pyspark. As this is a python frontend for code running on a jvm, it requires type safety and using float instead of int is not an option. I worked around the issue by wrapping the pandas pd.read_csv in a function that will fill user-defined columns with user-defined fill values before casting them to the required type. Here is what I ended up using:

def custom_read_csv(file_path, custom_dtype = None, fill_values = None, **kwargs):
    if custom_dtype is None:
        return pd.read_csv(file_path, **kwargs)
    else:
        assert 'dtype' not in kwargs.keys()
        df = pd.read_csv(file_path, dtype = {}, **kwargs)
        for col, typ in custom_dtype.items():
            if fill_values is None or col not in fill_values.keys():
                fill_val = -1
            else:
                fill_val = fill_values[col]
            df[col] = df[col].fillna(fill_val).astype(typ)
    return df

回答 9

首先删除包含NaN的行。然后对剩余的行进行整数转换。最后,再次插入删除的行。希望它能工作

First remove the rows which contain NaN. Then do Integer conversion on remaining rows. At Last insert the removed rows again. Hope it will work


回答 10

import pandas as pd

df= pd.read_csv("data.csv")
df['id'] = pd.to_numeric(df['id'])
import pandas as pd

df= pd.read_csv("data.csv")
df['id'] = pd.to_numeric(df['id'])

回答 11

假设您的DateColumn格式为3312018.0的字符串应转换为03/31/2018。并且,某些记录丢失或为0。

df['DateColumn'] = df['DateColumn'].astype(int)
df['DateColumn'] = df['DateColumn'].astype(str)
df['DateColumn'] = df['DateColumn'].apply(lambda x: x.zfill(8))
df.loc[df['DateColumn'] == '00000000','DateColumn'] = '01011980'
df['DateColumn'] = pd.to_datetime(df['DateColumn'], format="%m%d%Y")
df['DateColumn'] = df['DateColumn'].apply(lambda x: x.strftime('%m/%d/%Y'))

Assuming your DateColumn formatted 3312018.0 should be converted to 03/31/2018 as a string. And, some records are missing or 0.

df['DateColumn'] = df['DateColumn'].astype(int)
df['DateColumn'] = df['DateColumn'].astype(str)
df['DateColumn'] = df['DateColumn'].apply(lambda x: x.zfill(8))
df.loc[df['DateColumn'] == '00000000','DateColumn'] = '01011980'
df['DateColumn'] = pd.to_datetime(df['DateColumn'], format="%m%d%Y")
df['DateColumn'] = df['DateColumn'].apply(lambda x: x.strftime('%m/%d/%Y'))

声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。