Filtering a Bulk Property File With DuckDB

# macOS
brew install duckdb

# Python (any OS): the same engine as a library
pip install duckdb

# Or download the single CLI binary from duckdb.org and run:
duckdb property.duckdb

-- Read every part of the export, all columns as text first, then convert
-- the ones you filter on. TRY_CAST returns NULL instead of failing.
CREATE OR REPLACE VIEW raw AS
SELECT * FROM read_csv('exports/*.csv', header = true, all_varchar = true);

-- The export's headers contain spaces, so quote them once here and give
-- each a short name. Every later query uses the short names.
CREATE OR REPLACE VIEW props AS
SELECT
  "Property ID" AS property_id, "APN" AS apn,
  "Address" AS address, "City" AS city, "State" AS state, "Zip" AS zip,
  "County" AS county, "Property Type" AS property_type, "Owner Type" AS owner_type,
  nullif(trim(concat_ws(' ', "Owner 1 First Name", "Owner 1 Last Name")), '') AS owner_name,
  "Owner 2 First Name" AS owner2_first, "Owner 2 Last Name" AS owner2_last,
  "Owner Mailing Address" AS mail_street, "Owner Mailing City" AS mail_city,
  "Owner Mailing State"   AS mail_state,  "Owner Mailing Zip"  AS mail_zip,
  TRY_CAST("Bedrooms"           AS INTEGER) AS beds,
  TRY_CAST("Living Square Feet" AS INTEGER) AS sqft,
  TRY_CAST("Year Built"         AS INTEGER) AS year_built,
  TRY_CAST("Units Count"        AS INTEGER) AS units,
  TRY_CAST("Years Owned"        AS DOUBLE)  AS years_owned,
  TRY_CAST("Estimated Value"    AS BIGINT)  AS est_value,
  TRY_CAST("Estimated Equity"   AS BIGINT)  AS equity,
  TRY_CAST("Equity Percent"     AS DOUBLE)  AS equity_pct,
  TRY_CAST("DTV"                AS DOUBLE)  AS dtv,
  TRY_CAST("Open Lien Count"    AS INTEGER) AS open_lien_count,
  TRY_CAST("Last Sale Date"     AS DATE)    AS last_sale_date,
  -- Flags are the text 'true' or 'false'; blank becomes false here.
  coalesce("Corporate Owned" = 'true', false) AS corporate,
  coalesce("Free And Clear"  = 'true', false) AS free_and_clear,
  coalesce("Owner Occupied"  = 'true', false) AS owner_occupied,
  coalesce("Vacant?"         = 'true', false) AS vacant,
  -- No absentee column in the file: approximate it as "not owner occupied".
  -- Blank stays NULL, so unknowns drop out of absentee filters.
  "Owner Occupied" = 'false'                  AS absentee
FROM raw;

.timer on

-- What is in the file? One pass, several answers.
SELECT count(*)                         AS records,
       count(DISTINCT state || '|' || county) AS counties,
       count(est_value)                 AS with_value,
       count(equity_pct)                AS with_equity_pct
FROM props;

-- The exact spellings you will filter on.
SELECT property_type, count(*) AS n FROM props GROUP BY 1 ORDER BY n DESC;

-- How the flags are written in this file (before conversion).
SELECT "Owner Occupied", "Corporate Owned", count(*) FROM raw GROUP BY ALL ORDER BY 3 DESC;

-- The source's own owner classification, if you want it instead of a rule.
SELECT owner_type, count(*) AS n FROM props GROUP BY 1 ORDER BY n DESC;

-- High-equity, long-held single-family homes with absentee owners
-- (absentee = not owner occupied; see the typed view).
SELECT address, owner_name, mail_street, mail_city, mail_state, mail_zip,
       est_value, equity_pct, years_owned
FROM props
WHERE property_type = 'Single Family'       -- use the spelling from your file
  AND absentee
  AND equity_pct >= 50
  AND years_owned >= 10
ORDER BY equity_pct DESC;

