Simpler way to compare multiple cells and return value of cells that are similar in another column in Python

Melon123 0
Viewed 22

I have this Dataset (df) with 3 dob columns as they are from different sources

Name    DOB_1           DOB_2        DOB_3** 
Ann     20.05.19        20.05.19     20.05.19
Ben     31.10.16        30.10.16     30.10.16
Clara   28.04.16        22.04.16     29.05.16
Des     NaN             09.06.20     09.06.20
Ein     Did not report  09.07.97     09.07.97

I want to compare the DOB columns to create a new column ('DOB_Check'. If 2 or more DOBs are the same, I want the dates that are the same to be returned in the new column. However, if all 3 dates are different, I want 'To check manually' to be returned. The following is the desired output:

Name    DOB_1           DOB_2        DOB_3      DOB_Check
Ann     20.05.19        20.05.19     20.05.19   20.05.19
Ben     31.10.16        30.10.16     30.10.16   30.10.16
Clara   28.04.16        22.04.16     29.05.16   To check manually 
Des     NaN             09.06.20     09.06.20   09.06.20
Ein     Did not report  09.07.97     09.07.97   09.07.97

I ended up listing all the possible variations in conditions and outcomes and used np.select:

DOB_Conditions = [df['DOB_1'] == df['DOB_2'], df['DOB_1'] == df['DOB_3'], df['DOB_2'] == df['DOB_3']]
DOB_Output = [df['DOB_1'],df['DOB_1'],df['DOB_2']]

df['DOB_Check'] = np.select(DOB_Conditions, DOB_Output , default = 'To check manually')

This code works well but was wondering if there is a simpler way that I'm missing? Especially if I have more DOB sources/columns which will result in a lot more condition and outcome variations.

Appreciate any thoughts on this. Thank you.

compare pandas python
1 Answers

IIUC, you can stack, value_counts, filter the counts below threshold, then reindex with your custom fill_value:

df['DOB_Check'] = (df
   .filter(like='DOB')               # keep only DOB columns
   .replace('Did not report', pd.NA) # optional, to ensure not using this as value
   .stack().groupby(level=0).value_counts() # counts per row
   .loc[lambda x: x>1]                      # remove DOB with count <= 1
   .reset_index(level=1)['level_1']
  #.groupby(level=0).first()  # uncomment if more than 3 DOB columns
   .reindex(df.index, fill_value='To check manually')
)

output:

    Name           DOB_1     DOB_2     DOB_3          DOB_Check
0    Ann        20.05.19  20.05.19  20.05.19           20.05.19
1    Ben        31.10.16  30.10.16  30.10.16           30.10.16
2  Clara        28.04.16  22.04.16  29.05.16  To check manually
3    Des             NaN  09.06.20  09.06.20           09.06.20
4    Ein  Did not report  09.07.97  09.07.97           09.07.97
mozway0
Related