Don't include rows if data is unavailable

student210
student210 Newcomer
in Eikon Data APIs

Hello, I am trying to get some factor and price data for different companies that have ESG scores available as well. My code looks like:

df, err = ek.get_data("SCREEN(U(IN(Equity(active,public,primary))/*UNV:Public*/), IN(TR.ExchangeCountryCode,""SE""), TR.TRESGScore(Period=FY0)>0, CURN=USD)",
                      ["TR.PriceClose", "TR.PriceClose.Date", "TR.EBITMarginPercent", "TR.PretaxROAPercent", "TR.FreeOperatingCashFlowExclDividends","TR.RevenueSmartEstLastYrGrowth" , "TR.FreeCashFlow", "TR.WACCBeta"],
                      {'SDate': '2010-01-01', 'EDate': '2022-01-29','Frq':'M', 'SORTD': 'date'})

The only problem is that some companies don't have price data for all periods, but the empty periods are still included as NaN. Is there a easy way to make so that if there is no price data at date n, that whole row is not included.

Thanks in advance!

Tagged:

Best Answer

  • raksina.samasiri
    raksina.samasiri admin
    Answer ✓

    hi @student210

    To investigate this, RIC "BMAX.ST", which is one of the results of this screening query, was picked to check the output dataframe, please see it in detail below.

    1. There're rows with NaN and <NA> in the output dataframe (see screenshot 1 in the reply due to the limit of image per each comment)
    2. First, about the TR.PriceClose of BMAX.ST, its data's been available since its IPO date
      To remove the row before that, you may update the SDate and EDate Parameters or use the code below
      df3 = df3[df3['Date'] != '']
      1643605918991.png
    3. Next, to clarify how the output dataframe is returned, the date of each field is included as its parameter. It's shown that each row doesn't represent each date but fields are merged into the dataframe without joining each field's date
      1643606132572.png

    So I'd like to suggest you do the step below

    1. get the list of RIC from the screener criteria
      df1, err = ek.get_data("SCREEN(U(IN(Equity(active,public,primary))/*UNV:Public*/), IN(TR.ExchangeCountryCode,""SE""), TR.TRESGScore(Period=FY0)>0, CURN=USD)", "TR.RIC")
      rics = df1["RIC"].to_list()
    2. then use the list of RICs above as input instruments to get data from each set of data that return the same date-set (you may check this using CodeCreator app in Eikon or ask content specialist via MyRefinitivto clarify the content)
      df2, err = ek.get_data(rics,
                           ["TR.PriceClose", "TR.PriceClose.Date"],
                           {'SDate': '2010-01-01', 'EDate': '2022-01-29','Frq':'M', 'SORTD': 'date'})
      df2
    3. use the dataframe from the previous step and join them together if needed

    Hope this could help, please tell me in case you have any further questions

Answers

Categories