Convert DataFrame with 'N/As' to float to compute percent change

Question

I am trying convert the following DataFrame (contains several 'N/As') to float so that I can perform a percent change operation:

d = pd.DataFrame({"A":['N/A','$10.00', '$5.00'], 
           "B":['N/A', '$10.00', '-$5.00']})

Ultimately, I would like the result to be:

(UPDATE: I do not want to remove the original N/A values. I'd like to keep them there as placeholders.)

Because there aren't any flags for dealing with negative numbers, I cannot use:

pct_change(-1)

So, I need to use:

d['A'].diff(-1)/d['A'].shift(-1).abs()

But, I get the error:

TypeError: unsupported operand type(s) for -: 'str' and 'str'

For a first step, I am trying to convert the data from object/string to float, but the output is unexpected (to me). I am getting float 'NaNs' instead of the actual number.

>d['A_float'] = pd.to_numeric(d['A'], errors='coerce')
>d
        A         B  A_float
0     N/A       N/A      NaN
1  $10.00  -$100.00      NaN
2   $5.00    -$5.00      NaN
>d.dtypes
A           object
B           object
A_float    float64
dtype: object

As a simple test, I tried subtracting '1' from the value, but still got float 'NaN'.

>d['A_float_minus1_test'] = pd.to_numeric(d['A'], errors='coerce')-1
>d
        A         B  A_float  A_float_minus1_test
0     N/A       N/A      NaN                  NaN
1  $10.00  -$100.00      NaN                  NaN
2   $5.00    -$5.00      NaN                  NaN

>d.dtypes
A                       object
B                       object
A_float                float64
A_float_minus1_test    float64
dtype: object

Is there a simple way to get the following result? The way I am thinking is to individually change each DataFrame column to float, then perform the operation. There must be an easier way.

Desired output:

(UPDATE: I do not want to remove the original N/A values. I'd like to keep them there as placeholders.)

Thanks!

user_8259343 · Answer

To convert your columns from string to float, you can use apply, like such:

d['A_float'] = d['A'].apply(lambda x: float(x.split('$')[1]) if x != '' else 0.0)

The x.split('$')[1] is used to remove the $ character (and eventually the minus before).

Then I am not sure of what your are trying to do, but if you are trying to compute the percentage of A from B, you can use np.vectorize like this:

d['Percent'] = np.vectorize(percent)(d['A'],d['B'])
def percent(p1, p2):
     return (100 * p2) / p1

user_2215888 · Answer

import pandas as pd 

d = pd.DataFrame({"A":['N/A','$10.00', '$5.00'], 
           "B":['N/A', '$10.00', '-$5.00']})

# Covert to number, remove '$', assign to new columns
d[['dA','dB']] = d[['A','B']].apply(lambda s: s.str.replace('$','')).apply(pd.to_numeric, errors='coerce')

# Perform calculations across desired column
d[['dA','dB']] = d[['dA','dB']].diff(-1)/d[['dA','dB']].shift(-1).abs()

print(d)
        A        B   dA   dB
0     N/A      N/A  NaN  NaN
1  $10.00   $10.00  1.0  3.0
2   $5.00   -$5.00  NaN  NaN

Convert DataFrame with 'N/As' to float to compute percent change

2 Answers