Pandas to_json() 中文乱码,转化为json数组

问题出现与解决

Pandas进行数据处理之后,假如想将其转化为json,会出现一个bug,就是中文文字是以乱码存储的,也就是\uXXXXXX的形式,首先要解决的就是中文乱码的问题：翻了翻官网文档,查了源码的参数,(多谢网友提醒)需要设置js001 = df1.to_json(force_ascii=False),即可显示中文编码

然后需要解决的是df转json数组，这个必须得自己写。

DataFrame 转化为json数组

首先用demojson的类库尝试了一下,不行,依旧编码问题（当时还没查到参数force_ascii，于是决定使用原生代码完全自己写，这样就可以在数据类型转换的同时，顺便解决乱码的问题）.之后考虑python 原生的 json 应该有编码转换功能,查了查官网文档,确实可以,不过要比平时多加一个参数

listXY_json = json.dumps(listXY, sort_keys=True, indent=4, ensure_ascii=False)
#ensure_ascii：默认值True，如果数据中含有非ASCII的字符，则会类似\uXXXX的显示数据，设置成False后，就能正常显示

    
    

    
    
1
2

既然找到解决乱码的方法,那么想要将pandas中的数据类型存储到json中就只需要先将其转换为python自带的数据类型,再利用 json 类库其转换为json格式并存储就可以了,因为我自己是为了将python处理好的数据转换为json数组,然后利用echarts生成表格,所以我将其封装为了一个类,源码在最后

存储的的json前后对比

df1 = pd.DataFrame(pd.read_excel(u'excel/袁隆平院士报告处理后.xlsx'))
js001 = df1.to_json(force_ascii=False)
with open('json/testjson.json', 'w') as f:
  f.write(js001)

# “16”:6,“17”:6,“18”:3,“19”:4},“content”:{“0”:“研究调改王玉田院士项目入驻协议，完成签约仪式方案”,“1”:"\u5b

#处理后
df1 = pd.DataFrame(pd.read_excel(u’excel/袁隆平院士报告处理后.xlsx’))
dfts = DataFrameToJSONArray(df1, ‘json/wyt_xyz.json’) # 参数(df数据,文件存储路径)
dfts.funChangeDataFrameType() # 自动转换DataFrame的列数据类型
dfts.funSaveJSONArrayFile() # 存储JSON格式文件

# [[“2016-08-08”, “袁隆平院士观摩指导”], [“2016-08-09”,“修改完善袁隆平院士项目合作协议”],[“2016-08-10”,“完成袁隆平院士合作协议”],[“2016-08-31”,“袁隆平院士项目入驻院士港协议审核完毕”],…]

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

DataFrameToJSONArray()源码

# - * - coding: utf - 8 -*-
#
# 作者：田丰(FontTian)
# 创建时间:'2017/7/16'
# 邮箱：fonttian@Gmaill.com
# CSDN：http://blog.csdn.net/fontthrone
import sys
import pandas as pd
import json
import re

reload(sys)
sys.setdefaultencoding(‘utf-8’)

class DataFrameToJSONArray():
def init(self, dataframe, filepath=‘DataFrameToJSONArrayFile.json’):
self.__DataFrame = dataframe
self.__FilePath = filepath

