Extracting particular data from excel table

I have a .csv file, which I have imported as a table in matlab. The table has freuency values for each minute of the day for the entire 24 hours. I want to analyse the data by studying each hour separately and plotting a graph for each hour. How can extract such data?

[Dyuman Joshi]

You can reshape your data and do analysis accordingly -

%Sample data, 60*24 minutes in 24 hours
y = 1:60*24;
%After reshaping, each column will correspond to data of each hour
z = reshape(y,60,[])

z = 60×24

1 61 121 181 241 301 361 421 481 541 601 661 721 781 841 901 961 1021 1081 1141 1201 1261 1321 1381 2 62 122 182 242 302 362 422 482 542 602 662 722 782 842 902 962 1022 1082 1142 1202 1262 1322 1382 3 63 123 183 243 303 363 423 483 543 603 663 723 783 843 903 963 1023 1083 1143 1203 1263 1323 1383 4 64 124 184 244 304 364 424 484 544 604 664 724 784 844 904 964 1024 1084 1144 1204 1264 1324 1384 5 65 125 185 245 305 365 425 485 545 605 665 725 785 845 905 965 1025 1085 1145 1205 1265 1325 1385 6 66 126 186 246 306 366 426 486 546 606 666 726 786 846 906 966 1026 1086 1146 1206 1266 1326 1386 7 67 127 187 247 307 367 427 487 547 607 667 727 787 847 907 967 1027 1087 1147 1207 1267 1327 1387 8 68 128 188 248 308 368 428 488 548 608 668 728 788 848 908 968 1028 1088 1148 1208 1268 1328 1388 9 69 129 189 249 309 369 429 489 549 609 669 729 789 849 909 969 1029 1089 1149 1209 1269 1329 1389 10 70 130 190 250 310 370 430 490 550 610 670 730 790 850 910 970 1030 1090 1150 1210 1270 1330 1390

[Indrani]

Thank you.

But how do I do it for the attached csv file?

[Dyuman Joshi]

You can import the data by readcell and then reshape it accordingly to do analysis.

It seems like the data is for every milisecond, so 00:00.0 to 59:59.9 is an hour, and the data from B2-B36001 (i.e. 36000 miliseconds in an hour) is to be analaysed and B36002-B72001, B72002-B108001 and ..., so on.

So you can use

reshape(data,36000,[])

to get the output as data for each hour in each column. Then you can use functions on the 2nd dimension of the array to directly get result.

Also, there seems to be some missing data in the file. As the number of rows is not a multiple of 36000

Answer 1

[Sharad]

Hi,

In order to analyze the data by extracting and plotting the data for each hour, you can follow these steps:

• Load the .csv file (assuming the .csv file is present in the same directory as the script).

table = readtable('2023-01-12.csv');

• Seperate the Time and Value columns.

time = table.Time;
value = table.Value;

• Iterate for each hour, find the set of indices satisfying the value of each hour, and plot each of them in a seperate subplot.

% Repeat for each hour
for h=0:23
    subplot(4, 6, h+1);

    % Extract hour wise data
    idx = find(hour(time)==h);

    % plot each hour data on seperate subplot
    plot(value(idx));
    title(sprintf('Hour %d', h));
end

• The output will look something like this:

[Dyuman Joshi]

Given the amount of data OP has, using find() is going to be incredibly inefficient.

Logical indexing would be a better choice, but a clear better approach is available in the form of vectorization.

[Sharad]

Thanks for the approach

[Indrani]

Thank you!