0

Need to compare the user id in column 'pk' of first dataframe with user id in column 'user__id'of second dataframe and copy the matching data to a new dataframe. Both dataframes are to be imported from two different csv files.

Two dataframes to be compared for same user id

DataFrame A (user.csv)

 pk  phone
0 4972619  797979797971
1 4972608  454959544952 
2 4972597  959594594543
3 4972596  454555545444 
4 4972595  554545545547 
5 4972594  555555555555 
6 4972593  979797979791 
7 4972592  959579999999 
8 4972591  557979557454

DataFrame B (item.csv) 

       user__id    user__phone
0 4972593  979797979791 
1 7674145  525575779792 
2 9251355  554755975999 
3 1015996  945595774994 
4 4704137  957425457575 
5 2120715  545575979755 
6 4972595  554545545547 
7 1726034  495597794559 
8 1102155  955755557579 
9 4972597  959594594543
feedMe
  • 3,431
  • 2
  • 36
  • 61
Pranav Krishna
  • 11
  • 1
  • Need the result as a new DataFrame 'C' with matching user id datas in column 'id_user' and phone number in 'phone_number' – Pranav Krishna Feb 06 '19 at 10:29
  • would really appreciate if you can paste the data and expected output as text, no images please. – anky Feb 06 '19 at 10:30
  • @anky_91 I made up this data and saved it as an image. Can't seem to find the text file. I'm sorry. – Pranav Krishna Feb 06 '19 at 11:41
  • you can copy paste some samples as text atleast – anky Feb 06 '19 at 11:43
  • @anky_91 tried the first solution in the actual data that i need to analyze https://stackoverflow.com/questions/34417964/pandas-compare-two-dataframes-and-remove-what-matches-in-one-column but the end result i got in Jupyter notebook and Excel analysis are different. – Pranav Krishna Feb 06 '19 at 11:44
  • 1
    check this: https://stackoverflow.com/questions/53645882/pandas-merging-101 – anky Feb 06 '19 at 11:45
  • @anky_91 question updated with sample data. – Pranav Krishna Feb 06 '19 at 12:03
  • `df1.merge(df2,left_on=['pk'],right_on=['user__id'])` works? – anky Feb 06 '19 at 12:22
  • 1
    @anky_91 Thanks a lot. It worked! :) but i was not able to put it into a new dataframe. i did something like this: df3=df1.merge(df2,left_on=['pk'],right_on=['user__id']) – Pranav Krishna Feb 07 '19 at 09:26

1 Answers1

0

Something like this? - 

import pandas as pd
import numpy as np
df = pd.DataFrame({'pk' : np.random.randint(1,20,size=15)})
df2 = pd.DataFrame({'user_id' : np.random.randint(1,20,size=15)})
df3 = pd.merge(df,df2,left_on='pk',right_on='user_id',how='inner')
print(df3)


  ID
0   8
1   16
2   16
3   16
4   16
5   16
6   16
7   4
8   10
Umar.H
  • 22,559
  • 7
  • 39
  • 74