Need help transposing MAP file and PED file into one large table

1	rs3094315	742429
1	rs7419119	831876
1	rs13302957	880884
1	rs6696609	893289
1	rs8997	939517
1	rs9442372	1008567
1	rs147606383	1035194
1	rs4970405	1038818
1	rs11807848	1051029
1	rs4970421	1098500
1	rs1320571	1110294
1	rs2887286	1145994
1	rs79118541	1147410
1	rs3813199	1148140
1	rs113791678	1151643
1	rs78424188	1160450
1	rs12073590	1195018
1	rs6685064	1201155
1	rs61559999	1225655
1	rs60785581	1225708

The slightly modified ped has 942 rows, where each row is an individual and each column is a genotype correspond to the associated row of map file

HGDP00001

HGDP00003

HGDP00005

HGDP00007

HGDP00011

HGDP00013

HGDP00015

HGDP00017

HGDP00019

HGDP00021

HGDP00023

HGDP00025

HGDP00027

HGDP00029

HGDP00031

HGDP00033

HGDP00035

HGDP00037

I trying to get them into a single table with a similar formatting to some Affy array data I have (which has rows as SNP ids and columns as individuals).

I was wondering if anyone could help figure out a Python or Bash scripting solution to transpose the ped file such that the 1st row of the ped file becomes the 5th column of the map file, and the 2nd row of the ped becomes the 6th of the map file, and so on...

Basically, I want it to look like this (I presume subsequently taking out the 0/1 rows and location columns will be fairly simple?)

HGDP00001

HGDP00003

HGDP00005

HGDP00007

HGDP00011

HGDP00013

HGDP00015

HGDP00017

HGDP00019

HGDP00021

HGDP00023

HGDP00025

HGDP00027

HGDP00029

HGDP00031

HGDP00033

HGDP00035

HGDP00037

HGDP00039

HGDP00001

HGDP00003

HGDP00005

HGDP00007

HGDP00011

HGDP00013

HGDP00015

HGDP00017

HGDP00019

HGDP00021

HGDP00023

HGDP00025

HGDP00027

HGDP00029

HGDP00031

HGDP00033

HGDP00035

HGDP00037

HGDP00039

rs3094315

742429

rs7419119

831876

rs13302957

880884

rs6696609

893289

rs8997

939517

rs9442372

1008567

rs147606383

1035194

rs4970405

1038818

bash snp python table • 2.1k views

ADD COMMENT • link •

modified 3.9 years ago by Uma A • 220 • written 3.9 years ago by devenvyas • 570

R would make this super easy, no? Does the solution *have* to be in bash/python?

ADD REPLY • link written 3.9 years ago by RamRS ♦ 21k

Doesn't have to be, it is just that I have more experience with bash/python. I do have some background in R

ADD REPLY • link written 3.9 years ago by devenvyas • 570

3.9 years ago by

RamRS ♦ 21k

Houston, TX

RamRS ♦ 21k wrote:

Here's my (quick and dirty) solution in R:

##read files
map_file<-read.csv('./mapfile.csv')
ped_file<-read.csv('./pedfile.csv')

##transform and merge data

#create matrix of blanks
matrix_of_blanks<-matrix(data="",nrow=6,ncol=4)

#bind matrix of blanks to map file
tr_map<-rbind(matrix_of_blanks,as.matrix(map_file))

#combine transformed map file and transposed ped file
cbind(tr_map,t(ped_file))

ADD COMMENT • link modified 3.9 years ago • written 3.9 years ago by RamRS ♦ 21k

I ran this R code

setwd("/scratch/lfs/vyas/HGDPcomp")

##read files
map_file<-read.csv('all_snp_out.map')
ped_file<-read.csv('all_snp_nospace.ped')

##transform and merge data

#create matrix of blanks
matrix_of_blanks<-matrix(data="",nrow=6,ncol=4)

#bind matrix of blanks to map file
tr_map<-rbind(matrix_of_blanks,as.matrix(map_file))

#combine transformed map file and transposed ped file
cbind(tr_map,t(ped_file)) -> transposed
write.csv(transposed, file='table.csv')

and then I got this result back from the cluster:

