Python Pandas create new column based on another column value

Question

Based on dataframe column val_1 value, looked into other columns col_0-10 label prefix and then create another column mycol.

Dataframe look like:

id  col0    col1    col2    col3    col4    col5 col6   col7    col8    col9    col10 val_1
1   0   5   -5  5   -5  0 0 1   4   3   -3  1
2   0   0   0   0   0   0 0 4   -4  0   0  7
3   0   0   1   2   3   0 0 0   5   6   0  9

Required Dataframe after applying logic:

id  col0    col1    col2    col3    col4    col5 col6   col7    col8    col9    col10 val_1 mycol
1   0   5   -5  5   -5  0 0 1   4   3   -3  1  5
2   0   0   0   0   0   0 0 4   -4  0   0  7  4
3   0   0   1   2   3   0 0 0   5   6   0  9  6

I trid, but this doesn't work: df['mycol'] = df['col' + df['val_1'].astype(str)]

DDL to generate DataFrame:

df = pd.DataFrame({'id': [1, 2, 3],
                   'col0': [0, 0, 0],
                   'col1': [5, 0, 0],
                   'col2': [-5, 0, 1],
                   'col3': [5, 0, 2],
                   'col4': [-5, 0, 3],
                   'col5' : [0, 0, 0],
                   'col6': [0, 0, 0],
                   'col7': [1, 4, 0],
                   'col8': [4, -4, 5],
                   'col9': [3, 0, 6],
                   'col10': [-3, 0, 0],
                   'val_1' : [1, 7, 9]})

Thanks!

jezrael · Accepted Answer · 2020-07-08 09:11:25Z

6

Use DataFrame.lookup with add col prefix to values of val_1 column:

df['mycol'] = df.lookup(df.index, 'col' + df['val_1'].astype(str))
print (df)
   id  col0  col1  col2  col3  col4  col5  col6  col7  col8  col9  col10  \
0   1     0     5    -5     5    -5     0     0     1     4     3     -3   
1   2     0     0     0     0     0     0     0     4    -4     0      0   
2   3     0     0     1     2     3     0     0     0     5     6      0   

   val_1  mycol  
0      1      5  
1      7      4  
2      9      6

answered Jul 8, 2020 at 9:11

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

hmhmmm · Accepted Answer · 2020-07-08 09:16:50Z

1

You are on the right track, the only problem is that if you do it the way you did, you are returning the whole columns, not only the one value that you want.

Instead, you could use for example DataFrame.apply:

df['mycol'] = df.apply(lambda x: x['col'+str(x['val_1'])], axis=1)

answered Jul 8, 2020 at 9:16

hmhmmm

3331 silver badge4 bronze badges

Collectives™ on Stack Overflow

Python Pandas create new column based on another column value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related