MATLAB: Cell Dates to Timestamp conversion

cell arraysdateMATLABtimetimestamp

I have a 3480×2 cell. The first column shows the date (6/24/2015) and the second one shows the hour (8:33:49). How can I create a new array where the first column will appear as a UNIX timestamp?

For example, C(1,1)=1435134829 (which is equal to 6/24/2015 – 8:33:49).

Best Answer

In MATLAB R2014b or later, using datetime:

>> c = {'6/24/2015' '8:33:49'; '6/24/2015' '8:33:48'}
c = 
    '6/24/2015'    '8:33:49'
    '6/24/2015'    '8:33:48'
>> d = datetime(strcat(c(:,1),{' '},c(:,2)))
d = 
   24-Jun-2015 08:33:49
   24-Jun-2015 08:33:48
>> format long g
>> posixtime(d)
ans =
                1435134829
                1435134828

Hope this helps.

Related Solutions

MATLAB: Convert “Jul_26_2016__11-26-37_AM” to Date Vector

According to the datevec documentation (did you read it ?) you can simply put either AM or PM in the token position to read the AM/PM info:

>> datevec('Jul_26_2016__11-26-37_AM','mmm_dd_yyyy__HH-MM-SS_AM') % AM
ans =
        2016           7          26          11          26          37
>> datevec('Jul_26_2016__11-26-37_PM','mmm_dd_yyyy__HH-MM-SS_AM') % PM
ans =
        2016           7          26          23          26          37

MATLAB: How to custom arrange a timetable

Well, that isn't all that is in the files....

>> tRaw=readtable('2019_Jan-Mar_DA_LMPs.csv');
>> head(tRaw)
ans =
  8×28 table
    MARKET_DAY          NODE            TYPE       VALUE      HE1      HE2      HE3      HE4      HE5      HE6      HE7      HE8      HE9     HE10     HE11     HE12     HE13     HE14     HE15     HE16     HE17     HE18     HE19     HE20     HE21     HE22     HE23     HE24 
    __________    _________________    _______    _______    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____
    01/01/2019    {'ARKANSAS.HUB' }    {'Hub'}    {'LMP'}    22.54    22.25    22.03    22.03    20.98    20.81    21.39    22.57    23.18    24.04     25.1    25.79    25.39    24.96    24.58     24.3    24.87    29.72    32.43    30.38    29.71    27.39    27.07    25.51
    01/01/2019    {'ILLINOIS.HUB' }    {'Hub'}    {'LMP'}    19.46    19.32    19.45    19.52    19.75    20.07       21    21.91    21.75    23.25    24.08    24.67    24.37    24.02    23.53    23.32    24.59     29.9     33.1    31.05    30.21    27.63    25.89    24.31
    01/01/2019    {'INDIANA.HUB'  }    {'Hub'}    {'LMP'}    21.23    20.82     20.6    20.63    20.65    21.22     22.2    23.29    23.12    24.25       25    25.57    25.21    24.85    24.34    24.06    25.44    31.39    34.76    32.63    31.46     28.7    26.77    25.36
    01/01/2019    {'LOUISIANA.HUB'}    {'Hub'}    {'LMP'}    24.03    23.45    23.59    23.13    22.38    22.12    22.17    23.66    24.39    25.24    26.62    27.43     27.1    26.67     26.2    25.78    26.47    31.57    33.99    31.78    31.72    28.85    28.64    26.94
    01/01/2019    {'MICHIGAN.HUB' }    {'Hub'}    {'LMP'}    21.52    20.86    20.62     20.7    20.77    21.26    22.21    24.31    24.14    24.77    26.15    25.91    25.62    25.26    24.74    24.58    25.86     32.6    36.14    33.94    31.79    29.08    27.22    25.92
    01/01/2019    {'MINN.HUB'     }    {'Hub'}    {'LMP'}    22.11    20.62    21.11    20.03    20.48    20.29    20.44    21.02    21.03    21.22    21.78    22.31    22.95    22.77    22.35    22.57    23.55    28.78    34.51    31.03     30.5    27.55     24.8    23.68
    01/01/2019    {'MS.HUB'       }    {'Hub'}    {'LMP'}    20.74    20.26    20.22    20.17    20.36    20.87    21.88     22.6    22.42    24.11    24.77     25.1    24.87    24.54    23.94    23.73     25.1    31.56     34.1    31.81    31.53    28.47    26.04    24.74
    01/01/2019    {'TEXAS.HUB'    }    {'Hub'}    {'LMP'}     25.2    24.82     24.7    24.39    22.99    22.44    22.38    23.79     24.8    25.64    27.48    27.67    27.28    26.76    26.41    25.98     26.3    31.34    33.62    31.25    30.69    28.28    29.06    27.43
