find all - to find all occurrence of matching pattern one column of a data frame to other and get the corresponding value

Question

I am working on a requirement, there are 2 CSV as below -

CSV.csv

    Short Description                                                    Category
    Device is DOWN!                                                      Server Down
    CPU Warning Monitoron  XSSXSXSXSXSX.com                              CPU Utilization
    CPU Warning Monitoron  XSSXSXSXSXSX.com                              CPU Utilization
    CPU Warning Monitoron  XSSXSXSXSXSX.com                              CPU Utilization
    CPU Warning Monitoron  XSSXSXSXSXSX.com                              CPU Utilization
    Device Performance Alerts was triggered on Physical memory           Memory Utilization
    Device Performance Alerts was triggered on Physical memory           Memory Utilization
    Device Performance Alerts was triggered on Physical memory           Memory Utilization
    Disk Space Is Lowon ;E:                                              Disk Space Utilization
    Disk Space Is Lowon;C:                                               Disk Space Utilization
    Network Interface Down                                               Interface Down


and reference.csv

    Category                         Complexity
    Server Down                      Simple
    Network Interface down           Complex
    Drive Cleanup Windows            Medium
    CPU Utilization                  Medium
    Memory Utilization               Medium
    Disk Space Utilization Unix      Simple
    Windows Service Restart          Medium
    UNIX Service Restart             Medium
    Web Tomcat Instance Restart      Simple

Expected Output

Short Description                                                    Category                    Complexity
Device is DOWN!                                                      Server Down                 Simple
CPU Warning Monitoron  XSSXSXSXSXSX.com                              CPU Utilization             Medium
CPU Warning Monitoron  XSSXSXSXSXSX.com                              CPU Utilization             Medium
CPU Warning Monitoron  XSSXSXSXSXSX.com                              CPU Utilization             Medium
CPU Warning Monitoron  XSSXSXSXSXSX.com                              CPU Utilization             Medium
Device Performance Alerts was triggered on Physical memory           Memory Utilization          Medium
Device Performance Alerts was triggered on Physical memory           Memory Utilization          Medium
Device Performance Alerts was triggered on Physical memory           Memory Utilization          Medium
Disk Space Is Lowon ;E:                                              Disk Space Utilization      Medium
Disk Space Is Lowon;C:                                               Disk Space Utilization      Medium
Network Interface Down                                               Interface Down              Complex

Now, I need query CSV1.csv and pick values of 'Category' and find for all possible match in Category column of reference.csv and get the corresponding 'Complexity' from reference.csv and put the data against each category of CSV1.csv.

I am using find.all to achive this. I am unable to do it as expected. Is there any better way to achieve the same.

I tried using disct functions, that did not give result as expected.

Answer 1

A possible approach:

my_dict = dict(zip(reference_df['Category'].values, reference_df['Complexity'].values))

def match_key(key, default_value):
    for d_key in my_dict.keys():
        if key in d_key or d_key in key:
            return my_dict[d_key]

    return default_value

CSV1_df['Complexity'] = CSV1_df['Category'].apply(lambda x: match_key(x, 'default'))

Explanation:

1. Build a dict by zipping Category and Complexity columns in reference Dataframe, i.e. {'Server Down': 'Simple', 'Network Interface down': 'Complex'...}
2. Use apply and a lambda function to get corresponding Complexity values from the dictionary using each Category value in CSV1 Dataframe as key
3. We define a function to find if Category value in CSV1 Dataframe is a substring of any key in the dictionary or wise-versa and use it in apply
4. Save it to new column in CSV1 Dataframe