'Pandas: How to find the column that has certain value
I have several Excel files where several people send some information, but as people is too creative, they didn't preserve all the Excel template as instructed, so some files have 10 columns, others have 12, and so on.
There's a column that has only two possible values, "foo" and "bar", but it's not always in the same column, and the column aren't always named the same, so you can have "Thing" in column 12 and "THING (foo/bar)" in column 9
I'm writting a script to clean all this data (almost 200 files), and I need to get the foos and bars.
So my question is, is there a way to look in the dataframe for this patterns and tell me in which column are they?
Once I get in which column are the foos and bars, the rest of the problem is trivial, since the first column is always right, so I could use my extracted dataframe as df[[0, n]] where n is where the foos and bars are
Currently what I'm doing is iterating over the column names like:
for i in df.columns:
if len(df.loc[(df[i]=='foo') | (df[i]=='bar')]) > 0:
print(f'Found {i}')
But I really don't like the solution, I guess there must be a better way
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
