Visualising Ancestry DNA matches-Part 2-Loading files the first time

This post is part two of a series.

In the first post I showed you the files and software you can use to visualise Ancestry DNA matches. Today we’re going to load  the match and in-common-with files you downloaded using the DNAGedcom client, and have our first look at a graph.

Getting set up and loading the files is not difficult, but there are a lot of steps to follow and details to note. I’ve suggested some check points at which you should save your progress.  If you miss a detail you won’t have to start from the beginning. Just reopen the file and resume from the last save point.

The first time you try this, give yourself at least forty five minutes at a time when you feel ready to concentrate.

It’s much quicker when you get used to it. The entire process described below takes me less than five minutes.

Thank you to my husband and to Aillin O’Brien who tested these instructions and provided invaluable feedback.

An index to this series of posts is available here.

Preparation: Set up a Spreadsheet for Additional Input

There’s one final step of preparation before we load the data.

If we load the information as it is, the chart will show connections between the test taker (I’ll call that person “you”) and every one of their matches. All you will see is a mass of dots. It’s also likely to tie up your computer while it thinks about all those lines it has to draw. I’ve made this mistake more than once... The graph appears eventually, but it isn’t very useful.

This will also occur when a direct line relative who can be expected to share a substantial number of matches with you from across your tree has also tested - a sibling, parent, child or grandchild.

The most efficient way I have found to get around this is to load in a small additional spreadsheet. We can also use the new spreadsheet to add other information, but we’ll get to that later.

• Open Excel and create a new workbook:  File – New – Blank Workbook.
  
  

In the first row of your new workbook, type in the following column headings:

• matchID
• Match name
• Match admin
• Vertex 2
• Name
• Vertex Type
• Edge Type
• Visibility
• Comment

We need to enter one line of information in this table for each person with a large number of matches.

Under matchid you will enter the test ID number that was assigned to that person’s test by Ancestry. For your own test you can the URL when you go to your DNA page on Ancestry. It will look something like this – you need the part marked red:

https://www.ancestry.com.au/dna/insights/AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE

• Copy and paste your test ID number under both matchid and Vertex 2. 
• Type the word “Skip” under Visibility.             <<< Don’t miss this step!

That’s all that’s strictly necessary for it to work, but a little extra information will remind you what this line is for later:

• Put your name in three columns: Match name, Match admin and Name.
• comment is for reminders to yourself. Put whatever you like there. I added a short explanatory note about what this line does.

Repeat the process for each close relative (sibling, parent, child or grandchild) who has DNA tested.

This time, use your own id as the matchid, and your close relative’s id as Vertex 2.

If you’re not the administrator for the test, you can find their match ID on your DNA match page. The red part is your ID, and the blue part is your relative.

https://www.ancestry.com.au/dna/tests/AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE
/match/VVVVVVVV-WWWW-XXXX-YYYY-ZZZZZZZZZZZZ

Alternatively you can look up the match ID numbers in the matches files.

Save the file somewhere you will find it again. I’ll call this file the Additional Input file from now on.

OK, we’re all set. Now we create a NodeXL file and load the information in.

Create a NodeXL Workbook

The method required to use the template may vary with your version of Excel.
I have an Office 365 subscription. I select File – New – PERSONAL – NodeXLGraph

If this method doesn’t work for you, try searching for “NodeXL” in the Windows “Search programs and files” field or equivalent on your system, and double click the “NodeXL Template” file returned.

A new spreadsheet will open. It may check for template updates as it opens, and you will need to wait for 20 seconds for the splash screen to close. Once it does, your screen should look something like this:

A new ribbon called NodeXL Basic has appeared. It won’t be there when you open a normal file, it will only appear when you are using the special files created with the template. Click on the new ribbon and take a look. This is where most of the action will take place.

Load your files

Open your match list (m_yourname.csv), in-common-with list (icw_yourname.csv), and additional input file in the normal way then return to the new NodeXL sheet. With each load we have to tell the template which fields in the file hold information about people (‘vertices’) and relationships (‘edges’). I’ll tell you what to put in at each stage.

Important step before loading the first time:

On the NodeXL ribbon, click the Import button. It’s at the far left hand side.

• Choose Import Options… (the bottom item on the menu).
• Clear the box next to “Clear the NodeXL workbook before data is imported”. There should be no tick in the box.

Why: Otherwise, no matter how many files you load only the last one loaded will be in the spreadsheet. We need all three files – matches, in-common-with, and additional input – to go in.

Save the file now that you’ve adjusted the setting.

In-common-with file

• Select Import from the NodeXL ribbon and choose From Open Workbook…

• Select the in-common-with file (icw_yourname.csv) in the top box of the dialog that appears
• Leave “Columns have headers” checked
• Tick the boxes for “match id” and “icwid” under Is Edge Column. No other boxes should be ticked
• Confirm that “matchid” is selected in the  “Which edge column is Vertex 1?” dropdown
• Under “Which edge column is Vertex 2?” choose “icwid”
• Click import (say OK to the message about text wrapping if you get it.)

Check the import

Navigate to the Edges worksheet using the tabs at the bottom left of the screen.

You should see lots of ID numbers in the “Vertex 1” and “Vertex 2” columns.

The ID numbers will overlap each other and the other columns. That doesn’t matter. You should not see any other data entered in the sheet at this stage.

If the import looks correct, save your progress and carry on.

Matches file

• Open the import dialog again:
  

• Click on the matches file in the top box (m_yourname.csv)
• Set “testid” and “matchid” as edge columns (tick boxes). No other boxes in that column should be ticked.
• Under “Is Vertex 2 Property Column” check the boxes for:
  “name”,
  “admin”
  “SharedCM”,
  “note” and
  “matchurl”   (you’ll need to scroll all the way to the bottom to find this).
• Choose “testid” in the dropdown box under “Which edge column is Vertex 1”
• Choose “matchid” for “Which edge column is Vertex 2”.
• Click import (say OK to the message about text wrapping if you get it.)

Check the import

Navigate to the Vertices worksheet.

Check that the first column “Vertex 1” contains ID numbers. You should not see any names or other information in that column.

Scroll right and check that the “names”, “admin”, “shared CM”, “note” and “matchurl” columns have appeared and have information in them. You may need to scroll right to see them.

If this looks right, save you progress and continue.

Additional Input file

Important: Don’t forget to load this file!

• Open the import dialog again
  

• Click on the Additional Input file in the top box
• Set “matchid” and “Vertex 2” as edge columns (no other boxes should be ticked)
• Scroll down and tick “Name” and “Visibility” under Is Vertex 2 Property Column    <<< Don’t miss this step
• Choose “matchid” in the dropdown box under “Which edge column is Vertex 1”
• Choose “Vertex 2” in the dropdown box under “Which edge column is Vertex 2”
• Click import (and clear the text wrapping message if it appears)

Check the import

Move to the Vertices worksheet again and find the row with your own name (Control-F will bring up a search box).

Confirm that the word “Skip” is in the Visibility column.

If this looks right, save again and move to the next step.

Now make a chart!

Find the toolbar in the chart area, and click Show Graph.

Troubleshooting: If it takes more than a few seconds, there was probably a problem with the additional input file. When Excel has finished drawing thousands of dots, go back and check the Additional Input file instructions again and make sure you’ve loaded it. If it was missing and you’ve fixed it, click Refresh Graph which will have appeared where you found Show Graph before.

Don’t be disappointed if your chart looks like the image below (and it probably will). It will get better with a few tweaks.

Identify groups

On the NodeXL ribbon, find the Groups button. Click it and select “Group by connected component” from the option list.

This option works well for me, but if you have a lot of very interconnected matches you might find that one of the choices under “Group by Cluster” works better.

Refresh Graph will add the newly created grouping information to the chart. Your chart will become more colourful but no more tidy. Just one more step, and you’ll have something more interesting to look at.

Separate groups in the chart

You can access layout options from both the NodeXL Ribbon, and the chart area. Click on the dropdown in either location and select Layout Options from the dropdown menu.

Change the “Layout Style” option to “Lay out each of the graph’s groups in its own box” and click OK.

Click Lay Out Again to apply that change to the chart. You didn’t need to refresh the graph a second time because the data itself didn’t change, only the layout instructions.

This is how mine looks now. Each dot represents a person I have a DNA match with. Each line represents a relationship between two of my matches.

Move back to the Vertices worksheet and see what happens when you click on the chart dots.

That’s plenty for today. Don’t forget to save your file!

Go and get yourself a nice cup of tea (or whatever beverage you prefer) knowing that if you’ve made it this far you can definitely manage the next steps I have in mind.