1

I have been trying to search a dataframe for a list of numbers, every time a number matches in a column I would like to return the whole row and save it to a new dataframe, and then to an excel.

millreflist is the list of numbers - can be of random length.

TUCABCP is the dataframe I am searching.

PO is the column I am searching in for the numbers.

I have tried the code below using .loc, but when opening the new excel file I am just getting the header and no rows or data.

millreflistlength = len(millreflist)

for i in range(millreflistlength): TUCABCP = TUCABCP.loc[TUCABCP['PO'] == millreflist[i]]

TUCABCP.to_excel("NEWBCP.xlsx", header=True, index=False)

I have used the following question for reference, but it does not cover when you would like to search with a list of numbers: Selecting rows from a Dataframe based on values in multiple columns in pandas

Community
  • 1
  • 1
Shah
  • 11
  • 2
  • 1
    Please add an example dataset in text format. Have a read on [how to ask a good pandas question](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – Erfan Jan 27 '20 at 18:14

1 Answers1

1

Try something like this:

## Get list, where each element is the index of a row which you want to keep
indexes = TUCABCP[TUCABCP['PO'].isin(millreflist)]
## Filter the original df to get just the rows with indexes in the list
df = TUCABCP[TUCABCP.index.isin(indexes)]
OD1995
  • 1,647
  • 4
  • 22
  • 52
  • 2
    only need `df = TUCABCP.loc[TUCABCP['PO'].isin(millreflist)]` – ansev Jan 27 '20 at 18:13
  • @ansev Your answer worked. Just curious why was my code returning nothing back? – Shah Jan 27 '20 at 18:23
  • @Shah You were re-writing the value of `TUCABCP` in each loop. I guess there were no rows which had the final element of `millreflist` in the `'PO'` column, so you outputted an empty df – OD1995 Jan 27 '20 at 18:26
  • I fyou use the same excel name you rewrite the file. In addition, You don't need a loop here. You can use `Series.isin` a loop is slow – ansev Jan 27 '20 at 18:34