Chromosomse style Stacked Bar Chart?

[zarlor]

I'd like to make a chart in excel that looks like the following:


http://1.bp.blogspot.com/-Ch69snN2msk/T-ZEQZ4NhGI/AAAAAAAAB6U/NMK5uNdBkrg/s400/ChrMap623.jpg


I have data that looks like so:

Chr Start End MRCAs

1 41000000 61000000 Walter Green & Florence Bailey

1 48748650 52868170 Dumas Bailey & Josephine Tuma

1 150000000 207000000 John Bailey Green

2 1091127 4843060 James Caudill Jr. & Mary Adams AND Stephen James Caudill & Mary Elizabeth Fields

2 36000000 43000000 Charles Acquistapace & Louisa Meier

2 55969363 58340771 Dumas Bailey & Josephine Tuma


Not listed are the lengths of each chromosome (which are not all the same but I can easily include their endpoints, so no issues there.) Plus the names in my sample data above don't match the picture linked, but I hope you get the idea.


So basically I'd like to group the data by Chromosome and then by the Common Ancestor on that chromosome showing colors for them where their information starts and ends on that particular chromosome/bar. I suppose I could just set up formulae to include in the data which just determines the lengths of each segment (and all of the gaps between and out to the endpoint) but I'm not exactly sure how to really make a chart like that.


Does anyone have some ideas on what I would need to do (or if I even can do it in Excel? Do I need to try it in something like Access instead?)


Thanks for any help/ideas anyone may have.


Sincerely,

Lenny Zimmermann

 

[Hui]

Lenny


Firstly, Welcome to the Chandoo.org forums.


You should be able to do this as a stacked bar chart

It will require quite a few series

You will need to supply the lengths of each chromosome


My only question is that there are 5 Chromosomes shown in the chart

How do they relate to the either 3 or 6 sets of Chromosome 1 data?

 

[zarlor]

Well, the picture doesn't relate directly to the data I have listed. But in essence think of chromosome/bar 1 as starting from 1 to 247093448. So then the entry for "Walter Green & Florence Bailey" might be like the "Wirta Great Grandfather" from the picture, a particular colored segment on that bar. Then "Dumas Bailey & Josephine Tuma" would be a second line/cluster in the stacked bar, if that makes any sense.


The idea is to be able to look at, say, the red line and approximate position of it on the chart and be able to compare that to a new match to see if some new math has any overlap in that position. If so it's likely the new match will have a common ancestor behind the listed couple therefore greatly narrowing down the search of where to look on our shared family trees to find our common link. If any of that makes any sense. :/


Lenny

 

[zarlor]

Might help if I simplify the data:


Let's say I have the following


Chromosome | Start | End | Ancestor

1 | 1 | 100 | Chromosome length

1 | 25 | 50 | Ancestor A

1 | 45 | 75 | Ancestor B

1 | 90 | 100 | Ancestor B


I would like that to chart showing say just an outlined bar starting at 1 going to 100, then inside (or underneath) that bar empty space out to 25, red from 25 to 50, blank from 50-100.


Then another below that still inside (or underneath, whatever works) that is blank from 1-45, blue from 45-75, blank from 75-90 and blue again from 90-100.


Rinse and repeat for Chromosome 2, etc. Always just red for Ancestor A and blue for Ancestor B and so on.


I hope that's a little clearer about what I'm trying to accomplish.


Thanks again, I've just had a hard time wrapping my head around how to nest the charts together like that and especially how to just do it.


Lenny

 

[zarlor]

I also got another answer to this, if anyone is interested, at http://peltiertech.com/WordPress/clustered-stacked-column-bar-charts/comment-page-2/#comment-275765