subtracting unmatching column from two dataframe in python-CodePudding

I have two dataframes(missingData and bias) and one Series(missingDateUnique).

missingDateUnique = pd.Series({0: 2459650, 9: 2459654})

missingDate = pd.DataFrame({0: [2459650, 2459650,2459650,2459654,2459654,2459654], 1: [10, 10,10,14,14,14]},index=[0,1,2,9,10,11])

bias = pd.DataFrame({0: [2459651, 2459652,2459653,2459655,2459656,2459658,2459659], 1: [11, 12,13,15,16,18,19]})

As missingDateUnique values are not in bias dataFrame. I have to check for i 1 in bias dataframe and subtract the 1's column value with missingDate's value.

I was doing it like this

for i in missingDateUnique:
   if i 1 in bias[0].values:
       missingDate[1] = missingDate[1].sub(missingDate[0].map(bias.set_index(0)[1]),fill_value=0)

The result should be like---

In missingDate's 1st row instead of 10 it should be 11-10=1

Full output-----

2459650     1
2459650     1
2459650     1
2459654     1
2459654     1
2459654     1

For example 2459654 in missingDate i have to check for 2459655 and 2459653 both in bias and subtract with any one from that. If both 2459655 and 2459653 are not present then I have to check for 2459656 and 2459652 and so on

CodePudding user response：

You can subtract 1 from bias column 0 and map it to missingDate column 0

missingDate[2] = missingDate[0].map(bias.assign(key=bias[0]-1).set_index('key')[1]) - missingDate[1]

print(missingDate)

          0   1  2
0   2459650  10  1
1   2459650  10  1
2   2459650  10  1
9   2459654  14  1
10  2459654  14  1
11  2459654  14  1