CODEWITHSUNDEEP
pythonpandas
Shimada
Shimada

Reputation: 43

Match Value and Get its Column Header in python

Sample

I have 1000 by 6 dataframe, where A,B,C,D were rated by people on scale of 1-10.

In SELECT column, I have a value, which in all cases is same as value in either of A/B/C/D.

I want to change value in 'SELECT' to name of column to which it matches. For example, for ID 1, SELECT = 1, and D = 1, so the value of select should change to D.

import pandas as pd
df = pd.read_excel("u.xlsx",sheet_name = "Sheet2",header = 0)

But I am lost how to proceed.

Upvotes: 4

Views: 1549

Answers (3)

timgeb
timgeb

Reputation: 78690

Assuming the values in A, B, C, D are unique in each row with respect to SELECT, I'd do it like this:

>>> df
   ID  A  B  C  D  SELECT
0   1  4  9  7  1       1
1   2  5  7  2  8       2
2   3  7  4  8  6       8
>>>
>>> df_abcd = df.loc[:, 'A':'D']
>>> df['SELECT'] = df_abcd.apply(lambda row: row.isin(df['SELECT']).idxmax(), axis=1)
>>> df
   ID  A  B  C  D SELECT
0   1  4  9  7  1      D
1   2  5  7  2  8      C
2   3  7  4  8  6      C

Upvotes: 2

jezrael
jezrael

Reputation: 862691

Gwenersl solution compare all columns without ID and SELECT filtered by difference with DataFrame.eq (==), check first True value by idxmax and also if not exist matching value is set value no match with numpy.where:

cols = df.columns.difference(['ID','SELECT'])
mask = df[cols].eq(df['SELECT'], axis=0)

df['SELECT'] = np.where(mask.any(axis=1), mask.idxmax(axis=1), 'no match')
print (df)
   ID  A  B  C  D SELECT
0   1  4  9  7  1      D
1   2  5  7  2  8      C
2   3  7  4  8  6      C

Detail:

print (mask)
       A      B      C      D
0  False  False  False   True
1  False  False   True  False
2  False  False   True  False

Upvotes: 2

Vivek Kalyanarangan
Vivek Kalyanarangan

Reputation: 9081

Use - 

df['SELECT2'] = df.columns[pd.DataFrame([df['SELECT'] == df['A'], df['SELECT'] == df['B'], df['SELECT'] == df['C'], df['SELECT'] == df['D']]).transpose().idxmax(1)+1]

Output

    ID  A   B   C   D   SELECT  SELECT2
0   1   4   9   7   1   1   D
1   2   5   7   2   8   2   C
2   3   7   4   8   6   8   C

Upvotes: 1

Related Questions