Filter on multiple columns in pandas data frame using a loop

Question

Context: I have a data in excel that we process through Pandas to clean up and then further use it in ML model. In clean-up process, I'm trying to filter data based on multiple columns as an OR condition. This set of columns has header name as start of week date -so these 7 columns would represent 7 weeks. This column's header name changes every week. Hence, I can't keep the consistent code in place to pick the header name automatically.

Logic That I Have tried: I wrote a code chunk to print the "OR" condition using this date columns, after that I copy paste this print statement in Data frame in-dices part. Below is how it looks like:

I'm copy pasting the column as of now. But I guess I can built a logic to identify the date column by applying type-based-condition to column names

Sample Data:

 1/20/2019 1/27/2019  2/3/2019 2/10/2019    2/17/2019 2/24/2019  3/3/2019  \
0   0(80CS,8H)   0(80CS)   0(80CS)   0(80CS)      0(80CS)   0(80CS)   0(80CS)   
1   0(50CS,8H)   0(50CS)   0(50CS)   0(50CS)      0(50CS)   0(50CS)   0(50CS)   
2   0(40CS,8H)   0(40CS)   0(40CS)   0(40CS)      0(40CS)   0(40CS)   0(40CS)   
3   0(40CS,8H)   0(40CS)   0(40CS)   0(40CS)      0(40CS)   0(40CS)   0(40CS)   
4   0(40CS,8H)   0(40CS)   0(40CS)   0(40CS)      0(40CS)   0(40CS)   0(40CS)   
5   0(40CS,8H)   0(40CS)   0(40CS)   0(40CS)      0(40CS)   0(40CS)   0(40CS)   
6  12(25CS,8H)  15(25CS)  15(25CS)  15(25CS)     15(25CS)  15(25CS)  15(25CS)   
7  11(28CS,8H)  12(28CS)  12(28CS)  12(28CS)     12(28CS)  12(28CS)  12(28CS)   
8   8(30CS,8H)  10(30CS)  10(30CS)  10(30CS)  2(30CS,32T)  10(30CS)  10(30CS)   
9   0(40CS,8H)   0(40CS)   0(40CS)   0(40CS)      0(40CS)   0(40CS)   0(40CS)   

  3/10/2019 3/17/2019 3/24/2019 3/31/2019  4/7/2019  
0   0(80CS)   0(80CS)   0(80CS)   0(80CS)   0(80CS)  
1   0(50CS)   0(50CS)   0(50CS)   0(50CS)   0(50CS)  
2   0(40CS)   0(40CS)   0(40CS)   0(40CS)   0(40CS)  
3   0(40CS)   0(40CS)   0(40CS)   0(40CS)   0(40CS)  
4   0(40CS)   0(40CS)   0(40CS)   0(40CS)   0(40CS)  
5   0(40CS)   0(40CS)   0(40CS)   0(40CS)   0(40CS)  
6  15(25CS)  15(25CS)  15(25CS)  20(20CS)  20(20CS)  
7  12(28CS)  12(28CS)  12(28CS)  12(28CS)  12(28CS)  
8  10(30CS)  10(30CS)  10(30CS)  10(30CS)  10(30CS)  
9   0(40CS)   0(40CS)   0(40CS)   0(40CS)   0(40CS)


avail_col = ['1/20/2019',
   '1/27/2019', '2/3/2019', '2/10/2019', '2/17/2019', '2/24/2019',
   '3/3/2019', '3/10/2019', '3/17/2019', '3/24/2019', '3/31/2019',
   '4/7/2019']

##changing the data type of selected columns
for i in avail_col:
    avail_dat[i] = avail_dat[i].astype(str).apply(lambda x: x.split('(')[0])
    avail_dat[i] = avail_dat[i].str.replace('-','0')
    avail_dat[i] = avail_dat[i].astype(float)


or_str = ''
for i in avail_col:
    or_str = "(avail_dat['"+i+"'] >= 24) | "
    print(or_str)

Apparently I can't pass the variable to data frame to filter or I don't know how to do that yet, So I copy paste the printed statement to the below code to filter the data frame

 avail_dat = avail_dat[(avail_dat['1/20/2019'] >= 24) | 
(avail_dat['1/27/2019'] >= 24) | 
(avail_dat['2/3/2019'] >= 24) | 
(avail_dat['2/10/2019'] >= 24) | 
(avail_dat['2/17/2019'] >= 24) | 
(avail_dat['2/24/2019'] >= 24) | 
(avail_dat['3/3/2019'] >= 24) | 
(avail_dat['3/10/2019'] >= 24) | 
(avail_dat['3/17/2019'] >= 24) | 
(avail_dat['3/24/2019'] >= 24) | 
(avail_dat['3/31/2019'] >= 24) | 
(avail_dat['4/7/2019'] >= 24)
 ]

Is there a way that I can pass a variable instead of copy pasting every time ?

provide sample data for above and expected output

Sociopath
– Sociopath

2019-02-11 16:43:26 +00:00
Commented Feb 11, 2019 at 16:43 — Sociopath
– Sociopath, Commented Feb 11, 2019 at 16:43

Joery · Accepted Answer · 2019-02-11 20:02:18Z

2

You can do so by performing each of your filters separately and then merging them later. Like so:

import numpy as np

# add all your boolean series to a list
all_masks = []
for col in avail_col:
    condition = (avail_dat[col] >= 24)
    all_masks.append(condition)

# use numpy to select the rows where any record evaluates to True
mask = np.array(all_masks).any(axis=0)
avail_dat.loc[mask]

answered Feb 11, 2019 at 20:02

Joery

3661 silver badge5 bronze badges

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

Comments

Lucas H · Accepted Answer · 2019-02-11 20:10:25Z

0

Woah. There is a lot to think about here.

Firstly, I think you can do a lot better with the selection of the columns. For example you could do the following to generate the list of columns you want (since you said they are in 7 day increments):

columns_you_want = list(pd.date_range(start='1/20/2019',freq=pd.DateOffset(days=7),end='4/7/2019').strftime('%m/%d/%Y'))

Then, you can just do:

df_avail = df.filter(columns_you_want)

Finally, something like:

df_avail[df_avail>24].dropna(how='any',axis=0)

Seems to be what you want, though I'm not sure about the last step since you didn't provide any desired output.

answered Feb 11, 2019 at 20:10

Lucas H

1,0279 silver badges16 bronze badges

1 Comment

Lucas H Over a year ago

In this formulation, you would leave the section called "changing the data type of selected columns"

alexshchep · Accepted Answer · 2019-02-11 20:11:04Z

0

If I understood correctly you are comparing the number before the parenthesis and ignoring the minus sign. If so, you could try to transpose the dataframe and then apply the extract function, or you could the split function like the one you wrote, which might work better if you actually have decimals:

dft = df.transpose()
for col in dft.columns:
    dft[col] = dft[col].str.extract(r'-?([0-9]+)\(.*').astype(float)
mask = dft >= 24

answered Feb 11, 2019 at 20:11

alexshchep

2681 silver badge16 bronze badges

1 Comment

jayant singh Over a year ago

Sorry, I forgot to mention that there are other columns in avail_dat df. Transposing would not be a best approach in that case

Collectives™ on Stack Overflow

Filter on multiple columns in pandas data frame using a loop

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related