Visualising Ancestry DNA matches–Part 4-Updating

This is the fourth part of a series about using a free Excel template, NodeXL Basic, to visualise Ancestry DNA match lists. For previous posts, see the index to the series.

So you’ve made a chart and it’s just the way you want it. The next thing you know you have brand new interesting DNA matches and you’re dying to know how they fit in. Once you get used to it, creating a new graph is quite quick and easy. Or…. you could save a little time and upload more information into the same chart.

How new data is treated

Before you load more information into an existing chart it’s useful to understand what will happen.

Matches

• New matches are appended to the end of the Vertices sheet.
• Data for existing matches is overwritten (where the columns have the same name).
• Columns that were not present before are added.
• Columns that were present before that are not re-imported are unaffected. The existing data will still be there. New matches will have no values in that column.
• Matches who don’t appear in the new data are unaffected – you won’t lose them.

Relationships

• All the relationships in the new file will be added to the end of the list on the Edges sheet.
• This will duplicate existing relationships. Clean up is necessary.

This has some exciting implications for the kinds of things we will be able to do with the charts. I’m dying to get up to the posts where I’ll show them to you! One thing at a time…

Clearing duplicates

To make things easier on your computer, you should clear away duplicate relationship entries (edges) each time you reload data into an existing worksheet:

• On the NodeXL ribbon select Prepare Data, Count and Merge Duplicate Edges
• Clear the Count duplicate edges box
• Tick the Merge duplicate edges box
• Select Vertex 1 and Vertex 2 as the columns that contain duplicates.
  
• Click OK.

If you have a long in-common-with list you should take this step now, even if you haven’t reimported data.

When you first import data, one ‘Vertex’ row is created for each person, and one ‘Edge’ for each relationship. For fourth and closer cousins who match each other, two relationships are recorded: ‘A matches B’ and ‘B matches  A’. Excel’s Remove duplicates button won’t help because it doesn’t recognise these pairs of reciprocal relationships as duplicates. Each duplicated relationship adds a little to the processing load for your computer.

When you clear duplicates, the first duplicate entry starting from the top is kept and any others discarded. Keep this in mind if you use any of the other edge columns – you may want to choose “Vertex 1, Vertex 2 and this column”, or to sort the edges before clean up so that the ones you want to retain are at the top.

Updating data – the process

To update a worksheet with fresh data, use the following steps.

Save a copy first in case something goes wrong.

1. Import new in-common-with file – see Part 2
2. Import new match file – see Part 2
3. Clear duplicates – see above.
4. Recreate groups – see Part 2
5. Autofill columns – see Part 3
   

If the autofill process hasn’t caused your graph to refresh, then Refresh Graph now.

Note: If one or more of your new matches has a very close relationship to you and should be ‘skipped’, add them to the Additional Input file and reload that as well (see Part 2 for a reminder how to load it). Do so any time before step 3 above. Alternatively you can enter ‘Skip’ manually on the Vertices sheet.

Coming up…

I know a lot of people are interested in ways to work with a busy chart – that post will be next, I promise!