Skip to main content

Questions tagged [join]

Join several lines in a single one, inside a single file or from several files.

Filter by
Sorted by
Tagged with
0 votes
0 answers
46 views

How does the auto format of the join command work?

Here is an example: $ cat file1 hello there $ cat file2 some,very,long,line,hello,csv some,very,long,line,nope,csv $ join -t, -1 1 -2 5 file1 file2 hello,some,very,long,line,csv This works as ...
Jeenu's user avatar
  • 101
1 vote
0 answers
35 views

Problems with join on sorted key column but linux says "not in expected order" [duplicate]

File 1. Column1=old_id; Column2=snp_info (50 000 integers, so too large to show, containts 0,1,2,5 -> i.e., 020112202010511) 80024979 80024987 80025141 80107980 80922131 81666414 81667586 87021127 ...
Michiel Van Niekerk's user avatar
0 votes
0 answers
34 views

I want to join variables (columns) from two different files with a matching column using join - problems with output [duplicate]

Good day. I am new to linux and really hope someone can help me. I have two files: File 1 : Column1=id_nr; Column2=snp_info File 2 : Column1=id_nr; Column2=recoded_id_nr I want to join the two ...
Michiel Van Niekerk's user avatar
-1 votes
1 answer
49 views

Returning lines from a JSON log file which contain IP and timestamps read from a CSV file?

so I'm new to Shell Scripting and could use some help. So in my CSV file, I want to see if IP and timestamp values exists in some way in the line entries of the JSON log file, and if so, return that ...
Linuxnoob's user avatar
-1 votes
1 answer
77 views

Why no one thought of the concept of joins before the 70s? [closed]

So, let me see if I have my history right: In 1976, the first system sold as an RDBMS was Multics Relational Data Store. I don't know if it had JOIN, but let's assume it did. The UNIX command join ...
Sebastian Carlos's user avatar
0 votes
2 answers
2k views

Merge all csv files in directory column wise

Say the directory contained 3 csv files: The first csv: Name, John Age, 18 The second csv: Name, Jim Age, 21 The third csv: Name, Amy Age, 22 I would want the result to be: Name, John, Jim, Amy Age,...
Tap's user avatar
  • 25
1 vote
2 answers
77 views

Join two files ordered numerically according to second column

I have two files file 1 contains 2*J=0 EXP= 0.00000 2*J=4 EXP= 1.27911 2*J=8 EXP= 1.57613 2*J=12 EXP= 1.69134 2*J=10 EXP= 2.72705 2*J=16 EXP= 4.55689 2*J=20 EXP= 5.62138 file 2 contains 2*J=0 EXC= 0....
EnthusiastiC's user avatar
1 vote
1 answer
98 views

Why do I need to specify `-o auto` for join's `-e` option to work?

With the following two simple files: a.txt 1 a 2 b 5 c b.txt 2 x 4 y 5 z The following command does not behave like expected: $ join -a 1 -a 2 -e 0 a.txt b.txt 1 a 2 b x 4 y 5 c z I would expect ...
iago-lito's user avatar
  • 2,811
1 vote
2 answers
493 views

Bash Join command inserting delimiter characters into first element

I have two files: File1.txt: 510806153|1||||33245|65|6236067806|25001|7746250 510806153|1||||33245|65|6968349321|25001|8956584 File2.txt 510806153||||| When I do join -t \| -j 1 file1.txt file2.txt, ...
hiramthehero's user avatar
-2 votes
2 answers
134 views

how to merge and amend columns of two files

I need to merge two files based on the 1st and 2nd columns. I need to add the difference between 3rd and 4th columns and add TOTAL rows per type of 2nd column. Here are the two input files: File 1: ...
ekassis's user avatar
  • 249
2 votes
1 answer
969 views

Merge two files using first column

I have two different files and I'd like to do a merge of their information using the first column. File1.txt A,info1,info2 234,info3,info4 CD,info5,info6 File2.txt 234,ccc,bb CD,aaa,dd Expected ...
Tommaso's user avatar
  • 167
0 votes
3 answers
344 views

Join command not giving output when comparing large files