Error in rbind(matrix_of_blanks, as.matrix(map_file)) :
  number of columns of matrices must match (see arg 2)
Execution halted

I think I will try to transpose the ped file first, and then manually transform the map file in excel

ADD REPLY • link written 3.9 years ago by devenvyas • 570

ped_file<-read.csv('all_snp_nospace.ped', sep = '\t')
t(ped_file) -> transposed
write.csv(transposed, sep = '\t', quote=FALSE, file='table.csv')

The R method is not working. It is writing with comma-delimiters and the first row is a whole bunch of Vs and the first column appears to merging some columns

This is a bit of my output... (copied out of Excel)

V10

V11

V12

V13

V14

V15

HGDP00001

HGDP00003

HGDP00005

HGDP00007

HGDP00011

HGDP00013

HGDP00015

HGDP00017

HGDP00019

HGDP00021

HGDP00023

HGDP00025

HGDP00027

HGDP00029

HGDP00031

HGDP00033

HGDP00001.1

HGDP00003

HGDP00005

HGDP00007

HGDP00011

HGDP00013

HGDP00015

HGDP00017

HGDP00019

HGDP00021

HGDP00023

HGDP00025

HGDP00027

HGDP00029

HGDP00031

HGDP00033

X0.1

X0.2

AG.1

GG.1

AA.1

GG.2

AG.2

TT.1

GG.3

CC.1

CC.2

AA.2

CT.1

CC.3

CC.4

GG.4

GG.5

TT.2

TT.3

GG.6

AG.3

I will try the Plink method later today.

ADD REPLY • link modified 3.9 years ago • written 3.9 years ago by devenvyas • 570

write.csv will write csv. Not sure why you're writing csv here in the first place.

ADD REPLY • link written 3.9 years ago by RamRS ♦ 21k

Please note that I converted map and ped files to CSV before reading them in. If you're reading them directly, you're better off using read.table(file,sep="\t",header=FALSE,stringsAsFactors=FALSE)

This might solve the unequal number of rows problem. Also, please run step by step so you know which rbind is erroring out.

ADD REPLY • link written 3.9 years ago by RamRS ♦ 21k

ped_file<-read.table('all_snp_nospace.ped', sep="\t", header=FALSE, stringsAsFactors=FALSE)
t(ped_file) -> awesome
write.table(awesome, sep = '\t', quote=FALSE, file='table.csv')

Yielded this:

V1   V2   V3   V4   V5
V1   HGDP00001   HGDP00003   HGDP00005   HGDP00007
V2   HGDP00001   HGDP00003   HGDP00005   HGDP00007
V3   0   0   0   0
V4   0   0   0   0
V5   1   1   1   1
V6   0   0   0   0
V7   AG   AA   AA   AA
V8   GT   GT   TT   TT
V9   AA   AA   AA   AA
V10   CC   TT   CC   CC
V11   GG   GG   GG   GG
V12   AG   GG   GG   GG
V13   GG   GG   GG   GG
V14   AA   AA   AA   AA
V15   CT   TT   TT   CT
V16   GG   GG   GG   GG
V17   AG   GG   GG   AG
V18   TT   TT   TT   CT
V19   TT   TT   TT   TT
V20   GG   GG   GG   GG
V21   CC   CC   CC   CC
V22   CC   CC   CC   CC
V23   AA   AA   AA   AA
V24   CT   CC   CC   CC
V25   CC   CC   CC   CC
V26   CC   CC   CC   00
V27   GG   GG   GG   GG
V28   GG   GG   GG   GG
V29   TT   TT   TT   TT
V30   TT   TT   TT   TT
V31   GG   GG   GG   GG
V32   AG   AG   AA   AG
V33   AA   AA   AA   AA
V34   TT   TT   TT   TT
V35   CC   CC   CC   CC
V36   AA   AA   GG   AG
V37   AG   AG   GG   GG
V38   CT   CT   TT   CT
V39   AG   AG   GG   GG
V40   CC   CC   CT   CT
V41   GG   GG   AG   AG
V42   AA   AA   AC   AA
V43   GG   GG   GG   GG
V44   CC   CC   AA   AC
V45   CT   TT   CC   CT
V46   CT   CC   CC   CC
V47   CT   CC   TT   CT
V48   AG   GG   GG   GG
V49   CC   CC   CC   00

