Pandas vectorized way to get counts using conditional statement between two dataframes

user_6824915 0
Viewed 58

I have two dataframes (with unequal rows, but the same columns) such as the following.

DataFrame A:

dummy | probability
-------------------
  0   |    .1
-------------------
  0   |    .2

DataFrame B:

dummy | probability
-------------------  
  1   |    .05
-------------------
  1   |    .2

What I would like to do is implement a vectorized conditional check for pairwise elements.

My actual dataset has a few hundred thousand elements. So if I check element wise, using a double for loop would require at least 100000^2 iterations which I do not want at all.

I believe there is probably a way to do this using numpy and pandas that I am currently unaware of.

pseudocode should look something like this:

def vectorized_counts():

    A = 0
    B = 0
    tie = 0

    if element in dfA second column > element in dfB second column:
        A += 1
    elif element in dfA second column < element in dfB second column:
        B += 1
    else:
        tie += 1

    return list(A,B,tie)

For my test example above, we have:

A
.1 > .05
.2 > .05

B
.1 < .2

tie
.2 = .2

Hence:

A = 2
B = 1
tie = 1

What way can I go about this? It is simple enough to compare one element of a dataframe with the corresponding element of another dataframe. What is confusing me is how to compare every element of a dataframe with every element of another dataframe.

pandas numpy python
1 Answers

Here is an iteration-less function that (hopefully) does what you need:

def compare_probabilities(A, B):
    df = pd.concat([A] * B.shape[0], axis=0).reset_index(drop=True)
    df['Ap'] = df.probability
    df['Bp'] = B.probability.repeat(A.shape[0]).values
    AgtB = (df.Ap > df.Bp).sum()
    BgtA = (df.Ap < df.Bp).sum()
    #AeqB = (df.Ap == df.Bp).sum()
    AeqB = df.shape[0] - (AgtB + BgtA)
    return AgtB, BgtA, AeqB

A = pd.DataFrame({'dummy':[0,0], 'probability':[0.1,0.2]})
B = pd.DataFrame({'dummy':[1,1], 'probability':[0.05,0.2]})
print ("compare_probabilities: A>B is %d; B>A is %d;  A==B is %d"%compare_probabilities(A, B))

This should display:

compare_probabilities: A>B is 2; B>A is 1; A==B is 1

And it should work for unequal row sizes in A and B.

user_112223440
Related