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

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,:);

Answer 1

[Jon]

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,:);

[Jon]

Could also use the unique function for this, but would first have to exclude the rows you didn't want to deduplicate and then put the tables back together. I think the above is cleaner.

[Tessa Kol]

Thank you for the answer!

Answer 2

[Peter Perkins]

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