Data Code Helper-AI data and code assistant

Example

Python (pandas) script to open all CSV/TSV files in a directory, concatenate, and add 'src_file' and 'src_prefix': ```python from pathlib import Path import pandas as pd def load_concat(dir_path: str) -> pd.DataFrame: '''Load all CSV/TSV files, concatenate, and add lineage columns. Args: dir_path: Directory containing .csv and .tsv files. Returns: pandas.DataFrame with columns 'src_file' and 'src_prefix' added. ''' files = list(Path(dir_path).glob('*.csv')) + list(Path(dir_path).glob('*.tsv')) frames = [] for f in files: sep = '\t' if f.suffix.lower() == '.tsv' else ',' df = pd.read_csv(f, sep=sep) df['src_file'] = f.name df['src_prefix'] = f.stem.split('_')[0] frames.append(df) if not frames: return pd.DataFrame() df_all = pd.concat(frames, ignore_index=True) # Optional: canonicalize column names and dtypes df_all.columns = [c.strip().lower().replace(' ', '_') for c in df_all.columns] return df_all if __name__ == '__main__': df = load_concat('/path/to/drops') df.to_parquet('combined.parquet', index=False) ``` SQL pattern for robust transformations (example in BigQuery syntax): ```sql WITH cleaned AS ( SELECT SAFE_CAST(order_id AS INT64) AS order_id, PARSE_DATE('%Y-%m-%d', order_date) AS order_date, LOWER(TRIM(email)) AS email, revenue FROM staging.orders_raw ), validated AS ( SELECT * FROM cleaned WHERE order_id IS NOT NULL AND revenue IS NOT NULL ) SELECT * FROM validated; ```

Scenario

A marketing analyst receives monthly exports from multiple vendors. The Python script merges everything into a single, typed table with lineage columns for debugging. The SQL snippet is then used to standardize types, filter bad records, and publish a clean model for BI. Outcome: one reliable table powering dashboards; easy backfills and clear provenance.

Example

Airflow DAG that ingests from S3, runs dbt, loads to BigQuery, and notifies Slack: ```python from airflow import DAG from airflow.operators.bash import BashOperator from datetime import datetime, timedelta default_args = { 'owner': 'data', 'retries': 1, 'retry_delay': timedelta(minutes=5), } with DAG( 'daily_sales_pipeline', start_date=datetime(2025, 1, 1), schedule='0 2 * * *', default_args=default_args, catchup=False, ) as dag: extract = BashOperator( task_id='extract_s3', bash_command='aws s3 cp s3://my-bucket/raw/{{ ds }}/ /tmp/raw/ --recursive' ) transform = BashOperator( task_id='dbt_run', bash_command='cd /opt/airflow/dbt && dbt run --select tag:daily' ) load = BashOperator( task_id='bq_load', bash_command='bq load --autodetect --replace myproj.sales /tmp/raw/*.csv' ) notify = BashOperator( task_id='slack_notify', bash_command="curl -X POST -H 'Content-type: application/json' --data '{\n \'text\': \'daily_sales_pipeline finished for {{ ds }}\'\n}' $SLACK_WEBHOOK" ) extract >> transform >> load >> notify ``` macOS cron alternative for lightweight jobs: ```bash # Edit crontab: crontab -e 0 7 * * * /usr/local/bin/python3 /Users/me/jobs/pull_api.py >> /Users/me/jobs/logs/pull_api.log 2>&1 ```

Scenario

An analytics engineer must guarantee a 7:30 AM dashboard SLA. Airflow provides retries, alerting, and backfills; the DAG is idempotent (replace loads), and uses templated dates. For very small tasks or personal workflows, a cron entry on macOS is sufficient. Outcome: predictable delivery times, fewer manual steps, and clear operational visibility.

Example

Apps Script to de-duplicate form leads by email, normalize values, and publish to a clean tab: ```javascript function cleanAndSync() { const ss = SpreadsheetApp.getActive(); const src = ss.getSheetByName('Form Responses 1'); const data = src.getDataRange().getValues(); const header = data.shift(); const emailIdx = header.indexOf('Email'); const seen = new Set(); const cleaned = [header]; data.forEach(row => { row[emailIdx] = String(row[emailIdx]).toLowerCase().trim(); const key = row[emailIdx]; if (key && !seen.has(key)) { seen.add(key); cleaned.push(row); } }); const out = ss.getSheetByName('Leads') || ss.insertSheet('Leads'); out.clearContents(); out.getRange(1, 1, cleaned.length, cleaned[0].length).setValues(cleaned); } ``` Install a time-driven trigger in Apps Script to run daily at 07:00, or add a simple menu to run on demand.

Scenario

A RevOps manager needs a no-code-ish solution to keep a 'Leads' tab clean for the sales team without learning Python. The script de-dupes by email, normalizes case, and publishes a single source of truth in Sheets. Optional: push to CRM via API or export to CSV for uploading. Outcome: clean, consistent inputs for downstream teams with minimal engineering overhead.