工具&方法 | 小刘老师“再”出新招：JSON数据转为面板数据

前段时间，小编无意间曝光了小刘老师“得意之下不禁翘起二郎腿”的小动作，团队"大拿”“小拿”们听说此事后反响很大，纷纷表示惊呆了。（回顾点这里）

然鹅，小编发现平时习惯在办公室里高谈阔论的小刘老师，忽然开始变得“斯文”了？不顾众人讶异的眼神，小刘老师表示要再接再厉，再度祭出一篇新作——“JSON数据构建面板数据”！此篇文章一改大家往日对程序员“内向木讷”的印象，小编觉得吧，只要能够方便得到让经济类研究者心安的面板数据，“文绉绉”也行。并且，难得程序员要展露文采，小编也理应尽量保持他的“原滋原味”，大家“看招”吧！

引言

一骑红尘妃子笑，无人知是荔枝来 

早在唐朝末期，杨贵妃酷爱吃荔枝。由于荔枝素有“一日而色变，二日而香变，三日而味变，四五日外，色香味尽去矣”的特点，极难保鲜。所以当时都是通过不断在各地驿站更换马匹，以最快的速度，从岭南速取荔枝回长安，以供杨贵妃享用。

如今的互联网时代何尝不是如此，为了将数据以最快的速度呈现到用户的面前，全球各地的工程师都绞尽脑汁想对策。在时间的考验下，有两种格式被广泛使用——JSON以及HTML（XML的变种）格式。JSON格式以解析数据效率高、传输快、兼容性强著称，而HTML格式以展示效果好而闻名。如果写过爬虫项目的人应该会注意到，互联网上很多用了Ajax技术的网站都是使用JSON格式来传输数据的。更一般地，很多API数据，同样是JSON格式，例如百度地图API。

那么，JSON数据到底长什么样子呢？点击左下角“阅读原文”即可预览某位博主的JSON格式数据。（小编碎碎念：是不是一打开链接就眼花缭乱了？小编都看晕了⊙▽⊙，不过听小刘老师说，复制这段代码放到JSON解释器【https://www.json.cn/】进行查看，容易读懂。）

等等！上面链接获取到的数据，为什么与我们平时在网页上看到的内容不一样？那是因为我们访问这个网站的时候，除了有JSON数据，还包含很多其他类型文件。这里简单解释一下：一般情况下，当大家访问一个网站时，网站服务器会先发送一个网页框架回来，然后我们的网页浏览器再根据网页框架中的链接再次申请数据，像图片、JSON数据这样的文件，都是这时候才被我们获取到的。

JSON属于一种非结构化数据（所有格式的办公文档、文本、图片、XML, HTML、各类报表、图像和音频/视频信息等等都是非结构化数据），即不方便用二维逻辑表来表现的数据。很多时候，研究者做数据分析时，希望遇到是结构化数据，比如能在EXCEL表或者Stata等统计软件中进行计算处理。

在梳理JSON数据的过程中，虽然存在json_path这样的万能钥匙，但是对于不太熟悉JSON的人来说，当看到这种"扭来扭去"的格式，就会心生厌恶。这时，认识并初步掌握JSON数据相关知识，并将其转化为做研究所需数据格式的方法，成为一种必须。

JSON格式是什么？

此中有真意，欲辨已忘言

小刘老师小刘老师，那么什么是JSON呢？

笨！百度百科不都说了吗？

JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。它基于 ECMAScript的一个子集，采用完全独立于编程语言的文本格式来存储和表示数据。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。

下面，让我们再重新认识一下JSON数据的模样：

