Iterate over two columns at the same time and change value of cells based on conditions

Viewed 327

I have a dataframe in the following format:

Index Object1-Length Object1-Height Object2-Length Object2-Height
0 19 49 21 52
1 20 50 21 51
2 20 51 20 52
3 19 50 19 52
4 20 50 20 52

It continues with Object3, Object4 and so on...

I want to check two columns at the same time in the following way:

if ObjectX-Length >= 20 and ObjectX-Height >= 50 

then set both cells of ObjectX to 1, else set them to 0

So this is the table I want:

Index Object1-Length Object1-Height Object2-Length Object2-Height
0 0 0 1 1
1 1 1 1 1
2 1 1 1 1
3 0 0 0 0
4 1 1 1 1

Is there any way to do this?

EDIT: Combining both columns of each object into one column and setting that cell to 0 or 1 would also work!

4 Answers

Let's try:

# length like columns
l = df.filter(like='-Length').columns

# corresponding height columns
h = l.str.rstrip('Length') + 'Height'

# create boolean mask
m = (df[l].ge(20).values & df[h].ge(50).values).astype(int)

# assign the values
df[h], df[l] = m, m

Details:

First filter the Length like columns, then create the corresponding Height columns:

print(l)
['Object1-Length', 'Object2-Length']

print(h)
['Object1-Height', 'Object2-Height']

Create boolean mask representing the condition where the ObjectX-Length >= 20 and ObjectX-Height >= 50:

print(m)
array([[0, 1],
       [1, 1],
       [1, 1],
       [0, 0],
       [1, 1]])

assign the mask to corresponding columns:

print(df)
       Object1-Length  Object1-Height  Object2-Length  Object2-Height
Index                                                                
0                   0               0               1               1
1                   1               1               1               1
2                   1               1               1               1
3                   0               0               0               0
4                   1               1               1               1

not too elegant way:

r = [] 
for n in range(1, x): # where x = maximum X + 1 
    len_col = f'Object{n}-Length'
    height_col = f'Object{n}-Height'

    # create filter mask for current object {n}
    subset = df[[len_col, height_col]]
    msk = (df[len_col] >= 20) & (df[height_col] >= 50)

    subset.loc[msk,[len_col, height_col] = 1 
    subset.loc[~msk,[len_col, height_col] = 0 
    r.append(subset)


r = pd.concat(r)

You can filter all columns at once after removing the Index column. Then groupyby every two columns to set both to False if at least one is False.

df.set_index('Index', inplace=True)
df.ge([20,50]*(df.columns.size//2)).groupby(np.arange(df.columns.size)//2, axis=1).transform('min').astype('int')

Out:

       Object1-Length  Object1-Height  Object2-Length  Object2-Height
Index                                                                
0                   0               0               1               1
1                   1               1               1               1
2                   1               1               1               1
3                   0               0               0               0
4                   1               1               1               1

You can get help from numpy and reshape. Using reshape you can "move" each pair to a temporary 3rd dimension, then you can have a natural rows consisting the "interesting" columns you want to compare.

After this you can revert the reshape yo your original dataframe shape.

import pandas as pd
import numpy as np

# example dataset
df = pd.DataFrame(np.random.randint(100, size=(8, 6)), columns = ['A-L', 'A-H', 'B-L', 'B-H', 'C-L', 'C-H'])

m, n = df.shape
data = df.values

data = data.reshape((m, 2, -1), order='F')
cond = (data[:,(0,),:] >= 20) & (data[:,(1,),:] >= 50)
new_data = np.repeat(cond, 2, axis=1)
new_data = new_data.reshape((m, -1), order='F')
new_df = pd.DataFrame(new_data, columns=df.columns)

print(df)
print(new_df)


   A-L  A-H  B-L  B-H  C-L  C-H
0   36   99    0   65   44   41
1   31   52    7   33   27   80
2   39   89   57   18   72   76
3    4   13   11   38   21    9
4   72    4    9   16   78   96
5   26   79   80   53   36   81
6   81   59   93   49   39   24
7   45   49   52   54   63   70

     A-L    A-H    B-L    B-H    C-L    C-H
0   True   True  False  False  False  False
1   True   True  False  False   True   True
2   True   True  False  False   True   True
3  False  False  False  False  False  False
4  False  False  False  False   True   True
5   True   True   True   True   True   True
6   True   True  False  False  False  False
7  False  False   True   True   True   True
Related