0

I have two data frames, let's say A and B. A has the columns ['Name', 'Age', 'Mobile_number'] and B has the columns ['Cell_number', 'Blood_Group', 'Location'], with 'Mobile_number' and 'Cell_number' having common values. I want to join the 'Location' column only onto A based off the common values in 'Mobile_number' and 'Cell_number', so the final DataFrame would have A={'Name':,'Age':,'Mobile_number':,'Location':]

a = {'Name': ['Jake', 'Paul', 'Logan', 'King'], 'Age': [33,43,22,45], 'Mobile_number':[332,554,234, 832]}
A = pd.DataFrame(a)

b = {'Cell_number': [832,554,123,333], 'Blood_group': ['O', 'A', 'AB', 'AB'], 'Location': ['TX', 'AZ', 'MO', 'MN']}
B = pd.DataFrame(b)

Please suggest. A colleague suggest to use pd.Join but I don't understand how.

Thank you for your time.

suntoch
  • 1,834
  • 2
  • 13
  • 13
Arcanesaw
  • 29
  • 4
  • 1
    Hi Anurag. What have you tried so far ? Would be helpful if you include an example showing where you are having difficulty. In the meantime, here is a nice summary of pandas merge commands from a prior SO question : https://stackoverflow.com/questions/53645882/pandas-merging-101 – bici-sancta Mar 01 '21 at 21:14

1 Answers1

0

the way i see it, you want to merge a dataframe with a part of another dataframe, based on some common column. first you have to make sure the common column share the same name:

B['Mobile_number'] = B['Cell_number']

then create a dataframe that contains only the relevant columns (the indexing column and the relevant data column):

B1 = B[['Mobile_number', 'Location']]

and at last you can merge them:

merged_df = pd.merge(A, B1, on='Mobile_number')

note that this usage of pd.merge will take only rows with Mobile_number value that exists in both dataframes. you can look at the documentation of pd.merge to change how exactly the merge is done, what to include etc..

akonsk
  • 76
  • 6
  • Thank you but with that I also get other columns from B but I only want 'Location' from B. – Arcanesaw Mar 01 '21 at 21:47
  • I really appreciate it, Thank you. – Arcanesaw Mar 02 '21 at 01:00
  • While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. – John Conde Mar 02 '21 at 01:00