Posts: 14
Threads: 7
Joined: Feb 2021
Sep-27-2022, 07:58 PM
(This post was last modified: Sep-28-2022, 01:53 AM by Larz60+.
Edit Reason: reformatted
)
I have a json as shown below and I need to convert this to table format. (all post about this did not help me and I think is because the json structure)
I tried using pandas but it is not working as I expected.
I need something like this
id, t_id, value, date_timestamp, type
179328741654819, 963852456741, 499.66, 2020-09-22T15:18:17, in
This is my code: (I am using Jupyter notebook)
import json
import pandas as pd
from IPython.display import display
# load json file
pd_object = pd.read_json('file.json',typ='series')
df = pd.DataFrame(pd_object)
display(df)But it show only one column
result
Output: 0 {'id': '179328741654819', 't_values'...
200 rows × 1 columns
The json structure is:
Output: [
{
"id": "179328741654819",
"t_values": [
{
"t_id": "963852456741",
"value": "499.66",
"date_timestamp": "2020-09-22T15:18:17",
"type": "in"
},
{
"t_id": "852951753456",
"value": "1386.78",
"date_timestamp": "2020-10-31T14:46:44",
"type": "in"
}
]
},
{
"id": "823971648264792",
"t_values": [
{
"t_id": "753958561456",
"value": "672.06",
"date_timestamp": "2020-03-16T22:41:16",
"type": "in"
},
{
"t_id": "321147951753",
"value": "773.88",
"date_timestamp": "2020-05-08T18:29:31",
"type": "out"
},
{
"t_id": "258951753852",
"value": "733.13",
"date_timestamp": null,
"type": "in"
}
]
}
]
Posts: 7,320
Threads: 123
Joined: Sep 2016
Sep-28-2022, 08:19 AM
(This post was last modified: Sep-28-2022, 08:20 AM by snippsat.)
Use json_normalize for this.
Example.
import pandas as pd
lst = [
{
"id": "179328741654819",
"t_values": [
{
"t_id": "963852456741",
"value": "499.66",
"date_timestamp": "2020-09-22T15:18:17",
"type": "in"
},
{
"t_id": "852951753456",
"value": "1386.78",
"date_timestamp": "2020-10-31T14:46:44",
"type": "in"
}
]
},
{
"id": "823971648264792",
"t_values": [
{
"t_id": "753958561456",
"value": "672.06",
"date_timestamp": "2020-03-16T22:41:16",
"type": "in"
},
{
"t_id": "321147951753",
"value": "773.88",
"date_timestamp": "2020-05-08T18:29:31",
"type": "out"
},
{
"t_id": "258951753852",
"value": "733.13",
"date_timestamp": 'null',
"type": "in"
}
]
}
]>>> df = pd.json_normalize(lst, meta=['id'], record_path='t_values')
>>> df
t_id value date_timestamp type id
0 963852456741 499.66 2020-09-22T15:18:17 in 179328741654819
1 852951753456 1386.78 2020-10-31T14:46:44 in 179328741654819
2 753958561456 672.06 2020-03-16T22:41:16 in 823971648264792
3 321147951753 773.88 2020-05-08T18:29:31 out 823971648264792
4 258951753852 733.13 null in 823971648264792
>>> df = df.reindex(['id', 't_id', 'value', 'date_timestamp', 'type'], axis=1)
>>> df
id t_id value date_timestamp type
0 179328741654819 963852456741 499.66 2020-09-22T15:18:17 in
1 179328741654819 852951753456 1386.78 2020-10-31T14:46:44 in
2 823971648264792 753958561456 672.06 2020-03-16T22:41:16 in
3 823971648264792 321147951753 773.88 2020-05-08T18:29:31 out
4 823971648264792 258951753852 733.13 null in
python_student likes this post
Posts: 14
Threads: 7
Joined: Feb 2021
(Sep-28-2022, 08:19 AM)snippsat Wrote: Use json_normalize for this.
Example.
import pandas as pd
lst = [
{
"id": "179328741654819",
"t_values": [
{
"t_id": "963852456741",
"value": "499.66",
"date_timestamp": "2020-09-22T15:18:17",
"type": "in"
},
{
"t_id": "852951753456",
"value": "1386.78",
"date_timestamp": "2020-10-31T14:46:44",
"type": "in"
}
]
},
{
"id": "823971648264792",
"t_values": [
{
"t_id": "753958561456",
"value": "672.06",
"date_timestamp": "2020-03-16T22:41:16",
"type": "in"
},
{
"t_id": "321147951753",
"value": "773.88",
"date_timestamp": "2020-05-08T18:29:31",
"type": "out"
},
{
"t_id": "258951753852",
"value": "733.13",
"date_timestamp": 'null',
"type": "in"
}
]
}
]>>> df = pd.json_normalize(lst, meta=['id'], record_path='t_values')
>>> df
t_id value date_timestamp type id
0 963852456741 499.66 2020-09-22T15:18:17 in 179328741654819
1 852951753456 1386.78 2020-10-31T14:46:44 in 179328741654819
2 753958561456 672.06 2020-03-16T22:41:16 in 823971648264792
3 321147951753 773.88 2020-05-08T18:29:31 out 823971648264792
4 258951753852 733.13 null in 823971648264792
>>> df = df.reindex(['id', 't_id', 'value', 'date_timestamp', 'type'], axis=1)
>>> df
id t_id value date_timestamp type
0 179328741654819 963852456741 499.66 2020-09-22T15:18:17 in
1 179328741654819 852951753456 1386.78 2020-10-31T14:46:44 in
2 823971648264792 753958561456 672.06 2020-03-16T22:41:16 in
3 823971648264792 321147951753 773.88 2020-05-08T18:29:31 out
4 823971648264792 258951753852 733.13 null in
Thank you so much. This was I needed
Posts: 1
Threads: 0
Joined: Dec 2024
Data can also be seen using online tools just for you information. just try json to table converter tool.
(Sep-27-2022, 07:58 PM)python_student Wrote: I have a json as shown below and I need to convert this to table format. (all post about this did not help me and I think is because the json structure)
I tried using pandas but it is not working as I expected.
I need something like this
id, t_id, value, date_timestamp, type
179328741654819, 963852456741, 499.66, 2020-09-22T15:18:17, in
This is my code: (I am using Jupyter notebook)
import json
import pandas as pd
from IPython.display import display
# load json file
pd_object = pd.read_json('file.json',typ='series')
df = pd.DataFrame(pd_object)
display(df)But it show only one column
result
Output: 0 {'id': '179328741654819', 't_values'...
200 rows × 1 columns
The json structure is:
Output: [
{
"id": "179328741654819",
"t_values": [
{
"t_id": "963852456741",
"value": "499.66",
"date_timestamp": "2020-09-22T15:18:17",
"type": "in"
},
{
"t_id": "852951753456",
"value": "1386.78",
"date_timestamp": "2020-10-31T14:46:44",
"type": "in"
}
]
},
{
"id": "823971648264792",
"t_values": [
{
"t_id": "753958561456",
"value": "672.06",
"date_timestamp": "2020-03-16T22:41:16",
"type": "in"
},
{
"t_id": "321147951753",
"value": "773.88",
"date_timestamp": "2020-05-08T18:29:31",
"type": "out"
},
{
"t_id": "258951753852",
"value": "733.13",
"date_timestamp": null,
"type": "in"
}
]
}
]
Posts: 12,033
Threads: 486
Joined: Sep 2016
Dec-05-2024, 04:32 PM
(This post was last modified: Dec-05-2024, 04:33 PM by Larz60+.)
python comes with a built-in tool, json.tool
to use: python -m json.tool jsonfilename > textfilename
|
