So I have an excel sheet that contains in this order:

Sample_name | column data | column data2 | column data ... n

I also have a .txt file that contains

Sample_name

What I want to do is filter the excel file for only the sample names contained in the .txt file. My current idea is to go through each column (excel sheet) and see if it matches any name in the .txt file, if it does, then grab the whole column. However, this seems like a nonefficient way to do it. I also need to do this using python. I was hoping someone could give me an idea on how to approach this better. Thank you very much.

CodePudding user response：

Excel PowerQuery should do the trick:

1. Load .txt file as a table (list)
2. Load sheet with the data columns as another table
3. Merge (e.g. Left join) first table with second table
4. Optional: adjust/select the columns to be included or excluded in the resulting table

In Python with Pandas’ data frames the same can be accomplished (joining 2 data frames)

P.S. Pandas supports loading CSV files and txt files (as a variant of CSV) into a data frame