用 None 替换 Pandas 或 Numpy Nan 以与 MysqlDB 一起使用 [英] Replacing Pandas or Numpy Nan with a None to use with MysqlDB

问题描述

我正在尝试使用 MysqlDB 将 Pandas 数据框(或可以使用 numpy 数组)写入 mysql 数据库.MysqlDB 似乎不理解nan"，我的数据库抛出一个错误，指出 nan 不在字段列表中.我需要找到一种方法将nan"转换为 NoneType.

I am trying to write a Pandas dataframe (or can use a numpy array) to a mysql database using MysqlDB . MysqlDB doesn't seem understand 'nan' and my database throws out an error saying nan is not in the field list. I need to find a way to convert the 'nan' into a NoneType.

有什么想法吗?

推荐答案

@bogatron 说得对，你可以使用 where，值得注意的是，您可以在 Pandas 中本地执行此操作:

@bogatron has it right, you can use where, it's worth noting that you can do this natively in pandas:

df1 = df.where(pd.notnull(df), None)

注意:这会将所有列的数据类型更改为object.

Note: this changes the dtype of all columns to object.

示例:

In [1]: df = pd.DataFrame([1, np.nan])

In [2]: df
Out[2]: 
    0
0   1
1 NaN

In [3]: df1 = df.where(pd.notnull(df), None)

In [4]: df1
Out[4]: 
      0
0     1
1  None

<小时>

注意:您不能使用 astype，然后是 DataFrame fillna 方法:

---

Note: what you cannot do recast the DataFrames dtype to allow all datatypes types, using astype, and then the DataFrame fillna method:

df1 = df.astype(object).replace(np.nan, 'None')

不幸的是既没有这个，也没有使用 replace，适用于 None 见 这个(已关闭)问题.

Unfortunately neither this, nor using replace, works with None see this (closed) issue.

顺便说一句，值得注意的是，对于大多数用例，您不需要将 NaN 替换为 None，请参阅关于 的这个问题pandas 中 NaN 和 None 的区别.

As an aside, it's worth noting that for most use cases you don't need to replace NaN with None, see this question about the difference between NaN and None in pandas.

但是，在这种特定情况下，您似乎这样做了(至少在此回答时).

However, in this specific case it seems you do (at least at the time of this answer).

这篇关于用 None 替换 Pandas 或 Numpy Nan 以与 MysqlDB 一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！