Changing values in multiple columns of a pandas DataFrame using known column values

Question

Suppose I have a dataframe like this:

Knownvalue    A    B    C    D    E    F    G    H
  17.3413     0    0    0    0    0    0    0    0
  33.4534     0    0    0    0    0    0    0    0

what I wanna do is that when Knownvalue is between 0-10, A is changed from 0 to 1. And when Knownvalue is between 10-20, B is changed from 0 to 1,so on so forth.

It should be like this after changing:

Knownvalue     A    B    C    D    E    F    G    H
   17.3413     0    1    0    0    0    0    0    0
   33.4534     0    0    0    1    0    0    0    0

Anyone know how to apply a method to change it?

Answer 1

I first bucket the Knownvalue Series into a list of integers equal to its truncated value divided by ten (e.g. 27.87 // 10 = 2). These buckets represent the integer for the desired column location. Because the Knownvalue is in the first column, I add one to these values.

Next, I enumerate through these bin values which effectively gives me tuple pairs of row and column integer indices. I use iat to set the value of the these locations equal to 1.

import pandas as pd
import numpy as np

# Create some sample data.
df_vals = pd.DataFrame({'Knownvalue': np.random.random(5) * 50})
df = pd.concat([df_vals, pd.DataFrame(np.zeros((5, 5)), columns=list('ABCDE'))], axis=1)

# Create desired column locations based on the `Knownvalue`.
bins = (df.Knownvalue // 10).astype('int').tolist()
>>> bins
[4, 3, 0, 1, 0]

# Set these locations equal to 1.
for idx, col in enumerate(bins):
    df.iat[idx, col + 1] = 1  # The first column is the `Knownvalue`, hence col + 1

>>> df
   Knownvalue  A  B  C  D  E
0   47.353937  0  0  0  0  1
1   37.460338  0  0  0  1  0
2    3.797964  1  0  0  0  0
3   18.323131  0  1  0  0  0
4    7.927030  1  0  0  0  0