Data Categorization and clearing

user_6370335 0
Viewed 60

I'm writing a logic for KPI calculation and struggling to find a correct algorithm to get the first (min) date when event occured. We have:

Type   Date        Code   Order
 1   01/01/2020   123    A
-1   02/01/2020   123    A
 1   01/02/2020   321    B
-1   03/02/2020   321    B
 1   04/02/2020   321    B
 1   01/06/2020   111    C
 1   02/06/2020   111    C
 1   02/06/2020   111    C
-1   03/06/2020   111    C
 1   01/07/2020   222    D
 1   01/08/2020   333    E
 1   01/08/2020   333    E
-1   01/08/2020   333    E
-1   01/08/2020   333    E
 1   02/08/2020   333    E

Rules: PAIRS in col 'Type' that sum into 0 for same 'Code', 'Order' are removed. What I want to do is to get either ranking or category such that:

Type   Date        Code  Order Category
 1   01/01/2020   123    A     Remove
-1   02/01/2020   123    A     Remove
 1   01/02/2020   321    B     Remove
-1   03/02/2020   321    B     Remove
 1   04/02/2020   321    B     Keep
 1   01/06/2020   111    C     Keep
 1   02/06/2020   111    C     Keep
 1   02/06/2020   111    C     Remove
-1   03/06/2020   111    C     Remove
 1   01/07/2020   222    D     Keep
 1   01/08/2020   333    E     Remove
 1   01/08/2020   333    E     Remove
-1   01/08/2020   333    E     Remove
-1   01/08/2020   333    E     Remove
 1   02/08/2020   333    E     Keep

Once this is done I can use pivot_table to get min of Date for each Code-Order. Categorization of what to remove and what to keep is a struggle.

I tried to compare with next value in row to see if that works and it does, but not for all cases. I tried

df["Match"] =  df.Type.eq(df.Type.shift(periods=-1))

but it does not work for cases when I have only one row for each Order and Code, especially if it is the last one in the row.

pandas python
2 Answers

It looks like a stack puzzle : stack when "1", unstack when "-1", print what remains.

sample = iter("""Type   Date    Code    Order
1   01/01/2020  123 A
-1  02/01/2020  123 A
1   01/02/2020  321 B
-1  03/02/2020  321 B
1   04/02/2020  321 B
1   01/06/2020  111 C
1   02/06/2020  111 C
1   02/06/2020  111 C
-1  03/06/2020  111 C
1   01/07/2020  222 D
1   01/08/2020  333 E
1   01/08/2020  333 E
-1  01/08/2020  333 E
-1  01/08/2020  333 E
1   02/08/2020  333 E""".split('\n'))

from collections import defaultdict

# one stack per (code, order)
D = defaultdict(list)

h = next(sample)  # keep headers

for r in sample:
    (t,_,c,o) = r.split('\t')
    if t == "1":
        D[(c,o)].append(r)
    else:
        D[(c,o)].pop()

print(h)
for (_,v) in D.items():
    for e in v:
        print(e)

output :

Type    Date    Code    Order
1       04/02/2020      321     B
1       01/06/2020      111     C
1       02/06/2020      111     C
1       01/07/2020      222     D
1       02/08/2020      333     E
user_57633270

You can do shift(-1) and shift() to compare previous and next. And you can check if first row is 1 and second row is -1.

When I ran this against your dataset,

df.drop(df[((df['Code']==df['Code'].shift(-1)) & (df['Type'] == 1)  & (df['Type'].shift(-1) == -1)) |
           ((df['Code']==df['Code'].shift())   & (df['Type'] == -1) & (df['Type'].shift() == 1))].index,inplace=True)

I got the following results:

    Type        Date  Code Order
4      1  04/02/2020   321     B
5      1  01/06/2020   111     C
6      1  02/06/2020   111     C
9      1  01/07/2020   222     D
10     1  01/08/2020   333     E
13    -1  01/08/2020   333     E
14     1  02/08/2020   333     E

Running it once more removed the additional matched records.

df.drop(df[((df['Code']==df['Code'].shift(-1)) & (df['Type'] == 1)  & (df['Type'].shift(-1) == -1)) |
           ((df['Code']==df['Code'].shift())   & (df['Type'] == -1) & (df['Type'].shift() == 1))].index,inplace=True)

Once I ran it twice, I got the final result:

    Type        Date  Code Order
4      1  04/02/2020   321     B
5      1  01/06/2020   111     C
6      1  02/06/2020   111     C
9      1  01/07/2020   222     D
14     1  02/08/2020   333     E

I need to figure out how to reduce the cycle so it gets done in one go. Also, I am calling the dataframe a few times so it may not be very effective. I need to figure out a much faster and less memory intense solution. For now, the above code works and solves your problem.

user_138739800
Related