<span class="token keyword">def</span> <span class="token function">funChangeDataFrameType</span><span class="token punctuation">(</span>self<span class="token punctuation">)</span><span class="token punctuation">:</span>
    <span class="token keyword">for</span> i <span class="token keyword">in</span> <span class="token builtin">range</span><span class="token punctuation">(</span><span class="token builtin">len</span><span class="token punctuation">(</span>self<span class="token punctuation">.</span>__DataFrame<span class="token punctuation">.</span>columns<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
        s <span class="token operator">=</span> re<span class="token punctuation">.</span>sub<span class="token punctuation">(</span><span class="token string">r'\'&gt;'</span><span class="token punctuation">,</span> <span class="token string">''</span><span class="token punctuation">,</span> re<span class="token punctuation">.</span>sub<span class="token punctuation">(</span><span class="token string">r'\d'</span><span class="token punctuation">,</span> <span class="token string">''</span><span class="token punctuation">,</span> <span class="token builtin">str</span><span class="token punctuation">(</span><span class="token builtin">type</span><span class="token punctuation">(</span>self<span class="token punctuation">.</span>__DataFrame<span class="token punctuation">.</span>iloc<span class="token punctuation">[</span><span class="token punctuation">:</span><span class="token punctuation">,</span> i<span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">.</span>replace<span class="token punctuation">(</span><span class="token string">'\''</span><span class="token punctuation">,</span> <span class="token string">' '</span><span class="token punctuation">)</span><span class="token punctuation">.</span>replace<span class="token punctuation">(</span><span class="token string">'.'</span><span class="token punctuation">,</span>
                                                                                                           <span class="token string">' '</span><span class="token punctuation">)</span><span class="token punctuation">.</span>split<span class="token punctuation">(</span>
            <span class="token string">' '</span><span class="token punctuation">)</span><span class="token punctuation">[</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">]</span>
        <span class="token keyword">if</span> s <span class="token operator">==</span> <span class="token string">'Timestamp'</span><span class="token punctuation">:</span>
            self<span class="token punctuation">.</span>__DataFrame<span class="token punctuation">.</span>iloc<span class="token punctuation">[</span><span class="token punctuation">:</span><span class="token punctuation">,</span> i<span class="token punctuation">]</span> <span class="token operator">=</span> self<span class="token punctuation">.</span>__DataFrame<span class="token punctuation">.</span>iloc<span class="token punctuation">[</span><span class="token punctuation">:</span><span class="token punctuation">,</span> i<span class="token punctuation">]</span><span class="token punctuation">.</span>astype<span class="token punctuation">(</span><span class="token builtin">unicode</span><span class="token punctuation">)</span>
        <span class="token keyword">else</span><span class="token punctuation">:</span>
            self<span class="token punctuation">.</span>__DataFrame<span class="token punctuation">.</span>iloc<span class="token punctuation">[</span><span class="token punctuation">:</span><span class="token punctuation">,</span> i<span class="token punctuation">]</span> <span class="token operator">=</span> self<span class="token punctuation">.</span>__DataFrame<span class="token punctuation">.</span>iloc<span class="token punctuation">[</span><span class="token punctuation">:</span><span class="token punctuation">,</span> i<span class="token punctuation">]</span><span class="token punctuation">.</span>astype<span class="token punctuation">(</span>s<span class="token punctuation">)</span>
    <span class="token keyword">return</span> self<span class="token punctuation">.</span>__DataFrame

<span class="token keyword">def</span> <span class="token function">funSaveJSONArrayFile</span><span class="token punctuation">(</span>self<span class="token punctuation">)</span><span class="token punctuation">:</span>
    list001 <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span>
    <span class="token keyword">for</span> i <span class="token keyword">in</span> <span class="token builtin">range</span><span class="token punctuation">(</span><span class="token builtin">len</span><span class="token punctuation">(</span>self<span class="token punctuation">.</span>__DataFrame<span class="token punctuation">.</span>columns<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
        list001<span class="token punctuation">.</span>append<span class="token punctuation">(</span><span class="token builtin">list</span><span class="token punctuation">(</span>self<span class="token punctuation">.</span>__DataFrame<span class="token punctuation">.</span>iloc<span class="token punctuation">[</span><span class="token punctuation">:</span><span class="token punctuation">,</span> i<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span>

    list002 <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span>
    list003 <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span>

    <span class="token keyword">for</span> i <span class="token keyword">in</span> <span class="token builtin">range</span><span class="token punctuation">(</span><span class="token builtin">len</span><span class="token punctuation">(</span>list001<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
        <span class="token keyword">for</span> j <span class="token keyword">in</span> <span class="token builtin">range</span><span class="token punctuation">(</span><span class="token builtin">len</span><span class="token punctuation">(</span>self<span class="token punctuation">.</span>__DataFrame<span class="token punctuation">.</span>columns<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
            list003<span class="token punctuation">.</span>append<span class="token punctuation">(</span>list001<span class="token punctuation">[</span>j<span class="token punctuation">]</span><span class="token punctuation">[</span>i<span class="token punctuation">]</span><span class="token punctuation">)</span>
        list002<span class="token punctuation">.</span>append<span class="token punctuation">(</span>list003<span class="token punctuation">)</span>
        list003 <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span>

    Final_JSON <span class="token operator">=</span> json<span class="token punctuation">.</span>dumps<span class="token punctuation">(</span>list002<span class="token punctuation">,</span> sort_keys<span class="token operator">=</span><span class="token boolean">True</span><span class="token punctuation">,</span> indent<span class="token operator">=</span><span class="token number">4</span><span class="token punctuation">,</span> ensure_ascii<span class="token operator">=</span><span class="token boolean">False</span><span class="token punctuation">)</span>
    <span class="token keyword">with</span> <span class="token builtin">open</span><span class="token punctuation">(</span>self<span class="token punctuation">.</span>__FilePath<span class="token punctuation">,</span> <span class="token string">'w'</span><span class="token punctuation">)</span> <span class="token keyword">as</span> f<span class="token punctuation">:</span>
        f<span class="token punctuation">.</span>write<span class="token punctuation">(</span>Final_JSON<span class="token punctuation">)</span>
    <span class="token keyword">return</span> Final_JSON

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48

• def init(self, dataframe, filepath=‘DataFrameToJSONArrayFile.json’) dataframe需要传入的数据,filepath,json文件存储路径
• funChangeDataFrameType() 自动转换DataFrame数据类型,返回转换后的DataFrame
• funSaveJSONArrayFile() 保存文件