對(duì)pandas處理json數(shù)據(jù)的方法詳解

更新時(shí)間：2019年02月08日 17:00:58 作者：qq_24499417

今天小編就為大家分享一篇對(duì)pandas處理json數(shù)據(jù)的方法詳解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧

今天展示一個(gè)利用pandas將json數(shù)據(jù)導(dǎo)入excel例子,主要利用的是pandas里的read_json函數(shù)將json數(shù)據(jù)轉(zhuǎn)化為dataframe。

先拿出我要處理的json字符串：

strtext='[{"ttery":"min","issue":"20130801-3391","code":"8,4,5,2,9","code1":"297734529","code2":null,"time":1013395466000},\
{"ttery":"min","issue":"20130801-3390","code":"7,8,2,1,2","code1":"298058212","code2":null,"time":1013395406000},\
{"ttery":"min","issue":"20130801-3389","code":"5,9,1,2,9","code1":"298329129","code2":null,"time":1013395346000},\
{"ttery":"min","issue":"20130801-3388","code":"3,8,7,3,3","code1":"298588733","code2":null,"time":1013395286000},\
{"ttery":"min","issue":"20130801-3387","code":"0,8,5,2,7","code1":"298818527","code2":null,"time":1013395226000}]'

pandas.read_json的語(yǔ)法如下：

pandas.read_json(path_or_buf=None, orient=None, typ='frame', dtype=True, 
convert_axes=True, convert_dates=True, keep_default_dates=True, 
numpy=False, precise_float=False, date_unit=None, encoding=None, 
lines=False, chunksize=None, compression='infer')

第一參數(shù)就是json文件路徑或者json格式的字符串。

第二參數(shù)orient是表明預(yù)期的json字符串格式。orient的設(shè)置有以下幾個(gè)值：

(1).'split' : dict like {index -> [index], columns -> [columns], data -> [values]}

這種就是有索引，有列字段,和數(shù)據(jù)矩陣構(gòu)成的json格式。key名稱(chēng)只能是index,columns和data。

pandas處理json數(shù)據(jù)

'records' : list like [{column -> value}, ... , {column -> value}]

這種就是成員為字典的列表。如我今天要處理的json數(shù)據(jù)示例所見(jiàn)。構(gòu)成是列字段為鍵,值為鍵值,每一個(gè)字典成員就構(gòu)成了dataframe的一行數(shù)據(jù)。

'index' : dict like {index -> {column -> value}}

以索引為key,以列字段構(gòu)成的字典為鍵值。如：

pandas處理json數(shù)據(jù)

'columns' : dict like {column -> {index -> value}}

這種處理的就是以列為鍵，對(duì)應(yīng)一個(gè)值字典的對(duì)象。這個(gè)字典對(duì)象以索引為鍵,以值為鍵值構(gòu)成的json字符串。如下圖所示:

pandas處理json數(shù)據(jù)

'values' : just the values array。

values這種我們就很常見(jiàn)了。就是一個(gè)嵌套的列表。里面的成員也是列表，2層的。

pandas處理json數(shù)據(jù)

主要就說(shuō)下這兩個(gè)參數(shù)吧。下面我們回到示例中來(lái)。我們看前面可以發(fā)現(xiàn)示例是一個(gè)orient為records的json字符串。

這樣就好處理了?？创a：

# -*- coding: utf-8 -*-
"""
Created on Sun Aug 5 09:01:38 2018
@author: FanXiaoLei
"""
import pandas as pd
strtext='[{"ttery":"min","issue":"20130801-3391","code":"8,4,5,2,9","code1":"297734529","code2":null,"time":1013395466000},\
{"ttery":"min","issue":"20130801-3390","code":"7,8,2,1,2","code1":"298058212","code2":null,"time":1013395406000},\
{"ttery":"min","issue":"20130801-3389","code":"5,9,1,2,9","code1":"298329129","code2":null,"time":1013395346000},\
{"ttery":"min","issue":"20130801-3388","code":"3,8,7,3,3","code1":"298588733","code2":null,"time":1013395286000},\
{"ttery":"min","issue":"20130801-3387","code":"0,8,5,2,7","code1":"298818527","code2":null,"time":1013395226000}]'
 
df=pd.read_json(strtext,orient='records')
df.to_excel('pandas處理json.xlsx',index=False,columns=["ttery","issue","code","code1","code2","time"])

最終寫(xiě)入excel如下圖：

pandas處理json數(shù)據(jù)