Articles Comments

The Rose Bush » 23andMe Guide » 23andMe — Best Way to Download Your Ancestry Composition Data!

23andMe — Best Way to Download Your Ancestry Composition Data!

Ancestry Composition

As you no doubt know, 23andMe‘s Ancestry Composition tool is interesting and cute but doesn’t give exact end points. There’s been some discussion on the 23andMe boards about downloading Firebug and using it to get your data. That is fine as far as it goes, but I don’t like all the other add ons that Firebug tries to get you to download so I’ve been using this method. I’m sorry I didn’t post about it earlier, sometimes I think everyone knows these things. My bad. You must be logged in to do all this.

Getting Your Data

Instead of downloading another program, simply go to the Ancestry Composition page, right click anywhere in the main area and select ‘View Page Source’ The Ancestry Composition page is now found through the drop down at the top, “My Results” and is right under the header “Ancestry Overview”.

Right Click Menu Showing View Page Source Highlighted

Right Click Menu Showing View Page Source Highlighted

On the window that pops up, scroll down until you find the section with your name and your matches names below it.

Page Source from Ancestry Composition

Page Source from Ancestry Composition

I’ve blanked out the IDs of my shares but you’ll be able to see the IDs of your shares there. Copy just the ID of the person you want to get the exact Ancestry Composition segment ends of. The ID will look like this: 2556cd51c49d1c1a with different numbers and letters of course (that’s mine).

Copy the below text and paste it into your regular browser address bar.

https://www.23andme.com/you/ancestry/composition/fetch/?profile_id=2556cd51c49d1c1a&threshold=0.51

Switch everything between the profile_id= and the ampersand (&) to the profile ID you want to look at.  Hit enter.

You will get the following page (although sometimes it’s just one long line at the top of the page):

Ancestry Composition Info Broken Down

Ancestry Composition Info Broken Down

 

Copy and paste the info on this page to excel. As you can see, you can also change the threshold at that page to 0.75 for standard or 0.90 for conservative. You might be able to use Download-Them-All to get all your relatives, I didn’t check. If someone does check, please let me know if it works or not and I’ll update here to let everyone know.

Working in Excel

I used the ‘Text-to-Columns‘ feature of Excel — found under the Data tab — to separate the information by clicking delimited and “Next.” Check off ONLY other, enter a c in the box next to other and click finish.

I then copied the whole row, and pasted transposed (the icon is two boxes overlapping at a corner) in cell A3.

Text to Columns in Excel

Text to Columns in Excel

I then copied the whole row, and pasted transposed (the icon is two boxes overlapping at a corner) in cell A3, deleted row 1, and did a find and replace — press the alt button and the e button together, release both and push the f button, highlight the replace tab — replacing hr with chr to restore the chr verbiage. You can further clean the data by replacing all the [[ and ]] with single brackets and , ” (note there’s a space between the comma and endquote mark)  {  } and ” with nothing. You end up with the following:

Cleaned up Ancestry Composition Data from 23andMe

Cleaned up Ancestry Composition Data from 23andMe

In addition, you will want to search for ‘hap’ throughout the document. Where it says “hap1″, there will be a country, such as “scandinavian” before it. This is the title for the chromosome info that follows it. What I do is insert two columns before column A. Then find all for hap1, then cut and paste the country into column A and hap1 to column B. When I’m done, I go back and find all hap2 instances paste hap2 in column B. I then fill down the countries and hap1 or hap 2. At this point, you can sort by column B and separate your chromosomes. You can go further and highlight only hap1 lines and sort by column C to assemble your Ancestry in segment order. Keep in mind that, unless you have phased with a parent, hap1 and hap2 are not separated and could randomly be either parent.

 

Happy ethnicity hunting!

Credit: 23andMe

Share

Written by

Filed under: 23andMe Guide · Tags:

6 Responses to "23andMe — Best Way to Download Your Ancestry Composition Data!"

  1. Karin Corbeil says:

    There may be an easier way to get this info into an Excel spreadsheet.

    Go to http://www.DNAGedcom.com Register first if you are not already a user. Then go the the 23andMe tab, then “Download 23andMe data” from the drop down menu. Put your log in and password into the appropriate boxes. Choose one or all the files you want. Family Inheritance: Advanced; Ancestry Composition; and/or Ancestry Finder. Give it time to process and a zip file will be generated for you to download with the files you have chosen.

    The Ancestry Composition file will contain all your start/end information for each chromosome.

    If you have a large number of shares at 23andMe, choose only one file at a time to download. These can take a longer time to download.

    Karin Corbeil

    1. admin says:

      Thanks Karin. I don’t personally feel comfortable giving my 23andMe password to a third party site and don’t really recommend that. But, folks can make up their own minds.

      1. Karin Corbeil says:

        I certainly understand your concern. We use one-way encryption, so even the administrator of the database cannot know a user’s password.

        Karin

    2. susan covey says:

      Thanks. That worked really well. I am not too afraid of second parties having my password. It is possible to go in and immediately change my 23ME password.

  2. sandy miller says:

    Sorry, but that was about as clear as mud. I was able to view page source, but the only names I saw, other than mine, were my shares.

    1. admin says:

      Hi Sandy,

      I’m sorry you couldn’t follow the directions. I’m not certain how to make them more clear. You can only see the AC of those sharing with you, so that’s right. Then take the following (what’s between but not including these arrows) ->
      https://www.23andme.com/you/ancestry/composition/fetch/?profile_id=2556cd51c49d1c1a&threshold=0.51
      <- and replace the bolded segment with the ID of your match from the page view.

      Copy and paste the whole thing in the address bar (should be a different window or tab) and hit enter.

      Good luck