Jan-14-2019, 01:35 AM
I am not sure that I did all right, but the following example I just wrote should handle
your task:
In this example I am using numbers to fill these columns for simplicity.
your task:
import pandas as pd data = pd.DataFrame({'A': ['A'] * 10 + ['B'] * 7, # Customer 'B': ['C'] * 7 + ['D'] * 10, # Account 'From': [1, None, 2, None, 5, None, None, 7, None, 8, 10, 12, 14, None, 15, None, None], #From date 'To': [2, None, 3, None, 6, None, None, 8, None, 9, 11, 13, 15, None, 16, None, None], # To date 'N': [None] * 17}) # Missing or Not missing, all values aren't defined by default def fill_data(df): result = df.copy() both_empty = df.From.isnull() & df.To.isnull() result.loc[((df.From == df.To.shift(2)) & (~df.From.isnull()) & (~df.To.shift(2).isnull())).shift(-1) & both_empty, 'N'] = 'NM' result.loc[((df.From != df.To.shift(2)) & (~df.From.isnull()) & (~df.To.shift(2).isnull())).shift(-1) & both_empty, 'N'] = 'M' return result result = data.groupby(['A', 'B']).apply(fill_data) print(result.reset_index(drop=True))Note: You will need to convert 'From' and 'To' to datetime objects (see pd.to_datetime function).
In this example I am using numbers to fill these columns for simplicity.