using readtable on a csv file where some rows have partially missing data

Hi,

I'm trying to use 'readtable' to read in this csv file, which is weather station data:

https://dd.weather.gc.ca/climate/observations/hourly/csv/MB/climate_hourly_MB_5060600_2000_P1H.csv

I download the file locally, then run this

currFileName = '~/climate_hourly_MB_5060600_2000_P1H.csv';

currRecords = readtable(currFileName);

This reads in a 8738 x 3 table. It should be an 8738 x 28 table. I think gets confused at line 36 of the data where there's a big chunk of missing data for all columns after the location and time info.

Is there a way of cleanly reading this in? I tried giving 'readtable' a 'delimitedTextImportOptions' object, but couldn't get the parameters for the object right and it kept returning the 8738 x 3 table.

Thanks!

Answer 1

[Dyuman Joshi]

Specify comma as the delimiter -

T = readtable('climate_hourly_MB_5060600_2000_P1H.csv', 'delimiter', ',')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T = 8784×28 table
    Longitude_x_    Latitude_y_      StationName      ClimateID        Date_Time_LST_       Year    Month    Day    Time_LST_    Temp__C_    TempFlag    DewPointTemp__C_    DewPointTempFlag    RelHum___    RelHumFlag    WindDir_10sDeg_    WindDirFlag    WindSpd_km_h_    WindSpdFlag    Visibility_km_    VisibilityFlag    StnPress_kPa_    StnPressFlag    Hmdx    HmdxFlag    WindChill    WindChillFlag        Weather     
    ____________    ___________    _______________    __________    ____________________    ____    _____    ___    _________    ________    ________    ________________    ________________    _________    __________    _______________    ___________    _____________    ___________    ______________    ______________    _____________    ____________    ____    ________    _________    _____________    ________________

       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 00:00:00    2000      1       1     {'00:00'}     -32.6        NaN            -39.1             {0×0 char}           52       {0×0 char}          29               NaN             24              NaN             24.1              NaN             100.64            NaN         NaN       NaN          -48            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 01:00:00    2000      1       1     {'01:00'}       -33        NaN            -39.4             {0×0 char}           53       {0×0 char}          29               NaN             20              NaN             24.1              NaN             100.67            NaN         NaN       NaN          -47            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 02:00:00    2000      1       1     {'02:00'}     -33.4        NaN            -39.9             {0×0 char}           52       {0×0 char}          28               NaN             20              NaN             24.1              NaN             100.74            NaN         NaN       NaN          -47            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 03:00:00    2000      1       1     {'03:00'}     -33.8        NaN              NaN             {'M'     }          NaN       {'M'     }          29               NaN             20              NaN             24.1              NaN             100.77            NaN         NaN       NaN          -48            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 04:00:00    2000      1       1     {'04:00'}     -34.3        NaN            -37.7             {'E'     }           71       {0×0 char}          28               NaN             19              NaN             24.1              NaN             100.77            NaN         NaN       NaN          -48            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 05:00:00    2000      1       1     {'05:00'}     -34.6        NaN              -38             {'E'     }           71       {0×0 char}          28               NaN             19              NaN             24.1              NaN             100.81            NaN         NaN       NaN          -49            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 06:00:00    2000      1       1     {'06:00'}     -35.3        NaN            -38.8             {'E'     }           70       {0×0 char}          28               NaN             19              NaN             24.1              NaN             100.84            NaN         NaN       NaN          -49            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 07:00:00    2000      1       1     {'07:00'}       -36        NaN            -39.5             {'E'     }           70       {0×0 char}          28               NaN             19              NaN             24.1              NaN             100.84            NaN         NaN       NaN          -50            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 08:00:00    2000      1       1     {'08:00'}     -36.2        NaN            -39.8             {'E'     }           70       {0×0 char}          28               NaN             20              NaN             24.1              NaN             100.84            NaN         NaN       NaN          -51            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 09:00:00    2000      1       1     {'09:00'}     -36.3        NaN            -39.9             {'E'     }           70       {0×0 char}          28               NaN             22              NaN             24.1              NaN             100.88            NaN         NaN       NaN          -52            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 10:00:00    2000      1       1     {'10:00'}     -36.3        NaN            -39.9             {'E'     }           70       {0×0 char}          27               NaN             19              NaN             24.1              NaN             100.88            NaN         NaN       NaN          -51            NaN         {'Ice Crystals'}
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 11:00:00    2000      1       1     {'11:00'}     -36.1        NaN            -39.7             {'E'     }           70       {0×0 char}          27               NaN             22              NaN             24.1              NaN             100.91            NaN         NaN       NaN          -51            NaN         {'Clear'       }
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 12:00:00    2000      1       1     {'12:00'}     -35.5        NaN              -39             {'E'     }           70       {0×0 char}          27               NaN             20              NaN             24.1              NaN             100.88            NaN         NaN       NaN          -50            NaN         {'Clear'       }
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 13:00:00    2000      1       1     {'13:00'}     -35.1        NaN            -38.6             {'E'     }           70       {0×0 char}          26               NaN             20              NaN             24.1              NaN             100.91            NaN         NaN       NaN          -50            NaN         {'Clear'       }
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 14:00:00    2000      1       1     {'14:00'}     -34.9        NaN            -38.4             {'E'     }           71       {0×0 char}          27               NaN             20              NaN             24.1              NaN             100.94            NaN         NaN       NaN          -49            NaN         {'Clear'       }
       -94.07          58.74       {'CHURCHILL A'}    5.0606e+06    01-Jan-2000 15:00:00    2000      1       1     {'15:00'}     -34.9        NaN            -38.4             {'E'     }           71       {0×0 char}          27               NaN             24              NaN             24.1              NaN             100.98            NaN         NaN       NaN          -50            NaN         {'Clear'       }

[Dyuman Joshi]

For some reason, readtable() detects the delimiter for this file as colon, ':', which occurs in the 2 date-time data columns, thus it partitions into 3 columns initially.

Weird.

[Robert]

Should have figured that out, worked great, thank you!