[Python] Is there a way for SQL to filter out bad duplicate descriptions? [closed]

I have found this real invoice data online and I want to make a whole database out of it. My first step is to create a products table from the products found in the invoice csv sheet but my problem it the data is really bad and has a lot of duplicates.

I extracted the stock code and description then imported it to a products table where stock code became product_id.

My problem is that most of the stock code contains multiple descriptions.

I started by removing descriptions having bad keywords, but only those that can be removed without leaving a product_id description-less.

WITH bad_patterns AS (SELECT (ARRAY[
'%Sold%',
'%%?',
'%Dotcom%',
'%Wet%',
'%Rusty%',
'%Can''t%',
'%Found%',
'%Adjustment%',
'%Wrongly%',
'%Aside%',
'%Broken%',
'%Amazon%',
'%Lost%',
'%Error%',
'Check',
'Metal',
'Given Away',
'Faulty',
'Ebay',
'%Cant%',
'%Marked%',
'%Cargo%',
'%Wrong%',
'%Rcvd%',
'%Test%',
'%Cracked%',
'%Mailout%',
'%Ass%',
'%Fba%',
'%Difference%',
'%Dagamed%',
'%Problem%',
'%Crushed%',
'%Mia%',
'%Samples%',
'%Smashed%',
'%Adjust%',
'%Deco%',
'%Breakages%',
'%Mouldy%',
'%Coded%',
'%Unsaleable%',
'%Destroyed%',
'%Returned%',
'%Thrown Away%',
'%Oops%',
'%Mix Up%',
'%Display%',
'%Counted%',
'%Credit%',
'%Damage%',
'%Showroom%']) AS patterns
),

bad_descriptions AS
(SELECT dp.ctid, dp.product_id, dp.description
FROM (SELECT ctid, product_id, description, COUNT(*) OVER(PARTITION BY product_id) AS product_count FROM products) AS dp
CROSS JOIN bad_patterns
WHERE product_count > 1
AND
description ILIKE ANY (
bad_patterns.patterns))

DELETE FROM products p
WHERE ctid IN (SELECT ctid FROM bad_descriptions)
AND EXISTS (
SELECT 1 FROM products p2
CROSS JOIN bad_patterns
WHERE description NOT ILIKE ANY(bad_patterns.patterns)
AND p.product_id = p2.product_id);


** To visualize what's left , I extracted the table below to show multiple descriptions as an array in a single column using the SQL below.**

SELECT product_id, array_agg(DISTINCT description) AS descriptions
FROM products
GROUP BY product_id
HAVING COUNT(DISTINCT description) > 1
ORDER BY product_id;

product_id descriptions
16156L {"Wrap Carousel","Wrap, Carousel"}
17107D {"Flower Fairy 5 Drawer Liners","Flower Fairy 5 Summer Draw Liners","Flower Fairy,5 Summer B'Draw Liners"}

My problem now is those descriptions left are very similar, I'm not sure how to pick which one is correct and filter out the wrong one. Length is not on option since some shorter descriptions are more correct than the longer one and vice versa.

What's the best way to approach this? Using SQL only or Python if that's the only way possbile.

Thank you!

Continue reading...