Fetch values corresponding to id of each row python

Question

Is is possible to fetch column containing values corresponding to an id column? Example:-

df1

|   ID     | Value   | Salary |
|:---------:--------:|:------:|   
|    1     |   amr   | 34     |
|    1     |   ith   | 67     |
|    2     |   oaa   | 45     |
|    1     |   eea   | 78     |
|    3     |   anik  | 56     |
|    4     |   mmkk  | 99     |
|    5     |   sh_s  | 98     |
|    5     |   ahhi  | 77     |

df2

|   ID     | Dept    |
|:---------:--------:|
|    1     |   hrs   |
|    1     |   cse   |
|    2     |   me    |
|    1     |   ece   |
|    3     |   eee   |

Expected Output
|   ID     | Dept    | Value     |
|:---------:--------:|----------:|
|    1     |   hrs   | amr       |
|    1     |   cse   | ith       |
|    2     |   me    | oaa       |
|    1     |   ece   | eea       |
|    3     |   eee   | anik      |

I want to fetch each values in the 'Value' column corresponding to values in df2's ID column. And create column containing 'Values' in df2. The number of rows in the two dfs are not the same. I have tried

this

Not worked

do you mean a simple filter? `df1[df1['ID'].isin(df2['ID'].tolist())]` ? if you add in your expected output you'll have a perfect [mcve] — Umar.H, Mar 31 '21 at 15:25
It would help us to understand your question if you would [edit] your question to include the expected output — G. Anderson, Mar 31 '21 at 15:26

score 2 · Answer 1 · answered Mar 31 '21 at 15:34

IIUC , you can try df.merge after assigning a helper column by doing groupby+cumcount on ID:

out = (df1.assign(k=df1.groupby("ID").cumcount())
       .merge(df2.assign(k=df2.groupby("ID").cumcount()),on=['ID','k'])
       .drop("k",1))

print(out)

   ID Value Dept
0   1   Amr  hrs
1   1   ith  cse
2   2   oaa   me
3   1   eea  ece
4   3  anik  eee

score 0 · Answer 2 · answered Mar 31 '21 at 15:33

0

is this what you want to do?

df1.merge(df2, how='inner',on ='ID')

answered Mar 31 '21 at 15:33

eshirvana

23,227
3
22
38

score 0 · Answer 3 · answered Mar 31 '21 at 15:37

0

Since you have duplicated IDs in both dfs, but these are ordered, try:

df1 = df1.drop(columns="ID")
df3 = df2.merge(df1, left_index=True, right_index=True)

answered Mar 31 '21 at 15:37

Laurent

12,287
7
21
37

Fetch values corresponding to id of each row python

3 Answers3