using python I would like to remove duplicate rows based on first column but would like to keep all the values in second column

Question

I have below dataframe

              CVE ID             Product Versions
0      CVE-2022-46689                   Mac OS 12
1      CVE-2022-42856                      Safari
2      CVE-2022-46689             Windows 10 21h1
3      CVE-2022-41121             Windows 10 21h2
4      CVE-2022-42856                      Safari

I would like to remove duplicates based on the column CVE ID but also want to make sure that I store the value present in the 2nd column Product Versions (but remove the value if already present)

Something like this below:

              CVE ID             Product Versions
0      CVE-2022-46689            Mac OS 12, Windows 10 21h1
1      CVE-2022-42856            Safari
2      CVE-2022-41121            Windows 10 21h2

How should I do it?

Any help is appreciated

You're probably looking for a solution like this: stackoverflow.com/questions/38559541/… — radhadman
– radhadman, Commented Jan 4, 2023 at 1:25

Naveed · Accepted Answer · 2023-01-04 01:34:13Z

1

here is one way to do it

# drop duplicates (in memory)
# groupby CVE ID and join the resulting list of product version

out=(df.drop_duplicates(subset=['CVE ID','Product Versions'])
 .groupby(['CVE ID'],as_index=False)['Product Versions']
 .agg(','.join ))

out

            CVE ID  Product Versions
0   CVE-2022-41121  Windows 10 21h2
1   CVE-2022-42856  Safari
2   CVE-2022-46689  Mac OS 12, Windows 10 21h1

answered Jan 4, 2023 at 1:34

Naveed

11.7k2 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karan Nayak Over a year ago

Thank you for your help! this is the easiest and quickest solution!!!

user20843299 · Accepted Answer · 2023-01-04 01:26:23Z

This could work:

import pandas as pd

# Create the DataFrame
df = pd.DataFrame({'CVE ID': ['CVE-2022-46689', 'CVE-2022-42856', 'CVE-2022-46689', 'CVE-2022-41121', 'CVE-2022-42856'],
                'Product Versions': ['Mac OS 12', 'Safari', 'Windows 10 21h1', 'Windows 10 21h2', 'Safari']})

# Group the rows by the 'CVE ID' column
grouped = df.groupby('CVE ID')

# Initialize an empty list to store the results
result = []

# Iterate over the groups
for name, group in grouped:
    # Concatenate the values in the 'Product Versions' column
    product_versions = ', '.join(list(set(group['Product Versions'])))
    
    # Append the name and product_versions to the result list
    result.append({'CVE ID': name, 'Product Versions': product_versions})

# Convert the result list to a DataFrame
result = pd.DataFrame(result)

print(result)

Collectives™ on Stack Overflow

using python I would like to remove duplicate rows based on first column but would like to keep all the values in second column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related