Datasets to be automated

[Mikanebu]

We want to automate more datasets, so they will be updated regularly.

Acceptance criteria

• [ ] analysis fill

Tasks

• [x] find 6 datasets which are regularly updated and with less complex operations on cleaning
• [x] describe cleaning steps
  • supports download file and cache it
  • unzip
  • read .xls and .xlsx
  • read .txt
• [x] analysis on all core datasets
  • [x] prepare first 3 datasets for automation for required operations
  • [x] Analysis for the rest core datasets

Analysis

• eu-emissions-trading-system - https://github.com/datasets/eu-emissions-trading-system
  • download
  • upzip file
  • remove extra white spaces
  • regularity: annually
• sea-level-rise - https://github.com/datasets/sea-level-rise
  • download zip and csv
  • unzip and create 4 csv
  • remove first 2 rows
  • regularity: monthly
• oil prices - https://github.com/datasets/oil-prices
  • download xls
  • read sheet 1
  • skip first 2 rows
  • regualarity: daily
• gold-prices - https://github.com/datasets/gold-prices
  • download csv
  • remove first 5 rows
  • remove last row
  • regularity monthly
• co2-ppm - https://github.com/datasets/co2-ppm
  • download txt
  • remove line starting with #
  • replace white space with comma
  • there are total 6 files
  • regularity: monthly
• s-and-p-500 - https://github.com/datasets/s-and-p-500
  • download xlsx
  • remove first 5 rows
  • remove empty column
  • normalize date format from 1991.02 to 01-02-1991
  • add new headers
  • regularity: monthly

Output:

• eu-emissions-trading-system - https://github.com/datasets/eu-emissions-trading-system I could not scrape it, after unzipping it cannot process file. It works when file name is the same as zipped version.
• sea-level-rise - https://github.com/datasets/sea-level-rise I could not scrape it, after unzipping it cannot process file. It works when file name is the same as zipped version.
• oil prices - https://github.com/datasets/oil-prices Source is https://www.eia.gov/dnav/pet/hist_xls/RBRTEd.xls. It converts date format incorrecly from 5/20/1987 to 31917.0.
• gold-prices - https://github.com/datasets/gold-prices I could not remove last row. Source is http://www.bundesbank.de/cae/servlet/StatisticDownload?tsId=BBEX3.M.XAU.USD.EA.AC.C06&its_csvFormat=en&its_fileFormat=csv&mode=its
• co2-ppm - https://github.com/datasets/co2-ppm Source is in .txt format ftp://aftp.cmdl.noaa.gov/products/trends/co2/co2_mm_mlo.txt It did not work for txt as well, according to the tabular-py documentation, split into rows with a single "data” column.

[Mikanebu]

Analysis can be found in spreadsheet https://docs.google.com/spreadsheets/d/14kJluhePaMOx6vYBic0poVjDYK3I8-v_xF3sv4Focac/edit#gid=405792816

[Mikanebu]

3 datasets to be automated:

• gold-prices - https://github.com/datasets/gold-prices

  • modified: monthly
  • issue removing last row
  • Date format in YYYY-MMso we have 2 options
  • we can use type yearmonth according to the specs https://frictionlessdata.io/specs/table-schema/. But I cannot process it, since it throws casting error, opened issue https://github.com/frictionlessdata/tableschema-py/issues/201.
  • we can assign type date with format any. But, in this case, days will be automatically added as a current day. For example, if pipeline runs today, date will be 1991-12-{current-day}.

• oil-prices - https://github.com/datasets/oil-prices - day,week,month,year

  • modified: daily, weekly, monthly, yearly (total 8 files)
  • issue with parsing excel date format. It converts incorrectly, opened issue 20 days ago in tabulator-py - https://github.com/frictionlessdata/tabulator-py/issues/220

• s-and-p-500 - https://github.com/datasets/s-and-p-500

  • modified: monthly
  • normalize date format from 1991.01 to 1991-01, by replacing . with - for date column
  • issue with parsing excel date format. It converts incorrectly, opened issue 20 days ago in tabulator-py - https://github.com/frictionlessdata/tabulator-py/issues/220

[Mikanebu]

FIXED, We have now filled in all the analysis columns and here are the major new features need:

• Unpivot (6 datasets)
• Remove first rows(11 datasets) - supported by tabulator-py
• Remove columns(5 datasets)
• Add columns(1 dataset)
• Remove Last rows(2 dataset)
• Merge Columns(4 datasets)
• Find/replace(3 datasets)
• Unzip(3 datasets)

Analysis spreadsheet is located here https://docs.google.com/spreadsheets/d/14kJluhePaMOx6vYBic0poVjDYK3I8-v_xF3sv4Focac/edit#gid=405792816