How to change DataFrame column dtype with regex and lambda expression?

user_7124155 0
Viewed 198

Consider the following DataFrame:

df = pd.DataFrame({'a':['123','667','323'],
                   'b':['8000','1. 300', '56'],
                   'c':['11','apple', '100']})

#      a       b      c
# 0  123    8000     11
# 1  667  1. 300  apple
# 2  323      56    100

I want to change the data type to int if it's a string of numbers. The regular expression is easy enough:

pattern = '^\d+$'
df.apply(lambda x: x.str.match(pattern, flags=re.IGNORECASE))

How to change the data type to int of any column that matches (column a in this example)? The column names are not consistent so I can't hard-code it. Do I need applymap here?

pandas python regex
2 Answers

You can try using .apply() on each column to use .astype(), as follows:

df = df.apply(lambda x: x.astype(int, errors='ignore'))

Result:

df.to_dict()

{'a': {0: 123, 1: 667, 2: 323},          
 'b': {0: '8000', 1: '1. 300', 2: '56'},
 'c': {0: '11', 1: 'apple', 2: '100'}}

Column a has been converted to int type.

We can also make use of your approach to get the columns convertible to int, and then convert the columns accordingly:

Use .all() on default axis=0 to flag those columns with all row elements matching your regex pattern. Then, use .loc to locate those columns and get the column names into the list cols. Then, convert type to int with .astype() on this list of columns:

import re

pattern = '^\d+$'
cols = df.loc[:, df.apply(lambda x: x.str.match(pattern, flags=re.IGNORECASE)).all()].columns

df[cols] = df[cols].astype(int)

Result:

df.to_dict()

{'a': {0: 123, 1: 667, 2: 323},          
 'b': {0: '8000', 1: '1. 300', 2: '56'},
 'c': {0: '11', 1: 'apple', 2: '100'}}
user_150706970

You are maybe looking for pd.to_numeric():

ret = df.apply(pd.to_numeric, errors="ignore")
print(ret.dtypes)

The above expression applies pd.to_numeric() to each column of the data-frame, see pd.DataFrame.apply(fun, **kwargs). This is roughly equivalent to the dtype inference of pd.read_csv() for numeric data in .csv files. With pd.to_numeric(), you have the possibility to convert non-matching expressions to NaN (set errors="coerce"), or apply additional downcasting of the inferred types (add argument downcast="signed").

I see that this is not exactly answering the OP's question (no regex involved). However, I'd rather use the dtype inference functionality offered by pandas than develop one myself.

user_33889620
Related