如何使用Pandas从json格式获取数据？

Question

我得到的json数据如下：

     "data": [
        {
          "currency": "CNY",
          "standardDate": "2021-03-31T00:00:00+08:00",
          "reportDate": "2021-04-26T00:00:00+08:00",
          "reportType": "first_quarterly_report",
          "q": {
            "ps": {
              "np": {
                "t": 773183901
              }
            }
          },
          "date": "2021-03-31T00:00:00+08:00",
          "stockCode": "300413"
        }
      ],
    "data":["Like Above"] many times

当我使用pd.DataFrame to get a df时，'q'列的值显示如下：

     q
     {'ps': {'np': {'t': 773183901}}} 
     {'ps': {'np': {'t': 773183901}}} 
     {'ps': {'np': {'t': 773183901}}} 
     {'ps': {'np': {'t': 773183901}}} 

我如何才能在'q'列中巧妙地得到数字

    q
    773183901
    773183901
    773183901
    773183901

Answer 1

最好的方法是获得所需的列

df1 = pd.json_normalize(data, record_path=['q','ps','np'],  \
    meta=['currency','standardDate','reportDate','<All required fields>'])

Answer 2

按名称访问JSON/dict值的更直接的方法是使用.str[]访问器。如下所示，使用列q访问嵌套JSON深处的t值：

df['q'] = df['q'].str['ps'].str['np'].str['t']

演示

data = {'q':[
     {'ps': {'np': {'t': 773183901}}}, 
     {'ps': {'np': {'t': 773183902}}}, 
     {'ps': {'np': {'t': 773183903}}}, 
     {'ps': {'np': {'t': 773183904}}}]}

df = pd.DataFrame(data)

print(df)

                                  q
0  {'ps': {'np': {'t': 773183901}}}
1  {'ps': {'np': {'t': 773183902}}}
2  {'ps': {'np': {'t': 773183903}}}
3  {'ps': {'np': {'t': 773183904}}}


df['q'] = df['q'].str['ps'].str['np'].str['t']

print(df)

           q
0  773183901
1  773183902
2  773183903
3  773183904