Pandas replace string values in a column which has multiple variations

user_170339 0
Viewed 455

I am working with this csv file. It's a small dataset of laptop information.

laptops = pd.read_csv('laptops.csv',encoding="Latin-1")
laptops["Operating System"].value_counts()

Windows      1125
No OS          66
Linux          62
Chrome OS      27
macOS          13
Mac OS          8
Android         2
Name: Operating System, dtype: int64

I want to merge the variations of macOS and Mac OS under a single value "macOS".

I have tried this, which works.

mapping_dict = {
    'Android': 'Android',
    'Chrome OS': 'Chrome OS',
    'Linux': 'Linux',
    'Mac OS': 'macOS',
    'No OS': 'No OS',
    'Windows': 'Windows',
    'macOS': 'macOS'
}

laptops["Operating System"] = laptops["Operating System"].map(mapping_dict)

laptops["Operating System"].value_counts()

Windows      1125
No OS          66
Linux          62
Chrome OS      27
macOS          21
Android         2
Name: Operating System, dtype: int64

Is this is the only way or the best way of doing it? Assume such requirement might arise for multiple values (and not just macOS).

pandas data-cleaning dataframe numpy python
4 Answers
laptops['Operating System'] = laptops['Operating System'].str.replace(r'(?i)(mac|mc).*os', 'macOS', regex=True)
user_9676210

This code would do the trick. But you have to know the possible variants in advance. In case it is not feasible to know them in advance, it is going to be another issue not to be discussed here under python and pandas tags.

df['Operating System'][df['Operating System'].str.lower().isin(['mac', 'osx', 'macos'])] = 'Mac OS'

user_96667130

You can simply do

laptops['Operating System'] = laptops['Operating System'].replace('Mac OS', 'macOS')
user_96428040

I'd do this:

# Generate a dict of list, where each key is the name you want
# to assign and the lists contain the variations of the main name
aliases = {
    "macOS": ["mac", "osx", "Mac OS"],
    "Windows": ["win", "windows", "Windows"],
}

# Create a map so it's easier to lookup all the names
aliases_map = {v: k for k, v in aliases.items() for v in v}

# Replace all of the aliases with its respective main name
laptops["Operating System"] = laptops["Operating System"].replace(aliases_map)

Output of laptops["Operating System"].value_counts():

Windows      1125
No OS          66
Linux          62
Chrome OS      27
macOS          21
Android         2
Name: Operating System, dtype: int64
user_47131690
Related