Python - Pandas - Fill missing data based on existing Levels

Question

I use pandas and get my data from a SQL database
I have two tickers. One is a U.S stock, the other an European stock. Dates are not necessarily the same for both stocks (holidays, etc).
All my data is stored in a multi-index DataFrame.
Looking to fill missing values based on levels

Running the below code:

import pandas as pd
import datetime
ticker_date = [('US',datetime.date.today()-datetime.timedelta(3)),
('US',datetime.date.today()-datetime.timedelta(2)),
('US',datetime.date.today()-datetime.timedelta(1)),
('EU',datetime.date.today()-datetime.timedelta(3)),
('EU',datetime.date.today()-datetime.timedelta(1))]
index_df = pd.MultiIndex.from_tuples(ticker_date)
example = pd.DataFrame([12.2,12.5,12.6,15.1,15],index_df,['value'])

Output:

Output from code above

I am looking for a method to reshape my output filling the missing data with the previous value:

Objective: add a dec 11th line and fill with previous value

I'm not sure what's going on with your previous question where you posted this code as an "answer" to what is essentially the same question? — roganjosh
– roganjosh, Commented Dec 13, 2017 at 14:11
The objective was to formulate the question in a more clear and readable way — ruh
– ruh, Commented Dec 13, 2017 at 14:13
Except it leaves the other question in some limbo state. You should have edited the code into that existing question and not posted code as an answer if it doesn't answer the issue. — roganjosh
– roganjosh, Commented Dec 13, 2017 at 14:14
Didn't know I could edit the code into an existing question. New in stackoverflow. I will keep that in mind for my next question. Thxs — ruh
– ruh, Commented Dec 13, 2017 at 14:17

MaxU - stand with Ukraine · Accepted Answer · 2017-12-13 16:39:04Z

1

I'd do it this way:

In [24]: idx = pd.MultiIndex.from_product((
                   example.index.get_level_values(0).unique(), 
                   example.index.get_level_values(1).unique()))

In [25]: example = example.reindex(idx).ffill()

In [26]: example
Out[26]:
               value
US 2017-12-10   12.2
   2017-12-11   12.5
   2017-12-12   12.6
EU 2017-12-10   15.1
   2017-12-11   15.1
   2017-12-12   15.0

answered Dec 13, 2017 at 16:39

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ruh Over a year ago

Works perfectly! Thanks a lot

MaxU - stand with Ukraine Over a year ago

@ruh, glad it helps! :-)

ruh Over a year ago

Done, didn't know this feature!

Lloyd · Accepted Answer · 2019-11-26 19:07:12Z

0

You need to be careful with this solution. It does not account for crossing the primary index. For instance,

import pandas as pd
import datetime
ticker_date = [ ('US',datetime.date.today()-datetime.timedelta(3)),
                ('US',datetime.date.today()-datetime.timedelta(2)),
                ('US',datetime.date.today()-datetime.timedelta(1)),
                ('EU',datetime.date.today()-datetime.timedelta(2)),
                ('EU',datetime.date.today()-datetime.timedelta(1))]
index_df = pd.MultiIndex.from_tuples(ticker_date)
example = pd.DataFrame([12.2,12.5,12.6,15.1,15],index_df,['value'])

idx = pd.MultiIndex.from_product((
        example.index.get_level_values(0).unique(), 
        example.index.get_level_values(1).unique()))

example = example.reindex(idx).ffill()
print(example)

produces:

               value
US 2019-11-23   12.2
   2019-11-24   12.5
   2019-11-25   12.6
EU 2019-11-23   12.6  <== 
   2019-11-24   15.1
   2019-11-25   15.0

answered Nov 26, 2019 at 19:07

Lloyd

11 bronze badge

1 Comment

Das_Geek Over a year ago

Welcome to Stack Overflow! Please edit your answer and reword it so it addresses the question directly. As it's written, it appears to address the other answer. This is generally considered bad practice, as those kinds of clarifications should be done through comments. But since you don't currently have enough reputation to comment, rephrasing your answer will do :)

Collectives™ on Stack Overflow

Python - Pandas - Fill missing data based on existing Levels

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related