How to remove successive similar numbers in a pandas DF column

user_11534261 0
Viewed 48

I have a pandas DF with a column - this column can have 3 values, either 0, 1 or ' ' (see example below). What I want to do is remove all successive numbers that are similar. So a 0 can never be followed by a 0 and a 1 can never be followed by 1. Instead I want to replace these by a ' '.

Current pandas DF

time value
1:00 0
2:00
3:00 0
4:00 1
5:00
6:00
7:00 1
8:00 1
9:00 0

What I want

time value
1:00 0
2:00
3:00
4:00 1
5:00
6:00
7:00
8:00
9:00 0

I tried to work with loops, but cannot find a clean way to refer to 'the next same value'. Any simple solution for this?

pandas python-3.x loops
2 Answers

An itertools solution:

from itertools import chain, groupby

df.value = list(chain.from_iterable(
                          [key, *['']*(len(list(gr))-1)]
                          for key, gr in groupby(df.value.replace("", np.nan).ffill())
                      )
           )
  • replaceing empty strings with np.nan
  • forward filling the NaNs to get streams of 0's and 1's
  • grouping by 0's and 1's
  • placing back the key (which is 0 or 1) along with some empty strings (group's length - 1)
  • flattening these blocks with chain.from_iterable
  • casting to a list to assign it back to the dataframe

to get

   time value
0  1:00     0
1  2:00
2  3:00
3  4:00     1
4  5:00
5  6:00
6  7:00
7  8:00
8  9:00     0
user_93321870

We can use loc on value to drop the rows having empty strings, then shift and compare the filtered rows to create a boolean mask, next mask the values with empty string where the boolean mask holds True

s = df['value'].loc[lambda x: x != '']
m = s.eq(s.shift())
df.loc[m[m].index, 'value'] = ''

   time value
0  1:00   0
1  2:00      
2  3:00      
3  4:00   1
4  5:00      
5  6:00      
6  7:00      
7  8:00      
8  9:00   0
user_128331660
Related