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

10 次查看（过去 30 天）
Saray
Saray 2023-5-22
评论： Cris LaPierre 2023-5-24
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.

请先登录，再回答此问题。

回答（3 个）

FannoFlow
FannoFlow 2023-5-22
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
  2 个评论
FannoFlow
FannoFlow 2023-5-22
编辑：FannoFlow 2023-5-22
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 2023-5-23
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

请先登录，再进行评论。

Star Strider
Star Strider 2023-5-22
Ran in:
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 个评论
FannoFlow
FannoFlow 2023-5-22
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");

请先登录，再进行评论。

Cris LaPierre
Cris LaPierre 2023-5-22
Ran in:
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 个评论
显示 3更早的评论
Saray
Saray 2023-5-24
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
.
.
.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by