How can I read a .csv file into Matlab in which each column corresponds to a date?

user_12921500 0
Viewed 274

Original Raw Data

I have a .csv file which looks like this

Type,    Name,      2/18, 2/19, 2/20, 2/21
country, Australia, -3,  -6,   -3,   -1
country, Canada,    -5,   0,   -3,   -2
country, Germany,   11,   4,    3,    5
country, Ireland,    2,   0,    4,    1
state,   Alberta,    2,  10,    2,    1

and would like to read it into Matlab. You can see that the dates are comma separated across, and so each column is effectively a date in this original file.

Ideal Result

I would like the result to be a Matlab table that looks like the following:

    date          Australia   Canada  Germany  Ireland  Alberta
    __________    _________   ______  _______  _______  _______

    18/Feb/2020   -3          -5      11        2        2
    19/Feb/2020   -6           0       4        0       10
    20/Feb/2020   -3          -3       3        4        2
    21/Feb/2020   -1          -2       5        1        1

so that I can access for example all of Germany's data using my_table.Germany and my_table.date.

Current Situtaion

However, if I try to read the csv file using the readtable() function, I obtain the following:

    Type         Name           x2_18    x2_19    x2_20    x2_21
    _________    ___________    _____    _____    _____    _____

    "country"    "Australia"    -3       -6       -3       -1   
    "country"    "Canada"       -5        0       -3       -2   
    "country"    "Germany"      11        4        3        5   
    "country"    "Ireland"       2        0        4        1   
    "state"      "Alberta"       2       10        2        1

So I have a couple of problems:

1) I would like each column to correspond to a country/state name, not a day. The table somehow needs to be transposed.

2) The dates are being read in and assigned as the variable names of each column. They are in the format x2_18 for example, but I would like them to be datetime objects, so that I can change the format to 18th Feb 2020, for example.

How can I solve these problems?

Thank you!

(My Matlab version is R2017a)

EDIT ---------------------------------------

Max's answer pretty much solved my problem, except that delimitedTextImportOptions() was not available in my Matlab version. As workaround, I decided to read the first line only using fgetl() and use this to create the date vector. Finally, I read the rest of the file using readtable(), and build up the table manually as Max suggested. The code that worked for me is the following, which produced exactly the "Ideal Result" above:

fid = fopen(my_filename);
first_line = fgetl(fid);
Date = datetime(strsplit(first_line, ','),'InputFormat','MM/dd')';
fclose(fid);
Date([1 2]) = [];

opts = detectImportOptions(my_filename);
temp_table = readtable(filename,opts);
country = temp_table{:,2};

date_table = table(Date,'VariableNames',{'Date'});
data_matrix = temp_table{:,3:end}.';

data_table = array2table(data_matrix,'VariableNames', country);
data_full = [date_table,data_table];
import csv matlab
1 Answers

It's a bit tricky to rotate the content of the file... this cannot be done with the normal import functions. So I read in everything first (as a cell) and build the input that you wanted in MATLAB. Have a look:

opts = delimitedTextImportOptions('NumVariables',6,'DataLines',1);
DatTbl = readtable('myfile.csv',opts);

% extract dates
Times = datetime(DatTbl{1,3:end},'InputFormat','MM/dd');
% extract country names
country = DatTbl{2:end,2};
% extract information
Info = str2double(DatTbl{2:end,3:end});

%% build table
% standard table
Tbl = [table(Times.'),array2table(Info.','VariableNames',country)]
% timetable
TimTbl = array2timetable(Info.','VariableNames',country,'RowTimes',Times)

You can choose if you want to have a standard table, or a timetable (introduces R2016b, so it should work for you =) ). It should work with more countries and more dates. The transposition is done while building the tables Info.' with array2table/array2timetable, which are required to split the matrix into individual columns of the tables.

user_124701960
Related