0

I am trying to set the value of a pandas column based on another columns value. The new value should be set by iterating through a list, that has the same length like the unique values of col1

Example:

d = {'col1': [1, 2,2,2,3,3,4], 'col2': [1,1,1,1,1,1,1]}
df = pd.DataFrame(data=d)
items_to_add=[5,2,11,9]
#list has length of unique values in `column1`

Now i want to add for example 5 to column2 if column1is 1 and 2 to all column2 rows where column1 is 2 .... So i should get:

col1    col2
1       5
2       2
2       2
2       2
3       11
3       11
4       9

This code throws me an syntax error but i do not know why

items_to_add=[5,2,11,9]
for i in range(len(items_to_add)):
    df['col2'][df.col1[i]] = items_to_add[i]

What am I doing wrong? How can i fix it?

Pyd
  • 6,017
  • 18
  • 52
  • 109
Mauritius
  • 265
  • 1
  • 8
  • 23

3 Answers3

2

You can simply replace the values in col1 by creating a dictionary i.e 

di =  dict(zip(df['col1'].unique(), items_to_add))
# {1: 5, 2: 2, 3: 11, 4: 9}
df['col3'] = df['col1'].map(di)

   col1  col2  col3
0     1     1     5
1     2     1     2
2     2     1     2
3     2     1     2
4     3     1    11
5     3     1    11
6     4     1     9
Bharath M Shetty
  • 30,075
  • 6
  • 57
  • 108
0

This is what you do

df['col2'] = df['col1'].apply(lambda x: items_to_add[x-1])

By doing this you create a new column col2 where for every x in df[col1], you choose the (x-1)th indexed value of items_to_add.

Atul Shanbhag
  • 636
  • 5
  • 13
  • that looks good but it tells me. `list indices must be integer not float` ... no idea why he thinks z is float – Mauritius Sep 23 '18 at 14:03
  • Ohhhh you would have to make sure that `df['col1']` is of int type or cast the `x` to int like `items_to_add[int(x)-1]` – Atul Shanbhag Sep 23 '18 at 14:36
0

I think you need to remove duplicates then map,

df['col1'].map(dict(zip(df['col1'].drop_duplicates(),items_to_add)))
#out[]
0     5
1     2
2     2
3     2
4    11
5    11
6     9
Pyd
  • 6,017
  • 18
  • 52
  • 109