I tested out three variants of subsetting the dataframe before converting to numpy:

• subset column and row
• subset only column
• subset column, then subset row if types are mixed

Optimization testing script:

importpandasaspdimportnumpyasnpimporttimeitnp.random.seed(43)fornin [100,100_000]:forkinrange(2,6):print(k,n)cols=list('abcdef')df=pd.DataFrame(np.random.randint(0,10,size=(n,len(cols))),columns=cols)df['col']=np.random.choice(cols,n)sample_n=n//10idx=np.random.choice(df['col'].index.to_numpy(),sample_n)cols=np.random.choice(df['col'].to_numpy(),sample_n)timeit.timeit(lambda:df.drop(columns='col').lookup(idx,cols),number=1000)str_col=cols[0]df[str_col]=df[str_col].astype(str)df[str_col]=str_coltimeit.timeit(lambda:df.drop(columns='col').lookup(idx,cols),number=1000)

As a result of this testing I settled on the third option.

Is the implementation in#40140 (comment) not sufficient?

size=100_000df=pd.DataFrame({'a':np.random.randint(0,100,size),'b':np.random.random(size),'c':'x'})row_labels=np.repeat(np.arange(size),2)col_labels=np.tile(['a','b'],size)%timeitdf.lookup(row_labels,col_labels)# 22.3 ms ± 391 μs per loop (mean ± std. dev. of 7 runs, 10 loops each)  <--- this PR# 13.4 ms ± 17 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)  <--- proposed implementation

The implementationdf[['a', 'b']].sum().sum() pulls up the entire column, and does not support lookup of individual values by row/column. Is that what you are referring to?

If we are to move forward, this looks good, should get a whatsnew in enhancements for 3.0.

Done

@stevenae - sorry, linked to the wrong comment. I've fixed my comment above.

Ah, but I think I see. This avoids a large copy when only certain columns are used.

cc @pandas-dev/pandas-core

My take: this provides an implementation for what I think is a natural operation that is not straightforward for most users. It provides performance benefits that take into account columnar-based storage (subsetting columns prior to calling.to_numpy()). This seems like a worthy addition in my opinion, especially given the user feedback when the previous version was removed.

@stevenae - sorry, linked to the wrong comment. I've fixed my comment above.

Ah, but I think I see. This avoids a large copy when only certain columns are used.

Yes -- I ran a comparison (script at end) and found this PR implementation beats the comment you referenced on large mixed-type lookups.

Metrics

Script

importpandasaspdimportnumpyasnpimporttimeitnp.random.seed(43)defpd_lookup(df,row_labels,col_labels):rows=df.index.get_indexer(row_labels)cols=df.columns.get_indexer(col_labels)result=df.to_numpy()[rows,cols]returnresultfornin [100,100_000]:forkinrange(2,6):print(k,n)cols=list('abcdef')df=pd.DataFrame(np.random.randint(0,10,size=(n,len(cols))),columns=cols)df['col']=np.random.choice(cols,n)sample_n=n//10idx=np.random.choice(df['col'].index.to_numpy(),sample_n)cols=np.random.choice(df['col'].to_numpy(),sample_n)timeit.timeit(lambda:df.drop(columns='col').lookup(idx,cols),number=1000)timeit.timeit(lambda:pd_lookup(df.drop(columns='col'),idx,cols),number=1000)str_col=cols[0]df[str_col]=df[str_col].astype(str)df[str_col]=str_coltimeit.timeit(lambda:df.drop(columns='col').lookup(idx,cols),number=1000)timeit.timeit(lambda:pd_lookup(df.drop(columns='col'),idx,cols),number=1000)

Trying to make sure i understand correctly: this seems equivalent todf.loc[rows, cols].to_numpy().ravel()? (or maybedf.loc[rows, cols].stack().values might be better for preserving EAs?) And the main motivation is that this is more performant than those options?

Trying to make sure i understand correctly: this seems equivalent todf.loc[rows, cols].to_numpy().ravel()? (or maybedf.loc[rows, cols].stack().values might be better for preserving EAs?) And the main motivation is that this is more performant than those options?

Hi@jbrockmendel -- df.loc[rows, cols] returns all columns for all rows. Lookup only returns the values at paired columns and rows.

That makes sense, thanks. So more of adf[rows, cols].diag() (which doesnt exist)?

That makes sense, thanks. So more of adf[rows, cols].diag() (which doesnt exist)?

I think the best analogue from within pandas is is a for loop of .at[].

Overall I am -1 adding this back in. I think the utility of this function is limited in the general case of non-homogenous dataframes.

This pull request is stale because it has been open for thirty days with no activity. Pleaseupdate and respond to this comment if you're still interested in working on this.

@stevenae - I think we need agreement among core devs on whether this should be supported. While I'm sympathetic to users who found this useful prior to it's removal from pandas, there are a few arguments against which I find compelling.

• Other DataFrame libraries do not offer such a method (to my knowledge).
• The implementation can be achieved using existing functionality with what I measure (see below) as a 30% decrease in performance in non-homogeneous cases, and a 400% increase in performance in the homogeneous case.
• Methods that coerce to object dtype when used on non-homogeneous DataFrames is something that I would like to see less in the built-in methods of pandas, not more. Here it's my opinionnot that user's shouldn't be able to do it, but that it we should avoid it being built-in to pandas.

For the benchmark in bullet 2, I ran the code in#61185 (comment) with the following modification ofpd_lookup:

defpd_lookup(df,row_labels,col_labels):df=df.loc[:,sorted(set(col_labels))]rows=df.index.get_indexer(row_labels)cols=df.columns.get_indexer(col_labels)result=df.to_numpy()[rows,cols]returnresult

@stevenae - yes, I think that would be uncontroversial.

@stevenae - yes, I think that would be uncontroversial.

Okay! Will do in the next couple weeks

@rhshadrach doc update is at#61471