How to get the unmatched column name Using Python

user_7735179 0
Viewed 199

I am trying to Match multiple column in different sets and update an another column with the all the unmatched column name separated by , For Eg:

Update the result column with the unmatched column name

Input Data:

Col A   Col B   Col C   Col D   Col E  Col F

Ind      Aus    Chi     Ind      Aus
Aus      Usa    Nz      Aus      Uk
Chi      Ind    Chi     Ind     
Ber      Ger    Sri     Ber      Nz
Ind      Aus    Chi     Chi      Aus

Expected Output:

      Col F
Col AD, Col BC Unmatched
Col BC Unmatched

Col BC Unmatched
Col AD, Col BC Unmatched

Script i have been using so far:

if Col E != " ":
  if Col A  != Col D  && Col B != Col C:
     df['Col F'] = "Col AD, Col BC Unmatched"
  else:
     df['Col F'] = "Matched"

Not able to understand how to perform this

pandas python
2 Answers

Let me make an example.

You got something like

   A  B  C  D  E
0  f  e  b  a  d
1  c  b  a  c  b
2  f  f  a  b  c
3  d  c  c  d  c
4  f  b  b  b  e
5  b  a  f  c  d

Then you want to see if, for example, cols A == D and cols B == C and put the result in a new column as a string, right?

If this is the case, we can do a for loop

for idx in df.index:
    unmatch_list = []
    if not df.loc[idx, 'A'] == df.loc[idx, 'D']:
        unmatch_list.append('AD')
    if not df.loc[idx, 'B'] == df.loc[idx, 'C']:
        unmatch_list.append('BC')
    # etcetera...
    if len(unmatch_list):
        unmatch_string = ', '.join(unmatch_list) + ' Unmatched'
    else:
        unmatch_string = 'ALL MATCHED'
    df.loc[idx, 'MATCHES'] = unmatch_string

that gives

   A  B  C  D  E           MATCHES
0  f  e  b  a  d  AD, BC Unmatched
1  c  b  a  c  b      BC Unmatched
2  f  f  a  b  c  AD, BC Unmatched
3  d  c  c  d  c       ALL MATCHED
4  f  b  b  b  e      AD Unmatched
5  b  a  f  c  d  AD, BC Unmatched
user_68221780

Iam not sure about your question, as you state that you want all unmatched columns in the new column but your expected output seems to be something else.

If you only want some columns check, then you can do as @Max Perini shows. In addition to it you should check with an if statement if the unmatch_list is empty and then apply "Matched" to the new column.

If you really want ALL unmatched columns if Col E != '', you can do:

df = pd.DataFrame(
{
    "Col A": ["Ind", "Aus", "Chi", "Ber", "Ind"],
    "Col B": ["Aus", "Usa", "Ind", "Ger", "Aus"],
    "Col C": ["Chi", "Nz", "Chi", "Sri", "Chi"],
    "Col D": ["Ind", "Aus", "ind", "Ber", "Chi"],
    "Col E": ["Aus", "Uk", "", "Nz", "Aus"],
    "Col F": ["", "", "", "", ""],
})


for idx, row in df.iterrows():
    if row['Col E'] != '':
        match_str = ""
        for j, valA in enumerate(row):
            for k, valB in enumerate(row[(j+1):-2]):
                if valA != valB:
                    match_str += f"Col {chr(ord('A')+j)}{chr(ord('A')+(k+j+1))}, "
        if match_str == '':
            df.loc[idx, 'Col F'] = 'Matched'
        else:
            match_str = match_str[:-2] + " Unmatched"
            df.loc[idx, 'Col F'] = match_str
    else:
        df.loc[idx, 'Col F'] = ''

yielding:

  Col A Col B  ... Col E                                             Col F
0   Ind   Aus  ...   Aus  Col AB, Col AC, Col BC, Col BD, Col CD Unmatched
1   Aus   Usa  ...    Uk  Col AB, Col AC, Col BC, Col BD, Col CD Unmatched
2   Chi   Ind  ...                                                        
3   Ber   Ger  ...    Nz  Col AB, Col AC, Col BC, Col BD, Col CD Unmatched
4   Ind   Aus  ...   Aus  Col AB, Col AC, Col AD, Col BC, Col BD Unmatched
user_153288020
Related