Azure转录json到Pandasdf

Question

我正在尝试将Azure语音到文本转录服务（json）的输出转换为pd数据帧

以下是获得的json示例：

{
  "source": "https://batchtranscriptionstore1.blob.core.windows.net/recordings/20210221-1022043b576ef4.wav?fakecredentials123456789",
  "timestamp": "2020-06-16T09:30:21Z",
  "durationInTicks": 41200000,
  "duration": "PT4.12S",
  "combinedRecognizedPhrases": [
    {
      "channel": 0,
      "lexical": "hello world",
      "itn": "hello world",
      "maskedITN": "hello world",
      "display": "Hello world."
    }
  ],
  "recognizedPhrases": [
    {
      "recognitionStatus": "Success",
      "speaker": 1,
      "channel": 0,
      "offset": "PT0.07S",
      "duration": "PT1.59S",
      "offsetInTicks": 700000,
      "durationInTicks": 15900000,
      "nBest": [
        {
          "confidence": 0.898652852,
          "lexical": "hello world",
          "itn": "hello world",
          "maskedITN": "hello world",
          "display": "Hello world.",
          "words": [
            {
              "word": "hello",
              "offset": "PT0.09S",
              "duration": "PT0.48S",
              "offsetInTicks": 900000,
              "durationInTicks": 4800000,
              "confidence": 0.987572
            },
            {
              "word": "world",
              "offset": "PT0.59S",
              "duration": "PT0.16S",
              "offsetInTicks": 5900000,
              "durationInTicks": 1600000,
              "confidence": 0.906032
            }
          ]
        }
      ]
    }
  ]
}

使用下面的代码，我成功地用以下列创建了一个df：source，timestamp，durationInTicks，duration，combinedRecognizedPhrases

with open('file.json') as json_data:
    data = json.load(json_data)
ll =  pd.DataFrame(dict(list(data.items())[0:5]))

但我还需要在单独的列中列出“combinedRecognizedPhrases”的各个值。我该怎么做

Answer 1

用record_path试试pd.json_normalize()，然后加入

with open('file.json','r') as f:
    j = json.load(f)
df = pd.json_normalize(j,max_level=1)
df1 = pd.json_normalize(j,max_level=1,record_path=['combinedRecognizedPhrases'])

---

df2 = df[['source', 'timestamp', 'durationInTicks', 'duration']].join(df1)

Answer 2

根据@Manakin建议的答案和以下[link][1]，我提出了这个解决方案：

with open('file.json','r') as f:
    j = json.load(f)    
zz = pd.json_normalize(j, record_path=['combinedRecognizedPhrases'], meta=['source', 'durationInTicks', 'duration'])

[1]：http://（https://towardsdatascience.com/all-pandas-json-normalize-you-should-know-for-flattening-json-13eae1dfb7dd