Feature Engineering Salary Data using Categorical Column as a condition

Question

Need to convert salary amount to annualised salaries considering the Categorical Column :

'M' - monthly
'Y' - yearly
'W' - weekly
'B' - bi weekly

df = pd.DataFrame({'Name':['A','B','C','D','E'],
                  'sal_amt':[4500,50000,2000,3000,5000],
                  'sal_md':['M','Y','W','B','M']})
df.head()

#defined a function for my problem...

def func(row):
    if row['sal_md'] == 'M':
        return (row['sal_amt']*12)
    elif row['sal_md'] =='Y':
        return row['sal_amt'] 
    elif row['sal_md'] == 'H':
        return (row['sal_amt']*8760)
    elif row['sal_md'] == 'W':
        return (row['sal_amt']*52)
    elif row['sal_md'] == 'B':
        return (row['sal_amt']*26)
    elif row['sal_md'] == 'S':
        return row['sal_amt']
    elif row['sal_md'] == 'A':
        return row['sal_amt']


df['sal_annual'] = df.apply(func,axis=1)

https://i.stack.imgur.com/INXva.png

So, basically when I applied the function, I didn't received the desired result. — DEEPAK GIRDHAR, Jun 17 '19 at 19:53
Could you please provide a solution for the same, that would be of great help ! — DEEPAK GIRDHAR, Jun 17 '19 at 19:54
'sal_amt'*12 is a string concatenated 12 times. I think you at least forgot to put row[] around that name, as sal_amt without ' is not a defined variable... — B. Go, Jun 17 '19 at 21:08
Thanks for the help...got my desired result for the dataframe created, but the function is not working in the main data frame & throwing output in 'sal_annual' column as 'None' for all the rows. — DEEPAK GIRDHAR, Jun 18 '19 at 08:13
Hi Goyo, can you please provide the help in the above query ? I'm not getting desired result in the 'sal_annual' column, but if I'm creating a dummy data set & applying the function I'm getting results for the dummy data. — DEEPAK GIRDHAR, Jun 18 '19 at 10:20
As you've said, the function works fine on the dummy dataset, which means the error in your code is elsewhere. Keep looking until you can create a https://stackoverflow.com/help/minimal-reproducible-example. — Viktoriya Malyasova, Jun 18 '19 at 10:38

score 0 · Accepted Answer · answered Jun 18 '19 at 14:14

0

In [1]: import pandas as pd

In [2]: df = pd.DataFrame({'Name':['A','B','C','D','E'],
                      'sal_amt':[4500,50000,2000,3000,5000],
                      'sal_md':['M','Y','W','B','M']})

In [3]: multiplier_dict = {'M':12, 'Y':1, 'W':52, 'B':26}

In [4]: df['sal_multiplier'] = df.sal_md.map(multiplier_dict)

In [5]: df['sal_annual'] = df.sal_amt*df.sal_multiplier

In [6]: df.head()
Out[6]:
  Name  sal_amt sal_md  sal_multiplier  sal_annual
0    A     4500      M              12       54000
1    B    50000      Y               1       50000
2    C     2000      W              52      104000
3    D     3000      B              26       78000
4    E     5000      M              12       60000

Not exactly what you asked about, but exactly solves your problem in an easy and pythonic way.

answered Jun 18 '19 at 14:14

jo9k

690
6
19

Hi jo9k, thanks for another way to the problem, through function also I was able to do with the dummy data, the problem I'm facing is with my main data. – DEEPAK GIRDHAR Jun 18 '19 at 14:30
Does my way work with your real data? If not, what's the output? Is there any error? – jo9k Jun 18 '19 at 14:33
So basically I did rectified my formula also & tried your way also the output still remains the same, its calculating for the dummy data, but not for the original, I'm posting a screenshot of output above (query area) for your reference. – DEEPAK GIRDHAR Jun 18 '19 at 14:38
Try changing `df['sal_annual'] = df.sal_amt*df.sal_multiplier` into `df['sal_annual'] = df.sal_amt.astype(int)*df.sal_multiplier` – jo9k Jun 19 '19 at 06:47

Feature Engineering Salary Data using Categorical Column as a condition

1 Answers1