How to Loop over Numeric Column in Pandas Dataframe and filter Values?

Question

df:

Org_Name   Emp_Name   Age  Salary
0  Axempl    Rick       29    1000
1  Lastik    John       34    2000
2  Xenon     sidd       47    9000
3  Foxtrix   Ammy      thirty 2000
4  Hensaui   giny       33    ten  
5  menuia    rony      fifty  7000
6  lopex     nick       23    Ninety

I want loop over Numeric Column (Age, Salary) to check each value whether it is numeric or not, if string value present in Numeric column filter out the record and create a new data frame without that error.

Output :

  Org_Name   Emp_Name   Age  Salary
0  Axempl    Rick       29    1000
1  Lastik    John       34    2000
2  Xenon     sidd       47    9000

score 1 · Answer 1 · answered Nov 19 '19 at 21:24

You could extend this answer to filter on multiple columns for numerical data types:

import pandas as pd
from io import StringIO

data = """
Org_Name,Emp_Name,Age,Salary
Axempl,Rick,29,1000
Lastik,John,34,2000
Xenon,sidd,47,9000
Foxtrix,Ammy,thirty,2000
Hensaui,giny,33,ten  
menuia,rony,fifty,7000
lopex,nick,23,Ninety
"""

df = pd.read_csv(StringIO(data))
print('Original dataframe\n', df)

df = df[(df.Age.apply(lambda x: x.isnumeric())) &
        (df.Salary.apply(lambda x: x.isnumeric()))]
print('Filtered dataframe\n', df)

gives

Original dataframe
   Org_Name Emp_Name     Age  Salary
0   Axempl     Rick      29    1000
1   Lastik     John      34    2000
2    Xenon     sidd      47    9000
3  Foxtrix     Ammy  thirty    2000
4  Hensaui     giny      33   ten  
5   menuia     rony   fifty    7000
6    lopex     nick      23  Ninety
Filtered dataframe
   Org_Name Emp_Name Age Salary
0   Axempl     Rick  29   1000
1   Lastik     John  34   2000
2    Xenon     sidd  47   9000

Conner · Answer 2 · 2019-11-20T03:36:34.063

I believe this can be solved using Pandas' "to_numeric" function.

import pandas as pd

df['Column to Check'] = pd.to_numeric(df['Column to Check'], downcast='integer', errors='coerce')

df.dropna(axis=0, inplace=True)

Where 'Column to Check' is the column name that your are checking for values that cannot be cast as an integer (or any numeric type); in your question I believe you will want to apply this code to 'Age' and 'Salary'. "to_numeric" will convert any values in those columns to NaN if they could not be cast as your selected type. The "dropna" method will remove all rows that have a NaN in any of your columns.

To loop over the columns like you ask, you could do the following:

for col in ['Age', 'Salary']:
        df[col] = pd.to_numeric(df[col], downcast='integer', errors='coerce')
df.dropna(axis=0, inplace=True)

EDIT: In response to harry's comment. If there are preexisting NaNs in the data, something like the following should keep any valid row that had a preexisting NaN in one of the other columns.

for col in ['Age', 'Salary']:
    df[col] = pd.to_numeric(df[col], downcast='integer', errors='coerce')
    df = df[df[col].notnull()]

That's a good point. There are other ways to drop the appropriate rows from the DataFrame if there are already existing NaNs in the data. — Conner, Nov 20 '19 at 03:26

score 0 · Answer 3 · answered Nov 19 '19 at 21:22

You can use a mask to indicate wheter or not there is a string type among the Age and Salary columns:

mask_str = (df[['Age', 'Salary']]
            .applymap(lambda x: str(type(x)))
            .sum(axis=1)
            .str.contains("str"))
df[~mask_str]

This is assuming that the dataframe already contains the proper types. If not, you can convert them using the following:

def convert(val):
    try:
        return int(val)
    except ValueError:
        return val

df = (df.assign(Age=lambda f: f.Age.apply(convert), 
                Salary=lambda f: f.Salary.apply(convert)))

How to Loop over Numeric Column in Pandas Dataframe and filter Values?

3 Answers3