Don't include rows if data is unavailable

student210

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!

raksina.samasiri

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'] != '']

   
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
   

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