Which is actually close to what I need. Given the massiveness of the file, a trivial thing such as removing the V column and the V row in Excel will not be doable. Any suggestion on how to do so in R? I can manually add the empty spaces to the map file, so once the Vs are gone, I will be able to join them and have the table I need.

ADD REPLY • link written 3.9 years ago by devenvyas • 570

The Vs are temporary headers and row numbers, they should not be bound to the data itself. If you're using RStudio (you should, if you're not), use View(awesome) to check out the actual data. You should see headers and a column called row.names (or some such) with the Vxx values.

ADD REPLY • link written 3.9 years ago by RamRS ♦ 21k

The Vs showed up in Terminal and Excel (where it barely opens), so they are not temporary.

I redid it and added a row.names and col.names equals false in the write.table command, and the Vs were gone.

I will attempt to bind the tables later today.

I would use RStudio, but I am running my jobs on UF's High Performance Computing cluster, so everything is command-line. The files are sufficiently large that my i7 and my 8 gigs of DDR3 can't handle them.

ADD REPLY • link modified 3.9 years ago • written 3.9 years ago by devenvyas • 570

I see. That makes sense. he bind should work fine now.

ADD REPLY • link written 3.9 years ago by RamRS ♦ 21k

3.9 years ago by

Uma A • 220

India

Uma A • 220 wrote:

.tped file is what you want. Just use plink to transpose your .ped and .map files into .tped and .tmap files. .tped file contains the data in the format that you want. Use the following command to get the tped:

plink --file filename --recode --transpose --out transposedfilename

To open the transposed file you just created,

plink --tfile transposedfilename

In the end, you will have to append the first row containing sample names by using any desired method.

The following links contain the help available on the .tped and .tfam formats:

http://pngu.mgh.harvard.edu/~purcell/plink/dataman.shtml#recode

http://pngu.mgh.harvard.edu/~purcell/plink/data.shtml#tr

ADD COMMENT • link modified 3.9 years ago • written 3.9 years ago by Uma A • 220

I don't need to transpose my map file though, just the ped file, so that I can combine them into one big table.

ADD REPLY • link written 3.9 years ago by devenvyas • 570

The .tped file is a combination of ped and map files and your original files do not get affected in any way.

ADD REPLY • link written 3.9 years ago by Uma A • 220

For the --file option, wouldn't I need to do that twice since there are two files?

ADD REPLY • link written 3.9 years ago by devenvyas • 570

plink --file file --recode --transpose --out transposed.tped

I renamed my files to file.map and file.ped. This did not work like it was supposed to

Look at this output.

cut -f 1 transposed.tped.tped | head -1

