필터 지우기
필터 지우기

How to custom arrange a timetable

조회 수: 2 (최근 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