CODEWITHSUNDEEP
pythonpandasdataframe
vesuvius
vesuvius

Reputation: 435

Compare two columns in a dataframe and update the result in another column

I have an excel file which I imported as a dataframe. The dataset looks like this:

rule_id reqid1  reqid2  reqid3  
53139    0         0     1           
51181    1         1     0           
50412    0         1     1           
50356    0         0     1           
50239    0         1     0           
50238    1         1     0           
50014    1         0     1

I have converted the rule_id column into the index. I want the result to look like this:

rule_id reqid1  reqid2  reqid3  comparison1 comparison2 last_comp
53139    0         0     1           NaN         NaN         100
51181    1         1     0           1.0         50.0         0
50412    0         1     1           NaN         1.0          50
50356    0         0     1           NaN         NaN         100
50239    0         1     0           NaN         100.0        0
50238    1         1     0           1.0         50.0         0
50014    1         0     1           100.0       NaN         100

comparison1 column is the value comparison between reqid1 and reqid2 , comparison2 is the value comparison between reqid2 and reqid3 and last_comp is the value comparison between reqid3 and reqid4 but reqid4 is not available. So , the logic for these values is if I am comparing two columns and if both the columns has value of 0 then Null value will be captured in the new column. If the first column has 1 and the second column has 0 then 100 should be captured. If both the columns have 1 , then 1 should be captured in comparison1 column but if in reqid3 the value is 0 then in comparison2 100/2 , that is 50 should be captured. If in reqid3 , if the value is 0 then 0 should be captured in last_comp column and if the value is 1 , then 100 should be captured. But if reqid2 and reqid3 both have 1 , then 50 should be captured.

I am not able to write the code for this. Any type of help would be much appreciated.

Upvotes: 0

Views: 2081

Answers (2)

tasha
tasha

Reputation: 86

You'll need to figure out your logic. From what you wrote, this might cover the first two extra columns, using pandas for your dataframe.

import pandas as pd
# data
d = {'rule_id': [53139,51181,50412,50356,50239,50238,50014], 'reqid1':[0,1,0,0,0,1,1], 'reqid2':[0,1,1,0,1,1,0], 'reqid3':[1,0,1,1,0,0,1]}

df = pd.DataFrame(data=d)
# reorder columns
cols = df.columns.tolist()
cols = cols[-1:]+cols[:-1]
df = df[cols]

dataframe:

 rule_id  reqid1  reqid2  reqid3
0    53139       0       0       1
1    51181       1       1       0
2    50412       0       1       1
3    50356       0       0       1
4    50239       0       1       0
5    50238       1       1       0
6    50014       1       0       1

then logic for new columns:

c1 = list(map(lambda a,b: a if a==b else 100*a, df.reqid1, df.reqid2 ))
df['comp1']=c1

c2 = list(map(lambda b,c,c1: b if b==c else (b if b < c else 100/(b+c1)), df.reqid2, df.reqid3, df.comp1 ))
df['comp2']=c2


# convert your zeros to Nans with numpy:
import numpy as np

comps = ['comp1', 'comp2']
df[comps] = df[comps].replace({0:np.nan})

output:

   rule_id  reqid1  reqid2  reqid3  comp1  comp2
0    53139       0       0       1    NaN    NaN
1    51181       1       1       0    1.0   50.0
2    50412       0       1       1    NaN    1.0
3    50356       0       0       1    NaN    NaN
4    50239       0       1       0    NaN  100.0
5    50238       1       1       0    1.0   50.0
6    50014       1       0       1  100.0    NaN

Upvotes: 0

w-m
w-m

Reputation: 11232

Here is some simple code to get you started:

# Compare method, gets a row containing two values as input
def compare_values(row):
    a = row[0]
    b = row[1]

    # One of the rules
    if a == 1 and b == 0:
        return 100

    # TODO: implement other rules

    return None

# apply the `compare_values` method to all rows of ["reqid1", "reqid2"]
df["comparison1"] = df[["reqid1", "reqid2"]].apply(compare_values, axis=1)

# TODO: comparison2

I've left a couple of things for you to implement to get the exact output you want. But using this structure, you should be able to follow through.

Upvotes: 3

Related Questions