Pandas check if a column value equals another column's name, then set 0 or 1

user_11302142 0
Viewed 1897

this is a seemingly simple ask but I can't seem to figure it out.

I have a dataframe like this

enter image description here

I just want the value of the 'cat' column to be 1 if the value in 'animal' is 'cat', and similarly for other values of the 'animal' column.

Here is the example dataset:

data = pd.DataFrame({'animal':['cat','cat', 'dog', 'fish'], 'cat':[0,0,0,0],'dog':[0,0,0,0],'fish':[0,0,0,0],'lion':[0,0,0,0]})

The catch is that I can't just simply binarize the values, because even though 'lion' is not in my column values, it still exists as a binary column. (The binary columns are defined already).

I found this from another post but can't seem to figure out how it works.

df.fillna('').apply(lambda x : x.index==x.name).astype(int).replace(0,"")

My actual dataset has ~100 binary columns to loop through.

Any help is appreciated, thank you!

pandas python
2 Answers

I know an answer has been posted already but here is a method using the line you suggested.

data.loc[:,data.columns != 'animal'].apply(lambda x : data["animal"]==x.name).astype(int)

First, you start by calling all columns that are not called "animal" since you don't need to replace them. Then the lambda function iterates over every column checking the cases in which the names are the same. Finally "astype(int)" ensures cases in which our desired condition is true the values are displayed as 1.

To get the dataframe with the animal names just save it onto the variable, like this:

data.loc[:,data.columns != 'animal'] = data.loc[:,data.columns != 'animal'].apply(lambda x : data["animal"]==x.name).astype(int)

Out:
      animal  cat  dog  fish
    0    cat    1    0     0
    1    cat    1    0     0
    2    dog    0    1     0
    3   fish    0    0     1
user_144591720

You can use pd.get_dummies():

data = pd.DataFrame({'animal':['cat','cat', 'dog', 'fish'], 'cat':[0,0,0,0],'dog':[0,0,0,0],'fish':[0,0,0,0]})  
data = pd.get_dummies(data['animal'])
# To keep animal column use: data = data[['animal']].join(pd.get_dummies(data['animal']))
data
Out[1]: 
   cat  dog  fish
0    1    0     0
1    1    0     0
2    0    1     0
3    0    0     1

You can also loop through the columns and see if the value is equal to the column. That returns True or False. Then, simply call .astype(int) to transform to 1 and 0, respectively.

data = pd.DataFrame({'animal':['cat','cat', 'dog', 'fish'], 'cat':[0,0,0,0],'dog':[0,0,0,0],'fish':[0,0,0,0]})  
for col in data.columns[1:]:
    data[col] = (data['animal'] == col).astype(int)
data
Out[218]: 
  animal  cat  dog  fish
0    cat    1    0     0
1    cat    1    0     0
2    dog    0    1     0
3   fish    0    0     1
user_63667700
Related