0

I am trying to get total esg scores from 2016-2021 for all companies in S&P500, but not every ticker has esg scores and some of them does not have data for 2016-2021.

My current code look like this:

import yesg
df = pd.DataFrame()
sp500 = pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')[0]
tickers = sp500['Symbol'].tolist()
for t in tickers:
  data=yesg.get_historic_esg(t)['Total-Score']
  df.append(data)

Please tell me how to get the data I need. Thanks in advance for your help.

anonim
  • 67
  • 5

1 Answers1

1

You can try something like this :

sp500 = pd.read_html("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies")[0]
tickers = sp500["Symbol"].tolist()

scores_ok = []
scores_ko = []
for t in tickers:
    if (ser := yesg.get_historic_esg(t)) is not None:
        scores_ok.append(ser.loc[:, "Total-Score"].rename(t))
    else:
        scores_ko.append(t)
    
df = pd.concat(scores_ok, axis=1)

Output :

#503 tickers | (448 OK, 55 KO)

print(df)

             MMM    AOS   ABT  ...   ZBH  ZION   ZTS
Date                           ...                  
2014-09-01  73.0    NaN  64.0  ...  61.0  47.0  47.0
2014-10-01  72.0    NaN  64.0  ...  61.0  47.0  47.0
2014-11-01  73.0    NaN  64.0  ...  61.0  47.0  47.0
...          ...    ...   ...  ...   ...   ...   ...
2022-11-01   NaN    NaN   NaN  ...   NaN   NaN   NaN
2022-12-01   NaN    NaN   NaN  ...   NaN   NaN   NaN
2023-01-01   NaN  25.43   NaN  ...  27.3   NaN   NaN

[101 rows x 448 columns]
Timeless
  • 22,580
  • 4
  • 12
  • 30
  • Tanks a lot for your answer but I get the following error:An error has occurred. The ticker symbol might be wrong or you might need to wait to continue. – anonim Apr 15 '23 at 08:58
  • 1
    No, the code actually works (*otherwise, I c-wouldn't add it as an answer ;)*). What you're seeing is not an error but just a message [`print`](https://docs.python.org/fr/3/library/functions.html#print)ed by the function `get_historic_esg` when the data is not available for a given ticker. You need to wait until the code stops running (*and ignore the messages..*) and after that you'll get the exact DataFrame that I show in my answer/output. Good luck ;) – Timeless Apr 15 '23 at 09:01