cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

find and drop duplicate rows

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
1 ACCEPTED SOLUTION

Accepted Solutions
XanGregg
Staff

Re: find and drop duplicate rows

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 ) )

View solution in original post

5 REPLIES 5
sidewinder
Level I

Re: find and drop duplicate rows

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.
XanGregg
Staff

Re: find and drop duplicate rows

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

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);
bayesfactor
Level III

Re: find and drop duplicate rows

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?

txnelson
Super User

Re: find and drop duplicate rows

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

     

Jim