Map DataFrame column name to appropriate cell

user_16580065 0
Viewed 60

this is my first post here.

I don't exactly know how to formulate this question without an example, so it's hard to search for an answer. Anyway, I have a DataFrame that looks like this (with more columns and thousands of rows):

df = pd.DataFrame({"A": [1, 0, 0], "B": [0, 0, 1], "C": [0, 1, 0]})

I would like to create additional column eg. "Type", where value of each row would be column name of the column that contains 1 in this row. For example:

df = pd.DataFrame({"A": [1, 0, 0], "B": [0, 0, 1], "C": [0, 1, 0], "Type": ["A", "C", "B"]})

I hope this makes sense.

Thanks, Chris

pandas dataframe python
2 Answers

You can check for df equals 1, using .eq, and then use idxmax(axis=1) to get the column index of the entry equals 1 in that row, as follows:

df['Type'] = df.eq(1).idxmax(axis=1)

or simplify it for this case where the values are 0's and 1's (thanks for @wwii):

df['Type'] = df.idxmax(axis=1)

Alternatively, you can also use df.dot, as follows:

df['Type'] = df.dot(df.columns)

Result:

print(df)

   A  B  C Type
0  1  0  0    A
1  0  0  1    C
2  0  1  0    B
user_150706970

Using apply:

import pandas as pd

df = pd.DataFrame({"A": [1, 0, 0], "B": [0, 0, 1], "C": [0, 1, 0]})

def getColName(row):    
    x = (df.iloc[row.name] == 1)
    return x.index[x.argmax()]

df['Type'] = df.apply(getColName, axis=1)

print(df)

   A  B  C  Type
0  1  0  0  A
1  0  0  1  C
2  0  1  0  B
user_91922840
Related