How to custom arrange a timetable

3 visualizzazioni (ultimi 30 giorni)
Srilatha Raghavan
Srilatha Raghavan il 6 Nov 2020
Commentato: Peter Perkins il 19 Nov 2020
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

Accedi per rispondere a questa domanda.

Risposta accettata

dpb
dpb il 7 Nov 2020
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.
  2 Commenti
Srilatha Raghavan
Srilatha Raghavan il 7 Nov 2020
Thanks. Works perfectly!
Peter Perkins
Peter Perkins il 19 Nov 2020
The original question is a perfect application of the stack function: it would, for each date, take the 1x4 set of hourly values and create a 4x1 column of values, turning the Nx4 table into a 4*Nx2 table.
But as dpd demonstrates, that isn't the whole story. I suspect that stack would replace the loop in the middle of dpb's code, and maybe some other lines too.

Accedi per commentare.

Più risposte (0)

Categorie

Scopri di più su Dates and Time in Help Center e File Exchange

Tag

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by