How to handle a KeyError(key) from err error?

Question

Some of the ticker symbols are causing the code below to fail.

Is there a way to:

1. Identify which tickers are causing the code to fail (via a log, ECT) so I can remove them?
2. Ignore failing tickers and still write to my db?
3. Use any other workaround to get my data (even partially) into my db?

March 20th code

import pandas as pd
from quickfs import QuickFS as qf
import os
import json
from sqlalchemy import create_engine
from contextlib import suppress
import sys
import logging

# load th key 
api_key = '783afaac81792374538967ad9ef72226f07be115'
client = qf(api_key)


#Technology_Software_Infrastructure
tickers = ['ATEN:US','BB:US','BKI:US','BOX:US','CCCS:US','CINT:US','DAVA:US','DOCN:US','ETWO:US','EVTC:US','FLT:US','FORG:US','FOUR:US','GDDY:US','INFA:US','IOT:US','MFGP:US','MSP:US','NET:US','NEWR:US','ORCL:US','PAGS:US','PATH:US','PING:US','PLAN:US','PLTR:US','RAMP:US','S:US','SAIL:US','SOS:US','SQ:US','SQSP:US','STEM:US','SWI:US','TIXT:US','TOST:US','TUFN:US','TUYA:US','VHC:US','VMW:US','WEX:US','YEXT:US','ZUO:US','APCX:US','MDB:US','IIIV:US','AUID:US','INTZ:US','RPAY:US','RDWR:US','LLNW:US','MARK:US','MSFT:US','NTCT:US','CRWD:US','BAND:US','ARQQ:US','EPAY:US','FFIV:US','FIVN:US','MNDT:US','IMXI:US','HCP:US','VERI:US','PRTH:US','SUMO:US','SNCR:US','SNPS:US','VRAR:US','TCX:US','GSKY:US','ARBE:US','UEPS:US','VRNS:US','VRNT:US','VRSN:US','WIX:US','RXT:US','OSPN:US','DLO:US','PAYA:US','CGNT:US','SCWX:US','BL:US','DTSS:US','NVEI:US','ZENV:US','ACIW:US','ADBE:US','AKAM:US','ALLT:US','AVDX:US','BLIN:US','CHKP:US','CSGS:US','CYBR:US','CYRN:US','DOX:US','EEFT:US','REKR:US','CISO:US','ZS:US','PAYO:US','GET:US','EVOP:US','LYLT:US','APPN:US','AFRM:US','DBX:US','HOOD:US','CCSI:US','STER:US','MIME:US','BLZE:US','KNBE:US','TENB:US','KLTR:US','BASE:US','OKTA:US','ALTR:US','RELY:US','NLOK:US','CFLT:US','CETX:US','TAOP:US','QLYS:US','SPLK:US','SFET:US','FTNT:US','STNE:US','NTNX:US','SPSC:US','PANW:US','ALF:US','PAYS:US','GBOX:US']
#tickers = ['ATEN:US','BB:US']

# Selected metrics in the API resp = client.get_available_metrics()
metrics = ['period_end_date','revenue','gross_profit','rnd','operating_income','eps_diluted','shares_diluted','total_opex','total_current_assets','total_assets','accounts_payable','st_debt','total_current_liabilities','lt_debt','total_liabilities','total_equity','total_liabilities_and_equity','book_value','tangible_book_value','cfo_net_income','cf_cfo','cf_cfi','cf_cff','fcf','roa','roe','roic','roce','rotce','gross_margin','operating_margin','net_income_margin','fcf_margin','assets_to_equity','equity_to_assets','debt_to_equity','debt_to_assets','book_value_per_share','tangible_book_per_share','revenue_per_share','operating_income_per_share','fcf_per_share','revenue_growth','operating_income_growth','net_income_growth','eps_diluted_growth','shares_diluted_growth','total_assets_growth','total_equity_growth','fcf_growth','market_cap','period_end_price','price_to_fcf','price_to_earnings','price_to_book','price_to_tangible_book','price_to_sales','intangible_assets','goodwill','shares_eop','dividends','income_allocated_to_minority_interest','cash_and_equiv','noncurrent_capital_leases','minority_interest_liability']



#tickers = ['ADCT:US']
df = pd.DataFrame(client.get_data_batch(companies=tickers, metrics=metrics, period='FQ90:FQ'))

#if not df.shape[1]:
#   print('Empty dataframe')
#return

while True:
    try:
        ex = df.explode(list(metrics))
        engine = create_engine('postgresql://user:9999@localhost:9999/schema')
         # exit the loop since the operation succeeded
        ex.to_sql('TECHNOLOGY_SOFTWARE_INFRASTRUCTURE', engine, schema='COLLECT') 
        break
    except KeyError as e:
            key = e.args[0]  

            if key in metrics:
                print(f'bad metric {key}')
                metrics.remove(key)
            elif key in tickers:
                print(f'bad ticker {key}')
                tickers.removr(key)
            else:
                print(f'{key} is neither a metric nor a ticket')
print(ex)
      
print(df.keys())

March 20th message

ECT...
bad metric income_allocated_to_minority_interest
bad metric cash_and_equiv
bad metric noncurrent_capital_leases
bad metric minority_interest_liability
Traceback (most recent call last):
  File "/Users/kevin/Dropbox/My Mac (Kevin’s MacBook Pro)/Documents/GitHub/python3/deleteme.py", line 35, in <module>
    ex = df.explode(list(metrics))
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/frame.py", line 8239, in explode
    raise ValueError("column must be nonempty")
ValueError: column must be nonempty
[Finished in 10.6s with exit code 1]
[cmd: ['python3', '-u', '/Users/kevin/Dropbox/My Mac (Kevin’s MacBook Pro)/Documents/GitHub/python3/deleteme.py']]
[dir: /Users/kevin/Dropbox/My Mac (Kevin’s MacBook Pro)/Documents/GitHub/python3]
[path: /Library/Frameworks/Python.framework/Versions/3.9/bin:/Library/Frameworks/Python.framework/Versions/3.10/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin]

Answer 1

To answer your questions:

1. The tickets are not the problem, at least not from the error, as it says: KeyError: **'period_end_date'** and period_end_date is a metric. My guess is that you do not have that column in your dataframe at all. Because from the documentation of explode define here it should work with None

2. You can get the name of the failing metric as such, from here:

try:
    # code here
except KeyError as e:    
    failing_metric = e.args[0]

3. A workaround would be to get your dataframe and retry the operation until it succeeds:

#tickers = ['ADCT:US']
df = pd.DataFrame(client.get_data_batch(companies=tickers, metrics=metrics, period='FQ1:FQ'))

# We can't do anything with no data, so we return from the function
if not df.shape[1]:
    print('Empty dataframe')
    return

while True:
    try:
        ex = df.explode(list(metrics))
        engine = create_engine('postgresql://postgres:9999@localhost:9999/xx')

        # exit the loop since the operation succeeded
        ex.to_sql('TECHNOLOGY_SOFTWARE_INFRASTRUCTURE', engine, schema='COLLECT') 
        break
    except KeyError as e:
        key = e.args[0]
        
       if key in metrics:
           print(f'bad metric {key}')
           metrics.remove(key)
       elif key in tickers:
           print(f'bad ticker {key}')
           tickers.removr(key)
       else:
           print(f'{key} is neither a metric nor a ticket')