Replace NaN in dictionary-CodePudding

I have this code that pulls financial instruments info (ETF's) out of an API:

import xlwings as xw
import requests

# Open Excel file and reference ETF names and countries worksheet:
excel_file = xw.Book("""FinMkt.xlsm""")
wsCategparssel = excel_file.sheets["""ETFs Selec"""]

# Payload parameters intros objects:
parETFintro = "name="
parCountryintro = "&country="

# Payload parameters objects pulled from ETF names and countries worksheet:
parETFs = wsCategparssel.range("D2#").options(ndim=1).value
parCountries = wsCategparssel.range("B2#").options(ndim=1).value

# API URL:
url = "https://investing4.p.rapidapi.com/etfs/get-etf-information"

# Loop pull payload for each ETF/country pairing and get each respective response:
for parETF, parCountry in zip(parETFs, parCountries):
    payload =  parETFintro   parETF   parCountryintro   parCountry
    headers = {
        "content-type": "application/x-www-form-urlencoded", 
        "X-RapidAPI-Key": "MY API KEY", 
        "X-RapidAPI-Host": "investing4.p.rapidapi.com"
        }

    response = requests. Request("POST", url, data=payload, headers=headers)

Both parETFs and ParCountries are parameters pulled out of an Excel worksheet dynamic range, which for this example contains 2 items that return the following when print(response. Text):

{"data":{"1-Year Change":"- 4.4%","52 wk Range":"4.7 - 493","Asset Class":NaN,"Average Vol. (3m)":154991.0,"Beta":null,"Dividend Yield":"0.38%","Dividends (TTM)":1.8,"ETF Name":"iShares Self-Driving EV and Tech","Market Cap":null,"Open":4.75,"Prev. Close":4.75,"ROI (TTM)":"-","Shares Outstanding":null,"Todays Range":"4.75 - 4.75","Total Assets":null,"Volume":16129.0},"message":"Success!","status":200}
{"data":{"1-Year Change":"- 11.25%","52 wk Range":"196.61 - 233.92","Asset Class":NaN,"Average Vol. (3m)":779.0,"Beta":null,"Dividend Yield":null,"Dividends (TTM)":null,"ETF Name":"Amundi Euro Corporates UCITS","Market Cap":null,"Open":204.14,"Prev. Close":203.92,"ROI (TTM)":"-","Shares Outstanding":null,"Todays Range":"204.14 - 204.91","Total Assets":null,"Volume":null},"message":"Success!","status":200}

In both instruments Asset Class returns NaN and because of that I keep getting the we found extra characters at the end of the json input error when I try to use its transformed/exported json file into Power Query, so I need to replace those Nan say by - as a workaround.

So I've added a few lines of code towards the end, however the last line {key: "-" if value == np.isnan else value for key, value in data_only.items()} just won't solve my problem as Asset Class still returns NaN when I print(data_only_clean).

import xlwings as xw
import requests
import numpy as np
import json

# Open Excel file and reference ETF names and countries worksheet:
excel_file = xw.Book("""FinMkt.xlsm""")
wsCategparssel = excel_file.sheets["""ETFs Selec"""]

# Payload parameters intros objects:
parETFintro = "name="
parCountryintro = "&country="

# Payload parameters objects pulled from ETF names and countries worksheet:
parETFs = wsCategparssel.range("D2#").options(ndim=1).value
parCountries = wsCategparssel.range("B2#").options(ndim=1).value

# API URL:
url = "https://investing4.p.rapidapi.com/etfs/get-etf-information"

# Loop pull payload for each ETF/country pairing and get each respective response:
for parETF, parCountry in zip(parETFs, parCountries):
    payload =  parETFintro   parETF   parCountryintro   parCountry
    headers = {
        "content-type": "application/x-www-form-urlencoded", 
        "X-RapidAPI-Key": "MY API KEY", 
        "X-RapidAPI-Host": "investing4.p.rapidapi.com"
        }

    response = requests. Request("POST", url, data=payload, headers=headers)
    # Transform the response to JSON format:
    data_list = response.json()
    # Add conditional to skip to next item in case some returns code 400:
    if data_list["status"] != 200:
        continue
    # Extract "data" piece out of the response:
    data Only = data_list["data"]
    # Replace NaN by "-"":
    data_only_clean = {key: "-" if value == np.isnan else value for key, value in data_only.items()}

How can I replace any returned value NaN that might show up under any dictionary key in future API responses by something else that won't give me headaches, such as null or -?

Thanks in advance!

Leonardo

CodePudding user response：

it seems that the way np.isnan is used is wrong:

The way it's supposed to be used is rather np.isnan(value) instead of value==np.isnan

so your last line should be the following :

data_only_clean = {key: "-" if np.isnan(value) else value for key, value in data_only.items()}

if this doesn't work, you can also try to use math.isnan(...) (I hat that problem once):

data_only_clean = {key: "-" if mathisnan(value) else value for key, value in data_only.items()}