See if the values in a column contain % in a pandas dataframe

user_14451669 0
Viewed 569

I have a dataframe that has columns whose values contain % (literal percentage sign). I am trying to create a function to automatically convert these values to a decimal.

For example, with the below dataframe:

       var1        var2      var3     var4
id                                              
0    1.4515       1.52%    -0.5709    4%
1    1.57         1.605%   -0.012     8%
2    1.69253      1.657%   -0.754     9%
3    1.66331      1.686%   -0.0012    5%
4    1.739        1.716%   -0.04      12%
5    1.7447       1.61%    -0.0023    11%


def pct_to_dec(df):
    for col in df:
        print(col)
        if '%%' in df[col].astype(str):
            print(col)
            df[col] = df[col].replace({'%%':''}, regex=True)
            df[col] = df[col]/100

The function should print var2 and var4, and convert the values in both columns to decimal format. Through troubleshooting I have found that python is not seeing the percentage characters since when I do this code:

df.isin(['%%'])

It prints a dataframe of "False".

Lastly, I have tried to see if I'm using the wrong escape character. I've tried %%, /%, and \%. I am interested in seeing if I am on the right track, as well as if there is a simpler way to do what I'm trying to do.

multiple-columns substring escaping python
2 Answers

You can easily check this using the Series method .str.contains

It lets you check which rows of a Series has the string you passed. For example, if you run this code:

df['var2'].str.contains('%')

You'll get a series as a return with all rows equals True. So you just need to implement a for and get the index of the rows that have True values and do whatever you want.

Note that if your rows isn't str type you'll get NaN as a return, so be aware of the type of the columns.

user_127832370

You can as well use .str.endswith like in the following example:

for col in df.select_dtypes('object'):
    indexer_percent= df[col].str.endswith('%')
    df.loc[indexer_percent, col]= df.loc[indexer_percent, col].str.strip('%')
    df[col]= df[col].astype('float32')
    df.loc[indexer_percent, col]/= 100.0

On your data, this results in:

       var1     var2    var3  var4
id                                
0   1.45150  0.01520 -0.5709  0.04
1   1.57000  0.01605 -0.0120  0.08
2   1.69253  0.01657 -0.7540  0.09
3   1.66331  0.01686 -0.0012  0.05
4   1.73900  0.01716 -0.0400  0.12
5   1.74470  0.01610 -0.0023  0.11

The data is created by:

import pandas as pd
import io

infile=io.StringIO(
"""id var1        var2      var3     var4
0    1.4515       1.52%    -0.5709    4%
1    1.57         1.605%   -0.012     8%
2    1.69253      1.657%   -0.754     9%
3    1.66331      1.686%   -0.0012    5%
4    1.739        1.716%   -0.04      12%
5    1.7447       1.61%    -0.0023    11%"""
)

df= pd.read_csv(infile, index_col=0, sep='\s+')
user_69251850
Related