How to merge two files of different lines and column and output matching lines with colums?
Clash Royale CLAN TAG#URR8PPP
up vote
3
down vote
favorite
I have file1 (sample):
60108903374
60172485121
60108919381
60174213128
60108919951
60108919970
601112020106
601112020107
601112020108
601112020113
601112020114
60175472940
And file2:
60179970001,A
60172681920,A
60174202041,A
60172514180,A
60174314679,A
60174325306,A
60175472940,A
60174213128,A
60175328984,A
60175349857,A
60172796759,A
60172798922,A
60179195129,A
60172485121,B
60173483126,A
60172683175,A
60174521828,A
60142536314,B
60175347909,B
60175183031,B
I want to merge file1 and file2 with output matching based on the first column as well as shows the second column from file2.
Desired output:
60172485121,B
60174213128,A
file1 has ~80k lines and file2 has 500k lines.
Tried using:
join -1 1 -2 1 -o 1.1,2.2 file1 file2
text-processing join
edited Nov 17 at 0:31
Rui F Ribeiro
38.2k1475123
asked Sep 6 '16 at 13:46
Johan Hakim Fahmi
444
add a comment |
up vote
3
down vote
favorite
I have file1 (sample):
60108903374
60172485121
60108919381
60174213128
60108919951
60108919970
601112020106
601112020107
601112020108
601112020113
601112020114
60175472940
And file2:
60179970001,A
60172681920,A
60174202041,A
60172514180,A
60174314679,A
60174325306,A
60175472940,A
60174213128,A
60175328984,A
60175349857,A
60172796759,A
60172798922,A
60179195129,A
60172485121,B
60173483126,A
60172683175,A
60174521828,A
60142536314,B
60175347909,B
60175183031,B
I want to merge file1 and file2 with output matching based on the first column as well as shows the second column from file2.
Desired output:
60172485121,B
60174213128,A
file1 has ~80k lines and file2 has 500k lines.
Tried using:
join -1 1 -2 1 -o 1.1,2.2 file1 file2
text-processing join
edited Nov 17 at 0:31
Rui F Ribeiro
38.2k1475123
asked Sep 6 '16 at 13:46
Johan Hakim Fahmi
444
It is not important bu I think60175472940,Ais missing in the "desired output"
– JJoao
Sep 9 '16 at 9:17
add a comment |
up vote
3
down vote
favorite
up vote
3
down vote
favorite
I have file1 (sample):
60108903374
60172485121
60108919381
60174213128
60108919951
60108919970
601112020106
601112020107
601112020108
601112020113
601112020114
60175472940
And file2:
60179970001,A
60172681920,A
60174202041,A
60172514180,A
60174314679,A
60174325306,A
60175472940,A
60174213128,A
60175328984,A
60175349857,A
60172796759,A
60172798922,A
60179195129,A
60172485121,B
60173483126,A
60172683175,A
60174521828,A
60142536314,B
60175347909,B
60175183031,B
I want to merge file1 and file2 with output matching based on the first column as well as shows the second column from file2.
Desired output:
60172485121,B
60174213128,A
file1 has ~80k lines and file2 has 500k lines.
Tried using:
join -1 1 -2 1 -o 1.1,2.2 file1 file2
text-processing join
edited Nov 17 at 0:31
Rui F Ribeiro
38.2k1475123
asked Sep 6 '16 at 13:46
Johan Hakim Fahmi
444
I have file1 (sample):
60108903374
60172485121
60108919381
60174213128
60108919951
60108919970
601112020106
601112020107
601112020108
601112020113
601112020114
60175472940
And file2:
60179970001,A
60172681920,A
60174202041,A
60172514180,A
60174314679,A
60174325306,A
60175472940,A
60174213128,A
60175328984,A
60175349857,A
60172796759,A
60172798922,A
60179195129,A
60172485121,B
60173483126,A
60172683175,A
60174521828,A
60142536314,B
60175347909,B
60175183031,B
I want to merge file1 and file2 with output matching based on the first column as well as shows the second column from file2.
Desired output:
60172485121,B
60174213128,A
file1 has ~80k lines and file2 has 500k lines.
Tried using:
join -1 1 -2 1 -o 1.1,2.2 file1 file2
text-processing join
text-processing join
edited Nov 17 at 0:31
Rui F Ribeiro
38.2k1475123
asked Sep 6 '16 at 13:46
Johan Hakim Fahmi
444
edited Nov 17 at 0:31
Rui F Ribeiro
38.2k1475123
asked Sep 6 '16 at 13:46
Johan Hakim Fahmi
444
edited Nov 17 at 0:31
Rui F Ribeiro
38.2k1475123
edited Nov 17 at 0:31
Rui F Ribeiro
38.2k1475123
edited Nov 17 at 0:31
Rui F Ribeiro
38.2k1475123
38.2k1475123
asked Sep 6 '16 at 13:46
Johan Hakim Fahmi
444
asked Sep 6 '16 at 13:46
Johan Hakim Fahmi
444
asked Sep 6 '16 at 13:46
Johan Hakim Fahmi
444
444
It is not important bu I think60175472940,Ais missing in the "desired output"
– JJoao
Sep 9 '16 at 9:17
add a comment |
It is not important bu I think60175472940,Ais missing in the "desired output"
– JJoao
Sep 9 '16 at 9:17
It is not important bu I think
60175472940,A is missing in the "desired output"– JJoao
Sep 9 '16 at 9:17
It is not important bu I think
60175472940,A is missing in the "desired output"– JJoao
Sep 9 '16 at 9:17
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
accepted
join -t, <(sort file1) <(sort -t, file2)
The above does the job.
edited Sep 6 '16 at 15:18
Scott
6,57142650
answered Sep 6 '16 at 14:50
Johan Hakim Fahmi
444
add a comment |
up vote
0
down vote
awk -F, 'NF==1 a[$1]++;
NF>=2 && a[$1]' file1 file2
- if we have just one field, save it
- if we have more and the first field is saved, print it
output obtained:
60175472940,A
60174213128,A
60172485121,B
answered Sep 6 '16 at 15:55
JJoao
6,9441826
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
join -t, <(sort file1) <(sort -t, file2)
The above does the job.
edited Sep 6 '16 at 15:18
Scott
6,57142650
answered Sep 6 '16 at 14:50
Johan Hakim Fahmi
444
add a comment |
up vote
2
down vote
accepted
join -t, <(sort file1) <(sort -t, file2)
The above does the job.
edited Sep 6 '16 at 15:18
Scott
6,57142650
answered Sep 6 '16 at 14:50
Johan Hakim Fahmi
444
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
join -t, <(sort file1) <(sort -t, file2)
The above does the job.
edited Sep 6 '16 at 15:18
Scott
6,57142650
answered Sep 6 '16 at 14:50
Johan Hakim Fahmi
444
join -t, <(sort file1) <(sort -t, file2)
The above does the job.
edited Sep 6 '16 at 15:18
Scott
6,57142650
answered Sep 6 '16 at 14:50
Johan Hakim Fahmi
444
edited Sep 6 '16 at 15:18
Scott
6,57142650
edited Sep 6 '16 at 15:18
Scott
6,57142650
edited Sep 6 '16 at 15:18
Scott
6,57142650
6,57142650
answered Sep 6 '16 at 14:50
Johan Hakim Fahmi
444
answered Sep 6 '16 at 14:50
Johan Hakim Fahmi
444
answered Sep 6 '16 at 14:50
Johan Hakim Fahmi
444
444
add a comment |
add a comment |
up vote
0
down vote
awk -F, 'NF==1 a[$1]++;
NF>=2 && a[$1]' file1 file2
- if we have just one field, save it
- if we have more and the first field is saved, print it
output obtained:
60175472940,A
60174213128,A
60172485121,B
answered Sep 6 '16 at 15:55
JJoao
6,9441826
add a comment |
up vote
0
down vote
awk -F, 'NF==1 a[$1]++;
NF>=2 && a[$1]' file1 file2
- if we have just one field, save it
- if we have more and the first field is saved, print it
output obtained:
60175472940,A
60174213128,A
60172485121,B
answered Sep 6 '16 at 15:55
JJoao
6,9441826
add a comment |
up vote
0
down vote
up vote
0
down vote
awk -F, 'NF==1 a[$1]++;
NF>=2 && a[$1]' file1 file2
- if we have just one field, save it
- if we have more and the first field is saved, print it
output obtained:
60175472940,A
60174213128,A
60172485121,B
answered Sep 6 '16 at 15:55
JJoao
6,9441826
awk -F, 'NF==1 a[$1]++;
NF>=2 && a[$1]' file1 file2
- if we have just one field, save it
- if we have more and the first field is saved, print it
output obtained:
60175472940,A
60174213128,A
60172485121,B
answered Sep 6 '16 at 15:55
JJoao
6,9441826
answered Sep 6 '16 at 15:55
JJoao
6,9441826
answered Sep 6 '16 at 15:55
JJoao
6,9441826
answered Sep 6 '16 at 15:55
JJoao
6,9441826
6,9441826
add a comment |
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f308201%2fhow-to-merge-two-files-of-different-lines-and-column-and-output-matching-lines-w%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
It is not important bu I think
60175472940,Ais missing in the "desired output"– JJoao
Sep 9 '16 at 9:17