I have 2 files as below with one file of 20 gb and another in MBs file1.txt (100 MB) 6000000 6000001 6000003 file2.txt (20 gb) 6000000;dgdfgdfgdfgdfgdggeewtwtwtwetewtt 6000003;...
jay's user avatar
  • 1
0 votes
1 answer
775 views

join two text file in bash that is separated by comma

file 1: Doe, John, phone123 Dane, Jane, phone124 file 2 phone123, longdistance, 10mins phone124, local, 5mins phone124, longdistance, 6mins output Doe, John phone123 longdistance 10mins Dane, Jane ...
jl123's user avatar
  • 1
1 vote
1 answer
2k views

how to join inventory items in a comma delimited list?

I have ansbile inv # cat inv [all] vm1 vm2 I want to extract them in a comma delimited list using Jinja template so I add jinja template # cat comb_list.j2 {{ groups['all'] | map('extract', hostvars,...
Sollosa's user avatar
  • 1,941
1 vote
2 answers
971 views

Merging columns from multiple files and adding headers

I have over 50 files that have the following format: File1.tab A 25 B 56 C 87 D 82 E 91 F 34 File2.tab A 89 B 55 C 92 D 36 E 28 F 19 File3.tab A 98 B 76 C 82 D 76 E 49 F 42 etc. I would like the ...
tenten's user avatar
  • 13
0 votes
1 answer
504 views

How to create a new column in tsv files by combining two other columns on linux?

I have 70 directories that have files like below: STAN002 |______ STAN002_sasions.tsv PTAN044 |______ PTAN044_sasions.tsv STAN005 |______ STAN005_sasions.tsv STAN050 |______ ...
beginner's user avatar
  • 277
0 votes
2 answers
1k views

Join two tables based on two columns in common and add NA or empty values if does not match

I would like to merge two tables like this Table_1 Chr1 5 Chr1 10 Chr1 20 Chr2 10 Chr2 30 Table_2 Chr1 10 value value2 Chr1 20 value value2 Chr2 30 value value2 Desired output Chr1 5 Chr1 10 value ...
Eric González's user avatar
0 votes
1 answer
335 views

sorting on files and joining

I have two files. I want to sort the file and join on the first column. For example: First file: 100. ttt 200. 616te 300. 7a27 7373qq second file: 100. ttt 200. yoga 300. 7a27 ...
John's user avatar
  • 117
0 votes
3 answers
355 views

join multiple files efficiently

I know there are some posts to join multiple files but it took so much time. I have multiple files in which the first columns are for the patients' IDs, then I want to join multiple files, based on ...
user10345633's user avatar
0 votes
1 answer
193 views

join rows based on string in 2 columns of 2 files

I have a file1 with 3 columns and file2 with 4 columns on a Linux system. How do I join the two files based on the string in column 3 of file1 to the string in column 2 of file2? File2 is a large db ...
Arora's user avatar
  • 79
0 votes
2 answers
99 views

Compare two delimited files with first column

File1 james:hello richard:hello2 johnson:hello3 jam3es:hello ja1mes:hello File2 james:hello ja1mes:hello johnson:hello3 Expected output richard:hello2 jam3es:hello I have these 2 files, I'm trying ...
Johnson's user avatar
  • 11
1 vote
3 answers
277 views

I want to join two files and KEEP all the white space

I am trying to join two data files. The first one has 100.000 records where each line represents one person. The second file has 400.000 records where a person in the first file can appear multiple ...
Þórdís Þórarinsdóttir's user avatar
0 votes
1 answer
731 views

Sort and Join with letters and numbers

I have two files (file1 and file2) and I would like to mount a third (file3) containing a column that the two do not have. I usually do the sort and then together with the join. But it is not working, ...
Augusto II's user avatar
-1 votes
2 answers
63 views

How to join two files

I want to join two files, and I must use the command join. This is first file file1.txt SUBJID SEX DTHHRDY GTEX-1117F 2 4 GTEX-111CU 1 0 GTEX-111FC 1 1 GTEX-1122O 2 4 This is the second ...
Vane Almache's user avatar
3 votes
2 answers
6k views

