I have a dataframe with and id and value columns.
df=
id val
'a' 1
'b' 3
'c' 9
....
I have a list of (repeated) id values.
i_list=['a','a','a','b']
I need to map this list of (repeated) id values into the corresponding (repeated) value columns, using the dataframe pairs (id,val)
out_desired=[1,1,1,3]
Right now I am doing:
out_desired=[df[df.id==curr_id].val.values for curr_id in i_list ]
How to do this in a more efficient yet still concise way?
If the ids are lexicigraphically sorted you can use Series.searchsorted:
df.loc[df['id'].searchsorted(i_list), 'val'].to_numpy().tolist()
[1, 1, 1, 3]
Or you could set id as index (works for non sorted id too):
df.set_index('id').loc[i_list, 'val'].to_numpy().tolist()
# [1, 1, 1, 3]
If the id column is not sorted, sort and then do as above for the first approach to work:
print(df)
id val
0 c 1
1 b 3
2 a 9
df_ = df.sort_values(['id'])
df_.loc[df_['id'].searchsorted(i_list), 'val'].to_numpy().tolist()
[1, 1, 1, 3]
You can try using pandas.merge as it seems to be faster for me.
df = {'id': ['a', 'b', 'c'], 'value': [1,3,9]}
df = pd.DataFrame(df).set_index('id')
test = ['a', 'b', 'c']*8
%timeit df.merge(pd.DataFrame({'id':test}), left_index=True, right_on='id', how='right')['value'].values
1.32 ms ± 33.6 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
%timeit [df[df.index==curr_id].values for curr_id in test ]
5.81 ms ± 123 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
I believe it gives the right answer