Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- find and drop duplicate rows

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 8, 2009 1:05 PM
(6002 views)

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

Thanks,

mgm

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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