{
  "上海市": {
    "PROCODE": 310000,
    "DATA": [
      {
        "YEAR": 2009,
        "NEW": 93661,
        "EXIT": 45392,
        "STOCK": 657290,
        "APPLY_PATENT": 46032,
        "GRANT_PATENT": 33829,
        "GRANT_PATENT_FM": 14935,
        "GRANT_PATENT_SY": 9317,
        "GRANT_PATENT_WG": 9509,
        "APPLY_BRAND": 40747,
        "GRANT_BRAND": 27739
       },
       {
        "YEAR": 2010,
        "NEW": 109983,
        "EXIT": 33680,
  ......
    "云南省": {
    "PROCODE": 530000,
    "DATA": [
       {
        "YEAR": 2009,
        "NEW": 22072,
        "EXIT": 5346,
        "STOCK": 129401,
  ......

可以看到，以上JSON数据块是包含省名（省码：PROCODE）、年份（YEAR）、新注册企业数（NEW）、退出企业数（EXIT）和企业存续数（Stock）等相关企业信息。

那么，如何将它构建成下面这种二维表形式，也就是研究者通常说的面板数据（Panel Data）呢？

PROVINCE	PROCODE	YEAR	NEW	EXIT	STOCK
上海市	310000	2009	93661	45392	657290
上海市	310000	2010	109983	33680	733593
上海市	310000	2011	118616	45866	806343
上海市	310000	2012	121399	46757	880985
上海市	310000	2013	138087	43831	975241
......
云南省	530000	2009	22072	5346	129401
云南省	530000	2010	22264	6556	145109
......

往右滑动看更多

处理JSON数据的困境在哪里？

明月几时有，把酒问青天

JSON数据中，还有一些约定的术语，让我们再深入一点。把上述企业信息JSON数据块抽象一下，得到如下数据结构：{某省名: {"PROCODE": 市码,"DATA": [{"YEAR": 年份， ....等等其他数据}]}}

仔细观察，一层层花括号好像“俄罗斯套娃”的盒子，每个盒子，约定为一个“阶”。解析一下：“某省名”为第一阶，“PROCODE”和“DATA”为第二阶，“YEAR”为第四阶（注意：“[ ]”方括号其实可以看作{}花括号中省略了索引）。一般地，最大为四阶，即称这个JSON数据为四阶JSON数据。此外，将三阶以上（包括三阶）的JSON数据称为高阶JSON数据。

►二阶JSON数据与CSV数据：

二阶JSON可以很容易用pandas来转化为CSV格式的数据（CSV可以看成是纯文本的XLSX格式数据，兼容性更好）。代码如下：

In [1]: import pandas as pd

In [2]: json_data = {
             'col_a':{
                 'row1':123
                 , 'row2':321
                 }
             , 'col_b':{
                 'row1': 'abc'
                 , 'row2': 'abc'
                 }
             }

In [3]: df = pd.DataFrame(json_data)

In [4]: df.to_csv('./json_data.csv', encoding='utf_8_sig')

得到的二维表如下：

	col_a	col_b
row1	123	abc
row2	321	abc

往右滑动看更多

►高阶JSON数据的窘境：

但高阶JSON数据，由于维度复杂，如果再次用pandas，则很难将结果直观的展示出来，这里依然用代码来说明情况：

In [1]: import pandas as pd

In [2]: json_data = {
            'col_a':{
                'row1':{'detail2': 'qy'}
                , 'row2': {'detail1': 'qy'}
                }
            , 'col_b':{
                'row1': 123
                , 'row2': 'abc'
                }
            }

In [3]: df = pd.DataFrame(json_data)

In [4]: df.to_csv('./json_data.csv', encoding='utf_8_sig')

代码处理后，只能得到这样一个表格：

	col_a	col_b
row1	{'detail2': 'qy'}	123
row2	{'detail1': 'qy'}	abc

往右滑动看更多

显然，并没有达到我们预期效果。因为COL_A一列数据无法使用，所以必须进一步处理。（即使将结果放在SPSS、STATA等软件里，也要继续清理）

处理高阶JSON数据并不难

山穷水尽疑无路，柳暗花明又一村 

既然高阶JSON数据没法直接塞进二维表中，那么很自然的想到，将其分在多个二维表中保存，再根据要求来判断如何整合这些二维表。

import pandas as pd
import numpy as np
import json

def multi_json2dataframes(json_obj, data_key='_id'):
    """
    万能JSON转DataFrame函数(努力中)
    json_obj: json格式的对象，可以是字典、列表、json字符串
    data_key：拆开JSON数据后，用来连接不同子表的键
    """
    # 如果是以字符串的形式传入的JSON数据，则需要
    if isinstance(json_obj, (str, bytes)):
        json_obj = json.loads(json_obj)
       
    # 将初始数据转化为dataframes
    ori_df = pd.DataFrame(json_obj, dtype=np.object)
   
    # 包含JSON格式的数据，不适合作为key使用
    type_func = lambda obj: isinstance(obj, (dict, list))
    if ori_df.loc[:, data_key].apply(type_func).any():
        raise(Exception('{}列包含JSON格式的数据，不适合作为key使用'.format(data_key)))
   
    # 因为之后需要将不同维度的
    ori_df = ori_df.set_index(data_key)
    df_pool = {'$': ori_df} # 构建待处理的DataFrame池
    res = {}
    while bool(df_pool):
        # str_title是其对应的DataFrame中数据的json_path
        str_title, df_tmp = df_pool.popitem()
       
        # 判断一列中是否有JSON格式的数据
        json_dtype = df_tmp.apply(lambda col:col.apply(type_func).any(), axis=0)
       
        # 分离出不是JSON格式的列，保存下来
        str_part = df_tmp.loc[:, json_dtype[json_dtype==False].index]
        if not str_part.empty:
            str_part.index.name = data_key
            res[str_title] = str_part.copy()
       
        # 分离出是JSON格式的列，放回df_pool中
        json_part = df_tmp.loc[:, json_dtype[json_dtype==True].index]
       
       
        if json_part.empty: # 避免将空的DataFrame放入df_pool
            continue
       
        # 这里的ser_key的构造还有待商榷
        for col in json_part.columns:
            ser = json_part.loc[:, col]
            ser = ser.dropna() # 空数据没必要保存
           
            # 暂时不考虑同一阶JSON中字典与列表混用的情况
            type_detect = ser.apply(lambda obj: type(obj)).unique()
            if len(type_detect) > 1:
                res['__unfinished_json__'] = ser.copy()
                continue
           
            ser_key = '{}.{}'.format(str_title, col)
           
            # 这里还有改良空间， 暂不考虑列表包含列表的情况    
            if type_detect[0] == list:  
                # series中没有什么好函数做索引与值的运算，所以转为DataFrame
                ser_df = ser.to_frame().reset_index()
                """
                 由于这里的ser中，每个值都是一个列表，并且不附带data_key
                 我这里的做法，是把每个列表都先转为DataFrame，更改索引后，再合并
                 """

                index_func = lambda row: 

pd.Index([row.iloc[0]]*len(row.iloc[-1]))

                list_func = lambda row: (pd.DataFrame(row.iloc[-1])

.set_index(index_func(row)))
                """
                 这里ser_df.apply出来以后，是个series。
                 我不想附个新的变量了，就直接放进了concat里面
                 """
                ser_df = pd.concat(ser_df.apply(list_func, axis=1)
                                         .tolist()
                                   )
                df_pool[ser_key] = ser_df.copy()
                ser_df = None
            else:
                df_pool[ser_key] = pd.DataFrame(ser.to_dict()).T
    return res

上面这段代码，还有多个地方可以优化：

1、对内存的利用不是很合理，json_obj读进来以后，没有把已经处理好的数据移出内存，也没有使用多进程来对多个df_tmp进行处理。遇到大文件可能会运行缓慢。

2、只对df_tmp中的列进行“是否为JSON格式”判断。若df_tmp中有的列，既有普通数据，又有JSON数据，结果就会出乱，严重会报错。暂时没想好怎么在同一列中存储不同类型的数据，就先默认只要有JSON类型的数据，就把整列当成JSON格式来做了。

3、结果res中的key是我原本想用json_path，然而json_path的语法比较多，代码不够智能，而且可能存在错误。以后想出来更有条理的代码后再做更新。就目前而言，这个key不是很重要。

小编偷偷透露一下：关于以上优化的实现，大家可以关注小刘老师的github：https://github.com/Dyson1990/MyWheels3/blob/master/json_manager.py，由于官方限制无法放上外部链接，麻烦大家复制一下下啦~

见证处理结果！

嘈嘈切切错杂弹，大珠小珠落玉盘

经过一段优雅的指尖跳跃，开启“奇迹的时刻”到了。现在再来看一个更加复杂但稍微规整一点的JSON格式数据（赋值为json_data）：

{
"col_a": {
"index1": [{"detail1": ["qy11", "qy12"]}, {"detail2": "qy3"}, {"YEAR": 1996}],
"index2": [{"detail1": ["qy21", "qy22"]}, {"detail2": "qy4"}, {"YEAR": 1987}]
},
"col_b": {
"index1": 123,
"index2": "abc"
},
"col_c": {
"index1": "321",
"index2": "123"
},
"key": {
"index1": "row1",
"index2": "row2"
}
}

大家可以看见，这批数据有时间维度，但是却保存在JSON文件中的一个小角落里。调用之前的函数，并且将结果打印出来：

res_dict = multi_json2dataframes(json_data, 'key')
for data_title, df in res_dict.items():
    print('data_title: ', data_title)
    print(df)
    print('\n')

我们可以得到以下几张表格：

a.名为“$”的表格：

key	col_b	col_c
row1	123	321
row2	abc	123

往右滑动看更多

b.名为“$/col_a/2”的表格：

key	YEAR
row1	1996
row2	1987

往右滑动看更多

c.名为“$/col_a/1”的表格：

key	detail2
row1	qy3
row2	qy4

往右滑动看更多

d.名为“$/col_a/0/detail1”的表格：

key	0	1
row1	qy11	qy12
row2	qy21	qy22

往右滑动看更多

到这里，我们就已经把所有的JSON数据都转化为一个个的二维表了，而且每个表之间都有key可以关联上。

剩下的，就是把这些表格合并了。由于以上的表格全是DataFrame格式，用pd.merge即可轻松处理。若是要将表格“$/col_a/0/detail1”中的0列和1列合并为这样，可以在小刘老师的github中找到dataframe_manager.py，其中有相应函数可以处理，有需要的话，会在以后的文章进行详解，在此不再赘述。

key	data
row1	qy11
row1	qy12
row2	qy21
row2	qy22

往右滑动看更多

到此为止，问题圆满得到解决了。

后记

余音绕梁，三日不绝

“我写这个小项目的初衷，是为了完成MongoDB与MySQL数据库的互通。MongoDB是以BSON格式保存数据的（类似于JSON），与爬虫程序返回的JSON数据几乎是无缝对接。

然而我回过去看自己的代码，依然不是很满意。除了前面提到的一些待优化的问题，还有如下欠缺：

1、算上注释与空格，一共70行代码，又臭又长；

2、使用Pandas来解析字典虽然更加通俗易懂，但是效率必然大打折扣；

3、json_path的逻辑还存在问题。

虽然这些问题对解析MongoDB的数据不会造成影响，但毕竟不是我要的万能函数。我接下来会抽空从两个方面来试图对这个函数进行提升：

1、使用递归思想代替df_pool来遍历JSON数据，这样最后的结果可以就是输出字典了，具体实现的时候再考虑；

2、把JSON数据按“树”（甚至“图”这样的数据结构）来重新构造JSON数据（没听过的朋友请自行百度）。

如果失败了，我会再对我现有思路进行一些优化；如果成功了，我会另外再写一篇文章进行详细说明，敬请期待。”

（小编碎碎念：以上全部内容就是小刘老师的个人“诗集”，小编一个字没敢改，有没有被“熏陶”到？）

丨最后，对本文文风和技术内容有任何意见和建议，敬请留言。我们期待更好的进步！

往期推荐

数据Seminar

这里是大数据、分析技术与学术研究的三叉路口

作者：企研数据 ·  Dyson

审阅：企研数据 ·  简华

编辑：青酱

    欢迎扫描👇二维码添加关注