How to custom arrange a timetable

1 回表示 (過去 30 日間)
Srilatha Raghavan
Srilatha Raghavan 2020 年 11 月 6 日
コメント済み: Peter Perkins 2020 年 11 月 19 日
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

サインインしてこの質問に回答する。

採用された回答

dpb
dpb 2020 年 11 月 7 日
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 件のコメント
Srilatha Raghavan
Srilatha Raghavan 2020 年 11 月 7 日
Thanks. Works perfectly!
Peter Perkins
Peter Perkins 2020 年 11 月 19 日
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.

サインインしてコメントする。

その他の回答 (0 件)

カテゴリ

Help Center および File Exchange で Data Type Conversion についてさらに検索

タグ

Community Treasure Hunt

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

Start Hunting!

Translated by