Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas - select column using other column value as column name

Tags:

python

pandas

I have a dataframe that contains a column, let's call it "names". "names" has the name of other columns. I would like to add a new column that would have for each row the value based on the column name contained on that "names" column.

Example:

Input dataframe: pd.DataFrame.from_dict({"a": [1, 2, 3,4], "b": [-1,-2,-3,-4], "names":['a','b','a','b']})

  a  |  b  | names |
 --- | --- | ----  |
  1  |  -1 | 'a'   |
  2  |  -2 | 'b'   |
  3  |  -3 | 'a'   |
  4  |  -4 | 'b'   |

Output dataframe: pd.DataFrame.from_dict({"a": [1, 2, 3,4], "b": [-1,-2,-3,-4], "names":['a','b','a','b'], "new_col":[1,-2,3,-4]})

  a  |  b  | names | new_col | 
 --- | --- | ----  | ------  |
  1  |  -1 | 'a'   |    1    |
  2  |  -2 | 'b'   |   -2    |
  3  |  -3 | 'a'   |    3    |
  4  |  -4 | 'b'   |   -4    |
like image 272
ab3 Avatar asked Aug 03 '17 14:08

ab3


People also ask

How to get column value based on another column in pandas Dataframe?

Use pandas.DataFrame.query () to get a column value based on another column. Besides this method, you can also use DataFrame.loc [], DataFrame.iloc [], and DataFrame.values [] methods to select column value based on another column of pandas DataFrame.

How do I filter a list of columns in pandas?

When passing a list of columns, Pandas will return a DataFrame containing part of the data. The result will be similar. In this case, we’ll just show the columns which name matches a specific expression. We’ll use the quite handy filter method: we can also filter by a specific regular expression (regex).

How do I apply a label to another column in pandas?

Using Pandas Map to Set Values in Another Column The Pandas.map () method is very helpful when you’re applying labels to another column. In order to use this method, you define a dictionary to apply to the column. For our sample dataframe, let’s imagine that we have offices in America, Canada, and France.

How to select the columns in a Dataframe?

In this method we are going to select the columns using . operator with dataframe column name It will display the column name along with rows present in the column Example 1: In this example we are going to select id and name column Example 2: In this example we are going to select cost and quantity column


1 Answers

You can use lookup:

df['new_col'] = df.lookup(df.index, df.names)
df
#   a    b  names   new_col
#0  1   -1      a   1
#1  2   -2      b   -2
#2  3   -3      a   3
#3  4   -4      b   -4

EDIT

lookup has been deprecated, here's the currently recommended solution:

idx, cols = pd.factorize(df['names'])
df.reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]
like image 184
Psidom Avatar answered Oct 20 '22 05:10

Psidom