Azure transcription json to pandas df

Question

I am trying to convert the output of the Azure speech to text transcription service (json) to a pd data frame.

The following is an example of the obtained 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
            }
          ]
        }
      ]
    }
  ]
}

Using the following code I manage to make a df with the following columns: 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]))

But I also need the individual values of "combinedRecognizedPhrases" in separate columns. How can I do this?

Answer 1

Based on the answer suggested by @Manakin and the following [link][1], I came up with this solution:

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