Subscribe Bookmark RSS Feed

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

Joined:

Jun 23, 2011

Solution
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
sidewinder

Community Trekker

Joined:

Jun 23, 2011

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

Joined:

Jun 23, 2011

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