Create a column in a dataframe depending on values in another with if statement

Question

What I'm trying to do is:

df = pd.DataFrame({"Person":["John", "Myla", "Mary", "John", "Myla"],
               "Age1": [24, 6, 21, 33, 17],
               "Single": [False, True, True, True, False]})

   Age1 Person Single
 0  24 John False 
 1   6 Myla True 
 2  21 Mary True 
 3  33 John True 
 4  17 Myla False

From the above dataframe, I need to add a new column that will show "1" if the the age if the person is above the age of another dataframe.

My second dataframe is this:

df2 = pd.DataFrame({"Person":["John", "Myla", "Mary"],
               "Age1": [25, 10, 15] }).set_index(['Person'])

         Age1
 Person 
  John   25
  Myla   10 
  Mary   15

The results ideally would be:

   Age1 Person Single Final
 0 24 John False       0
 1  6 Myla True        0
 2 21 Mary True        1
 3 33 John True        1
 4 17 Myla False       1

So, all John's in my first table with an age greater that 25 in the second table will be given a 1, otherwise 2.

I tried doing it with a dictionary,

a=df2.set_index('Person').T.to_dict('list')

but my dictionary looks like this:

{'John': [24L], 'Mary': [15L], 'Myla': [0L]}

but it's getting me really confused. I also thought about merging the 2 dataframes and then comparing to create the new column, but is there any other more straight option?

Or if I manage to convert the 2nd ddataframe in a dictionary, I could use map df['Final'] = df.Person.map(dictionary) but still shouldn't compare the ages. Please help.

jezrael · Accepted Answer · 2018-06-26 13:44:14Z

1

Use map, compare by lt (<) and last cast boolean mask to integer:

df['Final'] = df['Person'].map(df2['Age1']).lt(df['Age1']).astype(int)
print (df)
  Person  Age1  Single  Final
0   John    24   False      0
1   Myla     6    True      0
2   Mary    21    True      1
3   John    33    True      1
4   Myla    17   False      1

Detail:

print (df['Person'].map(df2['Age1']))
0    25
1    10
2    15
3    25
4    10
Name: Person, dtype: int64

answered Jun 26, 2018 at 13:44

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create a column in a dataframe depending on values in another with if statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related