0

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.

goe
  • 337
  • 2
  • 14
  • Please add possible inputs and the expected output – Abhi_J Mar 25 '21 at 10:17
  • @jezrael - I have explored `merge` however it looks for exact match. `reference.csv` is static and `csv.csv` is dynamic and `category` filed in `csv1` is dynamic with possible matches. So I am using `find.all' to find all occurrence of matching patterns – goe Mar 25 '21 at 10:21
  • @Abhi_J I updated the question. I have explored merge however it looks for exact match. reference.csv is static and csv.csv is dynamic and category filed in csv1 is dynamic with possible matches. So I am using `find.all' to find all occurrence of matching patterns – goe Mar 25 '21 at 10:22
  • Exactly this is necessary add to question ;) – jezrael Mar 25 '21 at 10:22
  • @jezrael - I Agree will do it here after :-) – goe Mar 25 '21 at 10:23
  • 1
    Btw, I think you can try [this](https://stackoverflow.com/a/56315491/2901002) – jezrael Mar 25 '21 at 10:23
  • @Abhi_J I tried below `df1 = pd.read_csv('csv1.csv') df2 = pd.read_csv('reference.csv') my_dict = dict(zip(df2['Category'].values, df2['Complexity'].values)) print(my_dict) newdf = df1['Complexity'] = df1['Category'].apply(lambda x: my_dict [x]) ` I see error as `KeyError: 'Disk Space Utilization'` which is one of the category item in CSV1 – goe Mar 25 '21 at 11:20
  • @goe I've updated my answer with a new method to match the key. Also does jezrael's suggestion help? – Abhi_J Mar 25 '21 at 11:28
  • @Abhi_J Thank you, I tried as per your suggestion but this time no error however complexity is not getting updated. – goe Mar 25 '21 at 11:45
  • Updated? isn't Complexity a new column to be added to csv1, is it not added? – Abhi_J Mar 25 '21 at 11:51
  • @Abhi_J yes correct its not added. – goe Mar 25 '21 at 11:54
  • 1
    Which Dataframe are you printing/checking? btw `newdf = df1['Complexity'] = df1['Category'].apply(lambda x: my_dict [x])` this code will create a `pandas-series` called newdf not a dataframe. – Abhi_J Mar 25 '21 at 11:59
  • @Abhi_J `import pandas as pd df1 = pd.read_csv('csv1.csv') df2 = pd.read_csv('reference.csv') my_dict = dict(zip(df2['Category'].values, df2['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 df1['Complexity'] = df1['Category'].apply(lambda x: match_key(x, 'default_value')) print(df1)' I used this and `df1` is printing as expected. But its how can I write to CSV i.e i.e `df1` or `csv1.csv` – goe Mar 25 '21 at 12:04
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/230365/discussion-between-abhi-j-and-goe). – Abhi_J Mar 25 '21 at 12:12
  • @Abhi_J - I am requesting you help again, current code we are looking for the string match, for example `CPU Utilization` from reference file and look in the Category of actual column, this will work if it is `CPU Utilization of windows` However if the category filed in actual csv is `Utilization of CPU` then it might..what I can do to do all possible combinations something like `find.all` – goe Mar 26 '21 at 06:41

1 Answers1

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
Abhi_J
  • 2,061
  • 1
  • 4
  • 16