对于数据分析人员来讲,不少时候csv和xls格式的文件会更方面不少,但json格式做为网络数据传输通用格式,不少格式是以json格式保存,包括scrapy和pyspider等都是提供了默认的json格式,所以在不少场合须要将json格式转换为xls格式,笔者本身写了个转换脚本:python
def get_data_from_dict(data, result): #若是key不存在,则设置为空 for key in data: if key not in result: #若是一个key都不存在着直接加入一个key,不然选择第一个key的长度 if not result.keys(): result[key] = [] else: result[key] = len(result[result.keys()[0]])*[""] #而后分别加入值 for key in result: if key in data: result[key].append(data[key]) else: result[key].append("") return result def get_all_child(father_dict, spread_dict=None): '''将全部dict展开''' if spread_dict == None: spread_dict = {} for key in father_dict: if isinstance(father_dict[key], dict): spread_dict = get_all_child(father_dict[key], spread_dict) else: spread_dict[key] = father_dict[key] return spread_dict
经过json.load咱们能够将json文件转换为python中的字典格式,所以处理json文件对于python来讲其实就是对字典的处理,这里主要构建了两个函数,get_all_child()是用来将展开字典逐级展开,get_data_from_dict()则负责将字典的组合成pandas中的DataFrame样式格式,以一份安居客的json文件为例,能够关注个人github,附带送上数据,完整的代码以下:git
#coding:utf8 import json import six import pandas as pd from collections import defaultdict def get_data_from_dict(data, result=None): #若是key不存在,则设置为空 for key in data: if key not in result: #若是一个key都不存在着直接加入一个key,不然选择第一个key的长度 if not result.keys(): result[key] = [] else: result[key] = len(result[result.keys()[0]])*[""] #而后分别加入值 for key in result: if key in data: result[key].append(data[key]) else: result[key].append("") return result def get_all_child(father_dict, spread_dict=None): '''将全部dict展开''' if spread_dict == None: spread_dict = {} for key in father_dict: if isinstance(father_dict[key], dict): spread_dict = get_all_child(father_dict[key], spread_dict) else: spread_dict[key] = father_dict[key] return spread_dict filename = u"安居客新房.json" with open(filename,"r") as fr: result = defaultdict(list) for line in fr: linedata = json.loads(line) spread_data = get_all_child(linedata) result = get_data_from_dict(spread_data, result) data = pd.DataFrame(result) data.to_excel(u"安居客.xls",encoding="utf8")
---------------------END----WRITE----AT----http://my.oschina.net/Kanonpy/---------------------------
github