2

I am stuck trying to run an economic model using MATLAB - at the data importing part. For most of my code I'm using a freeware toolbox called IRIS.

I have quarterly dataset with 14 variables and 160 datapoints. Essentially the dataset is a 15X161 matrix- including the dates(col1) and variable names(B1:O1).

The command used for uploading data on IRIS is

d = dbload('filename.csv')

but this isn't working. Although MATLAB is creating a 1X1 array called d and creating fields under it (one for each variable). All cells display NaN - not a number.

Why is this happening?

I checked the tutorials on the IRIS toolbox website and tried running and loading a sample dataset from there using this command, but it leads to the same problem. Everywhere I checked- including MATLAB help, this seems to be the correct command to use when using IRIS, but somehow it isn't working.

I also tried uploading the data directly using MATLAB functions and not IRIS. The command I'm using is:

 d = dataset('XLSFile','filename.xls','ReadVarNames', true).

Although this is working, and I can see all the variable names, but MATLAB can't read the dates. I tried xlsread and importdata as well, but they don't read the variable names. Is there any way for me to upload the entire Excel sheet with the variable names and dates?

It would be best if I could get the IRIS command to work, since the rest of my code would be compatible with that.

The dataset looks somewhat like this..

HO_GDP     HO_CPI  HO_CPI  HO_RS  HO_ER  HO_POIL....
 4/1/1970  82.33    85.01  55.00  99.87  08.77
 7/1/1970  54.22     8.98  25.22  95.11  91.77
10/1/1970  85.41    85.00  85.22  95.34  55.00
 1/1/1971  85.99   899      8.89  85.1
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
S1234
  • 23
  • 1
  • 4
  • can you post part of the actual CSV data (headers plus one or two rows). Basically I am trying to see what delimiter the file has (commas I suspect), what is the type of each column. I can see from the above that the first column is a date, while the rest is numeric data. Is this the case for all your columns? I should say that I have no experience with [IRIS](http://code.google.com/p/iris-toolbox-project/). I've never used it before – Amro Jul 25 '12 at 21:48
  • The data above is from the actual CSV itself. And you are correct, the first column is a date, remaining 14 are numeric values. The first row is the header that consists of values such as HO_GDP, HO_CPI etc. After that is the actual data as has been shown above. The data goes upto 5 decimal places at times. – S1234 Jul 25 '12 at 23:04

1 Answers1

0

You can use the TEXTSCAN function to read the CSV file in MATLAB:

%# some options
numCols = 15;       %# number of columns
opts = {'Delimiter',',', 'MultipleDelimsAsOne',true, 'CollectOutput',true};

%# open file for reading
fid = fopen('filename.csv','rt');

%# read header line
headers = textscan(fid, repmat('%s',1,numCols), 1, opts{:});

%# read rest of data rows
%# 1st column as string, the other 14 as floating point
data = textscan(fid, ['%s' repmat('%f',1,numCols-1)], opts{:});

%# close file
fclose(fid);

%# collect data
headers = headers{1};
data = [datenum(data{1},'mm/dd/yyyy') data{2}];

The result for the above sample you posted (assuming values are comma-separated):

>> headers
headers = 
    'HO_GDP'    'HO_CPI'    'HO_CPI'    'HO_RS'    'HO_ER'    'HO_POIL'
>> data
data =
   7.1962e+05        82.33        85.01           55        99.87         8.77
   7.1971e+05        54.22         8.98        25.22        95.11        91.77
    7.198e+05        85.41           85        85.22        95.34           55
   7.1989e+05        85.99          899         8.89         85.1            0

Note how in the last line of the code we convert the date column to serial date number, so that we can store the entire data in one numeric matrix. You can always go back to string representation of dates using DATESTR function:

>> datestr(data(:,1))
ans =
01-Apr-1970
01-Jul-1970
01-Oct-1970
01-Jan-1971
Amro
  • 123,847
  • 25
  • 243
  • 454
  • I tried running your code but its throwing up an error: Error using datenum (line 179) DATENUM failed. Caused by: Error using dtstr2dtnummx Failed on converting date string to date... number. I managed to upload the excel file using this command: d=dataset('XLSFile','filename.xls','ReadVarNames',true) But,the data coloumn is coming in an excel text format. How can I convert this col back to a date format? Thank you for your help! – S1234 Jul 26 '12 at 06:45
  • @S1234: The code above works fine for the data sample you posted. Now it is very difficult for me to guess what the problem is with seeing your actual file... If it doesn't contain any sensitive data, consider uploading your file online so we can have a look at it (think Dropbox or any other similar site) – Amro Jul 26 '12 at 16:16