How to compare each value of row in a dataframe with each value in the row before with python?

user_13702063 0
Viewed 71

I have a dataframe, which looks something like this (number of columns and rows can differ):

                0         1         2
2015-01-02    ISIN1     ISIN2     ISIN3
2015-05-04    ISIN4     ISIN2     ISIN5
2015-09-01    ISIN4     ISIN5     ISIN6
2016-01-04    ISIN7     ISIN8     ISIN2
2016-05-02    ISIN9     ISIN7     ISIN10
2016-09-01    ISIN11    ISIN12    ISIN13
2017-01-02    ISIN11    ISIN12    ISIN14
2017-05-02    ISIN12    ISIN11    ISIN15
2017-09-01    ISIN12    ISIN16    ISIN17
2018-01-02    ISIN16    ISIN11    ISIN18
2018-05-02    ISIN4     ISIN8     ISIN7
2018-09-03    ISIN12    ISIN7     ISIN19
2019-01-02    ISIN20    ISIN21    ISIN22
2019-05-02    ISIN13    ISIN7     ISIN8
2019-09-02    ISIN23    ISIN24    ISIN15
2020-01-02    ISIN25    ISIN23    ISIN24
2020-05-04    ISIN24    ISIN26    ISIN4

My task is now to compare each value of each row with each value of the row before. I want to know if the value is in the row before or not. I want to get two dataframes as result.

  1. Keep the values which are not in the row before:

                    0         1         2
2015-01-02    ISIN1     ISIN2     ISIN3
2015-05-04    ISIN4     ISIN5
2015-09-01    ISIN6
2016-01-04    ISIN7     ISIN8     ISIN2
2016-05-02    ISIN9     ISIN10
2016-09-01    ISIN11    ISIN12    ISIN13
2017-01-02    ISIN14
2017-05-02    ISIN15
2017-09-01    ISIN16    ISIN17
2018-01-02    ISIN11    ISIN18
2018-05-02    ISIN4     ISIN8     ISIN7
2018-09-03    ISIN12    ISIN19
2019-01-02    ISIN20    ISIN21    ISIN22
2019-05-02    ISIN13    ISIN7     ISIN8
2019-09-02    ISIN23    ISIN24    ISIN15
2020-01-02    ISIN25    
2020-05-04    ISIN26    ISIN4

  2. Keep the values which are in the row before:

                    0         1         2
2015-01-02    
2015-05-04    ISIN2
2015-09-01    ISIN4     ISIN5
2016-01-04    
2016-05-02    ISIN7
2016-09-01    
2017-01-02    ISIN11    ISIN12    
2017-05-02    ISIN12    ISIN11    
2017-09-01    ISIN12    
2018-01-02    ISIN16    
2018-05-02    
2018-09-03    ISIN7
2019-01-02    
2019-05-02    
2019-09-02    
2020-01-02    ISIN23    ISIN24
2020-05-04    ISIN24

What I've explored so far:

for i in range(len(df)):
    print(np.isin(df.values[i, :], df.shift().values[i, :]))

creates this:

[False False False]
[False  True False]
[ True  True False]
[False False False]
[False  True False]
[False False False]
[ True  True False]
[ True  True False]
[ True False False]
[ True False False]
[False False False]
[False  True False]
[False False False]
[False False False]
[False False False]
[False  True  True]
[ True False False]

With appending this values to a list I would be able to create a new dataframe. But I think there must be a better way.

Does anyone have a clue how to do it without iterating through the dataframe?

Thank you very much!

Best regards, nepy

pandas dataframe compare numpy python
3 Answers

Here is a way to replace duplicate values by NaN:

df = pd.DataFrame(dict(a=[1,1,2,2,4], b=[0,5,6,6,8]), index=np.arange(5)+100)
mask = np.full_like(df, False, dtype=bool)
mask[1:] =  df.iloc[1:].reset_index(drop=True) == df.iloc[:-1].reset_index(drop=True)
df[mask] = None

The reset_index operations are needed because otherwise, pandas will attempt to do the == comparison on matching row indices.

Original DataFrame:

     a  b
100  1  0
101  1  5
102  2  6
103  2  6
104  4  8

After:

       a    b
100  1.0  0.0
101  NaN  5.0
102  2.0  6.0
103  NaN  NaN
104  4.0  8.0

For the reverse, you need to do 

mask = np.logical_not(mask)
user_62288910

Hey maybe You are looking for something like:

data = {'first': ['ok', 'none', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'none', 'ok'],
        'second': [1, 3, 4, 7, 8, 2, 4, 9, 6, 9]}
df = pd.DataFrame(data, columns = ['first', 'second'])

df_results = df.eq(df.shift())
df_results.where(df_results != False, df)

Hope it help

user_110141170

I digged a deep further. My solution is now:

import pandas as pd
import numpy as np

row_0 = np.array(['ISIN1', 'ISIN4', 'ISIN4', 'ISIN7', 'ISIN9', 'ISIN11', 'ISIN11', 'ISIN12', 'ISIN12', 'ISIN16', 'ISIN4', 'ISIN12', 'ISIN20', 'ISIN13', 'ISIN23', 'ISIN25', 'ISIN24'])
row_1 = np.array(['ISIN2', 'ISIN2', 'ISIN5', 'ISIN8', 'ISIN7', 'ISIN12', 'ISIN12', 'ISIN11', 'ISIN16', 'ISIN11', 'ISIN8', 'ISIN7', 'ISIN21', 'ISIN7', 'ISIN24', 'ISIN23', 'ISIN26'])
row_2 = np.array(['ISIN3', 'ISIN5', 'ISIN6', 'ISIN2', 'ISIN10', 'ISIN13', 'ISIN14', 'ISIN15', 'ISIN17', 'ISIN18', 'ISIN7', 'ISIN19', 'ISIN22', 'ISIN8', 'ISIN15', 'ISIN24', 'ISIN4'])

data = {0:row_0, 1:row_1, 2:row_2}

df = pd.DataFrame(data)
print(df)
df_in_row_before = df[pd.DataFrame(np.array([np.isin(df.values[i, :], df.shift().values[i, :]) for i in range(len(df))]))]

print(df_in_row_before)
df_not_in_row_before = df[pd.DataFrame(np.array([np.isin(df.values[i, :], df.shift().values[i, :], invert=True) for i in range(len(df))]))]
print(df_not_in_row_before)

This makes exactly what i needed. But if anyone have a better solution I'm happy to look at.

user_137020630
Related