Index pandas DataFrame with lists of indices and fill with value

user_2471385 0
Viewed 214

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!

pandas dataframe indexing python
1 Answers

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
user_42384080
Related