CODEWITHSUNDEEP
pythonpandas
J.D
J.D

Reputation: 1971

How to slice rows in each group from the first row to the row which contains a specific value?

I have a dataframe like this:

df_1 = pd.DataFrame({
    'ID' : ['A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C', 'C'],
    'VAL' : ['shoes', 'flowers', 'chairs', 'apples', 'dice', 'shoes', 'apples',
             'curtain', 'sand', 'socks', 'necklacs', 'tables', 'dishes', 'apples'],
    'SEQ' : [0, 1, 2, 3, 4, 0, 1, 2, 3, 0, 1, 2, 3, 4]
})

   ID       VAL  SEQ
0   A     shoes    0
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
4   A      dice    4
5   B     shoes    0
6   B    apples    1
7   B   curtain    2
8   B      sand    3
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4

I want to slice rows that until a value, for example, slice all rows from each ID group that until apple:

Out[110]: 
   ID       VAL  SEQ
0   A     shoes    0
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
4   B     shoes    0
5   B    apples    1
6   C     socks    0
7   C  necklacs    1
8   C    tables    2
9   C    dishes    3
10  C    apples    4

Upvotes: 2

Views: 145

Answers (3)

cs95
cs95

Reputation: 402593

GroupBy.cumsum is your friend:

mask = (df_1['VAL'].eq('apples')
                   .shift()
                   .astype(float)
                   .groupby(df_1['ID'])
                   .cumsum()
                   .lt(1))
df_1[mask]

   ID       VAL  SEQ
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
5   B     shoes    0
6   B    apples    1
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4

If it is possible an ID ends with the term you're looking for, the shift solution above (while convenient) will be inappropriate. Use GroupBy.apply with cumsum instead:

mask = (df_1['VAL'].eq('apples')
                   .groupby(df_1['ID'])
                   .apply(lambda x: x.shift().fillna(0).cumsum())
                   .lt(1))
df_1[mask]

   ID       VAL  SEQ
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
5   B     shoes    0
6   B    apples    1
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4

Upvotes: 4

BENY
BENY

Reputation: 323306

I am using transform

df_1[df_1.index<=df_1.VAL.eq('apples').groupby(df_1['ID']).transform('idxmax')]
Out[856]: 
   ID       VAL  SEQ
0   A     shoes    0
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
5   B     shoes    0
6   B    apples    1
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4

Upvotes: 2

piRSquared
piRSquared

Reputation: 294338

idxmax, groupby, concat

pd.concat([
    d.loc[:d.VAL.eq('apples').idxmax()]
    for _, d in df_1.groupby('ID')
])

   ID       VAL  SEQ
0   A     shoes    0
1   A   flowers    1
2   A    chairs    2
3   A    apples    3
5   B     shoes    0
6   B    apples    1
9   C     socks    0
10  C  necklacs    1
11  C    tables    2
12  C    dishes    3
13  C    apples    4

Upvotes: 5

Related Questions