how to build dataframe by using pd.read_json with url to read a column as a string

user_14947360 0
Viewed 111

Using the following format with pd.read_csv and the converter, I am able to retrieve the data from the api call and the Ticker symbols (called "code" here), I don't have the leading zeros stripped off. * api keys changed in string, will not work.

eg.

exchanges = ["SHE"]
for ex in exchanges:
    df = pd.read_csv(f'https://eodhistoricaldata.com/api/eod-bulk-last-day/{ex}? 
       api_token=5f13457294ba20.004391&date={str(today)}',
        converters={'Code': lambda x: str(x)})

results:

code exchange_short_name date ... close adjusted_close volume

000001 SHE 2021-11-17 ... 18.11 18.11 66464038

000002 SHE 2021-11-17 ... 19.46 19.46 62374971

Now, When switching to pd.read_json method, I can no longer use the converter function I used before to convert to string. As a result, those same tickers are having the leading zeros removed.

exchanges = ["SHE"]
for ex in exchanges:

    df = pd.read_json(f'https://eodhistoricaldata.com/api/eod-bulk-last-day/{ex}? 
       api_token=5f1323ba20.00238991&fmt=json&date={str(today)}')

results:

code exchange_short_name date ... close adjusted_close volume

1 SHE 2021-11-17 ... 18.11 18.11 66464038

2 SHE 2021-11-17 ... 19.46 19.46 62374971

Here is the data as seen in the actual api call via browser.

[{"code":"000001","exchange_short_name":"SHE","date":"2021-11-17","open":18.15,"high":18.3,"low":17.98,"close":18.11,"adjusted_close":18.11,"volume":66464038},{"code":"000002","exchange_short_name":"SHE","date":"2021-11-17","open":19.23,"high":19.53,"low":19.09,"close":19.46,"adjusted_close":19.46,"volume":62374971},

How can I modify that pd.read_json to read at least one column (code) in as a string?

pandas python json
1 Answers

Similar to converters, when adopting read_json use the dtype parameter passing a dict for converting values in certain columns.

df = pd.read_json(f'https://eodhistoricaldata.com/api/...', dtype={'code':str})
user_131441430
Related