Anurag Pandey Anurag Pandey - 3 months ago 6
Python Question

how to match two columns using one of the column as reference?

i have done some analysis and have found a particular pattern , and now i am trying to do some predictions .
I have a data set that predicts the ratings for students with a given number of accidents in their childhood .
My prediction matrix is looks some thing like this:

A
injuries ratings
0 5
1 4.89
2 4.34
3 3.99
4 3.89
5 3.77


and my dataset looks like this :

B

siblings income injuries total_scoldings_from father
3 12000 4 09
4 34000 5 22
1 23400 3 12
3 24330 1 1
0 12000 1 12


now i want to create a column name predictions that essentially matches the entries from A to B and returns

siblings income injuries total_scoldings_from_father predictions
3 12000 4 09 3.89
4 34000 5 22 3.77
1 23400 3 12 3.99
3 24330 1 1 4.89
0 12000 1 12 4.89


please help

Also suggest a title as mine lacks everything important for future references

Answer

You can use map if all values for mapping are in DataFrame A:

B['predictions'] = B['injuries'].map(A.set_index('injuries')['ratings'])
print (B)
   siblings  income  injuries  total_scoldings_from_father  predictions
0         3   12000         4                            9         3.89
1         4   34000         5                           22         3.77
2         1   23400         3                           12         3.99
3         3   24330         1                            1         4.89
4         0   12000         1                           12         4.89

Another solution with merge:

C = pd.merge(B,A)
print (C)
   siblings  income  injuries  total_scoldings_from_father  ratings
0         3   12000         4                            9     3.89
1         4   34000         5                           22     3.77
2         1   23400         3                           12     3.99
3         3   24330         1                            1     4.89
4         0   12000         1                           12     4.89
Comments