Look up values based on match between two columns in Pandas

user_6380689 0
Viewed 77

I have this data

import pandas as pd

data = {'Code':  ['20','22','21','21','21','21','20','20','20','20'],
        'Code2': ['1', '5', '2', '', '2', '2', '1', '', '1', '1']}

df = pd.DataFrame (data, columns = ['Code','Code2'])


   Code Code2
0   20  1
1   22  5
2   21  2
3   21  
4   21  2
5   21  2
6   20  1
7   20  
8   20  1
9   20  1

I'm trying to fill the missing values in Code2 based on previous associations. So, I know that when Code is 20, Code2 is 1, and when Code is 21, Code2 is 2. My output should look like this:

  Code  Code2
0   20  1
1   22  5
2   21  2
3   21  2
4   21  2
5   21  2
6   20  1
7   20  1
8   20  1
9   20  1

I figured I could do that using some joins but would be keen on using a one liner if possible.

pandas lookup
3 Answers

Use drop_duplicates and map:

data = {'Code':  ['20','22','21','21','21','21','20','20','20','20'],
        'Code2': ['1', '5', '2', '', '2', '2', '1', '', '1', '1']}

df = pd.DataFrame (data, columns = ['Code','Code2'])

m = df.drop_duplicates('Code').set_index('Code')['Code2']

df['Code2'] = df['Code'].map(m)
df

Output:

  Code Code2
0   20     1
1   22     5
2   21     2
3   21     2
4   21     2
5   21     2
6   20     1
7   20     1
8   20     1
9   20     1
user_63615310

You can use groupby.ffill:

df['Code2'] = df.Code2.replace('',np.nan).groupby(df.Code).ffill()

Output:

  Code Code2
0   20     1
1   22     5
2   21     2
3   21     2
4   21     2
5   21     2
6   20     1
7   20     1
8   20     1
9   20     1
user_42384080

Use groupby and transform the column Code2 using first:

df['Code2'] = df['Code2'].replace('', np.nan).groupby(df['Code']).transform('first')

Result:

  Code Code2
0   20     1
1   22     5
2   21     2
3   21     2
4   21     2
5   21     2
6   20     1
7   20     1
8   20     1
9   20     1
user_128331660
Related