Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted

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
Highlighted
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
Highlighted
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.
Highlighted
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

Highlighted

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);
Highlighted
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?

Highlighted
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
Article Labels

    There are no labels assigned to this post.