reshape dataframe with pd.pivot_table - missing data

Options
mohammed_sabur
mohammed_sabur Newcomer
in Eikon Data APIs

Hi, I've used the following line to retrieve time series monthly total returns together with the respective dates. unitr, e = ek.get_data(unilist,['TR.TotalReturn.date','TR.TotalReturn'],parameters={'SDate':'2018-02-01','EDate':'2019-04-30','Frq':'M','CH':'Fd'}) (Where unilist is the list of FTSE 350 constituents). I'm then trying to reshape the three columns but because there are missing dates (that appear as blank where stocks don't have sufficient history) I'm unable to reshape the data using pd.pivot_table. Ultimately I would like every date to be filled even if no return is available. Do you have any suggestions?

Tagged:

Best Answer

  • Alex Putkov.1
    Alex Putkov.1 ✭✭✭✭✭
    Answer ✓

    I'm not sure I understand what the end goal is here. If you're happy to drop the rows where data is not available you can use pandas replace and dropna methods. See an example on this thread. But this will result in non-uniform timeseries for different stocks. The alternative might be to fill the blanks with something. But then you need to define what you want to fill the blanks with that would make sense for your use case. One choice might be to fill a blank with the previous total return value (i.e. the value for the previous month), but I don't think it would make sense for all use cases. And it will only work where previous total return value is available.

Answers

Categories