Changing values ​​in multiple pandas DataFrame columns using known column values

Question

Suppose I have a data beat:

Knownvalue ABCDEFGH 17.3413 0 0 0 0 0 0 0 0 33.4534 0 0 0 0 0 0 0 0 

what I want to do is that when the Knownvalue is between 0-10, A changes from 0 to 1. And when the Knownvalue is between 10-20, B changes from 0 to 1, and so on and on.

It should be like this after the change:

 Knownvalue ABCDEFGH 17.3413 0 1 0 0 0 0 0 0 33.4534 0 0 0 1 0 0 0 0 

Does anyone know how to apply a method to change it?

Answer 1

First, I put the Knownvalue series on a list of integers equal to its truncated value divided by ten (for example, 27.87 // 10 = 2). These buckets are an integer for the desired column location. Since the Knownvalue is in the first column, I am adding it to these values.

Next, I list these bin values ​​that effectively give me tuple pairs of integer indices for rows and columns. I use iat to set the value of these locations 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 ABCDE 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 

Answer 2

Another approach would be to restore a frame from a Knownvalue column using get_dummies :

 >>> import string >>> new_cols = pd.get_dummies(df["Knownvalue"]//10).loc[:,range(8)].fillna(0) >>> new_cols.columns = list(string.ascii_uppercase)[:len(new_cols.columns)] >>> pd.concat([df[["Knownvalue"]], new_cols], axis=1) Knownvalue ABCDEFGH 0 17.3413 0 1 0 0 0 0 0 0 1 33.4534 0 0 0 1 0 0 0 0 

get_dummies does the hard work:

 >>> (df.Knownvalue//10) 0 1 1 3 Name: Knownvalue, dtype: float64 >>> pd.get_dummies((df.Knownvalue//10)) 1 3 0 1 0 1 0 1