Python: pandas: match row value to column name/ key's value

Question

Problem
"How to match a row value to a column name and take that intersecting value in pandas"

Context
We have a pandas df like this:

df = pd.DataFrame([{'name': 'john', 'john': 1, 'mac': 10}, {'name': 'mac', 'john': 2, 'mac': 20}], columns=["name", "john", "mac"])

Looking like this:

name | john | mac
john |  1   | 10
mac  |  2   | 20

Desired output

name | john | mac  | value
john |  1   | 10   | 1
mac  |  2   | 20   | 20

In words, the column "value" should take the number from the corresponding column where name intersects.

So, if name == 'john', then take the value from column 'john'
So, if name == 'mac', then take the value from column 'mac'

Tried so far
Bunch of lambdas (none successful).

Specifications
Python: 3.5.2
Pandas: 0.18.1

The definition of dataframe does not correspond to the table (different values) — Dennis Golomazov, Oct 21 '16 at 16:34

score 4 · Answer 1 · edited Nov 11 '21 at 05:13

4

Deprecation Notice: lookup was deprecated in v1.2.0

You could use DataFrame.lookup, which accepts the row and column labels to use:

In [66]: df
Out[66]: 
   name  john  mac
0  john     1   10
1   mac     2   20

In [67]: df["value"] = df.lookup(df.index, df.name)

In [68]: df
Out[68]: 
   name  john  mac  value
0  john     1   10      1
1   mac     2   20     20

Note that this will have problems with duplicate row labels (which could be trivially worked around by adding a reset_index). It should be faster than calling apply, which can be pretty slow, but if your frames aren't too large both should work well enough.

edited Nov 11 '21 at 05:13

Henry Ecker

31,792
14
29
50

answered Oct 21 '16 at 16:46

DSM

319,184
61
566
472

oh well. always a shorter way to go i guess. good one, cleaner than my answer. – Steven G Oct 21 '16 at 16:47
never used `lookup` before, this looks great – Dennis Golomazov Oct 21 '16 at 16:49
are there any speed differences for large data sets (2mln rows)? Is a lambda faster here or a lookup – John Oct 21 '16 at 16:52

Steven G · Answer 2 · 2016-10-21T16:36:55.620

well imo lambda is the way to go, but you can make it very short such has:

df = pd.DataFrame([{'name': 'john', 'john': 5, 'mac': 10}, {'name': 'mac', 'john': 10, 'mac': 15}], columns=["name", "john", "mac"])
df = df.set_index('name')
df
Out[64]: 
      john  mac
name           
john     5   10
mac     10   15

df['values'] = df.apply(lambda x: x[x.name], axis=1)
In[68]: df
Out[68]: 
      john  mac  values
name                   
john     5   10       5
mac     10   15      15

I did set the index to name for convinience but you could do it without it such has:

df = pd.DataFrame([{'name': 'john', 'john': 5, 'mac': 10}, {'name': 'mac', 'john': 10, 'mac': 15}], columns=["name", "john", "mac"])
df['values'] = df.apply(lambda x: x[x['name']], axis=1)
df
Out[71]: 
   name  john  mac  values
0  john     5   10       5
1   mac    10   15      15

your second example seems the most elegant to me and did the trick. thanks! — John, Oct 21 '16 at 16:46

Python: pandas: match row value to column name/ key's value

2 Answers2

Linked

Related