Creating new columns from existing column in python

Question

I have a dataframe that looks something like this:

data = [['A', 1, 100], ['A', 3, 100], ['A', 2, 100], ['A', 3, 100], ['A', 5, 100]]
df =  pd.DataFrame(data, columns = ['?', 'Rating', 'Amount'])

    ?   Rating  Amount
0   A   1       100
1   A   3       100
2   A   2       100
3   A   3       100
4   A   5       100

and I need to create new columns based on the Rating value substituting in the amount - looks something like this:

    ?   Rating  Amount  1   2   3   5
0   A   1       100     100 0   0   0
1   A   3       100     0   0   100 0
2   A   2       100     0   100 0   0
3   A   3       100     0   0   100 0
4   A   5       100     0   0   0   100

Right now I have this:

ratingnames = np.unique(list(df['Rating']))
ratingnames.sort()

d = pd.DataFrame(0, index=np.arange(len(df['Rating'])), columns=ratingnames)

for i in range(len(df['Rating'])):
    ratingvalue = df.loc[i, 'Rating']
    d.loc[i, ratingvalue] = df.loc[i, 'Amount']

df = pd.concat([df, d], axis = 1)

but I feel like it could be improved upon. Any suggestions? Thanks!

anky · Accepted Answer · 2020-01-03 16:25:48Z

2

IIUC, use get_dummies and multiply with df['Amount'], then concat on axis=1:

output = pd.concat((df,pd.get_dummies(df['Rating']).mul(df['Amount'],axis=0)),axis=1)

   ?  Rating  Amount    1    2    3    5
0  A       1     100  100    0    0    0
1  A       3     100    0    0  100    0
2  A       2     100    0  100    0    0
3  A       3     100    0    0  100    0
4  A       5     100    0    0    0  100

Timings:

edited Jan 3, 2020 at 16:25

answered Jan 3, 2020 at 16:07

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

3 Comments

anky Over a year ago

@ecaines You should not use apply when not required in pandas especially axis=1 is very slow. Added some timing test to demonsrate what I meant

ecaines Over a year ago

interesting, thanks for adding that in. very helpful

anky Over a year ago

@ecaines no problem ..happy coding :)

Georgina Skibinski · Accepted Answer · 2020-01-03 16:11:39Z

1

This will do the trick:

df=pd.concat([df, df.apply(lambda x: pd.Series({x["Rating"]: x["Amount"]}), axis=1).fillna(0).astype("int")], axis=1)

Output:

   ?  Rating  Amount    1    2    3    5
0  A       1     100  100    0    0    0
1  A       3     100    0    0  100    0
2  A       2     100    0  100    0    0
3  A       3     100    0    0  100    0
4  A       5     100    0    0    0  100

answered Jan 3, 2020 at 16:11

Georgina Skibinski

13.5k2 gold badges16 silver badges44 bronze badges

Collectives™ on Stack Overflow

Creating new columns from existing column in python

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