Merge multiple files by first column

My question is similar to this one: Merge multiple columns based on the first column values I have multiple files (10+) that I want to merge/join into one output file, for example: file 1 2000 0....
pascal's user avatar
  • 161
0 votes
1 answer
187 views

Merging two files based on a common column; add 0 for variants not present in file 2, and keep the original value in those which are present

I would like to merge two files, which share some common data. File 1 includes more than file 2. I want to merge the files based on their shared column (3) following the order of file 1 and I want to ...
kllrdr's user avatar
  • 71
1 vote
1 answer
70 views

merging two files according to a common column and add 0 to those which are not present in file 2

I would like to merge two files, which share some common data. File 1 includes more than file 2. I want to merge the files based on their shared column following the order of file 1 and I want to add ...
kllrdr's user avatar
  • 71
2 votes
1 answer
507 views

how to fetch ansible inventory & populate in an ansible variable?

I have an invnentory file with a group of servers defined [prod] prod1 ansible_host=10.10.2.30 prod2 ansible_host=10.10.2.40 prod3 ansible_host=10.10.2.50 prod4 ansible_host=10.10.2.60 I want to ...
Sollosa's user avatar
  • 1,941
1 vote
1 answer
187 views

Compare second column of two text files and print first columns of both files if match

I am trying to compare second columns of two text files and print the first columns of both files if match. I have tried the below awk codes but it was no use 1) awk 'NR==FNR {a[$2]=$2; next} {print $...
ash's user avatar
  • 23
-3 votes
1 answer
70 views

Join lines of text with repeated beginning with numbers

I was wondering if someone can help me out here as I'm trying to apply nothing but "sed with regular expression" to an XML file. I feel the approach is similar in nature. I would like to use ...
wayne001's user avatar
0 votes
1 answer
733 views

How to selectively join lines starting with a pattern [duplicate]

This post may be looked upon as a followup to a previous post. This is input file: module ABC x(a,b,c) module DEFG y(d,e,f, g,h,i) module HIJKL z(j,k,l) And output file should be: module ...
Sandeep's user avatar
  • 139
0 votes
1 answer
74 views

How to merge multiple files which are not in order and assign the name of file to a specific column in linux?

I have around 250 .gtf files. Here I'm showing 3 .gtf files. All the .gtf files have three columns that look like below: TUSCC120A.gtf TUSCC36.gtf TUSCC89B.gtf TUSCC120A.gtf looks like below: ...
beginner's user avatar
  • 277
2 votes
2 answers
546 views

Looking up values in one table and outputting it into another using join/awk

Table 1 (tab separated): NC_000001.11 1243 A T 0.14 NC_000005.11 1432 G C 0.0006 NC_000012.12 1284 A T 0.93428 NC_000021.9 9824 T C 0.9 Lookup table (tab separated) - this is in fact huge, around ...
tacrolimus's user avatar
0 votes
1 answer
107 views

How to join two files in linux?

I want to join two files on a Linux machine. I want to join the lines only included in the first file. First file is unzipped file (no header, only one column). 1_4 3_4 4_63 6_2 Second file is gz ...
user10345633's user avatar
3 votes
2 answers
498 views

Print Arrays Without Duplicates in AWK

I am looking to compare two files, printing only records with a matching ID number and without duplicate records. I have two files: file1.txt contains: Simons 0987768798980 West 09809867678 Vickers ...
Bumbling Badger's user avatar
0 votes
1 answer
139 views

How to get all the matches from a file based on names in another file? [duplicate]

I have a file named file1 with information like below: TCONS_00000011 XLOC_000003 - u q1:MSTRG.39|MSTRG.39.9|4|0.000000|0.000000|0.000000|7468 TCONS_00000012 XLOC_000004 - ...
maven's user avatar
  • 11
1 vote
0 answers
664 views

Using awk to join on multiple fields

I have file1 as follows: 2010,92614,0,1 2010,92614,4,1 and file2 as follows: 2010,0,907894 2010,1,17788 2010,2,2827 2010,3,1212 2010,4,669914 I would like to join the two files on fields 1 and 3 of ...
Mishal Ahmed's user avatar
1 vote
2 answers
250 views

