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
1 answer
95 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,...
user avatar
  • 25
1 vote
2 answers
48 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....
user avatar
1 vote
1 answer
25 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 ...
user avatar
  • 2,281
1 vote
2 answers
44 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, ...
user avatar
-2 votes
2 answers
73 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: ...
user avatar
  • 239
2 votes
1 answer
62 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 ...
user avatar
  • 161
0 votes
0 answers
27 views

How to join two files into one in multiple folders using specific columns and add file names where they originated from

I would like to ask, how can I join multiple files into one using a specific column and name it by the originating folder. Let me explain: I have the following files, having multiple columns and rows: ...
user avatar
  • 5
0 votes
3 answers
43 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;...
user avatar
  • 1
0 votes
1 answer
112 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 ...
user avatar
  • 1
1 vote
1 answer
75 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,...
user avatar
  • 1,621
1 vote
1 answer
186 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 ...
user avatar
  • 13
0 votes
1 answer
152 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 |______ ...
user avatar
  • 245
0 votes
2 answers
169 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 ...
user avatar
0 votes
1 answer
75 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 ...
user avatar
  • 115
0 votes
3 answers
129 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 ...
user avatar
0 votes
1 answer
61 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 ...
user avatar
  • 79
0 votes
2 answers
61 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 ...
user avatar
  • 11
1 vote
2 answers
67 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 ...
user avatar
0 votes
1 answer
119 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, ...
user avatar
-1 votes
2 answers
49 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 ...
user avatar
3 votes
2 answers
1k 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....
user avatar
  • 161
0 votes
1 answer
84 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 ...
user avatar
  • 71
1 vote
1 answer
61 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 ...
user avatar
  • 71
1 vote
1 answer
70 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 ...
user avatar
  • 1,621
1 vote
1 answer
82 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 $...
user avatar
  • 23
-3 votes
1 answer
65 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 ...
user avatar
0 votes
1 answer
73 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 ...
user avatar
  • 137
0 votes
1 answer
50 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: ...
user avatar
  • 245
2 votes
2 answers
252 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 ...
user avatar
0 votes
1 answer
50 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 ...
user avatar
3 votes
2 answers
282 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 ...
user avatar
0 votes
1 answer
58 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 - ...
user avatar
  • 11
1 vote
0 answers
272 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 ...
user avatar
1 vote
2 answers
78 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 ...
user avatar
  • 15
-3 votes
1 answer
46 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 ...
user avatar
4 votes
1 answer
629 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....
user avatar
  • 43
1 vote
1 answer
129 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' | ...
user avatar
0 votes
1 answer
64 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 ...
user avatar
1 vote
2 answers
149 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 ...
user avatar
2 votes
2 answers
779 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\...
user avatar
  • 283
1 vote
0 answers
62 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 ...
user avatar
0 votes
2 answers
110 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,...
user avatar
0 votes
1 answer
181 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 ...
user avatar
  • 285
5 votes
3 answers
569 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 ...
user avatar
2 votes
3 answers
485 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 ...
user avatar
  • 167
1 vote
2 answers
43 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, ...
user avatar
  • 153
0 votes
1 answer
80 views

join csv with many fields without disturbing order

I have 2 csv files. The first file is big (>400 fields and many rows > 1 mil) and needs to have another field appended to it, via a matched join. I want to join on a field $170 I have tried gawk '...
user avatar
  • 237
0 votes
1 answer
170 views

The "join" utility reports: file is not sorted, but in fact it is sorted

I have two files t1 and t2. root@localhost:~# root@localhost:~# cat t1 udp UNCONN 0 0 0.0.0.0:68 0.0.0.0:* users:(("dhclient",pid=479,fd=7)) 479 tcp LISTEN 0 128 127.0.0.1:6060 ...
user avatar
1 vote
2 answers
94 views

Merge rows using common values in any column

I have a tab-delimited file like shown below, and would like to merge the rows based on matches in any of the columns. The number of columns are usually 2, but could vary in some cases and be 3. ...
user avatar
0 votes
1 answer
105 views

How to use awk for joining two csv using a key column and write a another csv

I have two CSV with following data, file1.csv:- path(S1),filename,timestamp(S1),memory(S1) xx/APS50CC2,APS50CC2,23-09-2019,45 yy/APS50CC3,APS50CC3,18-09-2019,25 file2.csv :- path(S2),filename,...
user avatar

1
2 3 4 5