How to process xlsx with readtable for graphs/plots

2 views (last 30 days)
Rudolf
Rudolf on 4 May 2021
Commented: Star Strider on 5 May 2021
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

Sign in to answer this question.

Accepted Answer

Star Strider
Star Strider on 5 May 2021
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 Comments
Rudolf
Rudolf on 5 May 2021
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 on 5 May 2021
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
.

Sign in to comment.

More Answers (0)

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!