Historical Yearly ESG Data

EBO

I'm using the Refinitiv Python API (refinitiv-data) to pull ESG data (2013–2024) for multiple companies. I want a wide format like this:

I use the rd.get_history command (see screenshot below), and get a very large dataset with date in column 1, and instrument + variable for each column filled with many N/A values as the values are only available yearly. I have attached a photo of both, my formula and the output.

How can I:

• Get just year-end values for ESG metrics?
• Reshape to the wide format above

Thanks a lot!

Jirapongse

@EBO

Thank you for reaching out to us.

The code could be like this:

import pandas as pd
pd.set_option('future.no_silent_downcasting', True)

df = ld.get_data(
    universe = ['BOUY.PA','ENI.MI','IBM.N'],
    fields = ["TR.NumberofEmployees.fperiod","TR.NumberofEmployees", "TR.TemporaryStaffProportion", "TR.NumberofPartTimeEmployees", "TR.TargetsDiversityOpportunity", "TR.HealthSafetyTraining", "TR.EmployeeSatisfaction", "TR.AnalyticSalaryGap", "TR.SalariesCSRreporting", "TR.AnalyticEmploymentCreation", "TR.EmployeesCSRreporting", "TR.TradeUnionRep", "TR.TurnoverEmployees", "TR.AnalyticLayoffs", "TR.AnnouncedLayoffs", "TR.MgtDepartures"],
    parameters = {
        'Frq':'FY',
        'SDate':'-10Y',
        'EDate':'0Y',
        'Period':'FY0'
    })    
df_list = []
for column in df.columns[2:]:
    df_temp = df.melt(id_vars=['Instrument','Financial Period Absolute'], value_vars=column)
    df_list.append(df_temp.pivot(index=['Instrument','variable'], columns='Financial Period Absolute', values=['value']))

pd.concat(df_list)

The output is:

I used the df.melt() and df.pivot() functions to reformat the DataFrame. While this may not be the optimal method, it can give you an idea of how to approach the reformatting

wasin.w

Hello @EBO

My colleague wrote an https://developers.lseg.com/en/article-catalog/article/dataframe-manipulation-with-pandas-a-beginners-guide article. This article might help you too.

EBO

Thank you for the helpful answers! :) It worked.