Skip to main content

Download and store MTA turnstile data

Project description

# pymtattl

## Introduction

MTA Turnstile Data: http://web.mta.info/developers/turnstile.html

Download, process, and store MTA Turnstile Data in database

* `Downloader` class: automate downloading turnstile raw entry/exit data from MTA website into txt files (weekly, cumulated)
* `Cleaner` class: convert downloaded text files and write decumulated records to database.

Note 1: trying to be database agnostic, used sqlalchemy and tested with sqlite and postgres 10.

Note 2: be cautious about date range of files need to be appended to the database tables, avoid duplication or adding data of weeks prior to the ones in the tables.

## Table of Contents

* [Installation](#installation)

* [Download](#download)

* [Clean](#clean)

* [Caveats](#caveats)

* [To-Do](#to-do)

## Installation

pip install pymtattl

## Requirements

* Written for Python 3! Feel free to test and contribute using Python 2!
* Requires bs4, pandas, sqlalchemy

## Download

`Downloader`: download data within date range as weekly text files.

from pymtattl import Downloader

download = Downloader(date_range=("2018-01-01", "2018-02-01"),
main_path='./data/',
verbose=10)
data_path = download.run()

* `date_range`: *tuple*
- Define the start and end dates *(recommend testing with small date ranges, as downloading all files might be slow)*
- Example (yyyy-mm-dd): `("2018-01-01", "2018-02-01")`

* `main_path`: *string*, default './data/'
- A directory to store downloaded data files (will be created if dir not exists)
- Every run creates a new dir `download-yyyymmddhhmmss`, where all data files and log files are nested under

* `verbose`: *int*, default 10
- Log and print out when every n files are downloaded

* Returns full directory of parent folder `download-yyyymmddhhmmss`

## Clean

`Cleaner`: decumulate and store downloaded data files in database. Please make sure database already exists if not using sqlite.

from pymtattl import Cleaner

clean = Cleaner(date_range=None,
input_path='./data/download-20181227160016',
dbstring='postgresql://user:p@ssword@localhost:5432/mta_sample')
clean.run()

* Create 4 tables to save disk space and use end of last week numbers to be used as baseline for current week
- `turnstile`: decumulated entry/exit
- columns: *id, device_id, timestamp, description, entry, exit*
- `station`: mta staion defined by ca, unit pairs
- columns: *id, ca, unit*
- `device`: device location in each station
- columns: *id, station_id, scp*
- `previous`: memorize ending data from previous week, support decumulate accross weekly files
- columns: *id, device_id, timestamp, description, entry, exit, file_date*

* `date_range`: *tuple*, default None
- Define the start and end dates of the files to be added to database
- Example (yyyy-mm-dd): `("2018-01-01", "2018-02-01")`
- If None (default), will add all data files in folder

* `input_path`: *string*
- Directory of the downloaded text files to be added to database

* `dbstring`: *string*
- Database urls used by sqlalchemy
- dialect+driver://username:password@host:port/database
- postgres: 'postgresql://scott:tiger@localhost/mydatabase'
- mysql: 'mysql://scott:tiger@localhost/foo'
- sqlite: 'sqlite:///foo.db'
- more info: https://docs.sqlalchemy.org/en/latest/core/engines.html#postgresql

## Data Issues

* Some known data issues, might happen in multiple files and quite manual to detect and remove

- In turnstile_120428.txt, one line with empty ('') exit number
- In turnstile_120714.txt, first few lines could not be parsed
- Date strings were reformatted to `mm/dd/yyyy` (03/20/2018)
- In turnstile_170204.txt, A025, R023, 01-03-01, 02/01/2017, entry numbers start counting backwards
- In turnstile_170909.txt, C020, R233, 00-00-00, 09/02/2017, status switch between REGULAR and RECOVER AUD
- In turnstile_170318.txt, PTH03, R522, 00-00-09, 03/11/2017, every second record seems to be correct but every next one could increase by 80K. This seem to happen with smaller numbers as well. In turnstile_171028.txt, PTH07, R550, 00-01-06, 10/21/2017, entries increase and decrease by 8K.

* Incompatible data types and formats were detected, logged, and ignored during Downloading process. The package provides a workaround with other issues related to values:

- Count backwards: use absolute values after diff method is called
- Adjacent values inconsistent, but every second record correct: a second diff is called on values greater than certain threshold (Entry > 7000, Exit > 6000)
- Huge values: values still above threshold are dropped

## To-Do

* Batch processing of multiple data files together before decumulate step.

* Append station name to station table. (in pymtattl/utils.py)

* More to come...

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

pymtattl-1.1.1.tar.gz (10.2 kB view details)

Uploaded Source

File details

Details for the file pymtattl-1.1.1.tar.gz.

File metadata

  • Download URL: pymtattl-1.1.1.tar.gz
  • Upload date:
  • Size: 10.2 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/1.9.1 pkginfo/1.4.2 requests/2.19.1 setuptools/40.2.0 requests-toolbelt/0.8.0 tqdm/4.26.0 CPython/3.7.0

File hashes

Hashes for pymtattl-1.1.1.tar.gz
Algorithm Hash digest
SHA256 19403f310c34d78c5213ee4b52e92515ba04966ff0d31b4076c02436760d5b38
MD5 ffdebd33681157b403cd5cea7b9ff093
BLAKE2b-256 d4a8c60fedab05b01f1ae37de08e7369445f51b89228f96da5a9e6ae5fac2bd5

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page