CODEWITHSUNDEEP
pythonpandasdataframecompare
Robin
Robin

Reputation: 37

Comparing pandas Dataframe columns values one by one

df

        Date    Factory I1  D1  I2  D2  I3  D3

   0    1701    West    0   0   0   0   10  0

   1    1701    East    39  0   83  15  3   15

   2    1701    West    0   0   0   10  15  0

I want this:

 If D2>I2 Then I3=I2+I3,D3=D2+D3,I2=D2=0

 If D3>I3 Then I2=I2+I3,D2=D2+D3,I3=D3=0

else no change

So the data should be like this:

df

     Date   Factory I1  D1  I2  D2  I3  D3

     0  1701    West    0   0   0   0   10  0

     1  1701    East    39  0   86  30  0   0

     2  1701    West    0   0   0   0   15  10

How can I do?Please help~

Upvotes: 1

Views: 988

Answers (2)

jezrael
jezrael

Reputation: 863711

You need mask with assign:

m1 = df.D2 > df.I2
m2 = df.D3 > df.I3
df = df.mask(m1, df.assign(I3=df.I2+df.I3, D3=df.D2+df.D3, I2=0, D2=0))
df = df.mask(m2, df.assign(I2=df.I2+df.I3, D2=df.D2+df.D3, I3=0, D3=0))
print (df)
   Date Factory  I1  D1  I2  D2  I3  D3
0  1701    West   0   0   0   0  10   0
1  1701    East  39   0  86  30   0   0
2  1701    West   0   0   0   0  15  10

If there are columns with spaces:

m1 = df.D2 > df.I2
m2 = df.D3 > df.I3

def f1(x):
    df['I3']=df.I2+df.I3 
    df['D3']=df.D2+df.D3 
    df['I2'] = df['D2'] = 0
    return df

def f2(x):
    df['I2']=df.I2+df.I3 
    df['D2']=df.D2+df.D3 
    df['I3'] = df['D3'] = 0
    return df

df = df.mask(m1, f1)
df = df.mask(m2, f2)
print (df)
   Date Factory  I1  D1  I2  D2  I3  D3
0  1701    West   0   0  10   0   0   0
1  1701    East  39   0  86  30   0   0
2  1701    West   0   0  15  10   0   0

Or if want use assign (I3 was changed to I 3):

m1 = df['D2'] > df['I2']
m2 = df['D3'] > df['I 3']

d1 = {'I 3': lambda x: x['I2'] + x['I 3'],'D3':lambda x: x['D2']+x['D3'],'I2':0,'D2':0}
d2 = {'I2': lambda x: x['I2'] + x['I 3'],'D2':lambda x: x['D2']+x['D3'],'I3':0,'D3':0}
df = df.mask(m1, df.assign(**d1))
df = df.mask(m2, df.assign(**d2))
print (df)
   Date Factory  I1  D1  I2  D2  I 3  D3
0  1701    West   0   0   0   0   10   0
1  1701    East  39   0  86  30    3   0
2  1701    West   0   0   0   0   15  10

Upvotes: 2

rnso
rnso

Reputation: 24623

One can use easily understandable for loop and work over each row: 

newvals = []
for i in range(len(df.index)):
    ROW = df.iloc[i,:]
    if ROW.D2>ROW.I2:
        ROW.I3=ROW.I2+ROW.I3
        ROW.D3=ROW.D2+ROW.D3
        ROW.I2=ROW.D2=0 
    if ROW.D3>ROW.I3:
        ROW.I2=ROW.I2+ROW.I3
        ROW.D2=ROW.D2+ROW.D3
        ROW.I3=ROW.D3=0
    newvals.append(list(ROW))
newdf = pd.DataFrame(data=newvals, columns=df.columns)
print(newdf)

Output:

   Date Factory  I1  D1  I2  D2  I3  D3
0  1701    West   0   0   0   0  10   0
1  1701    East  39   0  86  30   0   0
2  1701    West   0   0   0   0  15  10

Upvotes: 0

Related Questions