How to process xlsx with readtable for graphs/plots

조회 수: 2 (최근 30일)
Rudolf
Rudolf 2021년 5월 4일
댓글: Star Strider 2021년 5월 5일
New to readtable and to get what i want out of an xlsx file, need some tips.
Need to make various graphs/plot from values located in column 14 in the file attached, and i need to sort out these values on month and hour. For example to have the possibility to get all values for the hour 00-01 during February. Or to find mean for each of the 24 hours for June.
Sorry for poor explaining. Thanks for any tips.
data = readtable('prices2019.xlsx');
% Sort by month
% and by the 24 hours

이 질문에 답변하려면 로그인하십시오.

채택된 답변

Star Strider
Star Strider 2021년 5월 5일
Ran in:
One approach is to use groupsummary to aggregate the data —
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/607625/prices2019.xlsx', 'VariableNamingRule','preserve')
T1 = 8761×32 table
Var1 Hours SYS SE1 SE2 SE3 SE4 FI DK1 DK2 Oslo Kr.sand Bergen Molde Tr.heim Tromsø EE EE_1 LV LV_1 LT LT_1 AT AT_1 BE BE_1 DE-LU DE-LU_1 FR FR_1 NL NL_1 ___________ ___________ ______ ______ ______ ______ ______ ______ _______ _______ ______ _______ ______ ______ _______ ______ ___ ______ ___ ______ ___ ______ ___ ____ ___ ____ _____ _______ ___ ____ ___ ____ 01-Jan-2019 {'00 - 01'} 443.21 281.74 281.74 281.74 281.74 281.74 281.74 281.74 485.19 485.19 485.19 451.26 451.26 451.26 NaN 281.74 NaN 281.74 NaN 281.74 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'01 - 02'} 438.43 100.18 100.18 100.18 100.18 100.18 100.18 100.18 489.96 489.96 489.96 450.77 450.77 450.77 NaN 100.18 NaN 100.18 NaN 100.18 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'02 - 03'} 409.08 99.78 99.78 99.78 99.78 99.78 -40.59 -40.59 489.17 489.17 489.17 451.36 451.36 451.36 NaN 99.78 NaN 99.78 NaN 99.78 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'03 - 04'} 386.8 45.37 45.37 45.37 45.37 45.37 -98.59 -98.59 481.21 481.21 481.21 449.77 449.77 449.77 NaN 45.37 NaN 45.37 NaN 45.37 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'04 - 05'} 355.16 48.05 48.05 48.05 48.05 48.05 -73.72 -73.72 469.47 469.47 469.47 446.69 446.69 446.69 NaN 48.05 NaN 48.05 NaN 48.05 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'05 - 06'} 349.09 80.48 80.48 80.48 80.48 80.48 -124.85 -124.85 471.26 471.26 471.26 449.27 449.27 449.27 NaN 80.48 NaN 80.48 NaN 80.48 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'06 - 07'} 358.84 254.08 254.08 254.08 254.08 254.08 -171.61 -171.61 479.12 479.12 479.12 459.32 459.32 459.32 NaN 254.08 NaN 254.08 NaN 254.08 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'07 - 08'} 409.48 389.58 389.58 389.58 389.58 389.58 -149.92 -149.92 488.37 488.37 488.37 458.63 458.63 458.63 NaN 389.58 NaN 389.58 NaN 389.58 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'08 - 09'} 418.53 374.66 374.66 374.66 374.66 374.66 -49.05 -49.05 473.85 473.85 473.85 455.14 455.14 455.14 NaN 374.66 NaN 374.66 NaN 374.66 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'09 - 10'} 409.28 214.09 214.09 214.09 214.09 214.09 -62.97 -62.97 477.43 477.43 477.43 457.73 457.73 457.73 NaN 214.09 NaN 214.09 NaN 214.09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'10 - 11'} 431.57 281.05 281.05 281.05 281.05 281.05 -49.05 -49.05 489.47 489.47 489.47 466.78 466.78 466.78 NaN 281.05 NaN 281.05 NaN 281.05 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'11 - 12'} 434.35 191.91 191.91 191.91 191.91 191.91 4.48 4.48 491.36 491.36 491.36 471.16 471.16 471.16 NaN 191.91 NaN 191.91 NaN 191.91 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'12 - 13'} 428.58 192.21 192.21 192.21 192.21 192.21 1.19 1.19 489.47 489.47 489.47 473.05 473.05 473.05 NaN 192.21 NaN 192.21 NaN 192.21 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'13 - 14'} 409.58 161.27 161.27 161.27 161.27 161.27 -0.2 -0.2 488.77 488.77 488.77 474.64 474.64 474.64 NaN 161.27 NaN 161.27 NaN 161.27 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'14 - 15'} 408.19 213.59 213.59 213.59 213.59 213.59 0 0 480.81 480.81 480.81 475.54 475.54 475.54 NaN 213.59 NaN 213.59 NaN 213.59 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'15 - 16'} 427.88 325.22 325.22 325.22 325.22 325.22 -0.3 -0.3 482 482 482 478.32 478.32 478.32 NaN 325.22 NaN 325.22 NaN 325.22 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
FirstTenRows = T1(1:10,[1 2 14])
FirstTenRows = 10×3 table
Var1 Hours Molde ___________ ___________ ______ 01-Jan-2019 {'00 - 01'} 451.26 01-Jan-2019 {'01 - 02'} 450.77 01-Jan-2019 {'02 - 03'} 451.36 01-Jan-2019 {'03 - 04'} 449.77 01-Jan-2019 {'04 - 05'} 446.69 01-Jan-2019 {'05 - 06'} 449.27 01-Jan-2019 {'06 - 07'} 459.32 01-Jan-2019 {'07 - 08'} 458.63 01-Jan-2019 {'08 - 09'} 455.14 01-Jan-2019 {'09 - 10'} 457.73
T2 = [table(T1.Var1+hours(1:size(T1,1)).'), T1(:,14)] % Create New Table With Usable Hour Values
T2 = 8761×2 table
Var1 Molde ____________________ ______ 01-Jan-2019 01:00:00 451.26 01-Jan-2019 02:00:00 450.77 01-Jan-2019 03:00:00 451.36 01-Jan-2019 04:00:00 449.77 01-Jan-2019 05:00:00 446.69 01-Jan-2019 06:00:00 449.27 01-Jan-2019 07:00:00 459.32 01-Jan-2019 08:00:00 458.63 01-Jan-2019 09:00:00 455.14 01-Jan-2019 10:00:00 457.73 01-Jan-2019 11:00:00 466.78 01-Jan-2019 12:00:00 471.16 01-Jan-2019 13:00:00 473.05 01-Jan-2019 14:00:00 474.64 01-Jan-2019 15:00:00 475.54 01-Jan-2019 16:00:00 478.32
Month = groupsummary(T2, 'Var1', 'month', 'mean', 'Molde')
Month = 24×3 table
month_Var1 GroupCount mean_Molde __________ __________ __________ Jan-2019 383 492.81 Feb-2019 336 533.18 Mar-2019 361 482.58 Apr-2019 360 406.39 May-2019 383 418.18 Jun-2019 360 376.77 Jul-2019 361 408.82 Aug-2019 383 383.87 Sep-2019 360 402.37 Oct-2019 361 352.16 Nov-2019 360 238.03 Dec-2019 383 263.84 Jan-2020 361 312.19 Feb-2020 359 366.76 Mar-2020 361 368.77 Apr-2020 360 359.79
Hour = groupsummary(T2, 'Var1', 'hourofday', 'mean', 'Molde')
Hour = 24×3 table
hourofday_Var1 GroupCount mean_Molde ______________ __________ __________ 0 365 364.96 1 366 357.12 2 365 347.8 3 365 341.68 4 365 337.9 5 365 338.36 6 365 352.8 7 365 373.73 8 365 389.82 9 365 400.83 10 365 402.18 11 365 400.22 12 365 396.83 13 365 393.1 14 365 389.71 15 365 386.94
Day = groupsummary(T2, 'Var1', 'dayofyear', 'mean', 'Molde')
Day = 365×3 table
dayofyear_Var1 GroupCount mean_Molde ______________ __________ __________ 1 24 456.8 2 24 266.01 3 24 492.26 4 24 283.53 5 24 504.59 6 24 283.74 7 24 496.61 8 24 283.14 9 24 491.29 10 24 276.13 11 24 492.46 12 24 292.23 13 24 495.35 14 24 306.02 15 24 479.92 16 24 327.15
Since these aggregation options are limited, another option would be to convert it to a timetable (table2timetable) and use retime to aggregate them.
.
  댓글 수: 2
Rudolf
Rudolf 2021년 5월 5일
Great answer, thank you very much! Guess i should rather look into timetable and retime then. Also because i see that this readtable functions are doing something wrong, values are incorrect. If looking at T2 i see that every 24th row there is one day alone on that spot. Hopefully picture explains:
Same on Month we see that GroupCount says 383 hours during January, but it should have been 744.
Star Strider
Star Strider 2021년 5월 5일
Ran in:
As always, my pleasure!
Thank you!
I ran this code in the online application, so I did not originally see that, since it only shows a limited number of rows. I trusted that the MATLAB date functions (usually reliable) would parse that correctly. They did not, and unfortunately, that problem propagates through the table. I will submit a bug report to MathWorks about it.
However, I did come up with a fix for it!
That fix is —
hours(rem(0:size(T1,1)-1,24)).')
I liooked at three consecutive day transitions in comparison with the original table (displayed at the end of this Comment). They all appear to work correctly (different sections are vertically concatenated, so it is necessary to look at them closely), so I assume the others do as well.
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/607625/prices2019.xlsx', 'VariableNamingRule','preserve');
FirstTenRows = T1(1:10,:)
FirstTenRows = 10×32 table
Var1 Hours SYS SE1 SE2 SE3 SE4 FI DK1 DK2 Oslo Kr.sand Bergen Molde Tr.heim Tromsø EE EE_1 LV LV_1 LT LT_1 AT AT_1 BE BE_1 DE-LU DE-LU_1 FR FR_1 NL NL_1 ___________ ___________ ______ ______ ______ ______ ______ ______ _______ _______ ______ _______ ______ ______ _______ ______ ___ ______ ___ ______ ___ ______ ___ ____ ___ ____ _____ _______ ___ ____ ___ ____ 01-Jan-2019 {'00 - 01'} 443.21 281.74 281.74 281.74 281.74 281.74 281.74 281.74 485.19 485.19 485.19 451.26 451.26 451.26 NaN 281.74 NaN 281.74 NaN 281.74 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'01 - 02'} 438.43 100.18 100.18 100.18 100.18 100.18 100.18 100.18 489.96 489.96 489.96 450.77 450.77 450.77 NaN 100.18 NaN 100.18 NaN 100.18 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'02 - 03'} 409.08 99.78 99.78 99.78 99.78 99.78 -40.59 -40.59 489.17 489.17 489.17 451.36 451.36 451.36 NaN 99.78 NaN 99.78 NaN 99.78 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'03 - 04'} 386.8 45.37 45.37 45.37 45.37 45.37 -98.59 -98.59 481.21 481.21 481.21 449.77 449.77 449.77 NaN 45.37 NaN 45.37 NaN 45.37 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'04 - 05'} 355.16 48.05 48.05 48.05 48.05 48.05 -73.72 -73.72 469.47 469.47 469.47 446.69 446.69 446.69 NaN 48.05 NaN 48.05 NaN 48.05 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'05 - 06'} 349.09 80.48 80.48 80.48 80.48 80.48 -124.85 -124.85 471.26 471.26 471.26 449.27 449.27 449.27 NaN 80.48 NaN 80.48 NaN 80.48 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'06 - 07'} 358.84 254.08 254.08 254.08 254.08 254.08 -171.61 -171.61 479.12 479.12 479.12 459.32 459.32 459.32 NaN 254.08 NaN 254.08 NaN 254.08 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'07 - 08'} 409.48 389.58 389.58 389.58 389.58 389.58 -149.92 -149.92 488.37 488.37 488.37 458.63 458.63 458.63 NaN 389.58 NaN 389.58 NaN 389.58 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'08 - 09'} 418.53 374.66 374.66 374.66 374.66 374.66 -49.05 -49.05 473.85 473.85 473.85 455.14 455.14 455.14 NaN 374.66 NaN 374.66 NaN 374.66 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 01-Jan-2019 {'09 - 10'} 409.28 214.09 214.09 214.09 214.09 214.09 -62.97 -62.97 477.43 477.43 477.43 457.73 457.73 457.73 NaN 214.09 NaN 214.09 NaN 214.09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
T2 = [table(T1.Var1+hours(rem(0:size(T1,1)-1,24)).'), T1(:,14)]
T2 = 8761×2 table
Var1 Molde ____________________ ______ 01-Jan-2019 00:00:00 451.26 01-Jan-2019 01:00:00 450.77 01-Jan-2019 02:00:00 451.36 01-Jan-2019 03:00:00 449.77 01-Jan-2019 04:00:00 446.69 01-Jan-2019 05:00:00 449.27 01-Jan-2019 06:00:00 459.32 01-Jan-2019 07:00:00 458.63 01-Jan-2019 08:00:00 455.14 01-Jan-2019 09:00:00 457.73 01-Jan-2019 10:00:00 466.78 01-Jan-2019 11:00:00 471.16 01-Jan-2019 12:00:00 473.05 01-Jan-2019 13:00:00 474.64 01-Jan-2019 14:00:00 475.54 01-Jan-2019 15:00:00 478.32
T1_Rows22_27 = [T1(22:27,[1 2 14]); T1(45:50,[1 2 14]); ; T1(70:75,[1 2 14])] % Reference
T1_Rows22_27 = 18×3 table
Var1 Hours Molde ___________ ___________ ______ 01-Jan-2019 {'21 - 22'} 473.25 01-Jan-2019 {'22 - 23'} 472.35 01-Jan-2019 {'23 - 00'} 469.67 02-Jan-2019 {'00 - 01'} 467.42 02-Jan-2019 {'01 - 02'} 468.02 02-Jan-2019 {'02 - 03'} 466.23 02-Jan-2019 {'20 - 21'} 534.63 02-Jan-2019 {'21 - 22'} 523.21 02-Jan-2019 {'22 - 23'} 517.06 02-Jan-2019 {'23 - 00'} 503.85 03-Jan-2019 {'00 - 01'} 493.39 03-Jan-2019 {'01 - 02'} 484.37 03-Jan-2019 {'21 - 22'} 535.69 03-Jan-2019 {'22 - 23'} 516.08 03-Jan-2019 {'23 - 00'} 495.17 04-Jan-2019 {'00 - 01'} 495.69
T2_Rows22_27 = [T2(22:27,:); T2(46:51,:); T2(70:75,:)] % Check
T2_Rows22_27 = 18×2 table
Var1 Molde ____________________ ______ 01-Jan-2019 21:00:00 473.25 01-Jan-2019 22:00:00 472.35 01-Jan-2019 23:00:00 469.67 02-Jan-2019 00:00:00 467.42 02-Jan-2019 01:00:00 468.02 02-Jan-2019 02:00:00 466.23 02-Jan-2019 21:00:00 523.21 02-Jan-2019 22:00:00 517.06 02-Jan-2019 23:00:00 503.85 03-Jan-2019 00:00:00 493.39 03-Jan-2019 01:00:00 484.37 03-Jan-2019 02:00:00 478.63 03-Jan-2019 21:00:00 535.69 03-Jan-2019 22:00:00 516.08 03-Jan-2019 23:00:00 495.17 04-Jan-2019 00:00:00 495.69
.

댓글을 달려면 로그인하십시오.

추가 답변 (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