How to replace gap rows in pandas

user_16251865 0
Viewed 86

I have a pandas dataframe with historical stock prices. The issue is that some columns contain prices up to a certain date, then some blank rows, and finally price data again. This is an example:

    date      Stock A   Stock B Stock C
0   6/30/1990   0.19    NaN     Nan
1   7/31/1990   0.19    NaN     Nan
2   8/31/1990   0.25    NaN     15.4
3   9/30/1990   0.34    NaN     17.4
4   10/31/1990  NaN     1.5     17
5   11/30/1990  NaN     1.8     NaN
6   12/31/1990  NaN     2.1     NaN
7   1/31/1991   NaN     2       NaN
8   2/28/1991   NaN     2.1     NaN
9   3/31/1991   NaN     NaN     NaN
10  4/30/1991   20.88   NaN     20.88
11  5/31/1991   18.25   NaN     18.25
12  6/30/1991   17      NaN     17
13  7/31/1991   17.25   NaN     17.25
14  8/31/1991   17.5    NaN     17.5

So what I am trying to do is in this dataframe is: -the columns like Stock A to replace all the rows above the last non-NaN value with a NaN value, in this case, replace all the values from the first 4 rows with NaNs -the columns like Stock B to leave them like that, since there are no gaps -the columns like Stock C, with more than 1 gap, keep only the last values. The resulting dataframe should be like this:

    date      Stock A   Stock B Stock C
0   6/30/1990   NaN     NaN     Nan
1   7/31/1990   NaN     NaN     Nan
2   8/31/1990   NaN     NaN     NaN
3   9/30/1990   NaN     NaN     NaN
4   10/31/1990  NaN     1.5     NaN
5   11/30/1990  NaN     1.8     NaN
6   12/31/1990  NaN     2.1     NaN
7   1/31/1991   NaN     2       NaN
8   2/28/1991   NaN     2.1     NaN
9   3/31/1991   NaN     NaN     NaN
10  4/30/1991   20.88   NaN     20.88
11  5/31/1991   18.25   NaN     18.25
12  6/30/1991   17      NaN     17
13  7/31/1991   17.25   NaN     17.25
14  8/31/1991   17.5    NaN     17.5

I have tried to do it manually in Excel, since I am fairly new to Python, but given the number of columns, the process is taking too long.

The solution would be related to iterating between all the columns and check wether the condition explained above checks, and then modifying such columns.

What could be a possible solution?

pandas python
2 Answers

This should do it for you.

import pandas as pd
import numpy as np

df = pd.DataFrame({'date': {0: '6/30/1990',
  1: '7/31/1990',
  2: '8/31/1990',
  3: '9/30/1990',
  4: '10/31/1990',
  5: '11/30/1990',
  6: '12/31/1990',
  7: '1/31/1991',
  8: '2/28/1991',
  9: '3/31/1991',
  10: '4/30/1991',
  11: '5/31/1991',
  12: '6/30/1991',
  13: '7/31/1991',
  14: '8/31/1991'},
 'Stock': {0: np.nan,
  1: 18.25,
  2: np.nan,
  3: np.nan,
  4: 18.25,
  5: np.nan,
  6: np.nan,
  7: np.nan,
  8: np.nan,
  9: np.nan,
  10: 20.88,
  11: 18.25,
  12: 17.0,
  13: 17.25,
  14: 17.5}})

# find the index value of last np.nan occurence
idx = np.where(df['Stock'].isnull())[-1][-1]

# Use np.where() to update values with index value below nan_idx
df['Stock'] = np.where(df.index < idx , np.nan , df['Stock'])

Output df:

    date        Stock
0   6/30/1990   NaN
1   7/31/1990   NaN
2   8/31/1990   NaN
3   9/30/1990   NaN
4   10/31/1990  NaN
5   11/30/1990  NaN
6   12/31/1990  NaN
7   1/31/1991   NaN
8   2/28/1991   NaN
9   3/31/1991   NaN
10  4/30/1991   20.88
11  5/31/1991   18.25
12  6/30/1991   17.00
13  7/31/1991   17.25
14  8/31/1991   17.50

If you want to do it on all your columns you can use a simple for loop.

for col in df.columns:
    idx = np.where(df[col].isnull())[-1][-1]
    df[col] = np.where(df.index < idx , np.nan , df[col])

