R internet P
Reputation: 33
Json Data To desired format
New to python and json parsing
Json Format:
Once I load the data and print the details of the columns as headers and traverse through the object, I can get them but the ask is to print it in a specific format as mentioned below which is becoming difficult to understand.
[
{
"table": {
"cols": [
"New Charges",
"From",
"To",
"Quantity",
"Rate",
"Frequency",
"Amount"
],
"rows": [
[
"Bar Charges",
"12/29/19",
"12/29/19",
"1.00",
"$71.65",
"One time",
"$71.65"
],
[
"Dining Services",
"12/31/19",
"12/31/19",
"1.00",
"$17.90",
"One time",
"$17.90"
],
[
"Incontinence supplies",
"12/18/19",
"12/18/19",
"1.00",
"$55.32",
"One time",
"$55.32"
],
[
"Bar Charges",
"01/11/20",
"01/11/20",
"1.00",
"$73.75",
"One time",
"$73.75"
],
[
"Room - Assisted Living",
"02/01/20",
"02/29/20",
"1.00",
"$4,695.00",
"Monthly",
"$4,695.00"
],
[
"Assisted Living Level 3",
"02/01/20",
"02/29/20",
"1.00",
"$1,700.00",
"Monthly",
"$1,700.00"
],
[
"Basic Medication Management",
"02/01/20",
"02/29/20",
"1.00",
"$275.00",
"Monthly",
"$275.00"
],
[
"Emergency Response Charge",
"02/01/20",
"02/29/20",
"1.00",
"$30.00",
"Monthly",
"$30.00"
]
],
"bbox": [
118,
1219,
2371,
1691
],
"data": [
[
"New Charges",
"From",
"To",
"Quantity",
"Rate",
"Frequency",
"Amount"
],
[
"Bar Charges",
"12/29/19",
"12/29/19",
"1.00",
"$71.65",
"One time",
"$71.65"
],
[
"Dining Services",
"12/31/19",
"12/31/19",
"1.00",
"$17.90",
"One time",
"$17.90"
],
[
"Incontinence supplies",
"12/18/19",
"12/18/19",
"1.00",
"$55.32",
"One time",
"$55.32"
],
[
"Bar Charges",
"01/11/20",
"01/11/20",
"1.00",
"$73.75",
"One time",
"$73.75"
],
[
"Room - Assisted Living",
"02/01/20",
"02/29/20",
"1.00",
"$4,695.00",
"Monthly",
"$4,695.00"
],
[
"Assisted Living Level 3",
"02/01/20",
"02/29/20",
"1.00",
"$1,700.00",
"Monthly",
"$1,700.00"
],
[
"Basic Medication Management",
"02/01/20",
"02/29/20",
"1.00",
"$275.00",
"Monthly",
"$275.00"
],
[
"Emergency Response Charge",
"02/01/20",
"02/29/20",
"1.00",
"$30.00",
"Monthly",
"$30.00"
]
]
}
},
{
"table": {
"cols": [
"Payments",
"Date",
"",
"",
"",
"Amount"
],
"rows": [
[
"Electronic payment",
"12/30/19",
"12/30/19",
"1.00",
"$6,997.32One time",
"($6,997.32)"
]
],
"bbox": [
114,
957,
2374,
1053
],
"data": [
[
"Payments",
"Date",
"",
"",
"",
"Amount"
],
[
"Electronic payment",
"12/30/19",
"12/30/19",
"1.00",
"$6,997.32One time",
"($6,997.32)"
]
]
}
},
{
"table": {
"cols": [
"Credits",
"From",
"To",
"Quantity",
"Rate",
"Frequency",
"Amount"
],
"rows": [
[
"n/a",
"n/a",
"n/a",
"n/a",
"n/a",
"n/a",
"$0.00"
]
],
"bbox": [
117,
1087,
2382,
1200
],
"data": [
[
"Credits",
"From",
"To",
"Quantity",
"Rate",
"Frequency",
"Amount"
],
[
"n/a",
"n/a",
"n/a",
"n/a",
"n/a",
"n/a",
"$0.00"
]
]
}
}
]
I am able to extract the headers for each column but how to convert it into below format:
# {
# "New Charges": "Bar Charges",
# "From": "12/29/19",
# "To": "12/29/19",
# "Quantity": "1.00",
# "Rate": "$71.65",
# "Frequency": "One time",
# "Amount": "$71.65"
# },
Not looking for exact code but some suggestions and guidance is highly appreciated.
Thanks in advance
Upvotes: 0
Views: 32
Answers (1)
Tom McLean
Reputation: 6369
Something like this will create key, value dictionaries using the column names:
tables_to_parse = [
{
"table": {
"cols": [
"New Charges",
"From",
"To",
"Quantity",
"Rate",
"Frequency",
"Amount"
],
"rows": [
[
"Bar Charges",
"12/29/19",
"12/29/19",
"1.00",
"$71.65",
"One time",
"$71.65"
],
[
"Dining Services",
"12/31/19",
"12/31/19",
"1.00",
"$17.90",
"One time",
"$17.90"
],
[
"Incontinence supplies",
"12/18/19",
"12/18/19",
"1.00",
"$55.32",
"One time",
"$55.32"
],
[
"Bar Charges",
"01/11/20",
"01/11/20",
"1.00",
"$73.75",
"One time",
"$73.75"
],
[
"Room - Assisted Living",
"02/01/20",
"02/29/20",
"1.00",
"$4,695.00",
"Monthly",
"$4,695.00"
],
[
"Assisted Living Level 3",
"02/01/20",
"02/29/20",
"1.00",
"$1,700.00",
"Monthly",
"$1,700.00"
],
[
"Basic Medication Management",
"02/01/20",
"02/29/20",
"1.00",
"$275.00",
"Monthly",
"$275.00"
],
[
"Emergency Response Charge",
"02/01/20",
"02/29/20",
"1.00",
"$30.00",
"Monthly",
"$30.00"
]
],
"bbox": [
118,
1219,
2371,
1691
],
"data": [
[
"New Charges",
"From",
"To",
"Quantity",
"Rate",
"Frequency",
"Amount"
],
[
"Bar Charges",
"12/29/19",
"12/29/19",
"1.00",
"$71.65",
"One time",
"$71.65"
],
[
"Dining Services",
"12/31/19",
"12/31/19",
"1.00",
"$17.90",
"One time",
"$17.90"
],
[
"Incontinence supplies",
"12/18/19",
"12/18/19",
"1.00",
"$55.32",
"One time",
"$55.32"
],
[
"Bar Charges",
"01/11/20",
"01/11/20",
"1.00",
"$73.75",
"One time",
"$73.75"
],
[
"Room - Assisted Living",
"02/01/20",
"02/29/20",
"1.00",
"$4,695.00",
"Monthly",
"$4,695.00"
],
[
"Assisted Living Level 3",
"02/01/20",
"02/29/20",
"1.00",
"$1,700.00",
"Monthly",
"$1,700.00"
],
[
"Basic Medication Management",
"02/01/20",
"02/29/20",
"1.00",
"$275.00",
"Monthly",
"$275.00"
],
[
"Emergency Response Charge",
"02/01/20",
"02/29/20",
"1.00",
"$30.00",
"Monthly",
"$30.00"
]
]
}
},
{
"table": {
"cols": [
"Payments",
"Date",
"",
"",
"",
"Amount"
],
"rows": [
[
"Electronic payment",
"12/30/19",
"12/30/19",
"1.00",
"$6,997.32One time",
"($6,997.32)"
]
],
"bbox": [
114,
957,
2374,
1053
],
"data": [
[
"Payments",
"Date",
"",
"",
"",
"Amount"
],
[
"Electronic payment",
"12/30/19",
"12/30/19",
"1.00",
"$6,997.32One time",
"($6,997.32)"
]
]
}
},
{
"table": {
"cols": [
"Credits",
"From",
"To",
"Quantity",
"Rate",
"Frequency",
"Amount"
],
"rows": [
[
"n/a",
"n/a",
"n/a",
"n/a",
"n/a",
"n/a",
"$0.00"
]
],
"bbox": [
117,
1087,
2382,
1200
],
"data": [
[
"Credits",
"From",
"To",
"Quantity",
"Rate",
"Frequency",
"Amount"
],
[
"n/a",
"n/a",
"n/a",
"n/a",
"n/a",
"n/a",
"$0.00"
]
]
}
}
]
def parse_table(table):
cols = table['cols']
rows = table['rows']
formatted_rows = [{
col:row_val for col, row_val in zip(cols, row)
} for row in rows]
return formatted_rows
parsed_tables = [parse_table(table['table']) for table in tables_to_parse]
Example row:
{
'New Charges': 'Bar Charges',
'From': '12/29/19',
'To': '12/29/19',
'Quantity': '1.00',
'Rate': '$71.65',
'Frequency': 'One time',
'Amount': '$71.65'
}
Upvotes: 1
Related Questions
- JSON data to list
- JSON to Python List
- Python list formatting with JSON
- How to take items in list and reform them into json
- Formatting my json
- List into json format using Python
- format JSON object into array like structure
- Python, getting list into correct JSON format
- Custom format to JSON
- Change list to json format in Python