query pandas dataframe using list with repeated index values and collect repeated output

user_5502009 0
Viewed 302

I have a list of index values. The values in the list can be repeated multiple times.

index_list = [1,3,4,3,2,20,55,30,45]

I want to query a pandas dataframe and extract the the values with indexes matching the values in the index_list

  • If the same index is present multiple times I want to extract the value multiple times.
  • The order of selected value must match the order of the index_list.

The index_list can be pretty long (100000) and the df quite small (400 rows, 2 columns)

The solution I used is based on a loop:

all_selected_values = []
for idx in index_list:
  all_selected_values.append(df.loc[df.index == idx,'selected_column'].values[0])

This works but can be quite slow.

Is there a more efficient way to do it?
Thanks!

data-science pandas dataframe python
1 Answers

Pandas way would be pass the index list in loc and parse the series you want , then convert to list:

all_selected_values2=df.loc[index_list,'selected_column'].tolist()

Example dataframe size: (11000,1):

np.random.seed(1)
index_list = np.random.randint(0,11000,1000) #1000 values
df = pd.DataFrame(np.random.randint(0,100,(11000,1)),columns=['selected_column'])

Verification:

all_selected_values = []
for idx in index_list:
    all_selected_values.append(df.loc[df.index == idx,'selected_column'].values[0])

all_selected_values2=df.loc[index_list,'selected_column'].tolist()

print(all_selected_values == all_selected_values2)
True

Comparison:

%%timeit
all_selected_values1 = []
for idx in index_list:
    all_selected_values.append(df.loc[df.index == idx,'selected_column'].values[0])
197 ms ± 9.04 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
all_selected_values2=df.loc[index_list,'selected_column'].tolist()
415 µs ± 29.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
user_98406370
Related