用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正确，您可以使用

@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

---

注意:您不能执行的操作使用

---

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，请参阅有关的问题熊猫中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屋！