reshape dataframe with pd.pivot_table - missing data

mohammed_sabur

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?

Alex Putkov.1

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.

mohammed_sabur

The pivot table function correctly sorted the data into rows.

mohammed_sabur

The pivot table function correctly sorted the data into rows.