0

I'm trying to get an API response from https://www.loves.com/api/sitecore/StoreSearch/SearchStores into a Pandas Dataframe. Specifically, I'm trying to load the 'Points' array into the data frame. I've seen other posts indicate that I should perhaps use json_normalize, and I've played around with that for a day but haven't made any progress.

import json
import requests

def get(url):
    response = requests.get(url)
    parsed = json.loads(response.text)
    return json.dumps(parsed, indent=4, sort_keys=True)

from pandas.io.json import json_normalize
df = json_normalize(get(lovesLocator), 'Points')

The error I keep receiving is:

TypeError: string indices must be integers
MrBubbles
  • 405
  • 2
  • 7
  • 19

1 Answers1

1
import pandas as pd
import requests
import json
points = json.loads(requests.get("https://www.loves.com/api/sitecore/StoreSearch/SearchStores").text)[0]["Points"]
df = pd.DataFrame(points)
print(df.shape) # (580, 21)
tdpr
  • 212
  • 1
  • 4
  • Well that's excellent. A quick question though. I'm not getting all of the keys when I print the data frame. I only see: 'Address1 Address2 AddressId AddressTypeId \'. How can I get all of the keys to print in the df? – MrBubbles Feb 07 '20 at 14:18
  • This is just a display issue. Pandas naturally limits the number of columns to be shown when printing a DataFrame. You can increase this limit: pd.set_option("display.max_columns", 500) pd.set_option("display.width", 1000) For more info, check [this post](https://stackoverflow.com/questions/11707586/how-do-i-expand-the-output-display-to-see-more-columns-of-a-pandas-dataframe) – tdpr Feb 07 '20 at 14:22