CODEWITHSUNDEEP
pythonpandasdataframe
jojo
jojo

Reputation: 165

Search in Pandas DataFrame

I have two dataframes as below:

import pandas as pd
raw_data = {
        'employee_id': ['4444', '5555', '6666','7777','8888'],
        'first_name': ['aa', 'Jason', 'Tina', 'Jake', 'Amy'],
        'last_name': ['Miller', 'Millers', 'Ali', 'Milner', 'Cooze'],
        'age': [42, 42, 36, 24, 73],
}
df1 = pd.DataFrame(raw_data, columns = ['employee_id','first_name', 'last_name', 'age'])


raw_data1 = {'employee_id': ['4444', '5555', '6666','7777'],
    'ip': ['192.168.1.101', '192.168.1.102','192.168.1.103','192.168.1.104'], 

}

df2 = pd.DataFrame(raw_data1, columns = ['employee_id', 'ip'])

I have to search (compare) df2['employee_id'] in df1 and if values are the same, add df2['ip'] to df1 as:

print df2['ip'].where(df2['employee_id']==df1['employee_id'])

But this isn't the right method:

ValueError: Can only compare identically-labeled Series objects

any suggestion for this issue would be appreciated.

Upvotes: 0

Views: 111

Answers (2)

Anton vBR
Anton vBR

Reputation: 18916

This is an updated answer as someone deleted the post with what I thought was the better solution.

on = "employee_id"
df3 = df1.set_index(on).join(df2.set_index(on)).fillna("IP missing")
df3["ip"].to_dict()

employee_id first_name  last_name   age ip          
4444        aa          Miller      42  192.168.1.101
5555        Jason       Millers     42  192.168.1.102
6666        Tina        Ali         36  192.168.1.103
7777        Jake        Milner      24  192.168.1.104
8888        Amy         Cooze       73  IP missing

{'4444': '192.168.1.101',
'5555': '192.168.1.102',
'6666': '192.168.1.103',
'7777': '192.168.1.104',
'8888': 'IP missing'}

Previous answer:

pd.merge(df1,df2,on="employee_id")

https://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=True, suffixes=('_x', '_y'), copy=True, indicator=False)

Gives

    employee_id first_name  last_name   age ip
0   4444    aa      Miller  42  192.168.1.101
1   5555    Jason   Millers 42  192.168.1.102
2   6666    Tina    Ali 36  192.168.1.103
3   7777    Jake    Milner  24  192.168.1.104

And possibly you want something like this:

pd.merge(df1,df2,on="employee_id").set_index("employee_id")["ip"].to_dict()

{'4444': '192.168.1.101',
 '5555': '192.168.1.102',
 '6666': '192.168.1.103',
 '7777': '192.168.1.104'}

Upvotes: 0

Zero
Zero

Reputation: 76917

Use merge

In [1286]: df1.merge(df2, on='employee_id')
Out[1286]:
  employee_id first_name last_name  age             ip
0        4444         aa    Miller   42  192.168.1.101
1        5555      Jason   Millers   42  192.168.1.102
2        6666       Tina       Ali   36  192.168.1.103
3        7777       Jake    Milner   24  192.168.1.104

Upvotes: 2

Related Questions