Python Pandas ffill or bffill with multiple condition
I have a dataset like below :
condition=[None,None,None,'condtion1',None,None,None,'conditon2',None,None,None]
add_val = [None,None,None,10,None,None,None,20,None,None,None]
df=pd.DataFrame({'event_condition':condition,'add_col':add_val})
I want to fillna of add_col in terms of condition.
When I meet condition 1 of event_condition, I want to ffill with value of 10 in a separate column called 'on_condition1'. It should not be changed for condition2. When I meet condition 2 of event_conditon, I want to ffill with value of 20 in a separate column called 'on_condition2' It should not be changed for condition1. Before meeting these conditions, I want to back fill with 0.
I tried groupby method but it did not work :
df.groupby(df['event_condition'].eq(cond).cumsum())['add_col'].ffill()
It will be like this :
condition=[None,None,None,'condtion1',None,None,None,'conditon2',None,None,None]
add_val = [None,None,None,10,None,None,None,20,None,None,None]
on_cond1=[0,0,0,None,10,10,10,None,10,10,10]
on_cond2=[0,0,0,None,0,0,0 ,None,20,20,20]
df=pd.DataFrame({'event_condition':condition,'add_col':add_val,'on_condition1':on_cond1,'on_condition2':on_cond2})
Solution
Use:
#create Series with non NaNs rows by event_condition
s = df.dropna(subset=['event_condition']).set_index('event_condition')['add_col']
#for each value add new column
for k, v in s.items():
#new column name
col = f'on_{k}'
#test all rows after match condition
m = df['event_condition'].eq(k).cummax()
#test rows for non NaNs
m1 = df['event_condition'].notna()
#set new column by 2 masks
df[col] = np.select([m1, m], [np.nan,v], 0)
print (df)
event_condition add_col on_condtion1 on_conditon2
0 None NaN 0.0 0.0
1 None NaN 0.0 0.0
2 None NaN 0.0 0.0
3 condtion1 10.0 NaN NaN
4 None NaN 10.0 0.0
5 None NaN 10.0 0.0
6 None NaN 10.0 0.0
7 conditon2 20.0 NaN NaN
8 None NaN 10.0 20.0
9 None NaN 10.0 20.0
10 None NaN 10.0 20.0
Another idea with forward fillig:
s = df.dropna(subset=['event_condition']).set_index('event_condition')['add_col']
for k, v in s.items():
col = f'on_{k}'
m = df['event_condition'].eq(k)
m1 = df['event_condition'].notna()
df[col] = np.select([m1, m.cummax()],
[np.nan, df['add_col'].where(m).ffill()], 0)
print (df)
event_condition add_col on_condtion1 on_conditon2
0 None NaN 0.0 0.0
1 None NaN 0.0 0.0
2 None NaN 0.0 0.0
3 condtion1 10.0 NaN NaN
4 None NaN 10.0 0.0
5 None NaN 10.0 0.0
6 None NaN 10.0 0.0
7 conditon2 20.0 NaN NaN
8 None NaN 10.0 20.0
9 None NaN 10.0 20.0
10 None NaN 10.0 20.0
With baxk fillig, is necsary change order for cumulative max:
s = df.dropna(subset=['event_condition']).set_index('event_condition')['add_col']
for k, v in s.items():
col = f'on_{k}'
m = df['event_condition'].eq(k)
m1 = df['event_condition'].notna()
df[col] = np.select([m1, m.iloc[::-1].cummax().iloc[::-1]],
[np.nan, df['add_col'].where(m).bfill()], 0)
print (df)
event_condition add_col on_condtion1 on_conditon2
0 None NaN 10.0 20.0
1 None NaN 10.0 20.0
2 None NaN 10.0 20.0
3 condtion1 10.0 NaN NaN
4 None NaN 0.0 20.0
5 None NaN 0.0 20.0
6 None NaN 0.0 20.0
7 conditon2 20.0 NaN NaN
8 None NaN 0.0 0.0
9 None NaN 0.0 0.0
10 None NaN 0.0 0.0
EDIT: If combine forward and back filling together:
condition=[None,None,None,'condtion1',None,None,None,'conditon2',None,None,None]
add_val = [None,None,None,10,None,None,None,20,None,None,None]
add_val1 = [None,None,None,70,None,None,None,50,None,None,None]
df=pd.DataFrame({'event_condition':condition,'add_col':add_val, 'add_col2':add_val1})
print (df)
event_condition add_col add_col2
0 None NaN NaN
1 None NaN NaN
2 None NaN NaN
3 condtion1 10.0 70.0
4 None NaN NaN
5 None NaN NaN
6 None NaN NaN
7 conditon2 20.0 50.0
8 None NaN NaN
9 None NaN NaN
10 None NaN NaN
df1 = df.dropna(subset=['event_condition']).set_index('event_condition')[['add_col','add_col2']]
print (df1)
add_col add_col2
event_condition
condtion1 10.0 70.0
conditon2 20.0 50.0
for k, v in df1['add_col2'].items():
col = f'on_{k}'
m = df['event_condition'].eq(k)
m1 = df['event_condition'].notna()
df[col] = np.select([m1, m.iloc[::-1].cummax().iloc[::-1]],
[np.nan, df['add_col2'].where(m).bfill()],
df['add_col'].where(m).ffill())
print (df)
event_condition add_col add_col2 on_condtion1 on_conditon2
0 None NaN NaN 70.0 50.0
1 None NaN NaN 70.0 50.0
2 None NaN NaN 70.0 50.0
3 condtion1 10.0 70.0 NaN NaN
4 None NaN NaN 10.0 50.0
5 None NaN NaN 10.0 50.0
6 None NaN NaN 10.0 50.0
7 conditon2 20.0 50.0 NaN NaN
8 None NaN NaN 10.0 20.0
9 None NaN NaN 10.0 20.0
10 None NaN NaN 10.0 20.0
- ImproperlyConfigured: You're using the staticfiles app without having set the STATIC_ROOT setting to a filesystem path
- With Pydantic V2 and model_validate, how can I create a "computed field" from an attribute of an ORM model that IS NOT part of the Pydantic model
- Simple log filtering with Flask
- How can I access different Anaconda environment from Pycharm (on Windows 10)
- Create regex-like (run length encoding) of string s for blocks of a given length k
- Failed scipy install using pip on Windows [Preparing metadata (pyproject.toml) did not run successfully]
- How to form tuple column from two columns in Pandas
- Filtering dictionary elements by few initial values
- Filter DataFrame events not in time windows DataFrame
- PyTorch .to(torch.device("cuda")) slow when first called?
- Finding many strings in directory
- How to set the default of a JSONField to empty list in Django and django-jsonfield?
- How to solve "OSError: telling position disabled by next() call"
- How can I stream a response from LangChain's OpenAI using Flask API?
- cannot import name 'randn_tensor' from 'diffusers.utils'
- Pylance doesn't catch all deprecated functions or classes
- Python: Cannot get attribute from superclass
- Python tkinter - grid configuration
- How do I override __getattr__ without breaking the default behavior?
- Algorithm for transformation of an 1-D-gradient into a special form of a 2-D-gradient
- Communication link failure with pyodbc
- Python - how to overwrite output from Markdown library
- Convert string based NaN's to numpy NaN's
- Selenium-Python returning empty text for one specific class or tag in HTML but not visible on webpage
- Referencing columns by assigned name in numpy array
- Pretty-print indices/coordinates of 2D Numpy array
- Can I force array numpy to keep its uint32 type?
- Reshape dataframe into a timeseries when time information is split into columns and index?
- Simple math addition
- Docker: ModuleNotFoundError: No module named 'tabulate'