Skip to content

Instantly share code, notes, and snippets.

@pratikmallya

pratikmallya/gist:a8c47928a7e358ffb59d75531bdce1f1

Created March 7, 2026 13:38
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save pratikmallya/a8c47928a7e358ffb59d75531bdce1f1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save pratikmallya/a8c47928a7e358ffb59d75531bdce1f1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
```
In [59]: duckdb.sql("""
...: UNPIVOT (
...: SELECT Agency, Mode, TOS,
...: * EXCLUDE ("NTD ID", "Legacy NTD ID", Agency,
...: "Mode/Type of Service Status", "Reporter Type",
...: "UACE CD", "UZA Name", Mode, TOS, "3 Mode")
...: FROM upt
...: WHERE Agency = 'Fort Worth Transportation Authority' AND Mode = 'CR'
...: )
...: ON COLUMNS(* EXCLUDE (Agency, Mode, TOS))
...: INTO NAME month VALUE upt
...: """)
Out[59]:
┌─────────────────────────────────────┬─────────┬─────────┬─────────┬──────────┐
│ Agency │ Mode │ TOS │ month │ upt │
│ varchar │ varchar │ varchar │ varchar │ double │
├─────────────────────────────────────┼─────────┼─────────┼─────────┼──────────┤
│ Fort Worth Transportation Authority │ CR │ PT │ 1/2002 │ 78375.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 2/2002 │ 64771.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 3/2002 │ 76589.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 4/2002 │ 68920.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 5/2002 │ 62796.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 6/2002 │ 63173.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 7/2002 │ 80804.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 8/2002 │ 78080.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 9/2002 │ 63093.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 10/2002 │ 72580.0 │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ Fort Worth Transportation Authority │ CR │ PT │ 4/2025 │ 67832.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 5/2025 │ 72677.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 6/2025 │ 71949.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 7/2025 │ 78107.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 8/2025 │ 73379.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 9/2025 │ 72977.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 10/2025 │ 76042.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 11/2025 │ 83109.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 12/2025 │ 129115.0 │
│ Fort Worth Transportation Authority │ CR │ PT │ 1/2026 │ 68170.0 │
├─────────────────────────────────────┴─────────┴─────────┴─────────┴──────────┤
│ 193 rows (20 shown) 5 columns │
└──────────────────────────────────────────────────────────────────────────────┘
```
@pratikmallya
Copy link
Copy Markdown
Author

pratikmallya commented Mar 7, 2026 

In [60]: duckdb.sql("""
    ...:     SELECT *
    ...:     FROM read_xlsx('/Users/pratikmallya/Downloads/ridership.xlsx', sheet='UPT')
    ...:     WHERE Agency = 'Fort Worth Transportation Authority'
    ...:     AND Mode = 'CR'
    ...: """)
Out[60]:
┌─────────┬───────────────┬──────────────────────┬──────────────────────┬───────────────┬─────────┬──────────────────────┬───┬─────────┬─────────┬─────────┬─────────┬─────────┬─────────┬──────────┬─────────┐
│ NTD ID  │ Legacy NTD ID │        Agency        │ Mode/Type of Servi…  │ Reporter Type │ UACE CD │       UZA Name       │ … │ 6/2025  │ 7/2025  │ 8/2025  │ 9/2025  │ 10/2025 │ 11/2025 │ 12/2025  │ 1/2026  │
│ varchar │    varchar    │       varchar        │       varchar        │    varchar    │ varchar │       varchar        │   │ double  │ double  │ double  │ double  │ double  │ double  │  double  │ double  │
├─────────┼───────────────┼──────────────────────┼──────────────────────┼───────────────┼─────────┼──────────────────────┼───┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼──────────┼─────────┤
│ 60007   │ 6007          │ Fort Worth Transpo…  │ Active               │ Full Reporter │ 22042   │ Dallas--Fort Worth…  │ … │ 71949.0 │ 78107.0 │ 73379.0 │ 72977.0 │ 76042.0 │ 83109.0 │ 129115.0 │ 68170.0 │
├─────────┴───────────────┴──────────────────────┴──────────────────────┴───────────────┴─────────┴──────────────────────┴───┴─────────┴─────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┤
│ 1 rows                                                                                                                                                                               299 columns (15 shown) │
└────────────────

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment