question

Upvotes
Accepted
0 1 1 3

reshape dataframe with pd.pivot_table - missing data

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?

eikoneikon-data-apipythonworkspacerefinitiv-dataplatform-eikonworkspace-data-apidatatime-seriespandaspivot-table
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
Accepted
39.4k 78 11 27

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.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
0 1 1 3

The pivot table function correctly sorted the data into rows.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.