How to put JSON object into a python dataframe-CodePudding

I have a json object that looks like this:

{
  "Category_One": [
    [
      "CAT_1_VALUE_LINE_ONE
    ],
    [
      "CAT_1_VALUE_LINE_TWO
    ],
],

  "Category_Two": [
    [
      "CAT_2_VALUE_LINE_ONE
    ],
    [
      "CAT_2_VALUE_LINE_TWO
    ],
],
  "Category_Three": [
    [
      "CAT_3_VALUE_LINE_ONE
    ],
    [
      "CAT_3_VALUE_LINE_TWO
    ],
]
}

I need to convert or transform it to something that looks like this (I believe this is known as a NESTED JSON Object?

{
   "Category_One": "CAT_1_VALUE_LINE_ONE",
   "Category_Two": "CAT_2_VALUE_LINE_ONE",
   "Category_Three": "CAT_3_VALUE_LINE_ONE"
},
{
   "Category_One": "CAT_1_VALUE_LINE_TWO",
   "Category_Two": "CAT_2_VALUE_LINE_TWO",
   "Category_Three": "CAT_3_VALUE_LINE_TWO"
},

and then I think once I get it in this format, I can put into a dataframe..

CodePudding user response：

First of all, your existing code has syntax errors. Then I can suggest one way to make it a list of dictionaries from existing dictionary and finally make a data frame like below-

import pandas as pd
data = {
       'Category_One': [['CAT_1_VALUE_LINE_ONE'],
        ['CAT_1_VALUE_LINE_TWO']],
       'Category_Two': [['CAT_2_VALUE_LINE_ONE'],
        ['CAT_2_VALUE_LINE_TWO']],
       'Category_Three': [['CAT_3_VALUE_LINE_ONE'],
        ['CAT_3_VALUE_LINE_TWO']]
      }

final_result = []
for i in range(len(data['Category_One'])):
    result = {}
    for key, value in data.items():
        result[key] = value[i][0]
    final_result.append(result)
df = pd.DataFrame(final_result)
print(df)

Output:

           Category_One          Category_Two        Category_Three
0  CAT_1_VALUE_LINE_ONE  CAT_2_VALUE_LINE_ONE  CAT_3_VALUE_LINE_ONE
1  CAT_1_VALUE_LINE_TWO  CAT_2_VALUE_LINE_TWO  CAT_3_VALUE_LINE_TWO

Working Code: https://replit.com/@SanyAhmed/listofdicttodf?v=1#main.py