Find entries that do not match between columns and iterate through columns

user_6357649 0
Viewed 309

I have two datasets that I need to validate against. All records should match. I am having trouble in determining how to iterate through each different column. 

import pandas as pd 
import numpy as np

df = pd.DataFrame([['charlie', 'charlie', 'beta', 'cappa'], ['charlie', 'charlie', 'beta', 'delta'], ['charlie', 'charlie', 'beta', 'beta']], columns=['A_1', 'A_2','B_1','B_2'])

df.head()

Out[83]: 
       A_1      A_2   B_1    B_2
0  charlie  charlie  beta  cappa
1  charlie  charlie  beta  delta
2  charlie  charlie  beta   beta

For example, in the above code, I want to compare A_1 to A_2, and B_1 to B_2, to return a new column, A_check and B_check respectively, that return True if A_1 matches A_2 as the A_Check for instance.

Something like this: 

df['B_check'] = np.where((df['B_1'] == df['B_2']), 'True', 'False')
df_subset = df[df['B_check']=='False']

But iterable across any given column names, where columns that need to be checked against will always have the same name before the underscore and always have 1 or 2 after the underscore.

Ultimately, the actual task has multiple data frames with varying columns to check, as well as varying numbers of columns to check. The output I am ultimately going for is a data frame that shows all the records that were false for any particular column check.

pandas numpy python
7 Answers

With a bit more comprehensive regex:

from itertools import groupby
import re

for k, cols in groupby(sorted(df.columns), lambda x: x[:-2] if re.match(".+_(1|2)$", x) else None):
    cols=list(cols)
    if(len(cols)==2 and k):
        df[f"{k}_check"]=df[cols[0]].eq(df[cols[1]])

It will pair together only columns which name ends up with _1 and _2 regardless what you have before in their names, calculating _check only if there are 2- _1 and _2 (assuming you don't have 2 columns with the same name).

For the sample data:

       A_1      A_2   B_1    B_2  A_check  B_check
0  charlie  charlie  beta  cappa     True    False
1  charlie  charlie  beta  delta     True    False
2  charlie  charlie  beta   beta     True     True
user_116101860

You can use wide_to_long if you know the first part of the column names, i.e. A,B...:

(pd.wide_to_long(df.reset_index(), ['A','B'], 'index','part',sep='_')
   .groupby('index').nunique().eq(1)
   .add_suffix('_check')
)

Output:

       A_check  B_check
index                  
0         True    False
1         True    False
2         True     True
user_42384080

Another way is to use dataframe reshaping using pd.MultiIndexes:

df = pd.DataFrame([['charlie', 'charlie', 'beta', 'cappa'], 
                   ['charlie', 'charlie', 'beta', 'delta'], 
                   ['charlie', 'charlie', 'beta', 'beta']], 
                  columns=['A_1', 'A_2','B_1','B_2'])

df.columns = df.columns.str.split('_', expand=True) #Creates MultiIndex column header
dfs = df.stack(0) #move the 'A' and 'B' and any others to rows
df_out = (dfs == dfs.shift(-1, axis=1))['1'].unstack() #Compare column 1 to column 2 and move 'A's and 'B's back to columns.
print(df_out)

Output:

      A      B
0  True  False
1  True  False
2  True   True
user_63615310

You may split the columns and groupby along axis=1 on the series of first value of the split result and call agg to compare

i_cols = df.columns.str.split('_')
df_check = (df.groupby(i_cols.str[0], axis=1).agg(lambda x: x.iloc[:,0] == x.iloc[:,-1])
              .add_suffix('_check'))

In [69]: df_check
Out[69]:
   A_check  B_check
0     True    False
1     True    False
2     True     True
user_101892140

Why not something as simple as this:

for i in df.columns:
    col=i.split('_1')[0]
    if ('_1' in i)&(col+'_2' in df.columns):
        df[col+'_check']=np.where(df[col+'_1']==df[col+'_2'],True,False)

I'm iterating through the column names and checking if it has '_1' and a corresponding column with a '_2' after which I'm using the same np.where condition like you've used in the question.

Hope that helps.

user_126903440

Let

df = pd.DataFrame([['charlie', 'charlie', 'beta', 'cappa'], ['charlie', 'charlie', 'beta', 'delta'], ['charlie', 'charlie', 'beta', 'beta']], columns=['A_1', 'A_2','B_1','B_2'])

I have always been a fan of one-liners, even though it may not be very PEP8. Here's one way: 

df_check = pd.DataFrame([pd.Series(df[f'{col_root}_1']==df[f'{col_root}_2'], name=f'{col_root}_Check') for col_root in list(set([i.split('_')[0] for i in df.columns]))]).T

df_check:

   B_Check  A_Check
0    False     True
1    False     True
2     True     True
user_127300880

Here, I used lambda to iterate each row at a time. And then select which columns to compare using a if else statement. 

df['A_check'] = df.apply(lambda row: 'True' if row['A_1'] == row['A_2'] else 'False', axis=1)
df['B_check'] = df.apply(lambda row: 'True' if row['B_1'] == row['B_2'] else 'False', axis=1)
print(df)
user_40138090
Related