Pandas DataFrame do something if a value is string

Question

I have a table with a column named 'Amount'. The cell values are mixture of numbers such as 1, 100, and 1000, and strings such as '(1000)' and '(999)' which indicates a negative value. How would I change the strings of negative values to numbers, like -1000 and -999? I don't know how to apply conditions on panda dataframes.

import pandas as pd
ws_actual = pd.read_excel(file_name, sheet_name=0)


Project Name    Amount
a   1000.53
b   (-100.2)
c   999.83
d   99999.1
e   333.62
f   (-10502.30)
g   (-2036.63)
h   25235
i   69103
j   5923
k   6920

post your sample data and your desired output

Abhi
– Abhi

2018-09-04 23:05:01 +00:00
Commented Sep 4, 2018 at 23:05 — Abhi
– Abhi, Commented Sep 4, 2018 at 23:05
added a sample image

SSS
– SSS

2018-09-04 23:12:03 +00:00
Commented Sep 4, 2018 at 23:12 — SSS
– SSS, Commented Sep 4, 2018 at 23:12
Post as text, not as an image

user3483203
– user3483203

2018-09-04 23:12:48 +00:00
Commented Sep 4, 2018 at 23:12 — user3483203
– user3483203, Commented Sep 4, 2018 at 23:12
How can I save it as text and how to upload here?

SSS
– SSS

2018-09-04 23:15:13 +00:00
Commented Sep 4, 2018 at 23:15 — SSS
– SSS, Commented Sep 4, 2018 at 23:15
I just copied and pasted the data in my question

SSS
– SSS

2018-09-04 23:16:33 +00:00
Commented Sep 4, 2018 at 23:16 — SSS
– SSS, Commented Sep 4, 2018 at 23:16

user3483203 · Accepted Answer · 2018-09-04 23:26:31Z

1

Simply use strip. Although in your question, it is ambiguous if the values in parenthesis contain negative symbols or if the parenthesis need to be replaced with a negative symbol. If it is the latter you will need a regular expression here.

df.Amount.astype(str).str.strip('()').astype(float)

0      1000.53
1      -100.20
2       999.83
3     99999.10
4       333.62
5    -10502.30
6     -2036.63
7     25235.00
8     69103.00
9      5923.00
10     6920.00
Name: Amount, dtype: float64

If you have strings such as (1000) that also need to be converted to a negative number:

print(df)

  ProjectName  Amount
0           a  (1000)
1           b     100
2           c   (999)

df.Amount.astype(str).str.replace(r'\(([-\d\.]+)\)', r'-\1').astype(float)

0   -1000.0
1     100.0
2    -999.0
Name: Amount, dtype: float64

edited Sep 4, 2018 at 23:26

answered Sep 4, 2018 at 23:16

user3483203

51.3k10 gold badges72 silver badges104 bronze badges

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

3 Comments

SSS Over a year ago

Thanks a lot!! I put df.Amount.astype(str).strip('()').replace(',',"").astype(float) since I realized that there is a comma in (). When I run this code, I get an error saying that 'ValueError: could not convert string to float: '271,796.82''. I think I removed comma, but it was obviously not successful. Could you give a solution to this?

user3483203 Over a year ago

Use .str.replace instead of just .replace

SSS Over a year ago

Yay!!! It worked with astype(str).str.replace(',',"").str.replace(r'(([-\d\.]+))', r'-\1').astype(float) !!! Thank you !!!

Collectives™ on Stack Overflow

Pandas DataFrame do something if a value is string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related