Adding values to a new column based on other two columns

Question

I have a data frame that looks like this. I have created 3 new columns that will take the value from other columns.I want function columns separate column and grab total hours for each user for each function.

 User         Function          Total hours   Damage Processing problem solve damages sweeper
 schae      Damage Processing      9.36
 Julie      Problem solve          9.70
 John       sweeper                18.9
 Dan       Damages                 1.83
 Dan       Damages                 1.83
 Julie     Damages                 1.83
 Dan       Problem solve           1.83

The expected output will look like

 User         Function          Total hours   Damage Processing  problem solve damages  sweeper
 schae     Damage Processing       9.36          9.36
 Julie     Problem solve           9.70                            9.70
 John      sweeper                 18.9                                                  18.9
 Dan       Damages                 1.83                                        1.83                                 
 Dan       sweeper                 1.83                                                  1.83
 Julie     Damages                 1.83                                        1.83
 Dan       Problem solve           1.83                            1.83

I think of pd.melt but it throws an error value var not present

res = pd.melt(result,id_vars = ['Function'],value_vars=['Total hours'])

Have you created 3 new columns or 4 ? `Damage processing, Problem solve, sweeper, Damages` — Suraj, Mar 26 '20 at 15:58
are the columns really lowercase or is that a typo? Also how does User `Dan` becomes a `sweeper` in row 5? in the input it was `Damages` — anky, Mar 26 '20 at 16:13

anky · Accepted Answer · 2020-03-26T16:19:10.263

here is a way using get_dummies and df.assign:

out = (df[['User','Function','Total hours']].assign(**pd.get_dummies(df['Function'])
       .mul(df['Total hours'],axis=0).replace(0,np.nan)))
print(out)

    User           Function  Total hours  Damage Processing  Damages  \
0  schae  Damage Processing         9.36               9.36      NaN   
1  Julie      Problem solve         9.70                NaN      NaN   
2   John            sweeper        18.90                NaN      NaN   
3    Dan            Damages         1.83                NaN     1.83   
4    Dan            Damages         1.83                NaN     1.83   
5  Julie            Damages         1.83                NaN     1.83   
6    Dan      Problem solve         1.83                NaN      NaN   

   Problem solve  sweeper  
0            NaN      NaN  
1           9.70      NaN  
2            NaN     18.9  
3            NaN      NaN  
4            NaN      NaN  
5            NaN      NaN  
6           1.83      NaN

This works as expected.Thanks. Is .mul is multiply? trying to understand how it worked — Learner, Mar 26 '20 at 16:45

score 0 · Answer 2 · answered Mar 26 '20 at 16:17

0

for i in range(len(df)):
    col = df.loc[i]['Function']
    df.at[i, col] = df.xs(i)['Total hours']
    print(col)

Try this ! Variable col finds the column for which you want to insert the value of Total hours.

answered Mar 26 '20 at 16:17

Suraj

2,253
3
17
48

Adding values to a new column based on other two columns

2 Answers2