Replace Values with corresponding numbers in the same dataframe

Question

Please help me to replace NR values with corresponding values as in the dataframe.

Answer 1

I'm not sure if this is most efficient way but it may help you.

df = read_csv('filename.csv')
grouped_df = df.groupby('City')
temp_df = []
for key, item in grouped_df:
    if 'NR' in item['Route'].values.tolist():
        values = item['Route'].values.tolist()
        to_change = [x for x in values if x != 'NR'][0]
        item = item.replace('NR', to_change)
        temp_df.append(item)
    else:
        temp_df.append(item)
final_df = pd.concat(temp_df, axis=0)

Output:

        City Route
4          A     3
5          B     4
6          C     5
7          D     7
8          D     7
9          D     7
10         D     7
2   Kolkatta     2
3   Kolkatta     2
0    Manipur    10
1    Manipur    10

Answer 2

A simpler solution:

1) We generate a dictionary with the pairs:

df = pd.DataFrame({'a':['Hyderabad','Chennai','Lucknow','Kolkatta','Manipur','Manipur','Lucknow','Hyderabad','Kolkatta'], 'b':[4, 5, 9, 2, 10, 'NR', 'NR','NR',2]})
df

    a           b
0   Hyderabad   4.0
1   Chennai     5.0
2   Lucknow     9.0
3   Kolkatta    2.0
4   Manipur     10.0
5   Manipur     NR
6   Lucknow     NR
7   Hyderabad   NR
8   Kolkatta    2.0

# Ge get a dictionary with the pairs
pairs = {a:b  for _,(a,b) in df.iterrows() if b!= 'NR'}
pairs 
{'Hyderabad': 4.0,
 'Chennai': 5.0,
 'Lucknow': 9.0,
 'Kolkatta': 2.0,
 'Manipur': 10.0}

2) We use the dictionary to fill the NRs

df.loc[df.b=='NR', 'b'] = df[df.b=='NR'].apply(lambda x: pairs[x.a], axis =1)

    a           b
0   Hyderabad   4.0
1   Chennai     5.0
2   Lucknow     9.0
3   Kolkatta    2.0
4   Manipur     10.0
5   Manipur     10.0
6   Lucknow     9.0
7   Hyderabad   4.0
8   Kolkatta    2.0