Friday, November 15, 2013

Converting USDA PSD data to GAMS GDX

USDA has some good agricultural production and trade data available at http://www.fas.usda.gov/psdonline/psdHome.aspx. Here we try to read the CSV file psd_alldata.csv and convert it to a usable GAMS GDX file.

$ontext

  
Convert psd_alldata.csv to a GDX file

  
Source:
  
http://www.fas.usda.gov/psdonline/psdDownload.aspx

  
Sorry for the obscure syntax, I am sure this will confuse users

  
Erwin Kalvelagen, 2013

$offtext


$set csv psd_alldata.csv
$set gdx psd_alldata.gdx

$version 241

$call csv2gdx %csv% id=alldata useheader=y index=2,4,5,9,11 value=12


set year 'superset' /1900*2050/;
alias
(commodity,country,attribute,unit,*);
parameter
alldata(commodity,country,year,attribute,unit);

$gdxin %gdx%
$load alldata


sets

  commodity2(commodity)
  country2(country)
  year2(year)   
'market year'
  attribute2(attribute)
  unit2(unit)
;



option
  commodity2 < alldata
  country2 < alldata
  year2 < alldata
  attribute2 < alldata
  unit2 < alldata
;


execute_unload '%gdx%', alldata, commodity2=commodity, country2=country,
                        year2=year, attribute2=attribute, unit2=unit;

The final GDX file looks like:

image

This file can be read as:

$ontext

  
Example: Read PSD data

$offtext


$set gdx psd_alldata.gdx


sets
  commodity
  country
  year       
'market year'
  attribute
  unit
;


parameter alldata(commodity,country,year,attribute,unit);

$gdxin  %gdx%
$load   commodity country year attribute unit
$loaddc alldata

Notes:

  • We use the tool csv2gdx to read the csv and produce a raw gdx file (the gdx file will be overwritten with the final version after adding the supporting sets).
  • The projection option is used to extract the sets from the parameter alldata. This is non-intuitive (i.e. ugly) syntax but it is fast.
  • The set year is explicitly declared to make sure the years are ordered. In the csv file the (marketing) years are not ordered. Refinement: in order to make sure the superset of years is not chosen too small, we probably should have used a $loaddc alldata instead of $load alldata.
  • The declaration of the sets look a bit funny in the gdx viewer:
    image
    This is due to the way we had to declare the sets for use with the projection option.