Merge two file skip last row of 1 file with awk

I'm trying to join two files removing duplicate head row and take only 1 last row for example: File1.txt head1 data1 data2 tail8 File2.txt head1 data3 data4 tail9 results desired in file3.txt: head1 ...
Darioit's user avatar
  • 15
-3 votes
1 answer
78 views

join f1 to f2 so that each join-able f1 record only joins once to the first available match-able in f2

Given the following two files which are sorted by column 1 and 2: f1: card1,value1,f1c11,f1c12 card2,value2,f1c21,f1c22 card3,value3,f1c31,f1c32 card4,value4,f1c41,f1c42 card4,value4,f1c411,f1c412 ...
Henry P's user avatar
4 votes
1 answer
2k views

Joining two sorted files gives error: join: <file>:112855: is not sorted:

I would like to merge a variable from one file to another in linux. The first variable contains the name I want to merge files on. I have sorted both files using both -f and -k: sort -f -k 1,1 SCZ.N....
LauraW's user avatar
  • 43
1 vote
1 answer
202 views

How to join results of two commands in IBM AIX ksh

I would like to perform script below but without creation of intermediate files (lsfs.out, df.out) on IBM AIX with ksh lsfs_out=`lsfs | sed -n '1d;p' | sort -b -k 3` df_out=`df -k | sed -n '1d;p' | ...
Prokhozhii's user avatar
0 votes
1 answer
82 views

How can I handle two separate file part as one (mount, virtual join, etc)? [duplicate]

I have tried to download a 8GB ISO image file. Unfortunately the file system was FAT32 on the pendrive. Max file size was only 4GB. The download process was aborted at a point, but as I used wget for ...
Konstantin's user avatar
1 vote
2 answers
806 views

Is it possible to use grep or awk to report duplicate output lines corresponding to repeating entries in an index file?

I can't seem to get either grep or awk to do a relatively simple index pull of a list. I suspect it's because of adjacent duplicates in the index file, something I wouldn't have thought would cause an ...
amrezans's user avatar
2 votes
3 answers
1k views

How to do anti-join or inverse join in bash

I want to perform what some data analysis software call an anti-join: remove from one list those lines matching lines in another list. Here is some toy data and the expected output: $ echo -e "a\nb\...
Josh's user avatar
  • 303
1 vote
0 answers
99 views

"join: file 2 is not in sorted order" only when writing to file

I hade to join two files where some processing is done on 2nd file on-the-fly (sorting, removing first row), then some processing is done on the resulting file and it is finally written on disk. join ...
David Shor's user avatar
0 votes
2 answers
187 views

vlookup function in unix with columns changed

Is it possible to send the values as stored in variables instead of file2 file1. Kindly let me know how to pass the variables in the above-mentioned query. I tried to pass the variables into the query,...
Kameshwaran's user avatar
0 votes
1 answer
567 views

specific case where sort and join are not working

I have an issue joining the two files linked below using join -t $'\t' -a1 file_1 file_2. I'm aware that one has to use specific flags when sorting in order to make join work, as explained in this and ...
PejoPhylo's user avatar
  • 345
5 votes
3 answers
764 views

Joining multiple column from different file using awk

I have 4 tsv (tab separated) files that look like this: file_1: abc 1 def 2 ghi 3 file_2: abc 2 ghi 3 file_3: def 1 ghi 2 jkl 4 file_4: ghi 3 jkl 4 I want to join those file to get 1 tsv file ...
Lulu' Nisrina's user avatar
2 votes
3 answers
778 views

Merge columns within a file based on column header

I want to merge different columns within my file that share the same column header. The file looks like this and can be tab-separated or something else: AaBbN CcDdEeN FfN AaBbN FfN 1 ...
And's user avatar
  • 167
1 vote
2 answers
47 views

Merge following portions of lines into current line in a 3 column file

I have a text file with format word @@@ type @@@ sentence on every line, sorted by 'word' in ascending order. Some lines however are not unique and they begin with the same word as the previous line, ...
andrewz's user avatar
  • 153

1
2 3 4 5 6