Find the column with duplicate value across each row & get related values

user_11232091 0
Viewed 68

I have a df as below

A       B       C       D       R       S       T       U
44.562  42.975  39.252  40.973  37.994  39.191  38.69   40.973
42.621  40.79   40.741  40.887  40.863  40.204  40.131  40.887
38.751  39.301  43.756  40.68   38.397  39.301  42.133  38.788
44.562  41.229  43.2    41.59   43.561  41.01   43.2    41.486
40.472  41.278  43.951  41.99   42.55   41.083  42.572  40.472

In each of these rows, there is a duplicate value.

Columns A,B,C & D are a group & columns R,S,T & U are a family. What I am trying to do is find the column in group R,S,T & U which are same value as one of the column from A,B,C & D and then get the value from the corresponding row in a new column res

Also, get value from a 'linked column' (A is linked to D & B is linked to C) in a new column res1

Expected Output

A       B       C       D       R       S       T       U       res     res1
44.562  42.975  39.252  40.973  37.994  39.191  38.69   40.973  40.973  44.562
42.621  40.79   40.741  40.887  40.863  40.204  40.131  40.887  40.887  42.621
38.751  39.301  43.756  40.68   38.397  39.301  42.133  38.788  39.301  43.756
44.562  41.229  43.2    41.59   43.561  41.01   43.2    41.486  43.2    41.229
40.472  41.278  43.951  41.99   42.55   41.083  42.572  40.472  40.472  41.99
pandas python-3.x python
3 Answers

One way is to run a set on each sub array to get the values that match - that should get us the values for res

forward = df.iloc[:, :4].to_numpy()
backward = df.iloc[:, 4:].to_numpy()

res = [np.intersect1d(first, last) for first, last in zip(forward, backward)]

cond = np.vstack([np.isin(arr, boolean) for arr, boolean in zip(forward, res)])

cond[:, [0, -1]] = cond[:, [-1, 0]]
cond[:, [1, 2]] = cond[:, [2, 1]]

res1 = forward[cond]

df.assign(res=np.concatenate(res), res1=res1)
user_71757130

@sammywemmy Thanks a lot for your help. Based on the answer you posted, i was able to figure out the solution to my problem. The code is below.

your np.intersect1d & np.concatenate were most helpful. If you can put the answer again, I will up-vote.

forward = df.iloc[:, :4].to_numpy()
backward = df.iloc[:, 4:].to_numpy()

res = np.concatenate([np.intersect1d(first, last)
                      for first, last in zip(forward, backward)]
                      )
df['res'] = res
a = df.loc[:,'A':'D'].isin(res).idxmax(1)
d = {"A":"D", "D":"A", "C":"B", "B":"C"}
df['res1'] = df.lookup(df.index,a.map(d))

Output

A   B   C   D   R   S   T   U   res     res1
44.562  42.975  39.252  40.973  37.994  39.191  38.690  40.973  40.973  44.562
42.621  40.790  40.741  40.887  40.863  40.204  40.131  40.887  40.887  42.621
38.751  39.301  43.756  40.680  38.397  39.301  42.133  38.788  39.301  43.756
44.562  41.229  43.200  41.590  43.561  41.010  43.200  41.486  43.200  41.229
40.472  41.278  43.951  41.990  42.550  41.083  42.572  40.472  40.472  41.990

​

user_112320910 
import pandas as pd
from io import StringIO
df = pd.read_csv(StringIO("""A       B       C       D       R       S       T       U
44.562  42.975  39.252  40.973  37.994  39.191  38.69   40.973
42.621  40.79   40.741  40.887  40.863  40.204  40.131  40.887
38.751  39.301  43.756  40.68   38.397  39.301  42.133  38.788
44.562  41.229  43.2    41.59   43.561  41.01   43.2    41.486
40.472  41.278  43.951  41.99   42.55   41.083  42.572  40.472"""),sep='\s+')

df['res'] = df.apply(lambda x: list(set(x[0:4]) & set(x[5:9]))[0],axis=1)
df['res1'] = df.apply(lambda x: x[3-(x == x['res']).argmax()], axis=1)
df

+----+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
|    |      A |      B |      C |      D |      R |      S |      T |      U |    res |   res1 |
+====+========+========+========+========+========+========+========+========+========+========+
|  0 | 44.562 | 42.975 | 39.252 | 40.973 | 37.994 | 39.191 | 38.69  | 40.973 | 40.973 | 44.562 |
|  1 | 42.621 | 40.79  | 40.741 | 40.887 | 40.863 | 40.204 | 40.131 | 40.887 | 40.887 | 42.621 |
|  2 | 38.751 | 39.301 | 43.756 | 40.68  | 38.397 | 39.301 | 42.133 | 38.788 | 39.301 | 43.756 |
|  3 | 44.562 | 41.229 | 43.2   | 41.59  | 43.561 | 41.01  | 43.2   | 41.486 | 43.2   | 41.229 |
|  4 | 40.472 | 41.278 | 43.951 | 41.99  | 42.55  | 41.083 | 42.572 | 40.472 | 40.472 | 41.99  |
+----+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
user_56602840
Related