Last occurrence of a Groupby object under certain conditions

Refresh	February 2019
Views	18 time

Let's say I have a DataFrame that looks like this:

    Categories  Values
0   Category 0       1
1   Category 0       0
2   Category 0      -1
3   Category 0       0
4   Category 1       1
5   Category 1       0
6   Category 1      -1
7   Category 1       0
8   Category 2       1
9   Category 2       0
10  Category 2      -1
11  Category 2       0
12  Category 3      -1
13  Category 3       0
14  Category 3       0
15  Category 3       1
16  Category 4      -1
17  Category 4       0
18  Category 4       0
19  Category 4       1
20  Category 5      -1
21  Category 5       0
22  Category 5       0
23  Category 5       1

I want a time-efficient way to get two things of the last non-zero entries of Values of each group:

(1):the indices,

(2):the entries

The desired output of (1) is: [2,6,10,15,19,23] in the form of pandas Series

The desired output of (2) is: [-1,-1,-1,1,1,1] in the form of pandas Series

Thank you in advance guys

EDIT: added the python code for generating the above DataFrame:

import pandas as pd

n = 4
m = 3
df = pd.DataFrame({'Categories': [f'Category {i//n}' for i in range(2*m*n)],
                   'Values' : [1,0,-1,0]*m+ [-1,0,0,1]*m})

mathguy

3 answers

Используйте boolean indexingдля фильтров только не равных 0значений с DataFrame.drop_duplicatesпомощью столбца Categoriesс только сохранить последний простофиля:

df1 = df[df['Values'].ne(0)].drop_duplicates('Categories', 'last')
print (df1)
    Categories  Values
2   Category 0      -1
6   Category 1      -1
10  Category 2      -1
15  Category 3       1
19  Category 4       1
23  Category 5       1

print (df1.index.tolist())
[2, 6, 10, 15, 19, 23]

print (df1['Values'].tolist())
[-1, -1, -1, 1, 1, 1]

jezrael

один из способов решения этой проблемы,

df['value']=df.groupby('Categories')['Values'].transform(lambda x: x.loc[x[::-1].ne(0).argmax()])
df['index']=df.groupby('Categories')['Values'].transform(lambda x: x[::-1].ne(0).argmax())

Примечание: Возможно, это не эффективный способ решить эту проблему, но я попробовал это простое решение для вас.

O / P:

    Categories  Values  value  index
0   Category 0       1     -1      2
1   Category 0       0     -1      2
2   Category 0      -1     -1      2
3   Category 0       0     -1      2
4   Category 1       1     -1      6
5   Category 1       0     -1      6
6   Category 1      -1     -1      6
7   Category 1       0     -1      6
8   Category 2       1     -1     10
9   Category 2       0     -1     10
10  Category 2      -1     -1     10
11  Category 2       0     -1     10
12  Category 3      -1      1     15
13  Category 3       0      1     15
14  Category 3       0      1     15
15  Category 3       1      1     15
16  Category 4      -1      1     19
17  Category 4       0      1     19
18  Category 4       0      1     19
19  Category 4       1      1     19
20  Category 5      -1      1     23
21  Category 5       0      1     23
22  Category 5       0      1     23
23  Category 5       1      1     23

Mohamed Thasin ah

Я первый фильтр ненулевых строк, в GroupBy:

In [11]: df1 = df[df.Values != 0]

In [12]: df1[df1.groupby("Categories")["Values"].transform(lambda x: x == x.iloc[-1])]
Out[12]:
    Categories  Values
2   Category 0      -1
6   Category 1      -1
10  Category 2      -1
15  Category 3       1
19  Category 4       1
23  Category 5       1

In [13]: df1[df1.groupby("Categories")["Values"].transform(lambda x: x == x.iloc[-1])].index
Out[13]: Int64Index([2, 6, 10, 15, 19, 23], dtype='int64')

Andy Hayden

Related Issues

Last questions

Hot tags

javascript java c# python android php jquery c++ html ios css sql mysql .net c r asp.net ruby-on-rails objective-c arrays node.js sql-server iphone regex ruby angularjs json swift django linux asp.net-mvc xml wpf angular spring string ajax git python-3.x excel windows xcode multithreading database pandas bash reactjs scala algorithm eclipse html5 vba performance macos postgresql mongodb laravel oracle visual-studio list vb.net wordpress linq entity-framework winforms unit-testing matlab typescript image python-2.7 perl twitter-bootstrap numpy css3 qt swing hibernate c++11 shell apache amazon-web-services haskell maven forms azure facebook powershell rest function excel-vba delphi tsql ruby-on-rails-3 symfony google-chrome api firebase docker dataframe oop