CODEWITHSUNDEEP
pythonjsonlist
Doug P
Doug P

Reputation: 319

Python - Convert Json to table structure

I have a JSON with the following structure below into a list in a python variable. I'd like to extract this JSON value as a table. My question is, how can I extract it from the list and how can I change it into a table?

Once I have converted it, I will insert the output into a Postgres table.

JSON structure

['
{
    "_id": {
        "$Col1": "XXXXXXX2443"
    },
    "col2": false,
    "col3": "359335050111111",
    "startedAt": {
        "$date": 1633309625000
    },
    "endedAt": {
        "$date": 1633310213000
    },
    "col4": "YYYYYYYYYYYYYYYYYY",
    "created_at": {
        "$date": 1633310846935
    },
    "updated_at": {
        "$date": 1633310846935
    },
    "__v": 0
}
']

Desired output:

enter image description here

Upvotes: 4

Views: 40073

Answers (3)

AlbertWang
AlbertWang

Reputation: 11

from json2table import convert

html=convert(json.loads(json_data))
print(html)

I've used this to great effect.

Upvotes: 0

abdeali004
abdeali004

Reputation: 473

Use the code below.

I have used PrettyTable module for printing in a table like structure. Use this - https://www.geeksforgeeks.org/how-to-make-a-table-in-python/ for table procedure.

Also, all the headers and values will be stored in headers and values variable.

import json
from prettytable import PrettyTable

value = ['''
       {
           "_id": {
               "$Col1": "XXXXXXX2443"
           },
           "col2": false,
           "col3": "359335050111111",
           "startedAt": {
               "$date": 1633309625000
           },
           "endedAt": {
               "$date": 1633310213000
           },
           "col4": "YYYYYYYYYYYYYYYYYY",
           "created_at": {
               "$date": 1633310846935
           },
           "updated_at": {
               "$date": 1633310846935
           },
           "__v": 0
       }''']

dictionary = json.loads(value[0])
headers = []
values = []
for key in dictionary:
    head = key
    value = ""
    if type(dictionary[key]) == type({}):
        for key2 in dictionary[key]:
            head += "/" + key2
            value = dictionary[key][key2]
            headers.append(head)
            values.append(value)

    else:
        value = dictionary[key]
        headers.append(head)
        values.append(value)

print(headers)
print(values)
myTable = PrettyTable(headers)

myTable.add_row(values)
print(myTable)

Output

['_id/$Col1', 'col2', 'col3', 'startedAt/$date', 'endedAt/$date', 'col4', 'created_at/$date', 'updated_at/$date', '__v']
['XXXXXXX2443', False, '359335050111111', 1633309625000, 1633310213000, 'YYYYYYYYYYYYYYYYYY', 1633310846935, 1633310846935, 0]

+-------------+-------+-----------------+-----------------+---------------+--------------------+------------------+------------------+-----+
|  _id/$Col1  |  col2 |       col3      | startedAt/$date | endedAt/$date |        col4        | created_at/$date | updated_at/$date | __v |
+-------------+-------+-----------------+-----------------+---------------+--------------------+------------------+------------------+-----+
| XXXXXXX2443 | False | 359335050111111 |  1633309625000  | 1633310213000 | YYYYYYYYYYYYYYYYYY |  1633310846935   |  1633310846935   |  0  |
+-------------+-------+-----------------+-----------------+---------------+--------------------+------------------+------------------+-----+

Upvotes: 6

Jayit Ghosh
Jayit Ghosh

Reputation: 141

  1. You can convert the Json to a dictionary in python using json.load.

  2. This dictionary can be converted to a dataframe using Pandas.Dataframe.

  3. You can export this dataframe as .csv using pandas.Dataframe.to_csv to be consumed in Postgres.

Note: This requires Pandas library to be installed. Or else,you can simply install Anaconda (if you are using any other IDE) and most frequently used packages come installed with it.

Upvotes: 8

Related Questions