Sahil Sahil - 1 month ago 15
Python Question

Comparing/Mapping different series in different Dataframes

I have two data frames. Dataframe "A" which is the main dataframe has 3 columns "Number", "donation" and "Var1" . Dataframe B has 2 columns "Number" and "location". The "Number" column in DataFrame B is a subset of "Number" in A. What I would like to do is form a new column in DataFrame A - "NEW" which would map the values of numbers in both the column and if its present in DataFrame B would add value as 1 else all other values will be 0.

>>>DFA
Number donation Var1
243 4 45
677 56 34
909 34 22
565 78 24
568 90 21
784 33 88
787 22 66
>>>DFB
Number location
909 PB
565 WB
784 AU


These are the two dataframes, I want the DFA with a new column which looks something like this.

>>>DFA
Number donation Var1 NEW
243 4 45 0
677 56 34 0
909 34 22 1
565 78 24 1
568 90 21 0
784 33 88 1
787 22 66 0


This has a new column which has value as 1 if the Number was present in DFB if absent it gives 0.

Answer

You could use the isin method:

DFA['NEW'] = (DFA['Number'].isin(DFB['Number'])).astype(int)

For example,

import pandas as pd

DFA = pd.DataFrame({'Number': [243, 677, 909, 565, 568, 784, 787],
                    'Var1': [45, 34, 22, 24, 21, 88, 66],
                    'donation': [4, 56, 34, 78, 90, 33, 22]})
DFB = pd.DataFrame({'Number': [909, 565, 784], 'location': ['PB', 'WB', 'AU']})

DFA['NEW'] = (DFA['Number'].isin(DFB['Number'])).astype(int)
print(DFA)

yields

   Number  Var1  donation  NEW
0     243    45         4    0
1     677    34        56    0
2     909    22        34    1
3     565    24        78    1
4     568    21        90    0
5     784    88        33    1
6     787    66        22    0
Comments