How to sum up numbers in time series from .txt file

I have a .txt file with time series matrix like this:

2014,01,29,14,04,0.200
2014,01,29,14,50,0.000
2014,01,29,15,50,0.000
2014,01,29,16,50,0.000
2014,01,29,17,50,0.000
2014,01,29,18,50,0.000
2014,01,29,19,50,0.000
2014,01,29,20,50,0.000
2014,01,29,21,50,0.000
2014,01,29,22,50,0.000
2014,01,29,23,50,0.000
2014,01,30,0,50,0.000
2014,01,30,01,15,0.787
2014,01,30,01,50,0.000
2014,01,30,01,51,0.200
2014,01,30,02,49,0.589
2014,01,30,02,50,0.200
2014,01,30,03,03,0.393
2014,01,30,03,22,7.088
2014,01,30,03,33,6.101
2014,01,30,03,44,10.358
2014,01,30,03,50,2.952
2014,01,30,03,55,1.969
2014,01,30,04,02,0.982
2014,01,30,04,09,0.200

The columns show year, month, day, hour, minute and precipitation rate, respectively. I need hourly precipitation data. I want to get output like this in a .txt file:

2014,01,29,14,0.200
2014,01,29,15,0.000
2014,01,29,16,0.000
...

And I need daily precipitation data in a other .txt file:

2014,01,29,0.200
2014,01,30,31.819
...

Tks for help!!

Answer 1

[Peng Li]

Load it using readtable. And do a splitapply or rowfun.

tbl = readtable('test.txt', 'ReadVariableNames', 0);

tbl.Properties.VariableNames ...
    = {'year', 'month', 'day', 'hour', 'minutes', 'precipationRate'};

[grp, hrTbl] = findgroups(tbl(:, {'year', 'month', 'day', 'hour'}));

hrTbl.preci = splitapply(@mean, tbl.(6), grp);
hrTbl.year  = splitapply(@(x) x(1), tbl.(1), grp);

And writetable it.

writetable(hrTbl, writePath);

Similar for your month and yearly table.

[pink flower]

I can 't get any results.

 hrTbl.year = splitapply(@(x), x(1), tbl.(1), grp);
                             ↑
Error: Expression or statement is incorrect--possibly unbalanced (, {, or [.

[Peng Li]

sorry have a typo before. using cell phone. I updated the answer. should work now.

[pink flower]

It still didn't work. But I got it this way:

tbl = readtable('test.txt');
[grp, hr] = findgroups(tbl.(2),tbl.(3),tbl.(4)); 
hrTbl.Year = splitapply(@(x)[x(1)], tbl.(1), grp);
hrTbl.Month = splitapply(@(x)[x(1)], tbl.(2),grp);
hrTbl.Day = splitapply(@(x)[x(1)], tbl.(3), grp);
hrTbl.Hour = splitapply(@(x)[x(1)], tbl.(4), grp);
hrTbl.Rainfall = splitapply(@nansum, tbl.(6), grp);
T = table(hrTbl.Year, hrTbl.Month, hrTbl.Day, hrTbl.Hour, hrTbl.Rainfall);
writetable(T, 'test_out.txt','Delimiter',' ');

[Peng Li]

strange. It works quite well on my side

tbl = readtable('test.txt', 'ReadVariableNames', 0);
tbl.Properties.VariableNames ...
    = {'year', 'month', 'day', 'hour', 'minutes', 'precipationRate'};
[grp, hrTbl] = findgroups(tbl(:, {'year', 'month', 'day', 'hour'}));
hrTbl.preci = splitapply(@mean, tbl.(6), grp);
>> hrTbl

hrTbl =

  15×5 table

    year    month    day    hour    preci 
    ____    _____    ___    ____    ______

    2014      1      29      14        0.1
    2014      1      29      15          0
    2014      1      29      16          0
    2014      1      29      17          0
    2014      1      29      18          0
    2014      1      29      19          0
    2014      1      29      20          0
    2014      1      29      21          0
    2014      1      29      22          0
    2014      1      29      23          0
    2014      1      30       0          0
    2014      1      30       1      0.329
    2014      1      30       2     0.3945
    2014      1      30       3     4.8102
    2014      1      30       4      0.591

[pink flower]

It worked out to calculate the hourly and daily accumulated! Tks!

[Akira Agata]

+1

Another possible solution is timetable and retime functions, like:

% Read text data file
tbl = readtable('test.txt','ReadVariableNames',false);
tbl.Properties.VariableNames ...
  = {'year', 'month', 'day', 'hour', 'minutes', 'precipationRate'};

% Arrange the data to timetable variable
Time = datetime(tbl.year,tbl.month,tbl.day,tbl.hour,tbl.minutes,0);
TT = timetable(Time,tbl.precipationRate,'VariableNames',{'precipationRate'});

% Calculate hourly mean using retime function
TT_hrAvg = retime(TT,'hourly','mean');

[Peng Li]

Good to know the retime function. Powerful!

[pink flower]

Very good!