MATLAB: How to custom arrange a timetable

timetable

I have four excel tables that have values for each hour for each day in the following format:
                 Hour 1          Hour 2          Hour 3          Hour 4 
  Date 1         
  Date 2         
  Date 3         
  Date 4
I want to have 2 columns with Date and the Value. So I basically need 8760 values in that format! I tried writing a code but I was messing it up. I tried manually doing that in excel and I don't think that is possible. I would love to get a headstart on how to get this. TIA

Best Answer

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.
Related Question