import os os.system("pip install xlsxwriter") import os os.system("pip install xlsxwriter") import os os.system("pip install xlsxwriter") import streamlit as st import pandas as pd import re from datetime import datetime import io st.set_page_config(page_title="Sensor Query Extractor", layout="wide") st.title("📦 Sensor Query Cleaner + Auto Tagger") uploaded_file = st.file_uploader("📄 Upload WhatsApp chat .txt file", type="txt") # --- Auto-tag sensor types --- sensor_keywords = { "Proximity": ["e2e", "e2b", "proximity", "tl-w", "tl-w5", "tlq", "tl-n", "pnp", "npn"], "Reed Switch": ["reed", "magnetic"], "Photoelectric": ["e3fa", "e3jk", "e3x", "e3c", "photo", "photocell"], "Capacitive": ["ec2", "ec5", "capacitive"], "Inductive": ["inductive"], "Pressure": ["pressure", "dz"], "Temperature": ["temperature", "pt100"], "Other": [] } def auto_tag(product_name): product_name = product_name.lower() for tag, keywords in sensor_keywords.items(): if any(k in product_name for k in keywords): return tag return "Other" # --- Discard phrases --- discard_phrases = [ "send", "u sell", "u don't", "if get", "then buy", "may take", "i think", "this is", "have this", "available", "sensing price", "each", "immediate", "packing", "version", "duplicate", "original", "chinese", "best", "price", "import", "stamp", "plug", "cable", "extra dalwa", "check kar lena", "confirm", "image omitted", "sir", "done", "rate dena", "update", "just sent", "is this okay", "check this", "same", "ok", "ho sakta hai", "dekh lena", "will share", "photo", "image", "not dispatch", "this sensor", "sensor update", "dispatch", "ready", "send photo", "send image", "this one", "share", "kindly", "missed voice call", "call back", "courier details", "delivery", "model no", "yes", "no", "voice call", "sec", "omitted", "call", "audio omitted", "video omitted", "document omitted", "location omitted", "contact omitted", "sticker omitted", "gif omitted", "deleted", "this message was deleted", "calling", "busy", "ringing", "missed call", "declined" ] # --- Extract helpers --- def extract_date(line): match = re.search(r"\[(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{2,4})", line) if match: day, month, year = match.groups() if len(year) == 2: year = '20' + year return f"{year}-{int(month):02d}-{int(day):02d}" return None def extract_quantity(text): if pd.isna(text): return None text = str(text).lower() match = re.search(r'(qty\s*\d+|\d+\s*pcs\b|\d+\s*nos\b|\d+\s*each\b|\d+\s*no\b)', text) return match.group(0) if match else None def remove_quantity(text): return re.sub(r'(qty\s*\d+|\d+\s*pcs\b|\d+\s*nos\b|\d+\s*each\b|\d+\s*no\b)', '', str(text), flags=re.IGNORECASE).strip() def remove_context(text): text = str(text) patterns = [ r'\bwe\s*require\b', r'\bwe\s*need\b', r'\bneed\b', r'\brequirement\s*of\b', r'\bwe\s*are\s*looking\s*for\b', r'\blooking\s*for\b', r'\bsend\s*me\b', r'\bcan\s*you\s*send\b', r'\bcan\s*you\s*share\b', r'\bgive\s*me\b', r'\bi\s*want\b', r'\bi\s*need\b', r'\bpls\s*share\b', r'\bplease\s*share\b', r'\bkindly\s*share\b', r'\bshare\s*the\b', r'\bsend\s*the\b' ] for p in patterns: text = re.sub(p, '', text, flags=re.IGNORECASE) return text.strip() def remove_sender(text): # Remove sender like "Majisha -5 :", "Box Silvassa :", etc. if ":" in text: return ":".join(text.split(":")[1:]).strip() return text.strip() def clean_product(text): text = remove_sender(text) text = remove_quantity(text) text = remove_context(text) text = re.sub(r"[^\w\s\-_/.,]", "", text) # remove special chars return text.strip() def extract_sender_name(line): """Extract sender name from WhatsApp chat line""" try: # Look for pattern: [date, time] Sender Name: message if "]" in line and ":" in line: # Split by ] to get the part after timestamp after_timestamp = line.split("]", 1)[1] # Split by : to get sender name sender_part = after_timestamp.split(":", 1)[0].strip() return sender_part if sender_part else "Unknown" except: pass return "Unknown" def has_dolphin_sender(line): """Check if the line contains 'Dolphin' as the sender""" # Check if "Dolphin" appears anywhere in the line (case insensitive) return "dolphin" in line.lower() def is_valid_product_query(text): """Check if the text represents a valid product query - must be actual product name/model""" if not text or len(text.strip()) < 2: return False text_lower = text.lower().strip() # Check for discard phrases if any(phrase in text_lower for phrase in discard_phrases): return False # Reject if it's just a conversational message conversational_starts = ["send", "u sell", "u don't", "if get", "then buy", "may take", "i think", "this is", "have this", "available", "in best", "for"] if any(text_lower.startswith(phrase) for phrase in conversational_starts): return False # Reject if it ends with conversational terms conversational_ends = ["each", "immediate", "packing", "version", "duplicate", "original", "chinese", "best", "price", "available", "please", "kya", "automation"] if any(text_lower.endswith(phrase) for phrase in conversational_ends): return False # Must have alphanumeric model pattern has_model_pattern = bool(re.search(r'[a-zA-Z]+\d+|[a-zA-Z]*\d+[a-zA-Z]+|\d+[a-zA-Z]+', text)) # Must have technical specifications with units has_tech_specs = bool(re.search(r'\d+\s*(mm|cm|inch|bar|psi|v|volt|amp|mpa|kg|ton)', text_lower)) # Must have sensor/product keywords product_keywords = ["sensor", "switch", "relay", "motor", "valve", "actuator", "controller", "encoder", "transmitter", "transducer"] has_product_keyword = any(keyword in text_lower for keyword in product_keywords) # Known brands with model numbers known_brands = ["omron", "festo", "sick", "banner", "schneider", "siemens", "allen", "bradley", "pepperl", "fuchs", "turck", "balluff"] has_brand_with_model = any(brand in text_lower for brand in known_brands) and has_model_pattern # Single brand names are not valid queries words = text_lower.split() if len(words) == 1 and words[0] in known_brands: return False # Must satisfy at least one condition return has_model_pattern or has_tech_specs or has_product_keyword or has_brand_with_model # --- Main logic --- if uploaded_file: raw_text = uploaded_file.read().decode("utf-8", errors="ignore") lines = raw_text.splitlines() records = [] filtered_out_count = 0 for line in lines: if not line.strip(): continue if not re.search(r"\[\d{1,2}/\d{1,2}/\d{2,4}", line): continue # Skip lines with "Dolphin" as sender if has_dolphin_sender(line): filtered_out_count += 1 continue date = extract_date(line) sender_name = extract_sender_name(line) msg_part = line.split("]")[-1] if "]" in line else line quantity = extract_quantity(msg_part) product_name = clean_product(msg_part) # Enhanced validation - only keep valid product queries if product_name and is_valid_product_query(product_name): sensor_type = auto_tag(product_name) records.append({ "Product Line": line.strip(), "Sender Name": sender_name, "Product Name (with Model)": product_name, "Sensor Type": sensor_type, "Extracted Quantity": quantity, "Date": date }) if records: df = pd.DataFrame(records) st.success(f"✅ Extracted {len(records)} Valid Product Queries") if filtered_out_count > 0: st.info(f"🔍 Filtered out {filtered_out_count} messages from 'Dolphin' sender") st.dataframe(df) # --- Download Excel --- output = io.BytesIO() with pd.ExcelWriter(output, engine='xlsxwriter') as writer: df.to_excel(writer, index=False, sheet_name="Sensor Queries") st.download_button( label="⬇ Download Excel", data=output.getvalue(), file_name="sensor_queries_cleaned_tagged.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ) # Show statistics st.subheader("📊 Query Statistics") col1, col2, col3 = st.columns(3) with col1: st.metric("Total Queries", len(records)) with col2: st.metric("Sensor Types", df['Sensor Type'].nunique()) with col3: queries_with_qty = df[df['Extracted Quantity'].notna()]['Extracted Quantity'].count() st.metric("With Quantity", queries_with_qty) # Show sensor type distribution st.subheader("🔧 Sensor Type Distribution") sensor_counts = df['Sensor Type'].value_counts() st.bar_chart(sensor_counts) else: st.warning("⚠ No valid product queries found after filtering")