Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

I have a big df with many numerical and categorical columns. One categorical column is…

  1. User

    I have a big df with many numerical and categorical columns. One categorical column is alphanumeric containing machine names including their specifications. This column is called Machine description. I want to add one more column 'Machine Group ID' to label them based on Machine description similarity. For example, let's assume 5 machine descriptions are: Montage W50, Montage, Montageeinheit u70, Brener, Montageeinheit. So, in this case Montage W50 and Montage would be given a group label 1 let's say. Montageeinheit u70 and Montageeinheit would be second group and Brenner is 3rd. Let's assume a similarity threshold of 75%. How can it be done with python?

  2. Assistant

    You can use the fuzzy matching technique to determine the similarity between machine descriptions and assign them to groups based on a similarity threshold.

    Here is an example of how you can do this using Python and the fuzzywuzzy library:

    import pandas as pd
    from fuzzywuzzy import fuzz
    from fuzzywuzzy import process
    
    # Load your dataframe
    df = pd.read_csv('your_dataframe.csv')
    
    # Function to find the group label for a given machine description
    def find_group(description, groups, threshold):
        for i, group in enumerate(groups):
            for machine in group:
                similarity = fuzz.token_set_ratio(description, machine)
                if similarity >= threshold:
                    return i + 1
        return None
    
    # Create a list of machine descriptions
    machine_descriptions = df['Machine description'].unique().tolist()
    
    # Create an empty list to store the groups
    groups = []
    
    # Iterate over each machine description
    for machine in machine_descriptions:
        # Find similar existing groups
        matches = process.extract(machine, groups, scorer=fuzz.token_set_ratio, limit=None)
        similar_groups = [match[0] for match in matches if match[1] >= 75]
        
        if similar_groups:
            # Append the machine to the first similar group
            groups[groups.index(similar_groups[0])].append(machine)
        else:
            # Create a new group with the machine
            groups.append([machine])
    
    # Create a dictionary to map machine descriptions to group labels
    machine_group_dict = {}
    for i, group in enumerate(groups):
        for machine in group:
            machine_group_dict[machine] = i + 1
    
    # Create a new column 'Machine Group ID'
    df['Machine Group ID'] = df['Machine description'].map(machine_group_dict)
    

    This code will create a new column 'Machine Group ID' in your dataframe that contains the group label for each machine description based on a similarity threshold of 75%.

  3. User

    Assume a big enough sample df and show output as well. Depict also the same using difflib.

  4. Assistant

    Sure! Here's an example using a sample dataframe and the difflib library:

    import pandas as pd
    import difflib
    
    # Sample dataframe
    df = pd.DataFrame({
        'Machine description': ['Montage W50', 'Montage', 'Montageeinheit u70', 'Brener', 'Montageeinheit']
    })
    
    # Function to find the group label for a given machine description
    def find_group(description, groups, threshold):
        for i, group in enumerate(groups):
            for machine in group:
                similarity = difflib.SequenceMatcher(None, description, machine).ratio()
                if similarity >= threshold:
                    return i + 1
        return None
    
    # Create a list of machine descriptions
    machine_descriptions = df['Machine description'].unique().tolist()
    
    # Create an empty list to store the groups
    groups = []
    
    # Iterate over each machine description
    for machine in machine_descriptions:
        # Find similar existing groups
        similar_groups = []
        for group in groups:
            for machine_group in group:
                similarity = difflib.SequenceMatcher(None, machine, machine_group).ratio()
                if similarity >= 0.75:
                    similar_groups.append(group)
                    break
        
        if similar_groups:
            # Append the machine to the first similar group
            similar_groups[0].append(machine)
        else:
            # Create a new group with the machine
            groups.append([machine])
    
    # Create a dictionary to map machine descriptions to group labels
    machine_group_dict = {}
    for i, group in enumerate(groups):
        for machine in group:
            machine_group_dict[machine] = i + 1
    
    # Create a new column 'Machine Group ID'
    df['Machine Group ID'] = df['Machine description'].map(machine_group_dict)
    

    The resulting dataframe will have the 'Machine Group ID' column with the group labels assigned to each machine description.

    For the sample dataframe in this example, the output will look like this:

       Machine description  Machine Group ID
    0          Montage W50                 1
    1              Montage                 1
    2  Montageeinheit u70                 2
    3               Brener                 3
    4       Montageeinheit                 2
    

    This means that 'Montage W50' and 'Montage' belong to group 1, 'Montageeinheit u70' and 'Montageeinheit' belong to group 2, and 'Brener' belongs to group 3.