calculating the average of a column of a csv file with specified steps and saving it in a new csv file

Hi everyone,

I have a csv file (10081x3) which I need to take average of 2nd and 3rd column every 60 steps and save in in the new csv file in column mood not row. It means I will have new csv file of 167x3. My file contains headers and numbers start from second row. I would be happy if some one helps me. Thanks in advance.

Answer 1

[FannoFlow]

read table to read the CSV as a table:

https://www.mathworks.com/help/matlab/ref/readtable.html

use mean to calculate the mean of the 2'nd and 3'rd columns.

write the new table using writetable

https://www.mathworks.com/help/matlab/ref/writetable.html?s_tid=doc_ta

[FannoFlow]

T = readtimetable("Data_1min.csv", VariableNamingRule="preserve", TextType="string");
T.Variables = T{:,:}.erase(",");
T = convertvars(T,T.Properties.VariableNames,"double");
T = retime(T,"regular","mean",TimeStep=minutes(60));
head(T)
writetimetable(T, "Data_1min_edit.csv");

[Saray]

Thanks for your response it works perfectly but I need my new csv file saves with dot instead of comma (which yours is like that) but with 3 decimal number. These are the first 5th results which are saved by cvs file that you wrote:

25296.88

25038.55

24746.32

24572.92

24448.72

but I need they be saved like that:

25.297

25.039

24.746

24.573

24.449

Answer 2

[Star Strider]

This was a bit more involved than I thought it would be —

T1 = readtable('Data_1min.csv', 'VariableNamingRule','preserve')

T1 = 10080×3 table
      Var1      T Box int. Media (C)    T Box ext. Media (C)
    ________    ____________________    ____________________

    00:00:00         {'25,148'}              {'24,520'}     
    00:01:00         {'25,155'}              {'24,521'}     
    00:02:00         {'25,161'}              {'24,515'}     
    00:03:00         {'25,170'}              {'24,530'}     
    00:04:00         {'25,174'}              {'24,566'}     
    00:05:00         {'25,185'}              {'24,533'}     
    00:06:00         {'25,201'}              {'24,619'}     
    00:07:00         {'25,206'}              {'24,693'}     
    00:08:00         {'25,206'}              {'24,632'}     
    00:09:00         {'25,211'}              {'24,529'}     
    00:10:00         {'25,221'}              {'24,515'}     
    00:11:00         {'25,224'}              {'24,570'}     
    00:12:00         {'25,222'}              {'24,563'}     
    00:13:00         {'25,221'}              {'24,486'}     
    00:14:00         {'25,228'}              {'24,567'}     
    00:15:00         {'25,244'}              {'24,535'}     

v23 = cellfun(@str2double, strrep(T1{:,[2 3]},',','.'));
VN = T1.Properties.VariableNames;
T2 = array2table(v23);
T2 = addvars(T2, T1{:,1}, 'before',1);
T2.Properties.VariableNames = VN

T2 = 10080×3 table
      Var1      T Box int. Media (C)    T Box ext. Media (C)
    ________    ____________________    ____________________

    00:00:00           25.148                   24.52       
    00:01:00           25.155                  24.521       
    00:02:00           25.161                  24.515       
    00:03:00            25.17                   24.53       
    00:04:00           25.174                  24.566       
    00:05:00           25.185                  24.533       
    00:06:00           25.201                  24.619       
    00:07:00           25.206                  24.693       
    00:08:00           25.206                  24.632       
    00:09:00           25.211                  24.529       
    00:10:00           25.221                  24.515       
    00:11:00           25.224                   24.57       
    00:12:00           25.222                  24.563       
    00:13:00           25.221                  24.486       
    00:14:00           25.228                  24.567       
    00:15:00           25.244                  24.535       

M2r = reshape(T2{:,2}, 60, []);
MM2r = mean(M2r,1).';
% size(MM2r)

M3r = reshape(T2{:,3}, 60, []);
MM3r = mean(M3r,1).';
Check = [mean(T2{1:60,[2 3]}); mean(T2{61:120,[2 3]})]

Check = 2×2

25.2969 24.6255 25.0385 24.2662

D3 = T1{1:60:end,1};
T3 = table(D3,MM2r,MM3r, 'VariableNames',VN)

T3 = 168×3 table
      Var1      T Box int. Media (C)    T Box ext. Media (C)
    ________    ____________________    ____________________

    00:00:00           25.297                  24.625       
    01:00:00           25.039                  24.266       
    02:00:00           24.746                  23.978       
    03:00:00           24.573                  23.821       
    04:00:00           24.449                  23.721       
    05:00:00           24.346                  23.624       
    06:00:00           24.262                  23.543       
    07:00:00           24.189                  23.474       
    08:00:00           24.126                  23.414       
    09:00:00           24.071                  23.366       
    10:00:00           24.025                  23.321       
    11:00:00           23.981                  23.271       
    12:00:00           23.931                  23.224       
    13:00:00           23.876                  23.154       
    14:00:00           23.816                  23.098       
    15:00:00           23.797                  23.116       

.

[FannoFlow]

you're making it too hard on yourself ;)

