0
$\begingroup$

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

enter image description here

$\endgroup$

2 Answers 2

1
$\begingroup$

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
$\endgroup$
1
  • $\begingroup$ I suggest you to avoid loops, they are too time consuming $\endgroup$
    – Leevo
    Commented Feb 21, 2020 at 8:53
0
$\begingroup$

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
$\endgroup$
2
  • $\begingroup$ but those are not NAN values it is "NR" i need to replace "NR" with specific value. $\endgroup$
    – phani437
    Commented Feb 21, 2020 at 9:39
  • $\begingroup$ Even simpler. Edited now. $\endgroup$
    – TitoOrt
    Commented Feb 21, 2020 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.