itayyamin/csv-joiner

1import streamlit as st2import pandas as pd3import json4import re5from io import BytesIO6 7st.set_page_config(page_title="CSV Joiner", page_icon="📊", layout="centered")8 9st.title("📊 CSV Join & Customer ID Extractor")10st.markdown("---")11 12def extract_customer_id(request_body):13    """Extract customerId from JSON request_body field"""14    if pd.isna(request_body):15        return None16    try:17        cleaned = request_body.replace('""', '"')18        data = json.loads(cleaned)19        return data.get('customerId', None)20    except:21        try:22            match = re.search(r'"customerId"\s*:\s*"([^"]+)"', str(request_body))23            if match:24                return match.group(1)25        except:26            pass27    return None28 29# Instructions30st.info("👋 **Welcome!** Upload both CSV files below to join them on @CommunicationId and extract customer IDs")31 32# File uploaders33col1, col2 = st.columns(2)34 35with col1:36    st.subheader("📁 First CSV File")37    file1 = st.file_uploader("Upload first CSV file", type=['csv'], key='file1')38    if file1:39        st.success(f"✅ {file1.name}")40    41with col2:42    st.subheader("📁 Second CSV File")43    file2 = st.file_uploader("Upload second CSV file", type=['csv'], key='file2')44    if file2:45        st.success(f"✅ {file2.name}")46 47# Process files when both are uploaded48if file1 is not None and file2 is not None:49    try:50        with st.spinner('🔄 Processing files...'):51            # Load files52            df1 = pd.read_csv(file1)53            df2 = pd.read_csv(file2)54            55            st.success(f"✅ File 1 loaded: {len(df1):,} rows")56            st.success(f"✅ File 2 loaded: {len(df2):,} rows")57            58            # Check for required column59            if '@CommunicationId' not in df1.columns or '@CommunicationId' not in df2.columns:60                st.error("❌ Error: Both files must have an '@CommunicationId' column!")61                st.stop()62            63            # Join dataframes64            df_joined = pd.merge(df1, df2, on='@CommunicationId', how='outer', suffixes=('_file1', '_file2'))65            66            # Extract customer IDs67            if 'request_body' in df_joined.columns:68                df_joined['customerId'] = df_joined['request_body'].apply(extract_customer_id)69            elif 'request_body_file1' in df_joined.columns:70                df_joined['customerId'] = df_joined['request_body_file1'].apply(extract_customer_id)71                mask = df_joined['customerId'].isna()72                if 'request_body_file2' in df_joined.columns and mask.any():73                    df_joined.loc[mask, 'customerId'] = df_joined.loc[mask, 'request_body_file2'].apply(extract_customer_id)74            else:75                st.warning("⚠️ No 'request_body' column found. Proceeding without customer ID extraction.")76            77            # Display results78            st.markdown("---")79            st.subheader("📊 Results Summary")80            81            col_a, col_b, col_c = st.columns(3)82            col_a.metric("Total Rows", f"{len(df_joined):,}")83            84            if 'customerId' in df_joined.columns:85                col_b.metric("Customer IDs Found", f"{df_joined['customerId'].notna().sum():,}")86                col_c.metric("Missing IDs", f"{df_joined['customerId'].isna().sum():,}")87            else:88                col_b.metric("Customer IDs Found", "N/A")89                col_c.metric("Missing IDs", "N/A")90            91            # Preview92            st.subheader("👀 Data Preview (first 10 rows)")93            if 'customerId' in df_joined.columns:94                preview_cols = ['@CommunicationId', 'customerId']95                # Add a few more interesting columns if they exist96                for col in df_joined.columns:97                    if col not in preview_cols and len(preview_cols) < 5:98                        if 'Date' in col or 'From' in col or 'Status' in col:99                            preview_cols.append(col)100                st.dataframe(df_joined[preview_cols].head(10), use_container_width=True)101            else:102                st.dataframe(df_joined.head(10), use_container_width=True)103            104            # Download section105            st.markdown("---")106            st.subheader("⬇️ Download Results")107            108            # Convert to CSV for download109            csv_buffer = BytesIO()110            df_joined.to_csv(csv_buffer, index=False)111            csv_buffer.seek(0)112            113            st.download_button(114                label="📥 Download Complete Results (CSV)",115                data=csv_buffer,116                file_name="joined_with_customerId.csv",117                mime="text/csv",118                use_container_width=True,119                type="primary"120            )121            122            st.success("✨ Processing complete! Click the button above to download your results.")123            124    except Exception as e:125        st.error(f"❌ Error processing files: {str(e)}")126        st.error("Please make sure:")127        st.error("• Both files are valid CSV files")128        st.error("• Both files have the '@CommunicationId' column")129        st.error("• Files are properly formatted")130        131        with st.expander("🔍 Show detailed error"):132            st.code(str(e))133else:134    st.warning("⏳ Please upload both CSV files to continue...")135    136    # Help section137    with st.expander("ℹ️ How to use this tool"):138        st.markdown("""139        **Step 1:** Upload your first CSV file using the left uploader140        141        **Step 2:** Upload your second CSV file using the right uploader142        143        **Step 3:** The tool will automatically:144        - Join the files on the `@CommunicationId` column145        - Extract `customerId` from the `request_body` JSON field146        - Show you a preview of the results147        148        **Step 4:** Download the complete results as a CSV file149        150        ---151        152        **Requirements:**153        - Both files must be CSV format154        - Both files must have an `@CommunicationId` column155        - Files should have a `request_body` column containing JSON data with `customerId`156        """)157 158# Footer159st.markdown("---")160st.markdown("*Made with ❤️ using Streamlit*")161 162 163