topic Re: find and drop duplicate rows in Discussions

find and drop duplicate rows

Wed, 08 Apr 2009 20:05:35 GMT

I have 2462 rows by 13 columns. Some of these rows contain duplicate values in 12 of the 13 columns. The column that always contains unique text is called PK.SAMPLE. How can I find these rows of almost exact duplicates and remove them from the data set?
Thanks,
mgm

Re: find and drop duplicate rows

sidewinder — Fri, 10 Apr 2009 14:25:47 GMT

Did the search function not work? Alternatively, you can select the data you want to ignore, right click in the far left column and then choose to exclude the data from your analysis.

Re: find and drop duplicate rows

XanGregg — Wed, 04 Oct 2017 20:03:50 GMT

I'm sure there are many ways to do this depending on depending on the degree of interaction versus automation (scripting) you want.

One interactive way I can think of is the use Tables:Summary and assign the 12 duped variables as grouping variables and no statistic. It will make a new table listing the number of occurrence of each 12-var combination. The new table is linked to the original, so if you select rows in the new table with NRows more that 1, you'll see dupes selected in the original.

Doing with with Big Class on height and weight, for instance, reveals one duplicated combination: height=64, weight=99.

Data Table( "Big Class" ) << Summary( Group( :height, :weight ) )

Re: find and drop duplicate rows

Wed, 04 Oct 2017 20:05:30 GMT

I wrote a script recently to check to make sure I wasn't adding duplicate rows to a data table. Maybe this will help.

The script iterates through all the rows in the table (i counter) and each of my 9 columns (k counter) comparing the values to those in the last row. If it finds any rows that are equal, it will delete the last row.

dt << CurrentDataTable;
TotalRows = NRows(dt);
eqFlag = 1;
deleteFlag = 0;

For(i=1, i<TotalRows,i++,
For(k =1, k <=9,k++,
If(Column(dt,k)sqbrack i sqbrack !=Column(dt,k)[TotalRows], eqFlag=0)
);
If(eqFlag == 1, deleteFlag = 1);
eqFlag = 1;
);

If(deleteFlag==1,dt<<SelectRows(TotalRows); dt<<Delete Rows);

Re: find and drop duplicate rows

bayesfactor — Fri, 10 Apr 2020 04:15:22 GMT

Excel has a one-button "Remove Duplicates"

Pandas has a one-liner drop_duplicates https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.drop_duplicates.html

We really have to write a loop and check every value ourselves?

Re: find and drop duplicate rows

txnelson — Fri, 10 Apr 2020 04:39:53 GMT

There is a function called Select Duplicate Rows() in JSL, or it can be used interactively under the pull down menus

Rows==>Row Selection==>Select Duplicate Rows