Change First Occurence

user_19634240 0
Viewed 31

I have a table that follows this format:

Index Another header Value
index1 a True
a True
a True
b True
index2 c True
index2 c True
index2 c True

The list is ordered by date (least recent to most recent). For each index, I want the most recent value in 'Another header' to remain True while changing the rest to False. And if the value only occurs once, it will become False. It should look something like this:

Index Another header Value
index1 a False
a False
a True
b False
index2 c False
c False
c True

Does anyone know how I can do this? All help is appreciated!

pandas dataframe python
1 Answers

Assuming the Value is already set to True, use boolean indexing:

g = df.groupby(['Index', 'Another header'])

# 0 = first of each group
m1 = g.cumcount().eq(0)
# 0 = last of each group
m2 = g.cumcount(False).ne(0)

df.loc[(m1|m2), 'Value'] = False

If the value is not yet set:

df['Value'] = ~(m1|m2)

output:

    Index Another header  Value
0  index1              a  False
1  index1              a  False
2  index1              a   True
3  index1              b  False
4  index2              c  False
5  index2              c  False
6  index2              c   True

intermediates:

    Index Another header  Value  cumcount     m1  cumcount2     m2  m1|m2
0  index1              a  False         0   True          2   True   True
1  index1              a  False         1  False          1   True   True
2  index1              a   True         2  False          0  False  False
3  index1              b  False         0   True          0  False   True
4  index2              c  False         0   True          2   True   True
5  index2              c  False         1  False          1   True   True
6  index2              c   True         2  False          0  False  False
user_163434640
Related