Pandas dataframe filter dynamic columns

Question

I have this multiple datasets that are needed to be processed by pandas.

DF 1:
DATE DATA1_ID DATA
20200101 XXX 123
20200102 YYY 456
...

DF 2:
DATE DATA2_ID DATA
20200101 AAA 098
20200102 BBB 765
...

What I want to achieve is to have a generic filter for those two tables like using df.loc[df.DATA1_ID == "XXX"] without having to change the columns names on either of the data frames.

I want to search this "ID" basing from the IDs columns that will work on both dataframes.

Sample behavior when running the script on both data frame:

Filtering ID = XXX. Results would be DF 1: 20200101 XXX 123. DF 2:
Filtering ID = AAA. Results would be DF 1: . DF 2: 20200101 AAA 098
Filtering ID = OOO. Results would be DF 1: . DF 2:

Does the position of the ID column change? Or is it always the second column over in the dataframes? — Scott Boston
– Scott Boston, Commented Aug 27, 2020 at 17:28
Sorry for the late response. But yes. the ID column position changes between dataframes. — Dense04
– Dense04, Commented Sep 1, 2020 at 6:09

89f3a1c · Accepted Answer · 2020-08-27 17:15:56Z

1

I can think of two options:

rename the columns of interest to one unique name. Depending on the number of dataframes and meaning of column the columns' names, this may be too clumsy.
If the columns you're wishing to access are all on the same position in the dfs, you could access the nth column name in the columns' list. Something like df.columns[1].
You can find the id column:

id_cols = [col for col in df.columns if 'id' in col]

edited Aug 27, 2020 at 17:15

answered Aug 20, 2020 at 13:25

89f3a1c

1,4881 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dense04 Over a year ago

Hello, sorry for the extremely late response. (1) there is no build-in module in pandas that can achieve what I want? (2) The column of the IDs can is not constant and can change without prior notice.

89f3a1c Over a year ago

@Dense04 I added an option that finds the ID column, supposing the id column is the one containing 'id' in its name. Depending on the possible values of the column names, you may need to refine the filter, but you get the idea.

Collectives™ on Stack Overflow

Pandas dataframe filter dynamic columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related