Hacker News Reader

Comment by aw3c2 | Hacker News Reader

I meant to post some SQLite and Postgres numbers but the CSV that the code generates (on "wget http://www.pjm.com/pub/operations/hist-meter-load/{1993..201...) is garbage:

    2010-01-15 00:00:00,DPL,24,2017.425
    ...
    ,1/3/2017,1,RTO
    ...
    7/5/2017,VMEU,22,97.495

View on HN

The Python code works for Python 3 and pandas v0.22.0. There are some rows that are mangled, but my assumption was this was an artifact of reading Excel files, and I didn't go back and fix every single row in the dataset.

  ACTUAL_DATE,ZONE_NAME,HOUR_ENDING,MW
  1993-01-01 00:00:00,AE,1,855.0
  1993-01-01 00:00:00,BGE,1,2358.0
  1993-01-01 00:00:00,DPL,1,1150.0
  1993-01-01 00:00:00,GPU,1,3772.0
  1993-01-01 00:00:00,JCPL,1,1632.0
  1993-01-01 00:00:00,METED,1,929.0
  1993-01-01 00:00:00,PECO,1,3015.0
  1993-01-01 00:00:00,PENLC,1,1211.0
  1993-01-01 00:00:00,PEPCO,1,2137.0

I tried both Python 2.7.14 and Python 3.6.4. pandas==0.22.0 and xlrd==1.1.0. https://transfer.sh/2T4fw/hourly_loads.csv.gz is what I get.

It looks like dates are stored not as dates but strings in the more recent spreadsheets.

edit: dateutil's parser.parse(x) & strftime("%Y-%m-%d") helps

Reply on news.ycombinator.com