-- Distribution of estimated value by county, in fixed bands.
SELECT state, county,
       CASE
         WHEN est_value <  150000 THEN '1: under 150k'
         WHEN est_value <  300000 THEN '2: 150k-300k'
         WHEN est_value <  600000 THEN '3: 300k-600k'
         WHEN est_value < 1000000 THEN '4: 600k-1m'
         ELSE                          '5: 1m and over'
       END AS band,
       count(*) AS n
FROM props
WHERE est_value IS NOT NULL
GROUP BY ALL
ORDER BY state, county, band;

-- Or let the data choose: quartiles of value per county.
SELECT state, county,
       quantile_cont(est_value, [0.25, 0.5, 0.75]) AS value_quartiles,
       count(*) AS n
FROM props
WHERE est_value > 0
GROUP BY ALL
ORDER BY n DESC;

-- Owner type from the Corporate Owned flag plus name patterns.
CREATE OR REPLACE MACRO owner_class(name, corp) AS
  CASE
    WHEN regexp_matches(upper(name), '\b(LLC|L L C|INC|CORP|LP|LTD|HOLDINGS|PROPERTIES)\b') THEN 'company'
    WHEN regexp_matches(upper(name), '\b(TRUST|TRUSTEE|TR|TRS)\b')                     THEN 'trust'
    WHEN regexp_matches(upper(name), '\b(ESTATE OF|HEIRS)\b')                         THEN 'estate'
    WHEN corp THEN 'company'
    ELSE 'individual'
  END;

SELECT owner_class(owner_name, corporate) AS owner_class,
       count(*)                           AS n,
       round(avg(equity_pct), 1)          AS avg_equity_pct
FROM props
GROUP BY 1
ORDER BY n DESC;

-- Free-and-clear properties held by individuals out of state.
SELECT *
FROM props
WHERE owner_class(owner_name, corporate) = 'individual'
  AND free_and_clear
  AND mail_state <> state;                  -- mailed outside the property's state

-- One reusable filter, parameterised with a CTE.
WITH params AS (
  SELECT 40 AS min_equity_pct, 200000 AS min_value, 900000 AS max_value
)
SELECT p.*
FROM props p, params
WHERE p.equity_pct BETWEEN params.min_equity_pct AND 100
  AND p.est_value BETWEEN params.min_value AND params.max_value
  AND p.property_type IN ('Single Family', 'Condominium')
  AND NOT p.corporate
  AND p.open_lien_count <= 1;

-- Convert once. The typed view becomes a typed, compressed, columnar file.
COPY (SELECT * FROM props) TO 'state.parquet' (FORMAT parquet);

-- From now on, query the Parquet file instead of the CSV parts.
CREATE OR REPLACE VIEW props AS SELECT * FROM 'state.parquet';

-- Work out a filter on a random 1% sample, then run it on everything.
SELECT * FROM props USING SAMPLE 1 PERCENT (bernoulli) WHERE absentee LIMIT 20;

-- See where the time goes in a slow query.
EXPLAIN ANALYZE
SELECT state, county, count(*) FROM props WHERE equity_pct >= 50 GROUP BY ALL;

-- Write a filtered list back out as CSV for a CRM or a mail house.
COPY (
  SELECT owner_name, owner2_first, owner2_last,
         mail_street, mail_city, mail_state, mail_zip,
         address, city, state, zip, county, apn, est_value, equity_pct
  FROM props
  WHERE absentee AND equity_pct >= 50
  ORDER BY mail_zip
) TO 'absentee_high_equity.csv' (HEADER);

-- One file per county, in a folder per state and county name.
COPY (SELECT * FROM props WHERE absentee)
TO 'by_county' (FORMAT csv, HEADER, PARTITION_BY (state, county));

import duckdb

con = duckdb.connect("property.duckdb")      # or duckdb.connect() for in-memory
con.sql("SET memory_limit = '4GB'")          # optional; DuckDB spills to disk above it

df = con.sql("""
    SELECT state, county, count(*) AS n, median(est_value) AS median_value
    FROM 'state.parquet'
    WHERE absentee AND equity_pct >= 50
    GROUP BY ALL
    ORDER BY n DESC
""").df()                                     # a pandas DataFrame

print(df.head(10))