JSON-converting dictionary object to dataframe

Question

[{
"last_upd_date": "2018-10-20T05:33:29",
"class_abbr_name": "-",
"reference_period": "1_year",
"performance_type_desc": "ความผันผวนของกองทุนรวม",
"performance_val": "9.9300",
"as_of_date": "29 มิถุนายน 2561"
}, {
"last_upd_date": "2018-10-20T05:33:29",
"class_abbr_name": "-",
"reference_period": "1_year_percentile",
"performance_type_desc": "ความผันผวนของกองทุนรวม",
"performance_val": "75.0000",
"as_of_date": "29 มิถุนายน 2561"
}, {
"last_upd_date": "2018-10-20T05:33:29",
"class_abbr_name": "-",
"reference_period": "10_year",
"performance_type_desc": "ความผันผวนของกองทุนรวม",
"performance_val": "20.8700",
"as_of_date": "29 มิถุนายน 2561"
}, {
"last_upd_date": "2018-10-20T05:33:29",
"class_abbr_name": "-",
"reference_period": "10_year_percentile",
"performance_type_desc": "ความผันผวนของกองทุนรวม",
"performance_val": "95.0000",
"as_of_date": "29 มิถุนายน 2561"
}]

I'm connecting to the API, and I want to extract the data to a dataframe. all the data above are only for one key. Maybe can I put this into a hierarchy table? Code

for proj_id in all_funds.proj_id:
req = limiter.call_get_api(url=f'https://api.sec.or.th/FundFactsheet/fund/{proj_id}/performance')
print(proj_id)
new_type = pd.read_json(req.content, typ='series', lines=True)
print(new_type)
past_return = past_return.append([{'proj_id': proj_id, 'performance_type_desc': new_type.get('performance_type_desc'),'performance_val':new_type.get('performance_val'),'as_of_date':new_type.get('as_of_date') }], ignore_index=True)

I've tried this, but it doesn't work since all the entry is all none. Are they any ways to put all this in a dataframe? It can be in any form.

Thank you so much

Please post your data and code as text. Images make it very difficult for others to try and run things. — AKX, Oct 20 '18 at 07:02
Possible duplicate of [Convert list of dictionaries to Dataframe](https://stackoverflow.com/questions/20638006/convert-list-of-dictionaries-to-dataframe) — jimmy, Oct 20 '18 at 07:15
It is a duplicate ;) Palm, if you google searched: 'site:stackoverflow.com your problem', dozen of similar questions and answers would have pop up ;) — Prayson W. Daniel, Oct 20 '18 at 07:25

score 1 · Answer 1 · answered Oct 20 '18 at 07:30

This is what you need, if you looking to convert your data into Pandas DataFarme, however you can get several answers on the SO if you google around it..

>>> import pandas as pd
>>> dj = [{
... "last_upd_date": "2018-10-20T05:33:29",
... "class_abbr_name": "-",
... "reference_period": "1_year",
... "performance_type_desc": "ความผันผวนของกองทุนรวม",
... "performance_val": "9.9300",
... "as_of_date": "29 มิถุนายน 2561"
... }, {
... "last_upd_date": "2018-10-20T05:33:29",
... "class_abbr_name": "-",
... "reference_period": "1_year_percentile",
... "performance_type_desc": "ความผันผวนของกองทุนรวม",
... "performance_val": "75.0000",
... "as_of_date": "29 มิถุนายน 2561"
... }, {
... "last_upd_date": "2018-10-20T05:33:29",
... "class_abbr_name": "-",
... "reference_period": "10_year",
... "performance_type_desc": "ความผันผวนของกองทุนรวม",
... "performance_val": "20.8700",
... "as_of_date": "29 มิถุนายน 2561"
... }, {
... "last_upd_date": "2018-10-20T05:33:29",
... "class_abbr_name": "-",
... "reference_period": "10_year_percentile",
... "performance_type_desc": "ความผันผวนของกองทุนรวม",
... "performance_val": "95.0000",
... "as_of_date": "29 มิถุนายน 2561"
... }]

Your Dictionary Data:

>>> dj
[{'last_upd_date': '2018-10-20T05:33:29', 'class_abbr_name': '-', 'reference_period': '1_year', 'performance_type_desc': 'ความผันผวนของกองทุนรวม', 'performance_val': '9.9300', 'as_of_date': '29 มิถุนายน 2561'}, {'last_upd_date': '2018-10-20T05:33:29', 'class_abbr_name': '-', 'reference_period': '1_year_percentile', 'performance_type_desc': 'ความผันผวนของกองทุนรวม', 'performance_val': '75.0000', 'as_of_date': '29 มิถุนายน 2561'}, {'last_upd_date': '2018-10-20T05:33:29', 'class_abbr_name': '-', 'reference_period': '10_year', 'performance_type_desc': 'ความผันผวนของกองทุนรวม', 'performance_val': '20.8700', 'as_of_date': '29 มิถุนายน 2561'}, {'last_upd_date': '2018-10-20T05:33:29', 'class_abbr_name': '-', 'reference_period': '10_year_percentile', 'performance_type_desc': 'ความผันผวนของกองทุนรวม', 'performance_val': '95.0000', 'as_of_date': '29 มิถุนายน 2561'}]

convert Your Json Dict Data to Pandas DataFrame:

>>> df = pd.DataFrame(dj)  # df if your Pandas DataFrame

Your DataFrame:

>>> df
         as_of_date class_abbr_name        last_upd_date  \
0  29 มิถุนายน 2561               -  2018-10-20T05:33:29
1  29 มิถุนายน 2561               -  2018-10-20T05:33:29
2  29 มิถุนายน 2561               -  2018-10-20T05:33:29
3  29 มิถุนายน 2561               -  2018-10-20T05:33:29

    performance_type_desc performance_val    reference_period
0  ความผันผวนของกองทุนรวม          9.9300              1_year
1  ความผันผวนของกองทุนรวม         75.0000   1_year_percentile
2  ความผันผวนของกองทุนรวม         20.8700             10_year
3  ความผันผวนของกองทุนรวม         95.0000  10_year_percentile

You are Welcome to the SO platform. – Karn Kumar Oct 20 '18 at 07:47 — Karn Kumar, Oct 20 '18 at 07:47

JSON-converting dictionary object to dataframe

1 Answers1