Fast way to filter for a value in a numpy array and get the DataFrame row

Question

I have this DataFrame:

pd.DataFrame({
    'as_of': pd.date_range('2020-01-01', '2020-01-05', freq='D'),
    'x': [np.array([str(np.random.random()) for _ in range(2)]) for _ in range(5)]
})

Imagine that this DataFrame with 50k rows and the numpy array size is variable, no more than 5 values though.

How can I filter the DataFrame to give me just the row that matches the value that is inside of the numpy array, in the most efficient manner?

Also, instead of a single value, how can I filter for the entire numpy array?

Don't use strings for one thing

Mad Physicist
– Mad Physicist

2021-08-05 21:38:17 +00:00
Commented Aug 5, 2021 at 21:38 — Mad Physicist
– Mad Physicist, Commented Aug 5, 2021 at 21:38
Completely agree. It's out of my control.

user270199
– user270199

2021-08-05 21:39:30 +00:00
Commented Aug 5, 2021 at 21:39 — user270199
– user270199, Commented Aug 5, 2021 at 21:39

C. Braun · Accepted Answer · 2021-08-05 21:11:20Z

1

If you are guaranteed that there will only be one array in x with the value you want, you can use the following:

search_val = 0.3

df = pd.DataFrame({
    'as_of': pd.date_range('2020-01-01', '2020-01-05', freq='D'),
    'x': [np.array([str(np.random.random()) for _ in range(2)]) for _ in range(5)]
})

# Add example row with array of different size and the value we are searching for
df.loc[5] = [pd.to_datetime('2020-10-06'), np.array([.1, .2, .3])]

# Gets the index of the array with the search value
df.loc[[(df["x"].explode() == search_val).idxmax()]]  # gives row 5

answered Aug 5, 2021 at 21:11

C. Braun

5,2611 gold badge22 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user270199 Over a year ago

Thanks. If I want to pass in search_val = np.array([.1, .2, .3]), how can I find its row?

C. Braun Over a year ago

Oh, if you're looking to compare against the whole array value, I'd suggest doing the approach here. search_arr = (.1, .2, .3) then df[df["x"].map(tuple).isin([search_arr])]

user270199 Over a year ago

I can do df['x'].isin([search_arr]) but I'm getting this warning:

FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison

Collectives™ on Stack Overflow

Fast way to filter for a value in a numpy array and get the DataFrame row

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related