Python Pandas: How do I create a column given a condition based on another column?

Question

Given the following dataframe:

df_test = pd.DataFrame(
    [[1, "BURGLARY"], [2, "PETIT LARCENY"], [3, "DANGEROUS DRUGS"], [4, "LOITERING FOR DRUG PURPOSES"], [5, "DANGEROUS WEAPONS"]],
      columns = ['id','ofns_desc']
)

I want to add a new column that simplifies the description in the ofns_desc column. I did the following:

THEFT = ["BURGLARY", "PETIT LARCENY"]
df_test.loc[df_test.ofns_desc.isin(THEFT), 'category'] = "THEFT"

DRUGS = ["DANGEROUS DRUGS", "LOITERING FOR DRUG PURPOSES"]
df_test.loc[df_test.ofns_desc.isin(DRUGS), 'category'] = "DRUGS"

Up to this point, the code above works:

But when I try to create an "OTHER" value for the category column, every value in the category column gets overwritten:

ALL_CAT = [THEFT, DRUGS]
df_test.loc[~df_test.ofns_desc.isin(ALL_CAT), 'category'] = "OTHER"

What am I doing wrong?

Answer 1

Problem is you test nested lists, so all values failed, you need join lists by + instead pass to [] like change:

ALL_CAT = [THEFT, DRUGS]

to:

ALL_CAT = THEFT + DRUGS

Another idea is create dictionaries and Series.map, last replace missing values by Series.fillna:

THEFT = ["BURGLARY", "PETIT LARCENY"]
DRUGS = ["DANGEROUS DRUGS", "LOITERING FOR DRUG PURPOSES"]
d = {"THEFT":THEFT, 'DRUGS':DRUGS}

#swap key values in dict
#http://stackoverflow.com/a/31674731/2901002
d1 = {k: oldk for oldk, oldv in d.items() for k in oldv}
print (d1)
{'BURGLARY': 'THEFT', 'PETIT LARCENY': 'THEFT',
 'DANGEROUS DRUGS': 'DRUGS', 'LOITERING FOR DRUG PURPOSES': 'DRUGS'}

df_test['category'] = df_test['ofns_desc'].map(d1).fillna("OTHER")
print (df_test)
   id                    ofns_desc category
0   1                     BURGLARY    THEFT
1   2                PETIT LARCENY    THEFT
2   3              DANGEROUS DRUGS    DRUGS
3   4  LOITERING FOR DRUG PURPOSES    DRUGS
4   5            DANGEROUS WEAPONS    OTHER