Resolved: fill values ONLY BETWEEN rows in a pandas DataFrame

In this post, we will see how to resolve fill values ONLY BETWEEN rows in a pandas DataFrame

Question:

I am working on a project with some pricing data for second hand cars, due to the nature of the data there are gaps in the dataframe I am working with.
I need to fill the gaps in the columns, but only if they are BETWEEN two values (i.e. there are values both above the Nan).
Here is a simplified example of what I am working with:

import pandas as pd
from datetime import datetime

date_index = pd.date_range(datetime(2023,2,1), datetime(2023,2,8))

prices_dict = {
    
    'car1': [1000, 1000, np.nan, np.nan, 1200, 1250, np.nan, np.nan],
    'car2': [np.nan, np.nan, np.nan, 500, 600, 610, 630, np.nan],
    'car3': [np.nan, 1000, np.nan, 950, np.nan, 950, np.nan, np.nan],
    'car4': [np.nan, 790, np.nan, np.nan, np.nan, 800, 750, 750],
}

prices_df = pd.DataFrame(data=prices_dict, index=date_index)

It looks like this:

--------    car1    car2    car3    car4
2023-02-01  1000.0          
2023-02-02  1000.0         1000.0   790.0
2023-02-03              
2023-02-04          500.0   950.0   
2023-02-05  1200.0  600.0       
2023-02-06  1250.0  610.0   950.0   800.0
2023-02-07          630.0           750.0
2023-02-08                          750.0

I have done some digging and know of the existence of .ffil() .bfill() and .fillna() but these seem to be uniquely forward or backwards filling. I need to fill forwards but only for blanks between two values.
The expected outcome is

--------    car1    car2    car3    car4
2023-02-01  1000.0          
2023-02-02  1000.0         1000.0   790.0
2023-02-03  1000.0         1000.0   790.0
2023-02-04  1000.0  500.0   950.0   790.0
2023-02-05  1200.0  600.0   950.0   790.0
2023-02-06  1250.0  610.0   950.0   800.0
2023-02-07          630.0           750.0
2023-02-08                          750.0

Many thanks!

Best Answer:

You can use interpolate with right parameters:

>>> prices_df.interpolate(method='pad', limit_area='inside', axis=1)
              car1   car2    car3   car4
2023-02-01  1000.0    NaN     NaN    NaN
2023-02-02  1000.0    NaN  1000.0  790.0
2023-02-03  1000.0    NaN  1000.0  790.0
2023-02-04  1000.0  500.0   950.0  790.0
2023-02-05  1200.0  600.0   950.0  790.0
2023-02-06  1250.0  610.0   950.0  800.0
2023-02-07     NaN  630.0     NaN  750.0
2023-02-08     NaN    NaN     NaN  750.0

If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com

Resolved: fill values ONLY BETWEEN rows in a pandas DataFrame

Resolved: How to make statement case insensitive when uploading a dta file with specified columns?

Resolved: Sort dataframe columns value by close it to another column

Resolved: PostgreSQL resample 1 minute OHLCV candle data into 5 minute OHLCV candle data

Question:

Best Answer:

Related Posts

Resolved: How to make statement case insensitive when uploading a dta file with specified columns?

Resolved: Sort dataframe columns value by close it to another column

Resolved: PostgreSQL resample 1 minute OHLCV candle data into 5 minute OHLCV candle data