Need help with deleting rows in my table
4 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Simen Bydal
am 22 Mär. 2019
Kommentiert: Guillaume
am 22 Mär. 2019
Hi,
We are working on a project where we have importated dailyreturn (column A) and dailyreturnindex (column B) as well as dailyreturntime2 (this is a date on the format 732316 found in column C). But the data includes non-trading days such as Norwegian holidays and we want to remove these. We therefore want to make a code with a rule such that if both dailyreturn(t) = dailyreturn(t-1) and dailyreturnindex(t) = dailyreturnindex(t-1) holds at the same time we want to delete that particular row.
Could someone please help me? I have attatched the code I have tried to write, but the deleting process is not working as I hoped.
Thanks in advance.
%% Read excel
dailyreturn = xlsread('storfil.xlsx','KOG','D4:D3692');
dailyreturnindex= xlsread('DailyOSEBXOSEAX.xlsx','OSEAX Index','D8:D3695');
dailyreturntime = xlsread('DailyOSEBXOSEAX.xlsx','OSEAX Index','A8:A3695');
dailyreturntime2 = dailyreturntime + 693960;
%%Convert to table
table_dailyreturn =array2table(dailyreturn);
table_dailyreturnindex =array2table(dailyreturnindex);
table_dailyreturntime = array2table(dailyreturntime);
table_dailyreturntime2 = array2table(dailyreturntime2);
merged_table=[table_dailyreturn table_dailyreturnindex table_dailyreturntime2];
array=table2array(merged_table);
%%delete non-trading days (see rule in description)
array = array(any(diff(array,1),2),:)
0 Kommentare
Akzeptierte Antwort
Guillaume
am 22 Mär. 2019
Why are you reading the data as arrays, converting to table, then converting back to array. Choose one type and stick to it rather than wasting time on unnecessary conversions.
Personally, I'd use tables, so:
dailyreturn = readtable('storfil.xlsx', 'Sheet', 'KOG', 'Range', 'D4:D3692', 'ReadVariableNames', false);
dailyreturnindex= readtable('DailyOSEBXOSEAX.xlsx', 'Sheet', 'OSEAX Index', 'Range', 'D8:D3695', 'ReadVariableNames', false);
dailyreturntime = readtable('DailyOSEBXOSEAX.xlsx', Sheet', 'OSEAX Index','Range', 'A8:A3695', 'ReadVariableNames', false);
I'm assuming your columns don't have header above (hence the 'ReadVariableNames', false).
I'm not sure why you're adding 693960 to the time. If you're using readtable matlab will automatically convert excel times into datetime.
You can then merge the 3 columns and manipulate the table directly.
dailyreturn.Properties.VariableNames = {'Value'}; %make sure names of columns are not the same in each table
dailyreturnindex.Properties.VariableNames = {'Index'};
dailyreturntime.Properties.VariableNames = {'Time'};
returns = [dailureturntime, dailyreturn, dailyreturnindex];
You could even convert the table to a timetable if you need to resample according to time. In any case, there's certainly no need to convert to an array.
If I understood correctly what you want:
noholsreturns = returns([true; diff(returns.Value) ~= 0 & diff(returns.Index) ~= 0], :)
%isbusday requires financial toolbox
%for norwegian holidays you would have to first create a holiday file with createholidays
noholsreturns = returns(isbusday(returns.Time), :);
4 Kommentare
Guillaume
am 22 Mär. 2019
1st one: keep the values if the difference in the first column is not 0 or the difference in the 2nd column is not 0
2nd one: discard values if the difference in the first column is 0 and the difference in the 2nd column is 0. invert that to keep values.
In boolean language:
Weitere Antworten (0)
Siehe auch
Kategorien
Mehr zu Data Type Conversion 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!