Set rows before a certain row in pandas without iterating

user_8261345 0
Viewed 112

I have a column in a pandas dataframe where I want to change any uninterrupted sequences of 'normal' preceding a 'large' to 'large.' I do not want to replace all instances of 'normal' in the column with 'large.' For example, I want to change the left column to the right column below:

input result
small small
small small
normal large
normal large
large large
small small
normal normal
small small
normal large
large large

This is straightforward with iteration:

for i, v in df['input'].iterrows():
    if v == 'large':
        index = i
        while df['input'].iloc(index-1) == 'normal':
            df['input'].iloc(index-1) = 'large'
            index -= 1

However this is inefficient. Is there a neat vectorised way to do this?

pandas vectorization dataframe python
2 Answers

try using bfill with where:

df1.loc[df1['input'].eq("normal")]  = np.nan
df1['result'] = df1.fillna(method='bfill').where(df1.notnull() | (df1.shift(-1) != 'small')).fillna('normal')

df1:

    input   result
0   small   small
1   small   small
2   NaN     large
3   NaN     large
4   large   large
5   small   small
6   NaN     normal
7   small   small
8   NaN     large
9   large   large
user_66603730

Here's one approach using bfill.

See Pandas docs for fillna for further information

import pandas as pd

input_frame = pd.Series([
    'small',
    'small',
    'normal',
    'normal',
    'large',
    'small',
    'normal',
    'small',
    'normal',
    'large',
]).to_frame()
# Change 'normal' to None in order to use bfill
input_frame = input_frame.replace({'normal': None})
input_frame['bfilled'] = input_frame[0].bfill()
# Change rows that were not bfilled to 'large' back to 'normal'
input_frame.loc[
    (input_frame['bfilled'] != 'large') & input_frame[0].isna(),
    'bfilled'
] = 'normal'
# Select the result, essentially drop the original column
result = input_frame['bfilled']

Sample outputs:

>>> input_frame = input_frame.replace({'normal': None})
>>> input_frame['bfilled'] = input_frame[0].bfill()
>>> input_frame
       0 bfilled
0  small   small
1  small   small
2   None   large
3   None   large
4  large   large
5  small   small
6   None   small  <--- This should be changed
7  small   small
8   None   large
9  large   large
>>> # Select the row(s) that need to be changed with
>>> input_frame.loc[(input_frame['bfilled'] != 'large') & input_frame[0].isna()]
      0 bfilled
6  None   small
user_98333490
Related