I am beginner in this journey of Python/VBA so I have a problem I kindly want do ask you.

So I have a list of rows in an excel spreadsheet (also I treat this data with Pandas to reduce the number of rows I have to analyse on Excel)

The fact is that I have dozens of thousands rows that in a specific column has values like these:

      col
0 -142.60
1  142.60
2 -565.78
3  565.78
4  -90.00
5   90.00
6   63.26
7  -63.26
8  117.96

So I just want to know how can I automatically delete rows that has a correspondent negative number and sum = 0.

I only want 117,96 row here.

CodePudding user response：

Assuming you have float in this column, you can compare the rows with the opposite of the next one, and use this information to subset only those rows.

# is the next row the opposite value?
m = df['col'].eq(-df['col'].shift())

# drop the matching rows and the next ones
df2 = df.loc[~(m|m.shift(-1))]

output:

      col
8  117.96

Used input:

      col
0 -142.60
1  142.60
2 -565.78
3  565.78
4  -90.00
5   90.00
6   63.26
7  -63.26
8  117.96

CodePudding user response：

It's not clear from the context whether the "correspondent negative number" is necessarily adjacent to the actual number. If the negative number might not be next to the positive number but is just somewhere in the array, we could do the following. Here, df is the data frame constructed from the Excel data.

# builds the same table with an additional column '_merge'.
# '_merge' column will be 'left_only' for rows whose negative
# does not appear and 'both' otherwise

df_all = df.merge(-df, how='left', indicator=True)

# selects rows with 'left_only' label

df = df.loc[df_all['_merge']=='left_only']

Input:

        0
0  -63.26
1  117.96
2 -565.78
3   63.26
4  142.60
5  565.78
6   90.00
7  -90.00
8 -142.60

Output:

        0
1  117.96