How to add array values to dictionary in python

Question

Hi I am working on python. I have one query which will return data from aws athena. Below is my array

result['ResultSet']['Rows']

It will display data as below.

[
  {
    "Data": [
      {
        "VarCharValue": "emp_id"
      },
      {
        "VarCharValue": "emp_dob"
      },
      {
        "VarCharValue": "emp_doj"
      },
      {
        "VarCharValue": "emp_name"
      },
      {
        "VarCharValue": "emp_type"
      },
      {
        "VarCharValue": "emp_department"
      }
    ]
  },
  {
    "Data": [
      {
        "VarCharValue": "102"
      },
      {
        "VarCharValue": "03/03/2018"
      },
      {
        "VarCharValue": "07/07/2019"
      },
      {
        "VarCharValue": "mahesh"
      },
      {
        "VarCharValue": "intern"
      },
      {
        "VarCharValue": "test"
      }
    ]
  },
  {
    "Data": [
      {
        "VarCharValue": "100"
      },
      {
        "VarCharValue": "01/01/2018"
      },
      {
        "VarCharValue": "05/05/2019"
      },
      {
        "VarCharValue": "Niranjan"
      },
      {
        "VarCharValue": "PartTime"
      },
      {
        "VarCharValue": "test"
      }
    ]
  },
  {
    "Data": [
      {
        "VarCharValue": "101"
      },
      {
        "VarCharValue": "02/02/2018"
      },
      {
        "VarCharValue": "06/06/2019"
      },
      {
        "VarCharValue": "Preetham"
      },
      {
        "VarCharValue": "fullTime"
      },
      {
        "VarCharValue": "test"
      }
    ]
  }
]

In the above data first it will display column names then it will display all the values. I want to modify this data to look like as below

emp_id    101
emp_name  Niranjan
emp_dob   01/01/2018

emp_id    102
emp_id    Preetham
emp_dob   02/02/2018

I recently started working on python. Can someone help me to organize this data. Any help would be greatly appreciated. Thanks

Answer 1

Assuming your input is in a list called result['ResultSet']['Rows'] you can do this:

# First convert your data into a table where the first list
# is the headers
result = [[dic['VarCharValue'] for dic in top_dic['Data']] for top_dic in result['ResultSet']['Rows']]

# Now result looks like this:
# [['emp_id', 'emp_dob', 'emp_doj', 'emp_name', 'emp_type', 'emp_department']
# ['102', '03/03/2018', '07/07/2019', 'mahesh', 'intern', 'test']
# ['100', '01/01/2018', '05/05/2019', 'Niranjan', 'PartTime', 'test']
# ['101', '02/02/2018', '06/06/2019', 'Preetham', 'fullTime', 'test']]

if len(result) > 1:
    # This turns the table (list of lists) into a list of dictionaries.
    result = [dict(zip(result[0], data)) for data in result[1:]]
    print(result)

Output:

[
    {'emp_id': '102', 'emp_dob': '03/03/2018', 'emp_doj': '07/07/2019', 'emp_name': 'mahesh', 'emp_type': 'intern', 'emp_department': 'test'},
    {'emp_id': '100', 'emp_dob': '01/01/2018', 'emp_doj': '05/05/2019', 'emp_name': 'Niranjan', 'emp_type': 'PartTime', 'emp_department': 'test'},
    {'emp_id': '101', 'emp_dob': '02/02/2018', 'emp_doj': '06/06/2019', 'emp_name': 'Preetham', 'emp_type': 'fullTime', 'emp_department': 'test'}
]

Answer 2

Your data in the list shoud be in the same order.

 result = [
      {
        "Data": [
          {
            "VarCharValue": "emp_id"
          },
          {
            "VarCharValue": "emp_dob"
          },
          {
            "VarCharValue": "emp_doj"
          },
          {
            "VarCharValue": "emp_name"
          },
          {
            "VarCharValue": "emp_type"
          },
          {
            "VarCharValue": "emp_department"
          }
        ]
      },
      {
        "Data": [
          {
            "VarCharValue": "102"
          },
          {
            "VarCharValue": "03/03/2018"
          },
          {
            "VarCharValue": "07/07/2019"
          },
          {
            "VarCharValue": "mahesh"
          },
          {
            "VarCharValue": "intern"
          },
          {
            "VarCharValue": "test"
          }
        ]
      },
      {
        "Data": [
          {
            "VarCharValue": "100"
          },
          {
            "VarCharValue": "01/01/2018"
          },
          {
            "VarCharValue": "05/05/2019"
          },
          {
            "VarCharValue": "Niranjan"
          },
          {
            "VarCharValue": "PartTime"
          },
          {
            "VarCharValue": "test"
          }
        ]
      },
      {
        "Data": [
          {
            "VarCharValue": "101"
          },
          {
            "VarCharValue": "02/02/2018"
          },
          {
            "VarCharValue": "06/06/2019"
          },
          {
            "VarCharValue": "Preetham"
          },
          {
            "VarCharValue": "fullTime"
          },
          {
            "VarCharValue": "test"
          }
        ]
      }
    ]
    json_data = {}
    for i in range(len(result[0]["Data"])):
        json_data[result[0]["Data"][i]["VarCharValue"]] = result[1]["Data"][i]["VarCharValue"]

    print(json_data)

Output

{'emp_id': '102', 'emp_dob': '03/03/2018', 'emp_doj': '07/07/2019', 'emp_name': 'mahesh', 'emp_type': 'intern', 'emp_department': 'test'}