How to match leading and trailing hyphen

user_13065899 0
Viewed 95

I have a Pandas dataframe with decimal values like below. '+' and '-' signs can either be leading or trailing.

df = pd.DataFrame({'amt': ['11.11', '+22.22', '33.33+', '-44.44', '55.55-', '66.66', '77.77', '8a8', '99', '97-9']})
    
    df['amt']
0     11.11
1    +22.22
2    33.33+
3    -44.44
4    55.55-
5     66.66
6     77.77
7       8a8
8        99
9      97-9
Name: amt, dtype: object

My requirement is to:

  1. Remove leading and trailing '+'
  2. Move trailing '-' to leading '-'

This is what I have done so far:

abs_ser = pd.to_numeric(df['amt'].str.strip().str.strip('+|-'), errors='coerce')
abs_ser
0    11.11
1    22.22
2    33.33
3    44.44
4    55.55
5    66.66
6    77.77
7      NaN
8    99.00
9      NaN
Name: amt, dtype: float64

df['clean_amt'] = np.where(df['amt'].str.match(r'(^-|-$)'), abs_ser * -1, abs_ser)
df[['amt', 'clean_amt']]
    amt clean_amt
0   11.11   11.11
1   +22.22  22.22
2   33.33+  33.33
3   -44.44  -44.44
4   55.55-  55.55
5   66.66   66.66
6   77.77   77.77
7   8a8     NaN
8   99      99.00
9   97-9    NaN

The regular expression is not matching the trailing '-'. Can someone help with correcting the regular expression?

I have tried the following and it gives me the desired result. However, I prefer the regex if it can do it in one pass of 'amt' column.

df['clean_amt'] = np.where((df['amt'].str.startswith('-') | df['amt'].str.endswith('-')), abs_ser * -1, abs_ser)
pandas regex
2 Answers

You can use

abs_ser = pd.to_numeric(df['amt'].str.strip().str.replace(r'^\+|\+$|^(.+)(-)$', r'\2\1'), errors='coerce')

See the regex demo.

Details

  • ^\+ - finds a + at the start
  • \+$ - finds a + at the end
  • ^(.+)(-)$ - captures any one or more chars at the start of the string (capturing the text into Group 1) and then captures a - at the end of the string into Group 2.

The replacement is the concatenated Group 2 and 1 values.

user_38329700

You could do the following:

# strip +
df['amt'] = df['amt'].str.strip('+')

# move -
mask = df['amt'].str.contains('-$')
df.loc[mask, 'amt'] = '-' + df.loc[mask, 'amt'].str.rstrip('-')

# transform to numeric
res = pd.to_numeric(df['amt'], errors='coerce')

print(res)

Output

0    11.11
1    22.22
2    33.33
3   -44.44
4   -55.55
5    66.66
6    77.77
7      NaN
8    99.00
9      NaN
Name: amt, dtype: float64
user_40015920
Related