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

23 views (last 30 days)
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.

Answers (3)

FannoFlow
FannoFlow on 22 May 2023
read table to read the CSV as a table:
use mean to calculate the mean of the 2'nd and 3'rd columns.
write the new table using writetable
  2 Comments
FannoFlow
FannoFlow on 22 May 2023
Edited: FannoFlow on 22 May 2023
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
Saray on 23 May 2023
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

Sign in to comment.


Star Strider
Star Strider on 22 May 2023
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
.
  1 Comment
FannoFlow
FannoFlow on 22 May 2023
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");

Sign in to comment.


Cris LaPierre
Cris LaPierre on 22 May 2023
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')
  5 Comments
Saray
Saray on 24 May 2023
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
.
.
.

Sign in to comment.

Categories

Find more on Environment and Settings in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!