-1

I have two .csv files, File1.csv has got ~100 columns and 60,000 rows, whereas File2.csv has got two columns and 57,000 rows. both files have got one common column (gene_symbol). I wanted to create a new file if gene_symbol of file1.csv and file2.csv matches and all the row should be pulled into new file including the header of file1.csv.

file1.csv

gene_symbol TCGA-HZ-A4BH-01A-11R-A26U-07    TCGA-IB-8127-01A-11R-2404-07
ENSG00000000003 2212    5047
ENSG00000000005 39  2
ENSG00000000419 1571    3337
ENSG00000000457 1046    1832
ENSG00000000460 441 512
ENSG00000000938 1059    1935
ENSG00000000971 4628    8834
ENSG00000001036 2388    3976
ENSG00000001084 1385    3352
ENSG00000001167 1395    4373
ENSG00000001460 484 913
ENSG00000001461 1741    4199

file2.csv

gene_symbol entrezgene
ENSG00000000003 7105
ENSG00000000005 64102
ENSG00000000419 8813
ENSG00000000457 57147
ENSG00000000460 55732
ENSG00000000938 2268
ENSG00000000971 3075
ENSG00000001036 2519
ENSG00000001084 2729
ENSG00000001167 4800
ENSG00000001460 90529
ENSG00000001461 57185

I am looking for the following output

file3.csv

gene_symbol entrezgene  TCGA-HZ-A4BH-01A-11R-A26U-07    TCGA-IB-8127-01A-11R-2404-07    TCGA-F2-7273-01A-11R-2156-07
ENSG00000000003 7105    2212    5047    3078
ENSG00000000005 64102   39  2   3
ENSG00000000419 8813    1571    3337    1876
ENSG00000000457 57147   1046    1832    1495
ENSG00000000460 55732   441 512 294
ENSG00000000938 2268    1059    1935    3323
ENSG00000000971 3075    4628    8834    23071
ENSG00000001036 2519    2388    3976    3715
ENSG00000001084 2729    1385    3352    3529
ENSG00000001167 4800    1395    4373    2996
ENSG00000001460 90529   484 913 772
ENSG00000001461 57185   1741    4199    4016
  • Please clarify whether your files are actually comma separated (.csv) or are delimited by some other characters (such as tabs or other whitespace). – steeldriver Jul 25 '18 at 13:15
1

I think GNU Coreutils join is your command:

join file2.csv file1.csv > file3.csv

In case your CSV files are comma separated and not tab separated, use instead:

join -t, file2.csv file1.csv > file3.csv

  • I checked, even after sorting two files it will not produce any file !!! – David Jul 25 '18 at 13:05
  • @David This really should work. For example, it works just fine on the example data that you showed. – Kusalananda Jul 25 '18 at 21:08
  • Thanks leo, This works ! but i need to retain the header in the first row, after sorting the headers are shifted in last row (from 1 to 70,000 th row) since its a big file i cannot open the file on my system. any solution for this? – David Jul 26 '18 at 0:58
  • Thanks leo, This works ! but i need to retain the header in the first row, after sorting the headers are shifted in last row (from 1 to 70,000 th row) since its a big file i cannot open the file on my system. any solution for this? – David Jul 26 '18 at 0:58
  • From your example, the samples are sorted except the header. It should work without sorting. In case that the real files are not sorted, just delete the header before sorting them. – Leo Jul 26 '18 at 7:40
0

Try this,

join <(sort -r file2.csv) <(sort -r file1.csv)  > file3.csv

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.