Comparing column values within a row within a Pandas Dataframe

Question

I have a specific and a general problem I'm trying to solve.

Specific Problem: I want to create a new column in a data frame that gives a 1 if column C1 is 8 and all other values in the row are less than 8.How do I logically negate all of the other columns at the same time? Here is the code from my flawed attempt:

df["C1is_8"] = df.apply(lambda row:(row['C1']==8)& ~(row['C1']<8) ,axis=1).astype(int)

The code below produces the dataframe for the code above.

dict = { 'C1':[4,3,0,0,2,3,4,5,8,8,8,8],
         'C2':[8,3,3,7,6,5,3,5,6,8,8,8],
         'C3':[2,3,6,4,5,0,0,4,6,7,8,8],
         'C4':[8,5,4,4,4,3,2,1,4,2,6,8]
       }
columns = ['C1','C2','C3','C4']
Index = [1,2,3,4,5,6,7,8,9,10,11,12]
df = pd.DataFrame(dict,index = Index,columns = columns)
df = OGdf[::-1]
df

General Problem: How do I rewrite some version of the code above so that I can generalize it (i.e. row[ i ] ) so that it could apply to any column not just 'C1'?

sacuL · Accepted Answer · 2018-11-17 00:58:24Z

2

I think this answer satisfies both your specific and your general problems, using filter and all:

# define the column you want to apply your first condition to
col = 'C1'

# Python 3.6 or above, with f-strings:
df['new_col'] = ((df[col] == 8) & (df.filter(regex=f'[^{col}]') < 8).all(1)).astype(int)
# Otherwise:
df['new_col'] = ((df[col] == 8) & (df.filter(regex='[^{}]'.format(col)) < 8).all(1)).astype(int)

>>> df
    C1  C2  C3  C4  new_col
1    4   8   2   8        0
2    3   3   3   5        0
3    0   3   6   4        0
4    0   7   4   4        0
5    2   6   5   4        0
6    3   5   0   3        0
7    4   3   0   2        0
8    5   5   4   1        0
9    8   6   6   4        1
10   8   8   7   2        0
11   8   8   8   6        0
12   8   8   8   8        0

answered Nov 17, 2018 at 0:58

sacuL

51.6k9 gold badges88 silver badges115 bronze badges

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

2 Comments

Karn Kumar Over a year ago

@ sacul, will mind explaining all, though i understand pandas.Series.all : Return True if all elements are True , i think when you say all(1) ie across the columns

sacuL Over a year ago

@pygo, that's right, it's checking that all values (besides the one filtered out) in a row (i.e. across all columns) satisfies the < 8 condition

Collectives™ on Stack Overflow

Comparing column values within a row within a Pandas Dataframe

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