如何将JSON字符串中的值“null”替换为“NaN”或“NaT”？

Question

import pandas as pd
inp= [{'c1null':10,'cols':{'c2':20,'c3time':null}, 'c4':'41'}, {'c1null':11,'cols':{'c2':null,'c3time':'2014-05-24 19:20'},'c4':'42'}, {'c1null':12,'cols':{'c2':20,'c3time':'2016-06-24 19:20'},'c4':'43'}]
df=pd.io.json.json_normalize(inp)
print(df)

在inpJSON字符串中存在值null，则上述脚本无法成功地对JSON进行规范化，得到如下预期结果：

^{pr2}$

现在，我使用pd.read_sql来获取数据帧，需要将值null替换为NaN或{}，当键名为*time时，我们可以使用pd.io.json.json_normalize。在

如何将dataframe JSON字符串列中的值null替换为NaN或{}？在

Answer 1

尝试添加

from numpy import nan as null
inp= [{'c1':10,'cols':{'c2':20,'c3time':null}, 'c4':'41'}, {'c1':11,'cols':{'c2':null,'c3time':'2014-05-24 19:20'},'c4':'42'}, {'c1':12,'cols':{'c2':20,'c3time':'2016-06-24 19:20'},'c4':'43'}]
df=pd.io.json.json_normalize(inp)
df
Out[494]: 
   c1  c4  cols.c2       cols.c3time
0  10  41     20.0               NaN
1  11  42      NaN  2014-05-24 19:20
2  12  43     20.0  2016-06-24 19:20

---

^{pr2}$