Having some trouble while using regex and pandas

Question

Iam working in a project to convert some invoices in PDFs to .xlsx for comparision reasons, but i ran into some trouble, in the conversion phase the program separated the minus signal from negative numbers, so what iam trying to do is, use regex to iterate through the name columns (Where the minus signal went) and match lines with a regex, if it matches it multiplicates the values column by -1 or concatenate a minus in front of the number, but i tried both ways but neither of them changed the values column.

Here's the Dataframe

          date                                    name               value
519  25/02/2022                              LOREM IPSUM            598,72
520  25/02/2022                              LOREM IPSUM            656,56
523  25/02/2022                              LOREM IPSUM -          220,32
524  25/02/2022                              LOREM IPSUM -          339,76

The result I expect is

         date                                    name               value
519  25/02/2022                              LOREM IPSUM            598,72
520  25/02/2022                              LOREM IPSUM            656,56
523  25/02/2022                              LOREM IPSUM -         -220,32
524  25/02/2022                              LOREM IPSUM -         -339,76

I tried using

r1 = re.compile(r"- $|-$")

for item in diference["name"]:
  if r1.match(item):
    diference["value"] = diference["value"]*(-1)

And

r1 = re.compile(r"- $|-$")

 for item in diference["name"]:
   if r1.match(item):
     diference["value"] = "-" + diference["value"]

But as i said neither of them gave me an error nor changed something

So, you have commas as decimal separators? And you want to keep them? Or convert to floats with . as decimal separator? What is the type of the value column? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 9, 2022 at 18:09
Yes commas are the decimal separator, when iam comparing values from two different columns i have to change the commas to dots, and change it back to commas after comparing, because it is the way we use it here in my country, so it doesn't really matter if they stay dots or commas. The only thing i want is to convert the values in the row that match regex to negative. — João Pedro
– João Pedro, Commented Jun 9, 2022 at 18:13

Wiktor Stribiżew · Accepted Answer · 2022-06-09 18:22:34Z

2

You can use

df['value'] = pd.to_numeric(df['value'].str.replace(',', '.'))
df.loc[df['name'].str.endswith('-'), 'value'] *= -1

Details

df['value'] = pd.to_numeric(df['value'].str.replace(',', '.')) converts the string numbers to numbers
df.loc[df['name'].str.endswith('-'), 'value'] *= -1 multiplies with -1 all values in value column where the name column ends with a -.

See a Pandas test:

import pandas as pd
df= pd.DataFrame({'name': ['LOREM IPSUM ', 'LOREM IPSUM -'], 'value':['598,72', '339,76']})
df['value'] = pd.to_numeric(df['value'].str.replace(',', '.'))
df.loc[df['name'].str.endswith('-'), 'value'] *= -1

Output:

>>> df
            name   value
0   LOREM IPSUM   598.72
1  LOREM IPSUM - -339.76

answered Jun 9, 2022 at 18:22

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Having some trouble while using regex and pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related