Finding the common columns when comparing two rows in a dataframe in python

Question

I have a dataframe of the below structure. I want to get the column numbers which has the same value (for a specific value) when i compare two rows.

So for example when I use the above sample df to compare two rows to get the columns which has 1 in it, I should get col(1) and col(3) when I compare row(0) and row(1). Similarly, when I compare row(1) and row(2), I should get col(1). I want to know if there is a more efficient solution in python.

NB: I want only the matching column numbers and also I will specify the rows to compare.

Do you want the column number, or the whole column in return? — user2285236
– user2285236, Commented May 16, 2016 at 16:23
Do you want all pairwise comparisons or will you specify which rows to compare? — user2285236
– user2285236, Commented May 16, 2016 at 16:31

score 3 · Accepted Answer · 2016-05-16 16:43:18Z

3

Consider the following dataframe:

import numpy as np
df = pd.DataFrame(np.random.binomial(1, 0.2, (2, 10000)))

It will be a binary matrix of size 2x10000.

np.where((df.iloc[0] * df.iloc[1]))

Or,

np.where((df.iloc[0]) & (df.iloc[1]))

returns the columns that have 1s in both rows. Multiplication seems to be faster:

%timeit np.where((df.iloc[0]) & (df.iloc[1]))
1000 loops, best of 3: 400 µs per loop

%timeit np.where((df.iloc[0] * df.iloc[1]))
1000 loops, best of 3: 269 µs per loop

edited May 16, 2016 at 16:43

answered May 16, 2016 at 16:39

user2285236

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

Comments

mark s. · Accepted Answer · 2016-05-16 16:44:43Z

0

Here's a simple function. You can modify it as needed, depending on how you represent your data. I'm assuming a list of lists:

df = [[1,1,0,1,1],
      [0,1,0,1,0],
      [0,1,0,0,1],
      [1,0,0,0,1],
      [0,0,0,0,0],
      [1,0,0,0,1]]

def compare_rows(df,row1,row2):
    """Returns the column numbers in which both rows contain 1's"""
    column_numbers = []
    for i,_ in enumerate(df[0]):
        if (df[row1][i] == 1) and (df[row2][i] ==1):
            column_numbers.append(i)
    return column_numbers

compare_rows(df,0,1) produces the output:

[1,3]

answered May 16, 2016 at 16:44

mark s.

6562 gold badges7 silver badges14 bronze badges

Collectives™ on Stack Overflow

Finding the common columns when comparing two rows in a dataframe in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related