74418

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

Question:

<strong>Problem</strong><br /> "How to match a row value to a column name and take that intersecting value in pandas"

<strong>Context</strong><br /> 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:<br />

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

<br /><strong>Desired output</strong>

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'<br /> So, if name == 'mac', then take the value from column 'mac'<br />

<strong>Tried so far</strong><br /> Bunch of lambdas (none successful). <br />

<strong>Specifications</strong><br /> Python: 3.5.2<br /> Pandas: 0.18.1

Answer1:

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

Answer2:

You could use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.lookup.html" rel="nofollow">DataFrame.lookup</a>, 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.

Recommend

  • Remove dynamically inserted code
  • How do I change the output filename of cuda_compile_ptx in CMake?
  • Error in Jquery upload file due to FFMPEG conversion using PHP
  • Loading data from on-premises hdfs to local SparkR
  • “Union Operation not allowed in sub query” is there a way to fix this in one single query?
  • Keras - Modifying lambda layer after `compile()`
  • CakePHP 3 Redirect ConsoleOutput to File
  • How to centre the origin in the centre of an imshow() plot
  • Python: pandas: match row value to column name/ key's value
  • For the .NET DateTime type, why is the inferred database type SqlDbTypes.DateTime instead of SqlDbTy
  • Implicit declaration of function is invalid in C99
  • How to efficiently query with filter on children at different levels?
  • Python converting strings in a list to numbers
  • A programming strategy to bypass the os thread limit?
  • How to select a particular section of JSON Data in R?
  • How to efficiently query with filter on children at different levels?
  • To find amount of data available in socket
  • Linking libjpeg library to a shared library, and libjpeg is undefined at runtime
  • translate coordinates relative to line segment
  • do while syntax for java
  • java convert to int
  • Breakout Paddle Collision Angle
  • Mathematical Equality for Func
  • Formula to remove entire words that start with certain characters
  • Intersection of segment with polygon
  • How do I create closures for model getter-setter in angular?
  • Shortest route between multiple points in mapkit for iphone app
  • Create ranking for vector of double
  • Pandas v0.20 returns NotImplemented when multiplying dataframe columns
  • Can XOR be expressed using SKI combinators?
  • Removing Duplicate Geometries
  • ASP.NET MVC Application won't update some controllers
  • Highlight and Bold text in JTextPane
  • Is there a javascript serializer for JSON.Net?
  • Where to put my custom functions in Wordpress?
  • How to make Safari send if-modified-since header?
  • PHP: When would you need the self:: keyword?
  • Buffer size for converting unsigned long to string
  • Does armcc optimizes non-volatile variables with -O0?
  • Binding checkboxes to object values in AngularJs