>> tail(tRaw)
ans =
  8×28 table
    MARKET_DAY          NODE            TYPE       VALUE      HE1      HE2      HE3      HE4      HE5      HE6      HE7      HE8      HE9     HE10     HE11     HE12     HE13     HE14     HE15     HE16     HE17     HE18     HE19     HE20     HE21     HE22     HE23     HE24 
    __________    _________________    _______    _______    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____
    03/31/2019    {'ARKANSAS.HUB' }    {'Hub'}    {'LMP'}     22.4    22.73    22.67    22.75    22.87    23.52    25.36    27.48    29.56    30.52    28.24       26    24.87    23.52    23.04    22.26    22.77    24.99    27.14    32.65    29.89     26.8    25.31    24.01
    03/31/2019    {'ILLINOIS.HUB' }    {'Hub'}    {'LMP'}    21.53    22.11    22.69    22.74    23.05    23.39    25.86    27.41    29.25    29.69    27.12    25.61    24.43    23.25    22.58    22.14    22.73    25.22    27.71    31.53    29.23    26.86    25.34    23.95
    03/31/2019    {'INDIANA.HUB'  }    {'Hub'}    {'LMP'}    22.49     23.2    23.76    23.68       24     24.5    27.07    28.98     30.6    31.52    29.31    27.07    25.82     24.3    23.88    22.81    23.43    26.17    28.93    33.15    31.45    27.79    26.29    24.89
    03/31/2019    {'LOUISIANA.HUB'}    {'Hub'}    {'LMP'}    22.68    22.72    24.13    24.03    24.01       24     26.1    28.49    30.77    31.47    29.71    27.09    26.28    26.36    24.97    25.44    25.49    25.81    29.42       38    37.45    29.06     26.4    24.52
    03/31/2019    {'MICHIGAN.HUB' }    {'Hub'}    {'LMP'}    22.95    22.84    23.73     23.6    23.93    24.25    27.23    29.39    31.36    31.99    29.58    27.36    26.02    24.35    24.11     23.1    23.76    26.28    29.48    34.01    32.45    28.13    25.99    24.67
    03/31/2019    {'MINN.HUB'     }    {'Hub'}    {'LMP'}    20.61    20.91    20.63    20.71    20.83    23.09    24.66    25.45    28.21    27.28    26.82       24    23.09    22.81    20.99    22.13    22.56     23.8    25.26     30.5    25.28    25.38    24.49    23.16
    03/31/2019    {'MS.HUB'       }    {'Hub'}    {'LMP'}    21.01    22.33    22.16    22.19    22.53    23.37    25.72    27.86    30.08    30.72    28.78    26.13    24.88    22.97     22.9    21.58    22.23    25.22    27.13    32.11    29.33    26.71    25.42    24.11
    03/31/2019    {'TEXAS.HUB'    }    {'Hub'}    {'LMP'}    22.54    23.04    23.38     23.7    23.87     24.4    26.46    28.98    30.67    32.23     29.7    27.46    26.44    25.68    25.11    24.78    25.09    26.01    29.52    35.71    34.36     28.6    26.77    24.69
