Add values from columns into a new column using pandas

Question

I have a dataframe:

id    category    value
1       1          abc
2       2          abc
3       1          abc
4       4          abc
5       4          abc
6       3          abc

Category 1 = best, 2 = good, 3 = bad, 4 =ugly

I want to create a new column such that, for category 1 the value in the column should be cat_1, for category 2, the value should be cat2.

in new_col2 for category 1 should be cat_best, for category 2, the value should be cat_good.

df['new_col'] = ''

my final df

id    category    value   new_col   new_col2
1       1          abc     cat_1     cat_best
2       2          abc     cat_2     cat_good
3       1          abc     cat_1     cat_best
4       4          abc     cat_4     cat_ugly
5       4          abc     cat_4     cat_ugly
6       3          abc     cat_3     cat_bad

I can iterate it in for loop:

for index,row in df.iterrows():
    df.loc[df.id == row.id,'new_col'] = 'cat_'+str(row['category'])

Is there a better way of doing it (least time consuming)

@Zero The second part of my que is not a duplicate, i suppose — Shubham R
– Shubham R, Commented Feb 7, 2018 at 9:44

jezrael · Accepted Answer · 2018-02-07 09:44:41Z

1

I think you need join string with column converted to string and map with join for second column:

d = {1:'best', 2: 'good', 3 : 'bad', 4 :'ugly'}
df['new_col'] = 'cat_'+ df['category'].astype(str)
df['new_col2'] = 'cat_'+ df['category'].map(d)

Or:

df = df.assign(new_col= 'cat_'+ df['category'].astype(str), 
               new_col2='cat_'+ df['category'].map(d))

print (df)
   id  category value new_col  new_col2
0   1         1   abc   cat_1  cat_best
1   2         2   abc   cat_2  cat_good
2   3         1   abc   cat_1  cat_best
3   4         4   abc   cat_4  cat_ugly
4   5         4   abc   cat_4  cat_ugly
5   6         3   abc   cat_3   cat_bad

edited Feb 7, 2018 at 9:44

answered Feb 7, 2018 at 9:37

jezrael

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

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

1 Comment

Shubham R Over a year ago

I have added one more column in my output (edit). If category = 1, its best, so the value in new_col2 should be cat_best , n so on

Sociopath · Accepted Answer · 2018-02-07 09:45:59Z

0

You can do it by using apply also:

df['new_col']=df['category'].apply(lambda x: "cat_"+str(x))

answered Feb 7, 2018 at 9:45

Sociopath

13.4k22 gold badges53 silver badges82 bronze badges

Collectives™ on Stack Overflow

Add values from columns into a new column using pandas

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related