Looking for function syntax to check dataframe column against two different dictionary key / value p-CodePudding

Dictionaries to compare to:

PositiveKey = {"{":"0", "A":"1", "B":"2"} 
NegativeKey = {"}":"0", "J":"1", "K":"2"}

Current DataFrame:

Column 1
0  000000002758A
1  000000326588B
2  000000000567J

Desired DataFrame:

Column 1
0  275.81
1  32658.82
2  -56.71

I am trying to find a way to check if the last character within a data point of a specific DF column matches against two dictionaries (categorized as needing a status as positive or negative). Depending on which dictionary key the last character of the data point matches with:

I need the data point to be updated with the corresponding value and the overall data point to be defined as a positive or negative integer. (IE line 0 should be "27581" and line 2 should be "-5671")
I also need the data points to be updated to move the decimal place two spots left. (IE line 0 should be "275.81" and line 2 should be "-56.71").

I suppose converting the data points to integers and then multiplying by /- .01 or dividing by /- 100 could work here? Any input is appreciated, thank you!

CodePudding user response：

You're idea is right using pd.eval:

I suppose converting the data points to integers and then multiplying by /- .01 or dividing by /- 100 could work here?

keys = {'^0 ': ''}
keys.update({f'{k}$': f'{v}/100' for k, v in PositiveKey.items()})
keys.update({f'{k}$': f'{v}/-100' for k, v in NegativeKey.items()})

df['Column 2'] = pd.eval(df['Column 1'].replace(keys, regex=True))

Output:

>>> df
        Column 1  Column 2
0  000000002758A    275.81
1  000000326588B  32658.82
2  000000000567J    -56.71

>>> keys
{'^0 ': '',
 '{$': '0/100',
 'A$': '1/100',
 'B$': '2/100',
 '}$': '0/-100',
 'J$': '1/-100',
 'K$': '2/-100'}

CodePudding user response：

You can apply a function to 'Column 1':

def get_number(x):
    if x[-1] in PositiveKey:
        return float(x[:-1]   PositiveKey[x[-1]]) / 100
    else:
        return - float(x[:-1]   NegativeKey[x[-1]]) / 100
    
df['Column 1'] = df['Column 1'].apply(get_number)

Output:

   Column 1
0    275.81
1  32658.82
2    -56.71