CODEWITHSUNDEEP
pythonpandasdataframe
Daniel Arges
Daniel Arges

Reputation: 365

How can I get a value from other dataframe's column based on other index?

Take this dataframe df fragment:

  col_1 col_2 col_3
0   aaa   !!!   sss
1   bbb   @@@   jjj
2   ccc   !!!   NaN
3   ddd   $$$   nnn
4   eee   %%%   xxx

I need to run a fillna() on col_3 to get the value of col_1 based on the first occurrence of the value of col_2.

To get it simple, this NaN value should by filled with aaa. It needs to be dynamic for the whole dataframe, and run for the whole col_3.

Upvotes: 1

Views: 41

Answers (1)

Joe Ferndz
Joe Ferndz

Reputation: 8508

Here's how to get this done:

  • Step 1: Do a Groupby of col_2 and find the values of col_1 but pick only the first entry of this value

  • Step 2: Convert this into a dictionary Both of these steps can be accomplished by doing:

    df.groupby('col_2')['col_1'].first().to_dict()

  • Step 3: Now do a fillna for col_3 using a lookup of value in col_2 but mapping it back to a dictionary. So the value in col_2 will be checked against the dictionary. The key would return a value. This value will be assigned back to col_3.

Putting all this together, the full code is as shown below:

import pandas as pd
import numpy as np
c = ['col_1','col_2','col_3']
d = [['aaa','!!!','sss'],
['bbb','@@@','jjj'],
['ccc','!!!',np.NaN],
['ddd','$$$','nnn'],
['eee','%%%','xxx'],
['fff','@@@',np.NaN],
['ggg','$$$',np.NaN],
['hhh','%%%',np.NaN]]
df = pd.DataFrame(d,columns=c)
print (df)
dx = df.groupby('col_2')['col_1'].first().to_dict()
df['col_3'] = df.col_3.fillna(df.col_2.map(dx))
print (df)

Output of this will be:

Original Dataframe:

  col_1 col_2 col_3
0   aaa   !!!   sss
1   bbb   @@@   jjj
2   ccc   !!!   NaN
3   ddd   $$$   nnn
4   eee   %%%   xxx
5   fff   @@@   NaN
6   ggg   $$$   NaN
7   hhh   %%%   NaN

Updated DataFrame:

  col_1 col_2 col_3
0   aaa   !!!   sss
1   bbb   @@@   jjj
2   ccc   !!!   aaa
3   ddd   $$$   nnn
4   eee   %%%   xxx
5   fff   @@@   bbb
6   ggg   $$$   ddd
7   hhh   %%%   eee

Added more records and tested:

Original:

   col_1 col_2 col_3
0    aaa   !!!   sss
1    bbb   @@@   jjj
2    ccc   !!!   NaN
3    ddd   $$$   nnn
4    eee   %%%   xxx
5    fff   @@@   NaN
6    ggg   $$$   NaN
7    hhh   %%%   NaN
8    iii   !!!   NaN
9    jjj   $$$   NaN
10   kkk   &&&   ttt

Updated:

   col_1 col_2 col_3
0    aaa   !!!   sss
1    bbb   @@@   jjj
2    ccc   !!!   aaa
3    ddd   $$$   nnn
4    eee   %%%   xxx
5    fff   @@@   bbb
6    ggg   $$$   ddd
7    hhh   %%%   eee
8    iii   !!!   aaa
9    jjj   $$$   ddd
10   kkk   &&&   ttt

Upvotes: 1

Related Questions