Find Median of the second column when first column is Date
1 Ansicht (letzte 30 Tage)
Ältere Kommentare anzeigen
Rawan Aloula
am 28 Sep. 2016
Kommentiert: Rawan Aloula
am 2 Okt. 2016
I have a an Excel file of two columns, First column is date in this format (mm/dd/yy), Second column is observations (number).
I have multiple observations each day and I want to find the Median of these observations for each day separately and save it an Excel file of two columns, first is the day, second is the result (Median).
I start doing this in Excel, but I realized its going to take me forever since I have a large amount of data.
How can I do this in Matlab ?
4 Kommentare
Guillaume
am 28 Sep. 2016
For several versions now, matlab has had readtable which is a lot more powerful than xlsread.
For slightly fewer versions, matlab has had findgroups and splitapply which are easier to use than accumarray (but more or less do the same).
Akzeptierte Antwort
Guillaume
am 28 Sep. 2016
In excel, you would be able to do this easily with a pivot table if only pivot tables supported calculating the median (you can get the sum, mean, standard deviation, and a few others, but not the median!).
In matlab, you'd read your excel file with readtable, then use findgroups and splitapply to Split Data into Groups and Calculate Statistics. Finally writetable back into excel.
Something like:
t = readtable('C:\somewhere\yourfile.xlsx');
[group, dates] = findgroups(t.NameOfDateColumn);
medobvservation = splitapply(@median, t.NameOfObservationColumn, group);
writetable(table(dates, medobvservation), 'C:\somewhere\newfile.xlsx');
Weitere Antworten (1)
Andrei Bobrov
am 28 Sep. 2016
if your MATLAB older than R2015b
[n,tt] = xlsread('2008 Data (find daily Median).xlsx');
[y,m,d] = datevec(n(:,1));
[a,b,c] = unique([y,m,d],'rows');
z = accumarray(c,n(:,2),[],@median);
out = [tt;cellstr(datestr(n(b,1),'mm.dd.yyyy')),num2cell(z)];
xlswrite('new_file.xlsx',out);
0 Kommentare
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!