Adding multiple json data to panda dataframes
I am using api to get 3 json data and I would like to add this data to 1 panda dataframes
This is my code I am passing the books which contains the id of the book as x and these 3 ids return me 3 different json objects with all the information about the book.
for x in books:
newDF = pd.DataFrame()
bookinfo = requests.get( http://books.com/?x})
books = bookinfo.json()
print(books)
These are the 3 arrays I get after printing the books,
{
u'bookInfo':[
{
u'book_created':u'2017-05-31',
u'book_rating':3,
u'book_sold':0
},
{
u'book_created':u'2017-05-31',
u'book_rating':2,
u'book_sold':1
},
],
u'book_reading_speed':u'4.29',
u'book_sale_date':u'2017-05-31'
}
{
u'bookInfo':[
{
u'book_created':u'2017-05-31',
u'book_rating':3,
u'book_sold':0
},
{
u'book_created':u'2017-05-31',
u'book_rating':2,
u'book_sold':1
},
],
u'book_reading_speed':u'4.29',
u'book_sale_date':u'2017-05-31'
}
{
u'bookInfo':[
{
u'book_created':u'2017-05-31',
u'book_rating':3,
u'book_sold':0
},
{
u'book_created':u'2017-05-31',
u'book_rating':2,
u'book_sold':1
},
],
u'book_reading_speed':u'4.29',
u'book_sale_date':u'2017-05-31'
}
What I would like to do is take u'bookInfo
from only 3 arrays and make them into 1 dataframe
+3
source to share
1 answer
IIUC:
pd.concat(
pd.DataFrame([requests.get( http://books.com/?x}).json() for x in books]),
ignore_index=True)
Alternatively, you can collect JSON responses into a list and do the following:
In [30]: pd.concat([pd.DataFrame(x['bookInfo']) for x in d], ignore_index=True)
Out[30]:
book_created book_rating book_sold
0 2017-05-31 3 0
1 2017-05-31 2 1
2 2017-05-31 3 0
3 2017-05-31 2 1
4 2017-05-31 3 0
5 2017-05-31 2 1
or
In [25]: pd.DataFrame([y for x in d for y in x['bookInfo']])
Out[25]:
book_created book_rating book_sold
0 2017-05-31 3 0
1 2017-05-31 2 1
2 2017-05-31 3 0
3 2017-05-31 2 1
4 2017-05-31 3 0
5 2017-05-31 2 1
where d
is the list of dicts you placed:
In [20]: d
Out[20]:
[{'bookInfo': [{'book_created': '2017-05-31',
'book_rating': 3,
'book_sold': 0},
{'book_created': '2017-05-31', 'book_rating': 2, 'book_sold': 1}],
'book_reading_speed': '4.29',
'book_sale_date': '2017-05-31'},
{'bookInfo': [{'book_created': '2017-05-31',
'book_rating': 3,
'book_sold': 0},
{'book_created': '2017-05-31', 'book_rating': 2, 'book_sold': 1}],
'book_reading_speed': '4.29',
'book_sale_date': '2017-05-31'},
{'bookInfo': [{'book_created': '2017-05-31',
'book_rating': 3,
'book_sold': 0},
{'book_created': '2017-05-31', 'book_rating': 2, 'book_sold': 1}],
'book_reading_speed': '4.29',
'book_sale_date': '2017-05-31'}]
0
source to share