T = readtimetable("Data_1min.csv", VariableNamingRule="preserve", TextType="string");
T.Variables = T{:,:}.erase(",");
T = convertvars(T,T.Properties.VariableNames,"double");
T = retime(T,"regular","mean",TimeStep=minutes(60));
head(T)
writetimetable(T, "Data_1min_edit.csv");

Answer 3

[Cris LaPierre]

Here's a simple way to do this in MATLAB

opts = detectImportOptions('Data_1min.csv','VariableNamingRule','preserve');
opts = setvartype(opts,[2 3],'double');
opts = setvaropts(opts,[2 3],'ThousandsSeparator',',');
Data = readtable('Data_1min.csv',opts);

data1min = groupsummary(Data,"Var1","hour","mean",[2 3])

data1min = 168×4 table
         hour_Var1          GroupCount    mean_T Box int. Media (C)    mean_T Box ext. Media (C)
    ____________________    __________    _________________________    _________________________

    [00:00:00, 01:00:00)        60                  25297                        24625          
    [01:00:00, 02:00:00)        60                  25039                        24266          
    [02:00:00, 03:00:00)        60                  24746                        23978          
    [03:00:00, 04:00:00)        60                  24573                        23821          
    [04:00:00, 05:00:00)        60                  24449                        23721          
    [05:00:00, 06:00:00)        60                  24346                        23624          
    [06:00:00, 07:00:00)        60                  24262                        23543          
    [07:00:00, 08:00:00)        60                  24189                        23474          
    [08:00:00, 09:00:00)        60                  24126                        23414          
    [09:00:00, 10:00:00)        60                  24071                        23366          
    [10:00:00, 11:00:00)        60                  24025                        23321          
    [11:00:00, 12:00:00)        60                  23981                        23271          
    [12:00:00, 13:00:00)        60                  23931                        23224          
    [13:00:00, 14:00:00)        60                  23876                        23154          
    [14:00:00, 15:00:00)        60                  23816                        23098          
    [15:00:00, 16:00:00)        60                  23798                        23116          

writetable(data1min(:,[1 3 4]),'Data_hourly.csv')

[FannoFlow]

Nice use of varopts.

I personally dont like to dig in to detectImportOptions too much, so I opted to parse the string after the fact.

But this is a very clean solution!

[Cris LaPierre]

Thanks. Here's perhaps an even cleaner way using readtimetable

opts = detectImportOptions('Data_1min.csv','VariableNamingRule','preserve');
opts = setvartype(opts,[2 3],'double');
opts = setvaropts(opts,[2 3],'ThousandsSeparator',',');

data1min  = readtimetable('Data_1min.csv',opts);
data_hourly = retime(data1min ,"hourly","mean")

data_hourly = 168×2 timetable
      Var1      T Box int. Media (C)    T Box ext. Media (C)
    ________    ____________________    ____________________

    00:00:00           25297                   24625        
    01:00:00           25039                   24266        
    02:00:00           24746                   23978        
    03:00:00           24573                   23821        
    04:00:00           24449                   23721        
    05:00:00           24346                   23624        
    06:00:00           24262                   23543        
    07:00:00           24189                   23474        
    08:00:00           24126                   23414        
    09:00:00           24071                   23366        
    10:00:00           24025                   23321        
    11:00:00           23981                   23271        
    12:00:00           23931                   23224        
    13:00:00           23876                   23154        
    14:00:00           23816                   23098        
    15:00:00           23798                   23116        

writetimetable(data_hourly,'Data_hourly.csv')

[Cris LaPierre]

@Saray I noticed you said the comma is a decimal separator, not a thousands separator. Here is how you could do that.

opts = detectImportOptions('Data_1min.csv','VariableNamingRule','preserve');
opts = setvartype(opts,[2 3],'double');
opts = setvaropts(opts,[2 3],'DecimalSeparator',',');

data1min  = readtimetable('Data_1min.csv',opts);
data_hourly = retime(data1min ,"hourly","mean")

data_hourly = 168×2 timetable
      Var1      T Box int. Media (C)    T Box ext. Media (C)
    ________    ____________________    ____________________

    00:00:00           25.297                  24.625       
    01:00:00           25.039                  24.266       
    02:00:00           24.746                  23.978       
    03:00:00           24.573                  23.821       
    04:00:00           24.449                  23.721       
    05:00:00           24.346                  23.624       
    06:00:00           24.262                  23.543       
    07:00:00           24.189                  23.474       
    08:00:00           24.126                  23.414       
    09:00:00           24.071                  23.366       
    10:00:00           24.025                  23.321       
    11:00:00           23.981                  23.271       
    12:00:00           23.931                  23.224       
    13:00:00           23.876                  23.154       
    14:00:00           23.816                  23.098       
    15:00:00           23.797                  23.116       

writetimetable(data_hourly,'Data_hourly.csv')

[Saray]

Thank you alot, the code is working perfectly but if I just want 3 numbers after decimal while numbers that are saving are like:

25.2968833333333

25.03855

24.7463166666667

.

.

.

[Cris LaPierre]

See this answer: https://www.mathworks.com/matlabcentral/answers/626518-precision-in-writetable