PDA

View Full Version : help needed with Excel or Access to store multiple sets of Raw Data



C J Wyatt III
11-09-2020, 06:02 PM
I am working on an idea which I believe will lead to a major improvement in distant autosomal matching. I am not experienced with Excel but I am using spreadsheets to hold a kit's raw data. I am needing to merge all of these into a single spreadsheet with columns for RSID, chromosome, position, and separate columns for the results of each kit.

If anyone can help me with this or already has a solution, I would be very grateful.

Alternatively, I have Access database software from my MS Office suit, but I am even less experienced with Access. If what I want is simpler or better to do with Access, I would be happy to take that approach.

If there is a inexpensive package using a different platform, I also would be willing to take a look at it.

Thank you for your consideration.

Jack

xenus
11-09-2020, 09:36 PM
you're good with excel or access if you like them.

When you're working with data you need to know how to do some of these things and the first part of this is learning how a program deals with tasks like this.

In excel you're looking at macros.

In access you're looking at what is essentially a database query.

Are you just trying to pull up a bunch of side by side comparisons for any given gene?
You'd nest this inside chromosomes and then have it look like this?


sample name/id
gene1
gene2
gen3


kit1
A/A
A/C
C/C


kit2
A/C
C/A
A/A


kit3
C/A
C/C
A/C

C J Wyatt III
11-10-2020, 02:15 AM
Are you just trying to pull up a bunch of side by side comparisons for any given gene?
You'd nest this inside chromosomes and then have it look like this?


sample name/id
gene1
gene2
gen3


kit1
A/A
A/C
C/C


kit2
A/C
C/A
A/A


kit3
C/A
C/C
A/C



Right.

When you open FTDNA FF raw data with Excel, you get columns for RSID, Chromosome, Location, and Result. I would like to merge other kits in by having columns for their results. Sometimes a kit might have an RSID which is not included in another, so when I merge the other kits in, Chromosome and Location needs to be the key.

I appreciate your help.

I am sure it is something simple which I am missing.

xenus
11-10-2020, 07:47 AM
there are a lot of tools to work with data like this. A bunch are listed https://isogg.org/wiki/Raw_DNA_data_tools

If i was more familiar with excel i'd probably have a better idea. With access it'd be importing the files and having the read into a database the right way but again i'm not actually that familiar with it.

I'll see if there are examples of the format to take a look myself.

If nothing else i can at least write a script to merge them like you're talking about.