Questions tagged [join]
Join several lines in a single one, inside a single file or from several files.
248
questions
0
votes
2
answers
1k
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,...
- 25
1
vote
2
answers
60
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....
- 23
1
vote
1
answer
42
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 ...
- 2,511
1
vote
2
answers
293
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,
...
- 13
-2
votes
2
answers
117
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:
...
- 249
2
votes
1
answer
430
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 ...
- 161
0
votes
3
answers
199
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;...
- 1
0
votes
1
answer
402
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 ...
- 1
1
vote
1
answer
832
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,...
- 1,833
1
vote
2
answers
667
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 ...
- 13
0
votes
1
answer
360
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
|______ ...
- 269
0
votes
2
answers
843
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 ...
- 277
0
votes
1
answer
214
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
...
- 117
0
votes
3
answers
225
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 ...
- 353
0
votes
1
answer
126
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 ...
- 79
0
votes
2
answers
88
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 ...
- 11
1
vote
3
answers
147
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 ...
0
votes
1
answer
438
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, ...
-1
votes
2
answers
57
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 ...
3
votes
2
answers
4k
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....
- 161
0
votes
1
answer
174
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 ...
- 71
1
vote
1
answer
66
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 ...
- 71
1
vote
1
answer
316
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 ...
- 1,833
1
vote
1
answer
131
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 $...
- 23
-3
votes
1
answer
68
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 ...
- 1
0
votes
1
answer
228
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 ...
- 137
0
votes
1
answer
68
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:
...
- 269
2
votes
2
answers
423
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 ...
- 123
0
votes
1
answer
83
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 ...
- 353
3
votes
2
answers
360
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 ...
- 345
0
votes
1
answer
115
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 - ...
- 11
1
vote
0
answers
469
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 ...
- 105
1
vote
2
answers
181
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
...
- 15
-3
votes
1
answer
57
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
...
- 1
4
votes
1
answer
1k
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....
- 43
1
vote
1
answer
170
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' | ...
- 115
0
votes
1
answer
73
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 ...
- 141
1
vote
2
answers
395
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 ...
- 13
2
votes
2
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\...
- 293
1
vote
0
answers
89
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 ...
- 11
0
votes
2
answers
151
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,...
0
votes
1
answer
350
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 ...
- 325
5
votes
3
answers
634
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 ...
2
votes
3
answers
616
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 ...
- 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, ...
- 153
0
votes
1
answer
96
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 '...
- 237
1
vote
1
answer
361
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 ...
- 1,716
1
vote
2
answers
150
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.
...
- 13
0
votes
1
answer
204
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,...
1
vote
2
answers
1k
views
How to join 2 files based one key and selected some specific column?
I have 2 files and want to inner join them using awk. This is written using sql server :
SELECT [file1.column1],[file2.column2] FROM file1
INNER JOIN file2 on file2.column1 = file1.column5;
This is ...
- 13