Why have my data values been replaced by "NaN" after using qcut?

Question

I am working with pandas dataframe of 9000 rows and 6 columns. At this point, I am trying to convert the continuous variable 'Experience' years of a job into the categorical variable 'Level' of expertise (beginner - intermediate - advanced - expert) for each of the 4 jobs (Commercial Manager - Business Developer - Web Marketer - Traffic Manager).

Giving that years experience range are not the same for each job, I utilised "qcut" to divide data into 4 groups as follows:

(You can run the code below to get the dataframe sample)

import pandas as pd


df = pd.DataFrame({'Job': ['Commercial Manager', 'Traffic Manager', 'Web Marketer', 'Commercial Manager', 'Commercial Manager', 'Web Marketer', 'Commercial Manager', 'Commercial Manager', 'Traffic Manager', 'Business Developer', 'Business Developer', 'Web Marketer', 'Traffic Manager', 'Traffic Manager', 'Commercial Manager', 'Business Developer', 'Traffic Manager', 'Commercial Manager', 'Business Developer', 'Business Developer', 'Web Marketer'], 
                   'Experience': [1.00000, 3.00000, 3.00000, 1.50000, 2.00000, 6.00000, 0.00000, 4.00000, 8.00000, 5.00000, 0.50000, 3.00000, 3.00000, 0.00000, 2.00000, 3.00000, 0.50000, 3.00000, 3.00000, 8.00000, 3.50000]})


levels = ["beginner", "intermediate", "advanced", "expert"]
jobs = ["Commercial Manager", "Business Developer", "Web Marketer", "Traffic Manager"]


def convert(levels, jobs):
  for j in jobs:
    df["Level"] = pd.qcut(df.loc[df["Job"] == j, "Experience"].rank(method="first"), q = 4, labels = levels, duplicates = "drop")
  return df

convert(levels, jobs)

This is the output after using "qcut":

    Job                     Experience       Level 
0   Commercial Manager      1.00000          NaN
1   Traffic Manager         3.00000          intermediate
2   Web Marketer            3.00000          NaN
3   Commercial Manager      1.50000          NaN
4   Commercial Manager      2.00000          NaN
5   Web Marketer            6.00000          NaN
6   Commercial Manager      0.00000          NaN
7   Commercial Manager      4.00000          NaN
8   Traffic Manager         8.00000          expert
9   Business Developer      5.00000          NaN 
10  Business Developer      0.50000          NaN 
11  Web Marketer            3.00000          NaN 
12  Traffic Manager         3.00000          intermediate
13  Traffic Manager         0.00000          beginner
14  Commercial Manager      2.00000          NaN
15  Business Developer      3.00000          NaN
16  Traffic Manager         0.50000          beginner
17  Commercial Manager      3.00000          NaN
18  Business Developer      3.00000          NaN
19  Business Developer      8.00000          NaN
20  Web Marketer            3.50000          NaN

It appears that it only worked for "Traffic Manager" and it replaced the other level experience with NaN. I am really lost. Any help please?

Some of these solutions may help: https://stackoverflow.com/a/40548606/5783745 — stevec, Sep 19 '20 at 02:19
Thank you but I've already looked on them, it didn't work. Still stuck :( — Ada, Sep 24 '20 at 14:36

score 0 · Accepted Answer · answered Sep 25 '20 at 00:41

You want to do this in a groupby operation:

import numpy
import pandas

levels = ["beginner", "intermediate", "advanced", "expert"]
jobs = ["Commercial Manager", "Business Developer", "Web Marketer", "Traffic Manager"]

df = pandas.DataFrame({
    'Job': numpy.random.choice(levels, size=150), 
    'Experience': numpy.random.uniform(0.25, 10.5, size=150)
}).assign(
    level=df.groupby(['Job'])['Experience'] # for each unique job...
            # apply a quantile (quartile) cut 
            .apply(lambda g: pd.qcut(g, q=4, labels=levels, duplicates="drop"))
)

score 0 · Answer 2 · answered Oct 04 '20 at 10:27

  # I would just change two things to what Paul suggested (jobs instead of levels and the rank(method="first") because there was still an error:

levels = ["beginner", "intermediate", "advanced", "expert"]
jobs = ["Commercial Manager", "Business Developer", "Web Marketer", "Traffic Manager"]

df = pandas.DataFrame({
  'Job': numpy.random.choice(jobs, size=150), 
  'Experience': numpy.random.uniform(0.25, 10.5, size=150)
}).assign(
  level=df.groupby(['Job'])['Experience'] # for each unique job...
        # apply a quantile (quartile) cut 
        .apply(lambda g: pd.qcut(g.rank(method="first"), q=4, labels=levels, duplicates="drop"))
)

Why have my data values been replaced by "NaN" after using qcut?

2 Answers2