0

I have a big file with ~ 1000 columns. The first 2 columns are chromosome and SNP (single nucleotide polymorphism)position, the rest are samples. Here is the first few line and rows:

#CHROM  POS    P0431    P432    P433    P434    P435
Chr01   180349  G   G   G   N   G
Chr01   180372  N   N   N   N   A
Chr01   180389  A   N   A   N   N

There are samples from 3 different groups in this data-set and I want to separate and store in a separate file.

here is a file that I have ample IDs for group1.

$ head group1
P0431
P434
P435

So, I want to find these samples in my main data and store them as group1.data.

$ head group1.data
#CHROM  POS    P0431    P434    P435   
Chr01   180349  G   N   G
Chr01   180372  N   N   A
Chr01   180389  A   N   N
  • Is the data file delimited by multiple spaces, or by tabs? – steeldriver Jul 8 at 22:53
1

With csvcut and csvformat from csvkit, and assuming (possibly multiple) spaces as input delimiters:

$ csvcut -d' ' -S -c "#CHROM,POS,$(paste -sd, < group1)" group1.data | csvformat -T
#CHROM  POS P0431   P434    P435
Chr01   180349  G   N   G
Chr01   180372  N   N   A
Chr01   180389  A   N   N
0
$ cat tst.awk
NR==FNR {
    groups[++numGroups] = $1
    next
}
FNR==1 {
    for (i=1; i<=NF; i++) {
        f[$i] = i
    }
}
{
    printf "%s%s%s%s", $1, OFS, $2, OFS
    for (groupNr=1; groupNr<=numGroups; groupNr++) {
        group = groups[groupNr]
        printf "%s%s", $(f[group]), (groupNr<numGroups ? OFS : ORS)
    }
}

$ awk -f tst.awk group1 file
#CHROM POS P0431 P434 P435
Chr01 180349 G N G
Chr01 180372 N N A
Chr01 180389 A N N

If you want the output tab-separated instead of blank-separated then add a line at the top of the script that says BEGIN { OFS="\t" }.

If you want the output to look visually tabular instead you can do that with printfs inside awk or by piping the above to column:

$ awk -f tst.awk group1 file | column -t
#CHROM  POS     P0431  P434  P435
Chr01   180349  G      N     G
Chr01   180372  N      N     A
Chr01   180389  A      N     N
  • 1
    That is a very sweet piece of code. One highlight is that it will process multiple files for the same group, EVEN IF each file has the columns shuffled in a different way, or some of the columns are missing (provided they are not for the given group). – Paul_Pedant yesterday
0

You can just do this with awk:

awk 'NR==1,NR==4 {print $1,$2,$3,$6,$7}' group1 > group1.data

That will just print fields 1, 2, 3, 6 and 7 of the first four lines and give the output that you requested and append it into the group1.data file:

#CHROM POS P0431 P434 P435
Chr01 180349 G N G
Chr01 180372 N N A
Chr01 180389 A N N

If you want it to look cleaner then you can use OFS='\t' to set the delimiter as a tab:

awk 'NR==1,NR==4 {print $1,$2,$3,$4,$5}' OFS='\t' group1 > group1.data

The output:

#CHROM  POS     P0431   P434    P435
Chr01   180349  G       N       G
Chr01   180372  N       N       A
Chr01   180389  A       N       N

I tested this with the delimiters being multiple spaces, tabs, and s combination of both and received those outputs each time.

  • You missed the important part of the question "how to select columns in a file by the column name...". Also, limiting your script to only work for 4 lines of input isn't a great idea. – Ed Morton Jul 10 at 14:11
  • @EdMorton It isn't indicated in the question or in the expected output that he needs more than the first four lines. In fact, the first four lines are what is specified. To get that output, he can also use the column number instead of the name so it doesn't really matter in this case. – Nasir Riley Jul 10 at 14:28
  • It matters because she doesn't know the column numbers, just their names (a common problem when dealing with CSVs generated from various tools/people and so all containing the same relevant data but in different orders or even if you just want to future-proof your tool from layout changes and/or new columns being added before the columns you care about), and every example in every question has a finite number of lines of input/output - that doesn't mean their real data is that specific number of lines long, it's just a minimal example for the purposes of asking a question. – Ed Morton Jul 10 at 14:39
  • @EdMorton There is nothing in the question that indicates that the column numbers aren't known nor did she say that. In fact, she states that those are the first few lines and rows. Considering that very little is specified or clear in the question, it's not reasonable to expect anyone to address every possibility of what might actually be there. The only thing that we can do is speculate which is effectively what you're doing. It would have been better for no one to post an answer because it doesn't look like we're going to get anymore information as you comment above still lacks an answer. – Nasir Riley Jul 10 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.