how to reindex python dataframe based on column grouping

user_11974121 0
Viewed 82

i am a newbie in python. please assist. I have a huge dataframe consisting of thousands of rows. an example of the df is shown below.

        STATE        VOLUME
INDEX           
1       on         10
2       on         15
3       on         10
4       off        20
5       off        30
6       on         15
7       on         20
8       off        10
9       off        30
10      off        10
11      on         20
12      off        25

i want to be able to index this data based on the 'state' column such that the first batch of 'on' and 'off' registers as index 1, the next batch of 'on' and 'off' registers as index 2 etc etc... i want to be able to select a group of data if i select rows with index 1.

       ID        VOLUME
INDEX           
1       on         10
1       on         15
1       on         10
1       off        20
1       off        30
2       on         15
2       on         20
2       off        10
2       off        30
2       off        10
3       on         20
3       off        25
pandas rows indexing python
2 Answers

You can try this with pd.Series.eq with pd.Series.shift and take cumsum using pd.Series.cumsum

df.index = (df['STATE'].eq('off').shift() & df['STATE'].eq('on')).cumsum() + 1
df.index.name = 'INDEX'

      STATE  VOLUME
INDEX
1        on      10
1        on      15
1        on      10
1       off      20
1       off      30
2        on      15
2        on      20
2       off      10
2       off      30
2       off      10
3        on      20
3       off      25

Details

The idea is to find where an off is followed by an on.

# (df['STATE'].eq('off').shift() & df['STATE'].eq('on')).cumsum() + 1

      eq(off).shift  eq(on)  eq(off).shift & eq(on)
INDEX
1               NaN    True                   False
2             False    True                   False
3             False    True                   False
4             False   False                   False
5              True   False                   False
6              True    True                    True
7             False    True                   False
8             False   False                   False
9              True   False                   False
10             True   False                   False
11             True    True                    True
12            False   False                   False
user_124164530

You could try this with pd.Series.shift and pd.Series.cumsum:

df.index=((df.STATE.shift(-1) != df.STATE)&df.STATE.eq('off')).shift(fill_value=0).cumsum()+1

Same as this with np.where:

temp=pd.Series(np.where((df.STATE.shift(-1) != df.STATE)&(df.STATE.eq('off')),1,0))
df.index=temp.shift(1,fill_value=0).cumsum().astype(int).add(1)

Output:

df
  STATE  VOLUME
1    on      10
1    on      15
1    on      10
1   off      20
1   off      30
2    on      15
2    on      20
2   off      10
2   off      30
2   off      10
3    on      20
3   off      25

Explanation: With (df.STATE.shift(-1) != df.STATE)&df.STATE.eq('off'), you will get a mask with the last value when it changes to 'off':

(df.STATE.shift(-1) != df.STATE)&df.STATE.eq('off')

1     False
2     False
3     False
4     False
5      True
6     False
7     False
8     False
9     False
10     True
11    False
12     True

Then you shift it to include that last value, and then you do a cumsum() knowing that True: 1 and False: 0:

((df.STATE.shift(-1) != df.STATE)&df.STATE.eq('off')).shift(fill_value=0)
1         0
2     False
3     False
4     False
5     False
6      True
7     False
8     False
9     False
10    False
11     True
12    False

((df.STATE.shift(-1) != df.STATE)&df.STATE.eq('off')).shift(fill_value=0).cumsum()
1     0
2     0
3     0
4     0
5     0
6     1
7     1
8     1
9     1
10    1
11    2
12    2

And finally you add 1(+1) to the index, to get the desired result.

user_136762020
Related