>> unique(tRaw.NODE)
ans =
  8×1 cell array
    {'ARKANSAS.HUB' }
    {'ILLINOIS.HUB' }
    {'INDIANA.HUB'  }
    {'LOUISIANA.HUB'}
    {'MICHIGAN.HUB' }
    {'MINN.HUB'     }
    {'MS.HUB'       }
    {'TEXAS.HUB'    }
>> height(tRaw)
ans =
   720
>> height(tRaw)/8
ans =
    90
>> unique(tRaw.TYPE)
ans =
  1×1 cell array
    {'Hub'}
>> unique(tRaw.VALUE)
ans =
  1×1 cell array
    {'LMP'}
>>

There are eight sets of data for each date -- outline to proceed from above.

uNodes=unique(tRaw.NODE);
nNodes=numel(uNodes);
data=[];
for i=1:nNodes:height(tRaw)
  data=[data;tRaw{i:i+nNodes-1,5:end}.'];
end
t=tRaw.MARKET_DAY(1)+hours(0:height(tRaw)/nNodes*24-1).';
tData=[table(t) array2table(data)];
tData.Properties.VariableNames=[tRaw.Properties.VariableNames(1) uNodes.'];
tData.MARKET_DAY.Format='MM/dd/yy HH';

results in

>> [head(tData);tail(tData)]
ans =
  16×9 table
    MARKET_DAY     ARKANSAS.HUB    ILLINOIS.HUB    INDIANA.HUB    LOUISIANA.HUB    MICHIGAN.HUB    MINN.HUB    MS.HUB    TEXAS.HUB
    ___________    ____________    ____________    ___________    _____________    ____________    ________    ______    _________
    01/01/19 00       22.54           19.46           21.23           24.03           21.52         22.11      20.74       25.20  
    01/01/19 01       22.25           19.32           20.82           23.45           20.86         20.62      20.26       24.82  
    01/01/19 02       22.03           19.45           20.60           23.59           20.62         21.11      20.22       24.70  
    01/01/19 03       22.03           19.52           20.63           23.13           20.70         20.03      20.17       24.39  
    01/01/19 04       20.98           19.75           20.65           22.38           20.77         20.48      20.36       22.99  
    01/01/19 05       20.81           20.07           21.22           22.12           21.26         20.29      20.87       22.44  
    01/01/19 06       21.39           21.00           22.20           22.17           22.21         20.44      21.88       22.38  
    01/01/19 07       22.57           21.91           23.29           23.66           24.31         21.02      22.60       23.79  
    03/31/19 16       22.77           22.73           23.43           25.49           23.76         22.56      22.23       25.09  
    03/31/19 17       24.99           25.22           26.17           25.81           26.28         23.80      25.22       26.01  
    03/31/19 18       27.14           27.71           28.93           29.42           29.48         25.26      27.13       29.52  
    03/31/19 19       32.65           31.53           33.15           38.00           34.01         30.50      32.11       35.71  
    03/31/19 20       29.89           29.23           31.45           37.45           32.45         25.28      29.33       34.36  
    03/31/19 21       26.80           26.86           27.79           29.06           28.13         25.38      26.71       28.60  
    03/31/19 22       25.31           25.34           26.29           26.40           25.99         24.49      25.42       26.77  
    03/31/19 23       24.01           23.95           24.89           24.52           24.67         23.16      24.11       24.69  
>>

Above in an outer loop to process the remainder of the files in sequence will let you build the full year of data.

It would be simpler to build in sequence if you renamed the files to use ordered dates written in year-month sequence so would sort in order. But, failing that, build the table in whatever order the files are processed and then sort() the end result by date.

Best Answer

Related Solutions

MATLAB: Convert “Jul_26_20​16__11-26-​37_AM” to Date Vector

MATLAB: How to custom arrange a timetable

Related Question

MATLAB: Convert “Jul_26_2016__11-26-37_AM” to Date Vector