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?

(Note: text is much more convenient than images, because you can copy and paste text. Accordingly I've reverted to the text versions.) — DSM
– DSM, Commented Oct 13, 2015 at 20:54

Alexander · Accepted Answer · 2015-10-14 00:23:23Z

5

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

edited Oct 14, 2015 at 0:23

answered Oct 13, 2015 at 21:08

Alexander

111k32 gold badges212 silver badges208 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dome some Over a year ago

Could you explain" for n, c in enumerate(idx): df.iat[n, c + 1] = 1" ?

Leb Over a year ago

@ZixuanZhang what he's doing with the idx variable is to check if Knownvalue is less than 10 convert to 0, greater than 10 but less than 20 convert to 1 so on and forth. He's doing that by applying floor function which take a float and rounds it down. enumerate takes the the list of idx and provide you with the index and value which are assigned to n and c respectively. From that he's assigning 1 to their respective dataframe column using iat.

dome some Over a year ago

@Leb I have just figured it out myself! Thanks for the specific explanation any way!

DSM · Accepted Answer · 2015-10-13 21:22:58Z

4

A different approach would be to reconstruct the frame from the 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  A  B  C  D  E  F  G  H
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

answered Oct 13, 2015 at 21:22

DSM

355k67 gold badges606 silver badges504 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related