Remove successive rows from a table where a specific column value is duplicated

2 views (last 30 days)
Tessa Kol
Tessa Kol on 17 Nov 2021
Answered: Peter Perkins on 24 Nov 2021
Dear all,
I have the following csv-file (see attachement). If I take a sample set from this dataset as an example here:
time NLDOR00110B0512_STAT_02_N_Nt_1202
'9-4-2021 00:33' 1
'9-4-2021 00:38' 1
'9-4-2021 00:43' 2
'9-4-2021 00:43' 2 ---> to be removed
'9-4-2021 00:44' 3
'9-4-2021 00:45' 2
'9-4-2021 00:46' 1
'9-4-2021 00:48' 1
'9-4-2021 00:53' 1
...
'9-4-2021 03:03' 1
'9-4-2021 03:08' 1
'9-4-2021 03:13' 1
'9-4-2021 03:18' 1
'9-4-2021 03:21' 2
'9-4-2021 03:22' 3
'9-4-2021 03:23' 3 ---> to be removed
'9-4-2021 03:23' 2
'9-4-2021 03:25' 1
'9-4-2021 03:28' 1
I want to remove the successive rows from a table where a specific column value is duplicated which are values 2 and 3. I would like to keep allf of the duplicates of the value 1.
I tried the following code, but without success
RawData = readtable('...Metingen-data-2021-11-17 10_48_18.csv'); % Import file from folder location
idx = diff([inf;findgroups(RawData.NLDOR00110B0512_STAT_02_N_Nt_1202)])~=0;
RawData = RawData(idx,:);

Accepted Answer

Jon
Jon on 17 Nov 2021
If I am understanding correctly I think this will do what you are asking
T = readtable('Metingen-data-2021-11-17 10_48_18.csv');
ijmp = [0;diff(T.Var2)~=0];
isdup = ~ijmp & T.Var2~=1;
Tnew = T(~isdup,:);
  2 Comments

Sign in to comment.

More Answers (1)

Peter Perkins
Peter Perkins on 24 Nov 2021
rowfun would be one way to get Jon's suggestion of using unique to work:
>> t = readtable("Metingen-data-2021-11-17 10_48_18.csv");
[variable names warning removed]
>> t.Properties.VariableNames = ["Time" "X"];
>> t2 = rowfun(@myFun,t,"InputVariables",["Time" "X"],"GroupingVariables","X","OutputVariableNames","Time")
t2 =
36511×3 table
X GroupCount Time
_ __________ ___________________
0 17 2021-04-30 13:09:46
0 17 2021-07-27 14:40:25
0 17 2021-07-27 14:44:01
0 17 2021-07-27 14:49:01
0 17 2021-07-27 14:54:01
0 17 2021-07-27 14:59:01
0 17 2021-07-27 15:04:01
0 17 2021-07-27 15:09:01
0 17 2021-07-27 15:14:01
0 17 2021-07-27 15:19:01
0 17 2021-07-27 15:24:01
0 17 2021-07-27 15:29:01
[snip]
where myFun is
function t = myFun(t,x)
if x ~= 1
t = unique(t);
end
end

Products


Release

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by