1 rs3094315 0 742429 G A A A A A A A G A G A G A G A A A A A A A A A G A A A G A A A G A G A A A A A G A A A G G G A A A G A G A A A A A A A A A G A A A A A A A A A G A A A G A G A A A A A A A A A A A G A A A G A A A A A G G A A A A A A G A G A A A G A G A G A A A A A A A A A A A A A A A G A A A A A A A A A G A G G G A A A A A G A G A A A A A G A G A A A G A A A A A G A 0 0 G A A A A A A A G A G A G A G A G A G A A A G A G G A A G G A A G A G A A A G A G A G A G A A A G G A A A A G A A A A A A A A A A A G A G A A A A A G A G A G A A A A A G A G A A A G A A A A A A A A A G G A A G A A A G A G G A A 0 0 G A G G G A A A A A G A A A G A G A A A A A G A A A A A A A G A A A A A A A G A A A G A A A A A A A G A A A A A A A G A A A A A A A A A G A G A A A A A G A G A G A G A A A G A G A G A G A A A G G G A G A G G G A G G G G G G G G G A G G G A G A G G G G G G G A G A G A G A G A A A A A A A A A A A G A G A G A G A G A A A A A A A A A G A A A G A A A A A A A A A G A 0 0 A A A A A A A A G A A A A A A A A A A A A A A A G A A A G A A A A A A A A A A A A A A A A A G A G A A A A A A A A A G A 0 0 G A A A G A A A A A A A A A G A A A A A A A G G A A G A A A A A A A G A G A A A G A A A G A G A G A A A A A A A A A G A G A G A A A A A G A G A A A G A A A A A A A A A G G G G A A G A G A A A 0 0 G A G A A A A A G A G A A A A A A A G G G A G A A A A A A A 0 0 G A G A G A G A A A G A A A G A G A A A A A A A A A A A G A A A A A A A A A A A A A A A A A A A G A G A A A A A A A A A A A A A G A G A G A A A A A G A G A 0 0 A A G A A A G A A A A A G A A A A A G A A A A A G A G A G A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A G A G A A A A A A A A A G A 0 0 A A G A G A A A G A A A A A A A A A G A G A A A G A A A A A A A A A G G A A A A A A A A G A A A A A A A A A G A G A A A A A A A A A G A G A A A A A A A A A A A A A A A A A A A G A A A A A A A A A G A A A A A A A A A A A A A A A A A 0 0 G A A A A A A A A A A A A A A A A A A A A A A A A A G A G A A A A A A A A A A A G A G A A A A A A A A A A A A A A A A A G A A A A A A A G A G G G A A A A A A A G A A A A A G A A A A A G A A A G G A A A A A A G A A A G A G A A A A A A A A A A A A A A A A A G A A A A A G A A A G A G A G A G A G A G A A A G G G G G G G G G G G G G G G G G A A A G A G A G A G G G G G A G A G G A A G G G G G G G A G A G G G A G A G G G A G A G G A A A A A A G A G A A A A A G A A A A A A A G A G A A A A A A A A A A A A A A A G A A A A A A A A A A A A A A A A A A A A A A A G A G G G A A A G A G A A A G A A A G A A A G A A A A A A A G A A A A A A A G A A A A A A A A A A A A A A A A A A A A A G A A A A A 0 0 G A G A G A G A G A G A G A G A A A G G G A G G G G G G G G G A A A A A A A A A A A A A A A A A A A A A G A G A A A A A A A A A A A A A G A A A G A G A A A A A A A A A A A A A A A A A A A G A A A A A A A A A A A A A A A G A G A A A A A A A A A A A A A G A A A A A A A G G A A G G A A A A A A A A A A G G A A A A G A A A A A G A G A A A A A A A A A A A A A G A G G G G G G A A A A A A A A G A G A A A A A A A A A G A G A A A A A A A A A A A A A A A A A A A A A A A A A A A G A A A A A A A A A G G A A G A G A A A A A A A G A A A A A G A A A A A A A A A G A A A G A A A G A A A A A A A G A G A G A G A A A A A A A G G G A G G G A A A A A G A G A G G A A G A A A G A G G G A G G G A A A A A A A A A A A A A A A G A A A G A A A G A A A G A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A G A A A G A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A G A A A G A A A G A G A A A A A A A A A G A A A G A A A A A A A G A A A A A G A G A G A G A A A A A G A G A G A A A A A A A G A G A A A A A A A G A A A A A A A A A A A G A G A A A A A A A A A A A A A A A A A G A A A G A G G G A G A G G G G A A G G G G G A G A G G 0 0 0 0 G G 0 0 0 0 0 0 0 0

ADD REPLY • link written 3.9 years ago by devenvyas • 570

Firstly, to get your genotypes together, get your original ped/map set into a tab delimited format by using the following command:

plink --file original_filename --recode --tab --output tabSeparated

For the --file flag, you don't need to do that twice, because your .ped and .map files are a pair, and thus they should be of the same name like data.ped and data.map. Thus, your file name (for the --file flag) then becomes only "data"

Also, you don't need to give any extension to the output file. The command automatically creates a .tped and .tfam file pair for the name you have provided. Thus your output file option should only be "transposed". Thus, final command should be:

plink --file tabSeparated --recode --transpose --out transposed

When you load this file in Excel, you can get the individual genotypes in one column only.

Finally, you can merge the first 2 columns of your output file to get the first column of out file containing chromosome number and dbSNP ID together. For that matter, you can do any type of minor formatting in any text editor or Excel

ADD REPLY • link modified 3.9 years ago • written 3.9 years ago by Uma A • 220

Please log in to add an answer.

Similar posts • Search »