0

Hi,

I have two csv files: File1 (appx 18800 rows):

            p1  p10 p16 p19 p25 p3  p5  p6  p8  p9
    A3      567 0   3   0   18  17  8   4   6   7
    B23     490 7   6   2   23  26  20  14  12  29
    A56     737 1   4   1   6   4   1   4   8   5
    Z56     145 6   4   0   11  17  5   9   22  11
    D89     68  0   0   34  4   0   0   0   0   0
    A12     46  0   0   8   0   0   0   0   0   0
    A15     72  0   0   8   0   1   0   0   0   0
    D4      40  0   0   0   0   1   5   18  0   0
    Z6       7  0   1   0   1   1   10  1   2   0
    X3      49  0   0   125 0   0   0   0   0   0

File2 (appx. 400 rows)

        name    tax price class order 

        B23      kat 35    2      1
        Z56      mat 26    3      2
        D4       kat 26    4      1

Now I would like to compare those two files by first column. If the value in the the second file is present in the first file I would like to keep entire matching row. Below an example of an output:

            p1  p10 p16 p19 p25 p3  p5  p6  p8  p9
    B23     490 7   6   2   23  26  20  14  12  29
    Z56     145 6   4   0   11  17  5   9   22  11
    D4      40  0   0   0   0   1   5   18  0   0

Edit: File1 cat

    "","p1","p10","p16","p19","p25","p3","p5","p6","p8","p9"
"p1_1_length_2509_cov_19.337112",567,0,3,0,18,17,8,4,6,7
"p1_10_length_1072_cov_559.052910",4900,7,6,2,23,26,20,14,12,29
"p1_11_length_1032_cov_5800.211050",73784,1,4,1,6,4,1,4,8,5
"p1_12_length_1022_cov_10156.344134",145873,6,4,0,11,17,5,9,22,11
"p1_13_length_946_cov_7.164835",77,17936,61876,5257,6085,196,8383,24956,4656,14687
"p1_14_length_921_cov_15.662469",68,0,0,34,4,0,0,0,0,0
"p1_16_length_800_cov_7.126300",46,0,0,8,0,0,0,0,0,0
"p1_17_length_758_cov_12.328051",72,0,0,8,0,1,0,0,0,0
"p1_19_length_722_cov_5.621849",40,0,0,0,0,1,5,18,0,0

File2 cat:

name,superkingdom,phylum,class,order,family,genus,species
p10_1003_length_529_cov_12.940299,Viruses,,,,Poxviridae,Alphaentomopoxvirus,Anomala cuprea entomopoxvirus
p10_1021_length_525_cov_6.801508,Viruses,,,Herpesvirales,Alloherpesviridae,Batrachovirus,Ranid herpesvirus 1
p10_1047_length_521_cov_4.852792,Viruses,,,,,,Hudisavirus sp.
p10_1152_length_501_cov_22.430481,Viruses,,,,Mimiviridae,Cafeteriavirus,Cafeteria roenbergensis virus
p10_139_length_1152_cov_892.463415,Viruses,,,,,,Hudisavirus sp.
p10_149_length_1130_cov_7.540379,Viruses,,,Picornavirales,Picornaviridae,Enterovirus,Enterovirus C

closed as unclear what you're asking by slm Jun 19 '18 at 4:57

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • try head -n1 file1; awk 'FNR==NR {a[$1]; next} $1 in a' file2 file1 > file3 – msp9011 Jun 18 '18 at 12:50
  • Unless you are specifically looking for a solution in r, there are dozens of almost identical generic questions with posted solutions - try searching terms like "awk match files" or "join files first column" – steeldriver Jun 18 '18 at 12:53
  • It does not work for me. "head -n1 file1;" copy the first row, but after second part there is nothing in output – k_a_r_o_l Jun 18 '18 at 13:05
  • Is your data actually CSV (i.e. comma separated) or whitespace separated? Try awk 'FNR==NR {if (FNR>2) a[$1]=1; next} FNR==1 || $1 in a' File2 File1 – steeldriver Jun 18 '18 at 13:26
  • it only returns first row from file1 as an output. It,s comma separated. But now i see that in file phrases from coluumn 1 in file1 are in brackets and in file 2 they are no. – k_a_r_o_l Jun 18 '18 at 13:38
1

You want to extract all lines in your first file whose first column correspond to a column in your second file.

Since the first file seems to only contain text in its first column (and the rest is numbers), we could simply use grep for this.

In bash or any other shell that understands process substitutions, this would be a matter of

grep -F -f <( awk -F, 'NR > 1 { print $1 }' <file2 ) file1 >newfile

In other shells you would first write the output of the awk command to a temporary file and then use that with grep -f.

The awk would generate output like

p10_1003_length_529_cov_12.940299
p10_1021_length_525_cov_6.801508
p10_1047_length_521_cov_4.852792
p10_1152_length_501_cov_22.430481
p10_139_length_1152_cov_892.463415
p10_149_length_1130_cov_7.540379

and the grep would use these as fixed string patterns to match against each line in your first file.

You could do it all in awk too, which would include reading in the first column of the second file first, as keys in an associative array, and then testing the first column of the first file against these keys:

awk -F, 'NR==FNR && FNR>1 { keys[sprintf("\"%s\"", $1)] }
         NR!=FNR && FNR>1 && ($1 in keys)' file2 file1

The strange sprintf() is there because the first column in file1 is double quoted. It just adds double quotes to the data read from file2.

The FNR>1 skips the headers of both files while NR==FNR is true if we're reading from file2.

0

This is a job best suited for a programming language rather than a scripting language, because you're not just working with one stream. One basic algorithm would be:

  1. Read first file line by line. Construct a list of all first elements. (Depending on the language you use, a hash or dict might be most efficient.)
  2. Read the second file line by line. If the first element exists in your list from #1, save it to your output file.
  • awk? any sugestions – k_a_r_o_l Jun 18 '18 at 12:41
  • I'd suggest python or perl. – mattdm Jun 18 '18 at 12:49

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