Sibling Patterns in an Excel Stacked Bar Chart<\/h4>\n
Today I was getting a headache trying to find a way to put the paternal patterns information into Excel in a Bar Chart. Here is the best I could do for the first 2 Chromosomes:<\/p>\n
The spreadsheet data format is above. I chose Stacked Bar Chart. Then I had to transpose the row and columns. The slight glitch is that I had to create an extra duplicate pattern when that occurred to get the results in one bar per chromosome. I used the end point for each pattern. The bar assumes the start is at zero for each chromosome which isn’t totally accurate, but close enough, I suppose for a bar chart. The bar chart is meant to represent all the paternal changes in patterns for me and my 3 siblings.<\/p>\n When I check the change in patterns to the number of crossovers in the work of M MacNeill, it appears that I have missed a pattern change on each of the above crossovers. Hopefully, I will find them as I go through the process and re-check my work. I guess I’m batting 2 out of 3 now.<\/p>\n It is possible that they aren’t missing at all. Perhaps in all the work I did to represent the information in an Excel Chart above, I misrepresented the work I had done. Here is my spreadsheet for Chromosome 1:<\/p>\n Here is what M MacNeill has for Joel, Heidi and Sharon’s paternal Starts and Stops on Chromosome 1:<\/p>\n It looks like MacNeill has 6 paternal starts and stops. I don’t count the last one as that goes to the end of the Chromosome. Again, I run into the conversion issue between my Build 37 and MacNeill’s Build 36 work. Here is what happen when I put the approximate crossover locations side by side:<\/p>\n This shows that we both have 6 crossover, which is good. It gets a bit confusing. Note that I had to add a crossover at my position 23289397. That is because there was a gap. That is the gap where the 4 siblings must match the same paternal grandparent. Normally, there shouldn’t be any gap between the Stop from one pattern and the Start of the next one. So it turns out I’m doing better than I thought. That is encouraging to know. For the last pattern, I don’t have an entry, because the crossover is at the same spot as the Stop of the previous pattern.<\/p>\n However, I am comparing my 4 sibling work to MacNeill’s 3 sibling. Also MacNeill had a start of 742429 and mine was higher. That means that there must be a pattern between 742,429 and 1,062,638. I checked, and there aren’t many extra locations there. I suppose I did as well as I could do. I do wonder where Jon’s Chromosome 1 crossovers are, though. Perhaps he has a double crossover with another sibling or one that is very close in location to another sibling.<\/p>\n Here is how my 3 tested siblings and I look compared to each other at Gedmatch in the browser:<\/p>\n The lines don’t match up perfectly, but I have for 3 crossovers for Sharon in a row. I am J, my brother Jon is F and only has one crossover of his own. These are the combined paternal\/maternal crossovers. When I map it out using a visual method, it appears that Jon may have no recombination in Chromosome 1.<\/p>\n If that is true, it would make him a good candidate for finding Frazer or Lentz relatives at Chromosome 1.<\/p>\n Assigning crossovers is getting a little ahead of myself, but I would like to see if I am on the right track.<\/p>\n Here the Dad4Pattern represents Joel, Sharon, Heidi and Jon. There appears to be a logic to assigning these crossovers that I have in the XSib column. The first crossover I have going to Sharon. That is between the first Stop and the second Start. Sharon’s B in ABAA goes to an A in the AAAA\u00a0homozygous region. That means all siblings match the same paternal grandparent in this region. The next crossover goes to me as I’m represented by the A in the ABBB pattern. The 3 other siblings remain matched to each other. Then Heidi gets the next crossover as she goes from matching Sharon and Jon to matching me. The next to the last crossover, I had as Jon. But it has to be Heidi as she went from matching me and Sharon to matching Jon. If Jon was the one that changed to matching Heidi, the pattern would have gone to AAAA. Likewise, the last crossover I had as Heidi, but it has to be Joel. I went from matching Sharon to matching Heidi and Jon.<\/p>\n There are a few cross checks to the method. One is to check to see what MacNeill has done. Another way is to check to see known matches. I noticed above that Jon had matches to known matches on my paternal grandmother on either side of the wrong crossover that I had assigned to him, so that was likely not a good crossover. Another note is that there is a least one other homozygous region. That is between the 191M stop and 192M start above. That means that there should be an AAAA pattern stuck in there, but it is not necessary to know at this stage.<\/p>\n A lot of the above work was to make sure that I had the right number of crossovers in the right places. I was worried that if I didn’t, that I wouldn’t be applying the right rules to the right areas of the spreadsheets.<\/p>\n Here are my AAAB paternal Patterns with start and stop in Access language:<\/p>\n Here are some examples of fixes that are needed within these AAAB areas:<\/p>\n Basically, if there is a blank in the first 3 positions, it should be filled in by the non-blank in that area. But how do I write that into a formula? Here is one way:<\/p>\n This says if Joel’s Dad base or Heidi’s Dad base is null, put Sharon’s value in. I ran that and it updated 2165 rows.<\/p>\n Next:<\/p>\n <\/p>\n This time only about 1400 rows were updated. The last time we fill in Heidi’s value if Joel or Sharon had a missing Dad base.<\/p>\n I’ll check my work. I see a flaw in my logic already. I shouldn’t have put the 2 ‘Is not null’s’ on the same line and Access sees that as an and. I wanted an ‘OR’, so they should’ve been on separate lines. Here is the revised query putting Heidi’s Dad base in the empty spot of Sharon and Joel.<\/p>\n <\/p>\n Note that I had to put the position criteria for the Paternal Patterns in twice also. See, I had missed 4121 rows. I went through this with the 2 other siblings.<\/p>\n The AABA also has potential for filling in.<\/p>\n In the first line there is ?T??. In and AABA pattern, we know that the first and last position will also need to be T. In the second line, we don’t know what to fill in. In the 3rd line we can put a C in the last column.<\/p>\n My ID locations for AABA look like this:<\/p>\n The queries will be similar to last time except that they will involve Joel, Sharon and Jon and leave out Heidi.<\/p>\n This was a more popular fill-in. In the query above, if I had a Dad Base and Sharon and Jon didn’t it went to Sharon or Jon. I then did the same thing for Sharon and Jon. Here I check my results.<\/p>\n These are the same ID Lines shown as above before I did the query. This now shows that Joel, Sharon and Jon have the same bases for this AABA Pattern. This is even true when we don’t know the base Heidi has on her paternal side as in the first row.<\/p>\n Here is what an AABB Pattern area looks like before I fill in the bases:<\/p>\n The rule is if Joel or Sharon or Heidi or Jon have one base and the other is missing, fill in the missing one with the one that is there. However, as in the second row, where Heidi and Jon are both missing, nothing may be filled in. This will take a little thought. Perhaps I can do this in 2 steps:<\/p>\n This says if I have a base AND Sharon doesn’t, give her my base, then also do the same and\u00a0fill in Heidi’s base to Jon’s missing base from dad. This query filled in a little less than 20,000 bases with the push of a Run button. Then I’ll do the opposite:<\/p>\n This time Sharon’s base goes to me and Jon’s goes to Heidi. I’ll check good old ID 45494.<\/p>\n It looks like I filled in what I wanted to and didn’t fill in what should not have been filled in.<\/p>\n The other combinations will be variations on what has just been done. Either 3 will match each other and one won’t or there will be 2 pairs that match each other within the pair.<\/p>\n This is the first pattern of my siblings’ 1st Chromosome.<\/p>\n Another ho-hum 20,000 rows filled in.<\/p>\n Here Heidi fills in Joel and Jon:<\/p>\n The updated rows go down the 3rd time I run this.<\/p>\n This will be where Joel and Heidi match paternally and Sharon and Jon match.<\/p>\n Jon is probably missing a lot of bases due to being tested on with the Ancestry Version 2.<\/p>\n This query says in the ID areas where there is an ABBA pattern put Jon’s dad base into Joel’s missing dad base area and put Heidi’s dad base into Sharon’s missing dad base area in the table called tbl4SibsPPatternFillin.<\/p>\n Here, I made a mistake. Note that I had Access overwrite a bracket “]” that didn’t get erased. That means that I will have to run this query again to get my bases back from Jon. Here is what the above Update Query did.<\/p>\n Fortunately, Jon still has the bases that I gave him. I’ll redo the query to get my bases back.<\/p>\n This query will fix my error. It says if I have an end bracket as a base, fill it up with what Jon has.<\/p>\n This time I won’t touch my bases, but make sure that Sharon, Heidi and Jon match.<\/p>\n So that should have filled in all the paternal patterns.<\/p>\n This should be a little trickier. Previously, we had identified one AAAA pattern in Chromosome 1. This can be seen between 19 and 23 below. All the paternal areas are orange.<\/p>\n There is no other area on this Chromosome that is all orange or all green for all siblings. However, how do I identify all the other quadruple A patterns? It is not as easy as the other patterns because this pattern may occur within other patterns. I could make a chromosome map for each chromosome as above, however, it becomes a chicken and egg problem. It would be nice to know the AAAA areas so I could draw the map.<\/p>\n Here is a spreadsheet where I checked the number of IDs from the Start to the previous Stop.<\/p>\n When the amount was more that 500 IDs, I highlighted that number in yellow. Above between the Stop of ABAA and the Start of ABBB on Chromosome 1, there was an AAAA pattern for 1478 position numbers.<\/p>\n The next yellow area is in Chromosome 2 which is a larger region of AAAA Here is an interesting situation:<\/p>\n This yellow area is above the amount I chose as a minimum of 500 positions. However, as I look at my worksheet, I see that the ABBA pattern extends beyond ID# 285124. So I will do a new query based on the new fill-in table. Here is the new ABBA:<\/p>\n This shows that the ABBA pattern goes to the end of Chromosome 6. I can fill in the extra letters by hand and adjust my spreadsheet.<\/p>\n Chromosome 7 appears to have an AAAA pattern for about 847 ID#’s. This is how MacNeill mapped my Chromosome 7.<\/p>\n He would have the ABBB Paternal Pattern with me being the A. This is how I had visually mapped Chromosome 7:<\/p>\n These end pieces are difficult where there is a half identical region. I will stick with my as I do notice a small match with my Hartley-related 2nd cousin Pat:<\/p>\n This may become more clear once my brother Jon is mapped out. In fact Jon is Fully Identical with me in that region:<\/p>\n Jon also matches cousin Pat in that same spot:<\/p>\n Ergo, I must match Pat aka Hartley DNA at the first part of Chromosome 7.<\/p>\n Here is Jon mapped out no Chromosome 7:<\/p>\n Jon (F) and Heidi (H) got a full dose of Hartley DNA at Chromosome 7.<\/p>\n That was a bit of a long exercise, but the intention was to prove to myself that an AAAA pattern of over 500 positions (or my ID#s) is a valid AAAA Pattern.<\/p>\n As I have now convinced myself that this small area was indeed an AAAA area, I can proceed. I made a formula\u00a0in Excel\u00a0that takes the other Patterns’ Stops and Starts and puts them into Access language.<\/p>\n The formula adds an ID# to the beginning and subtracts one from the end so the AAAA patterns have their own range.<\/p>\n <\/p>\n Having found a pattern boundary that was off at the end of Chromosome 6, I will check the other boundaries.\u00a0According to my spreadsheet, the first AAAA should end at 6604.<\/p>\n The actual Access Data table is different by one:<\/p>\n That mean that the I need to add an ‘A’ to the missing space and change the start of the ABBB Pattern from 6605 to 6604 – a pretty minor change. I made a few more minor changes. However, I’ll hold off on making the AAAA pattern changes for now. That is in case the boundary changes again due to other changes I’ll be making.<\/p>\n This is about how far I got last time when I was trying to phase 3 siblings. My interpretation of this portion of the process is to look at the heterozygous siblings. Where they have a new base on the Dad side, they will know that the other base goes on the Mom side.<\/p>\n First I made a new table to put the new information in. It is just a copy of my last table of the fill-ins based on patterns. Here is a query just to find the alleles for each sibling that are different from each other:<\/p>\n Here is the Update Query. I better get it right as it is doing a lot of things:<\/p>\n The first part has the criteria that makes a person heterozygous. I forgot to make sure that the mom base was missing, so I need to add an ‘is null’ phrase:<\/p>\n This may not be necessary, but just makes sure I am not overwriting anything that is already there. So when mom’s base is missing add the base that isn’t the dad base. Or more specifically, add allele2. This changes 39,260 rows.<\/p>\n Next to get the opposite effect, I change most of the alleles 1’s to 2’s and the 2’s to 1’s.<\/p>\n That changed over 10% of all the results. To check, here is a query from the older un-updated table showing just my results where I’m heterozygous and my allele1 was from Dad:<\/p>\n Here is the updated table.<\/p>\n![\"pattern-bar-chart\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/09\/Pattern-Bar-Chart.png\")
<\/a><\/p>\nFinding the Two Missed Paternal Crossovers<\/h4>\n
![\"chr1startstop\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Chr1StartStop.png\")
<\/a><\/p>\n![\"macneillstartstopchr1\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/MacNeillStartStopChr1.png\")
<\/a><\/p>\n![\"macneill-checkchr1\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/MacNeill-CheckChr1.png\")
<\/a><\/p>\nGedmatch Check<\/h5>\n
![\"chr1-4sibs\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Chr1-4Sibs.jpg\")
<\/a><\/p>\n![\"jonvisphasechr1\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/JonVisPhaseChr1.jpg\")
<\/a><\/p>\nassigning Paternal crossovers for Chromosome 1<\/h5>\n
![\"assignxoverchr1\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AssignXoverChr1.png\")
<\/a><\/p>\nTime to Push the Button: Back to Phasing by MS Access<\/h4>\n
First aAAB<\/h5>\n
![\"aaabstartstop\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AAABStartStop.png\")
<\/a><\/p>\n![\"aaabexample\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AAABExample.png\")
<\/a><\/p>\n![\"update\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Update.png\")
<\/a><\/p>\n![\"aaabfillin\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AAABFillin.png\")
<\/a><\/p>\n![\"407rows\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/407rows.png\")
<\/a><\/p>\n![\"aaabrev\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AAABRev-1.png\")
<\/a><\/p>\nAAbA Paternal pattern<\/h5>\n
![\"aabappatternstartstop\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AABAPPatternStartStop.png\")
<\/a><\/p>\n![\"aabaexample\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AABAExample.png\")
<\/a><\/p>\n![\"idsaaba\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/IDsAABA.png\")
<\/a><\/p>\n![\"aabaqueryfillin\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AABAQueryFillin.png\")
<\/a><\/p>\n![\"checkaaba\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/CheckAABA.png\")
<\/a><\/p>\naabb pattern<\/h5>\n
![\"aabbpattern\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AABBPattern.png\")
<\/a><\/p>\n![\"aabbpatternarea\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AABBPatternArea.png\")
<\/a><\/p>\n![\"aabbfillinquery\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AABBfillinQuery.png\")
<\/a><\/p>\n![\"aabbquery2\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AABBQuery2.png\")
<\/a><\/p>\n![\"aabbcheck\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AABBCheck.png\")
<\/a><\/p>\nabaa Fill-in<\/h5>\n
![\"abaaquery\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/ABAAQuery.png\")
<\/a><\/p>\n![\"abaa2\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/ABAA2.png\")
<\/a><\/p>\n![\"abaa3\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/ABAA3.png\")
<\/a><\/p>\nabab Dad pattern<\/h5>\n
![\"ababquery\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/ababquery.png\")
<\/a><\/p>\n![\"ababq2\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/ababq2.png\")
<\/a><\/p>\nabba<\/h5>\n
![\"abbaq1\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/abbaq1.png\")
<\/a><\/p>\n![\"joelsharonabba\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/JoelSharonABBA.png\")
<\/a><\/p>\n![\"mistake\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Mistake.png\")
<\/a><\/p>\n![\"fixqurey\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/FixQurey.png\")
<\/a><\/p>\n![\"fixresults\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Fixresults.png\")
<\/a><\/p>\nabbb – the last paternal combo<\/h5>\n
![\"abbbq1\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/ABBBQ1.png\")
<\/a><\/p>\n![\"heidiabbb\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/HeidiABBB.png\")
<\/a><\/p>\n![\"jonabbb\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/JonABBB.png\")
<\/a><\/p>\nFinding the AAAA ‘Patterns’<\/h4>\n
<\/a><\/p>\n![\"startminusstop\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/StartMinusStop.png\")
<\/a><\/p>\n
\npattern.<\/p>\n![\"chr6and7\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Chr6and7.png\")
<\/a><\/p>\n![\"newabba\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/NewABBA.png\")
<\/a><\/p>\n![\"newabbaresults\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/NewABBAresults.png\")
<\/a><\/p>\nHowever, what about Chromosome 7?<\/h5>\n
![\"chr7macneill\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Chr7MacNeill.jpg\")
<\/a><\/p>\n![\"chr7joel\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Chr7Joel.jpg\")
<\/a><\/p>\n![\"chr7joelpat\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Chr7JoelPat.jpg\")
<\/a><\/p>\n![\"jonjoelchr7\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/JonJoelChr7.jpg\")
<\/a><\/p>\n![\"jonjoelchr7pat\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/JonJoelChr7Pat.jpg\")
<\/a><\/p>\n![\"chr7vismapjon\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Chr7VisMapJon.jpg\")
<\/a><\/p>\nFilling in the aaaa’s<\/h5>\n
![\"aaaagaps\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AAAAGaps.png\")
<\/a><\/p>\nInspecting my work<\/h5>\n
![\"aaaaspreadsheetchrq1\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/AAAASpreadsheetChrq1.png\")
<\/a><\/p>\n![\"chr1correction\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Chr1correction.png\")
<\/a><\/p>\nFilling In Mom Bases From Dad Bases<\/h4>\n
Finding heterozygous siblings<\/h5>\n
![\"heterozgous4sibs\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/Heterozgous4Sibs.png\")
<\/a><\/p>\n![\"updatemomfromdad4sibs\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/UpdateMomFromDad4Sibs.png\")
<\/a><\/p>\n![\"4sibheteromomfromdadrev\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/4SibHeteroMomFromDadRev.png\")
<\/a><\/p>\n![\"otherallelemomfromdad4sib\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/OtherAlleleMomFromDad4Sib.png\")
<\/a><\/p>\n![\"qryoldtable\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/qryoldtable.png\")
<\/a><\/p>\n
![\"updatedtablemomfromdad\"](\"http:\/\/www.jmhartley.com\/HBlog\/wp-content\/uploads\/2016\/10\/updatedtablemomfromdad.png\")
<\/a><\/p>\n