Edit: New solution now that we have more sample in and out data.

df = pd.DataFrame({'date': {0: '6/30/1990',
  1: '7/31/1990',
  2: '8/31/1990',
  3: '9/30/1990',
  4: '10/31/1990',
  5: '11/30/1990',
  6: '12/31/1990',
  7: '1/31/1991',
  8: '2/28/1991',
  9: '3/31/1991',
  10: '4/30/1991',
  11: '5/31/1991',
  12: '6/30/1991',
  13: '7/31/1991',
  14: '8/31/1991'},
 'StockA': {0: 0.19,
  1: 0.19,
  2: 0.19,
  3: 0.25,
  4: 0.34,
  5: np.nan,
  6: np.nan,
  7: np.nan,
  8: np.nan,
  9: np.nan,
  10: 20.88,
  11: 18.25,
  12: 17.0,
  13: 17.25,
  14: 17.5},
 'StockB': {0: np.nan,
  1: np.nan,
  2: np.nan,
  3: np.nan,
  4: 1.5,
  5: 1.8,
  6: 2.1,
  7: 2.0,
  8: 2.1,
  9: np.nan,
  10: np.nan,
  11: np.nan,
  12: np.nan,
  13: np.nan,
  14: np.nan},
 'StockC': {0: np.nan,
  1: np.nan,
  2: 17.4,
  3: 17.4,
  4: np.nan,
  5: np.nan,
  6: np.nan,
  7: np.nan,
  8: np.nan,
  9: np.nan,
  10: 20.88,
  11: 18.25,
  12: 17,
  13: 17.25,
  14: 17.5}})

The updated question is a bit more complicated than the original, but if we identify how many nan and non-nan clusters there are, we can use their lengths with some conditionals to get the desired result.

from itertools import groupby

for col in df.columns:
    if col.startswith('Stock'):
        
        # these list comps tell us how many clusters of nans and non-nans we have
        nan_cluster_list = [len(list(g)) for k, g in groupby(df[col].isnull()) if k]
        non_nan_cluster_list =  [len(list(g)) for k, g in groupby(df[col].notnull()) if k]
        
        # Stock A
        if len(nan_cluster_list) < len(non_nan_cluster_list):
            idx = np.where(df[col].isnull())[0][-1]
            df[col] = np.where(df.index < idx , np.nan , df[col])
            
        # Stock B, this could be taken out, just here for understanding logic
        elif len(nan_cluster_list) > 1 and len(non_nan_cluster_list) == 1:
            pass

        # Stock C
        elif len(nan_cluster_list) > 1 and len(non_nan_cluster_list) > 1:
            idx = np.where(df[col].isnull())[0][-1]
            df[col] = np.where(df.index < idx , np.nan , df[col])

Output df:

    date        StockA  StockB  StockC
0   6/30/1990   NaN     NaN     NaN
1   7/31/1990   NaN     NaN     NaN
2   8/31/1990   NaN     NaN     NaN
3   9/30/1990   NaN     NaN     NaN
4   10/31/1990  NaN     1.5     NaN
5   11/30/1990  NaN     1.8     NaN
6   12/31/1990  NaN     2.1     NaN
7   1/31/1991   NaN     2.0     NaN
8   2/28/1991   NaN     2.1     NaN
9   3/31/1991   NaN     NaN     NaN
10  4/30/1991   20.88   NaN     20.88
11  5/31/1991   18.25   NaN     18.25
12  6/30/1991   17.00   NaN     17.00
13  7/31/1991   17.25   NaN     17.25
14  8/31/1991   17.50   NaN     17.50
user_73274830

try this:

df.loc[:df.Stock.isnull().idxmax(), 'Stock'] = None
print(df)
>>>
    date        Stock
0   6/30/1990   NaN
1   7/31/1990   NaN
2   8/31/1990   NaN
3   9/30/1990   NaN
4   10/31/1990  NaN
5   11/30/1990  NaN
6   12/31/1990  NaN
7   1/31/1991   NaN
8   2/28/1991   NaN
9   3/31/1991   NaN
10  4/30/1991   20.88
11  5/31/1991   18.25
12  6/30/1991   17.00
13  7/31/1991   17.25
14  8/31/1991   17.50
user_167556710
Related