Index pandas DataFrame with lists of indices and fill with value

Question

I have two pandas DataFrames. idxs has indices with multiple y values for each x. I also have an 0-filled matrix called mat that holds all possible indices, like below:

idxs = pd.DataFrame({"x":np.repeat([1,4,20,50, 84],2), "y":np.random.randint(100, size=10)})
mat = pd.DataFrame(0, index=np.arange(100), columns=np.arange(100))

How do I most efficiently use the rows of idxs to fill in mat with 1s.

The best I've been able to do is capture them by row:

mat.loc[4, list(idxs.loc[lambda df: df['x'] == 4, "y"])] = 1

and then loop over the values in x. However, I want to avoid a mask over each value in x. I imagine there must be a way to index based on two lists of x and y values but I'm not able to find it. Any help is appreciated! Thanks!

Quang Hoang · Accepted Answer · 2020-12-11 21:29:48Z

2

Option 1: You can try your approach a bit faster with updating by groups:

for x,ys in idxs.groupby('x')['y']:
    mat.loc[x,ys] = 1

Option 2: Copy the whole data out, update using numpy indexing, and assign back:

data = mat.to_numpy()
data[idxs['x'], idxs['y']] = 1
mat.loc[:] = data

answered Dec 11, 2020 at 21:29

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

Index pandas DataFrame with lists of indices and fill with value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related