Python - Convert Json to table structure

Question

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:

Oh sorry, the table structure is just an example, I will insert the output into a DB table (Postgres). — Doug P
– Doug P, Commented Mar 13, 2022 at 9:35
First you need to convert every string with JSON to a Python dict. You find information about how to do that in the package json. — md2perpe
– md2perpe, Commented Mar 13, 2022 at 9:41

Jayit Ghosh · Accepted Answer · 2022-03-13 09:45:10Z

9

You can convert the Json to a dictionary in python using json.load.
This dictionary can be converted to a dataframe using Pandas.Dataframe.
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.

answered Mar 13, 2022 at 9:45

Jayit Ghosh

1511 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LoneWanderer Over a year ago

Pandas has sql export/import features afaik pandas.pydata.org/docs/user_guide/io.html?highlight=sql. And pandas.pydata.org/docs/user_guide/io.html?highlight=sql#io-sql

abdeali004 · Accepted Answer · 2022-03-13 10:05:48Z

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  |
+-------------+-------+-----------------+-----------------+---------------+--------------------+------------------+------------------+-----+

The article writes about tabulate as first option which cannot be used with json input.

AlbertWang · Accepted Answer · 2023-10-11 01:14:42Z

1

from json2table import convert

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

I've used this to great effect.

answered Oct 11, 2023 at 1:14

AlbertWang

213 bronze badges

Collectives™ on Stack Overflow

Python - Convert Json to table structure

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related