Apply for loop in multiple dataframe for multiple columns?

user_14280573 0
Viewed 1609

Dataframe is like below: Where I want to change dataframes value to 'dead' if age is more than 100.

import pandas as pd
raw_data = {'age1': [23,45,210],'age2': [10,20,150],'name': ['a','b','c']}
df = pd.DataFrame(raw_data, columns = ['age1','age2','name'])

raw_data = {'age1': [80,90,110],'age2': [70,120,90],'name': ['a','b','c']}
df2 = pd.DataFrame(raw_data, columns = ['age1','age2','name'])

Desired outcome

df=
    age1    age2    name
0   23      10       a
1   45      20       b
2   dead    dead     c

df2=
    age1    age2    name
0   80      70       a
1   90      dead     b
2   dead    90       c

I was trying something like this:

col_list=['age1','age2']
df_list=[df,df2]

def dead(df):
  for df in df_list:
    if df.columns in col_list:
      if df.columns >=100:
        return 'dead'
    else:
      return df.columns

df.apply(dead)

Error shown: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()

I am looking for a loop that works on all dataframe.

Please correct my function also for future learning :)

data-science pandas dataframe python
7 Answers

With your shown samples, please try following. Using filter, np.where functions of pandas, numpy respectively.

c = df.filter(regex='age\d+').columns
df[c] = np.where(df[c].ge(100),'dead',df[c])
df

Alternative approach with where:

c=df.filter(like='age').columns
df[c] = df[c].where(~df['c'].ge(100),'dead')

Explanation:

  • Getting columns which has same name like age in c variable.
  • Then using np.where to check if respective(all age columns) are greeter/equal to 100, if yes then set it to dead or keep it as it is.
user_58665800

I did the following:

col_list=['age1','age2']
df_list=[df,df2]

for d in df_list:
    for c in col_list:
        d.loc[d[c]>100, c] = 'dead'
user_144881410

One possible solution is to use Pandas' mask, which is similar to if-else, but vectorized.

def dead(df):
    col_list = ['age1', 'age2']
    df = df.copy()
    temporary = df.filter(col_list)
    temporary = temporary.mask(temporary >= 100, "dead")
    df.loc[:, col_list] = temporary
    return df

Apply function to the dataframe:

df.pipe(dead)
 
   age1  age2 name
0    23    10    a
1    45    20    b
2  dead  dead    c
user_71757130

#inspired by @jib and @ravinder

col_list=['age1','age2']
df_list=[df,df2]

for d in df_list:
  for c in col_list:
    d[c]=np.where(d[c]>100,'dead',d[c])
df #or df2

output:

   age1  age2 name
0    23    10    a
1    45    20    b
2  dead  dead    c
user_142805730

You can do:

def check_more_than_100(x):
    v = None
    try:
        v = int(x)
    except:
        pass
    if v is not None:
        return (v > 100)
    return (False)
    
df['age1'] = df['age1'].apply(lambda x : 'dead' if check_more_than_100(x) else x)
df['age2'] = df['age2'].apply(lambda x : 'dead' if check_more_than_100(x) else x)

df2['age1'] = df2['age1'].apply(lambda x : 'dead' if check_more_than_100(x) else x)
df2['age2'] = df2['age2'].apply(lambda x : 'dead' if check_more_than_100(x) else x)

This should take care of non-int values if any.

user_14103030

I used this answer to a similar question. Basically you can use the .where() function from numpy to set based on the conditional.

import pandas as pd
import numpy as np
raw_data = {'age1': [23,45,210],'age2': [10,20,150],'name': ['a','b','c']}
df = pd.DataFrame(raw_data, columns = ['age1','age2','name'])

raw_data = {'age1': [80,90,110],'age2': [70,120,90],'name': ['a','b','c']}
df2 = pd.DataFrame(raw_data, columns = ['age1','age2','name'])

col_list=['age1','age2']
df_list=[df,df2]

def dead(df_list, col_list):
    for df in df_list:
        for col in col_list:
            df[col] = np.where(df[col] >= 100, "dead", df[col])
    return df_list


df

dead([df], col_list)
user_97373250

Extracting numeric columns and then using numpy where -

df_cols  = df._get_numeric_data().columns.values
df2_cols  = df2._get_numeric_data().columns.values
df[df_cols] = np.where(df[df_cols].to_numpy() > 100, 'dead', df[df_cols])
df2[df2_cols] = np.where(df2[df2_cols].to_numpy() > 100, 'dead', df2[df2_cols])
user_154380330
Related