Joining two csv files on common column and removing the second last column
Clash Royale CLAN TAG#URR8PPP
up vote
1
down vote
favorite
I have two csv files:
file1:
C1, 1, 0, 1, 0, 1
C2, 1, 0, 1, 1, 0
C3, 0, 0, 1, 1, 0
file2:
C3, 1.2
C1, 2.3
C2, 1.8
I want to merge these two files based on C column (which produces):
C1, 1, 0, 1, 0, 1, 2.3
C2, 1, 0, 1, 1, 0, 1.8
C3, 0, 0, 1, 1, 0, 1.2
And then remove the second last column (to produce):
C1, 1, 0, 1, 0, 2.3
C2, 1, 0, 1, 1, 1.8
C3, 0, 0, 1, 1, 1.2
shell-script awk join merge
asked yesterday
Coder
1106
add a comment |
up vote
1
down vote
favorite
I have two csv files:
file1:
C1, 1, 0, 1, 0, 1
C2, 1, 0, 1, 1, 0
C3, 0, 0, 1, 1, 0
file2:
C3, 1.2
C1, 2.3
C2, 1.8
I want to merge these two files based on C column (which produces):
C1, 1, 0, 1, 0, 1, 2.3
C2, 1, 0, 1, 1, 0, 1.8
C3, 0, 0, 1, 1, 0, 1.2
And then remove the second last column (to produce):
C1, 1, 0, 1, 0, 2.3
C2, 1, 0, 1, 1, 1.8
C3, 0, 0, 1, 1, 1.2
shell-script awk join merge
asked yesterday
Coder
1106
What did you try? Post your own efforts to the question
– Inian
yesterday
@Inian My try was this: (1) sort two files based on the common column, (2) apply join on this column (3) then awk all the columns except the second last.
– Coder
yesterday
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have two csv files:
file1:
C1, 1, 0, 1, 0, 1
C2, 1, 0, 1, 1, 0
C3, 0, 0, 1, 1, 0
file2:
C3, 1.2
C1, 2.3
C2, 1.8
I want to merge these two files based on C column (which produces):
C1, 1, 0, 1, 0, 1, 2.3
C2, 1, 0, 1, 1, 0, 1.8
C3, 0, 0, 1, 1, 0, 1.2
And then remove the second last column (to produce):
C1, 1, 0, 1, 0, 2.3
C2, 1, 0, 1, 1, 1.8
C3, 0, 0, 1, 1, 1.2
shell-script awk join merge
asked yesterday
Coder
1106
I have two csv files:
file1:
C1, 1, 0, 1, 0, 1
C2, 1, 0, 1, 1, 0
C3, 0, 0, 1, 1, 0
file2:
C3, 1.2
C1, 2.3
C2, 1.8
I want to merge these two files based on C column (which produces):
C1, 1, 0, 1, 0, 1, 2.3
C2, 1, 0, 1, 1, 0, 1.8
C3, 0, 0, 1, 1, 0, 1.2
And then remove the second last column (to produce):
C1, 1, 0, 1, 0, 2.3
C2, 1, 0, 1, 1, 1.8
C3, 0, 0, 1, 1, 1.2
shell-script awk join merge
shell-script awk join merge
asked yesterday
Coder
1106
asked yesterday
Coder
1106
asked yesterday
Coder
1106
asked yesterday
Coder
1106
asked yesterday
Coder
1106
1106
What did you try? Post your own efforts to the question
– Inian
yesterday
@Inian My try was this: (1) sort two files based on the common column, (2) apply join on this column (3) then awk all the columns except the second last.
– Coder
yesterday
add a comment |
What did you try? Post your own efforts to the question
– Inian
yesterday
@Inian My try was this: (1) sort two files based on the common column, (2) apply join on this column (3) then awk all the columns except the second last.
– Coder
yesterday
What did you try? Post your own efforts to the question
– Inian
yesterday
What did you try? Post your own efforts to the question
– Inian
yesterday
@Inian My try was this: (1) sort two files based on the common column, (2) apply join on this column (3) then awk all the columns except the second last.
– Coder
yesterday
@Inian My try was this: (1) sort two files based on the common column, (2) apply join on this column (3) then awk all the columns except the second last.
– Coder
yesterday
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
You just have create a hash-map on the second file on the C column and use that on the first file as below. The actions right next FNR==NR applies to the first file specified at the end and the subsequent action happens on the last file. This is because of the special variables in awk, FNR and NR which track line numbers per file and across the files respectively.
awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]; 1' file2 file1
answered yesterday
Inian
3,745823
This works: awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]"," $NF; 1' file2 file1 (It prints that second last column after merging as the last column.)
– Coder
yesterday
add a comment |
up vote
1
down vote
Try also
join -t, -o1.1,1.2,1.3,1.4,1.5,2.2 <(sort file1) <(sort file2)
answered yesterday
RudiC
3,0411211
It will work. But, when I have more columns, it has to be something else. Can you guide?
– Coder
yesterday
Be more specific.
– RudiC
yesterday
let us say file 1 has 100 columns and file 2 has 20.
– Coder
23 hours ago
While in principle that might be doable it will be quite some typing effort. Did you consider acutapproach?
– RudiC
18 hours ago
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
You just have create a hash-map on the second file on the C column and use that on the first file as below. The actions right next FNR==NR applies to the first file specified at the end and the subsequent action happens on the last file. This is because of the special variables in awk, FNR and NR which track line numbers per file and across the files respectively.
awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]; 1' file2 file1
answered yesterday
Inian
3,745823
This works: awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]"," $NF; 1' file2 file1 (It prints that second last column after merging as the last column.)
– Coder
yesterday
add a comment |
up vote
1
down vote
accepted
You just have create a hash-map on the second file on the C column and use that on the first file as below. The actions right next FNR==NR applies to the first file specified at the end and the subsequent action happens on the last file. This is because of the special variables in awk, FNR and NR which track line numbers per file and across the files respectively.
awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]; 1' file2 file1
answered yesterday
Inian
3,745823
This works: awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]"," $NF; 1' file2 file1 (It prints that second last column after merging as the last column.)
– Coder
yesterday
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
You just have create a hash-map on the second file on the C column and use that on the first file as below. The actions right next FNR==NR applies to the first file specified at the end and the subsequent action happens on the last file. This is because of the special variables in awk, FNR and NR which track line numbers per file and across the files respectively.
awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]; 1' file2 file1
answered yesterday
Inian
3,745823
You just have create a hash-map on the second file on the C column and use that on the first file as below. The actions right next FNR==NR applies to the first file specified at the end and the subsequent action happens on the last file. This is because of the special variables in awk, FNR and NR which track line numbers per file and across the files respectively.
awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]; 1' file2 file1
answered yesterday
Inian
3,745823
answered yesterday
Inian
3,745823
answered yesterday
Inian
3,745823
answered yesterday
Inian
3,745823
3,745823
This works: awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]"," $NF; 1' file2 file1 (It prints that second last column after merging as the last column.)
– Coder
yesterday
add a comment |
This works: awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]"," $NF; 1' file2 file1 (It prints that second last column after merging as the last column.)
– Coder
yesterday
This works: awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]"," $NF; 1' file2 file1 (It prints that second last column after merging as the last column.)
– Coder
yesterday
This works: awk -v FS="," -v OFS="," 'FNR==NR unique[$1]=$2; next $1 in unique $NF=unique[$1]"," $NF; 1' file2 file1 (It prints that second last column after merging as the last column.)
– Coder
yesterday
add a comment |
up vote
1
down vote
Try also
join -t, -o1.1,1.2,1.3,1.4,1.5,2.2 <(sort file1) <(sort file2)
answered yesterday
RudiC
3,0411211
It will work. But, when I have more columns, it has to be something else. Can you guide?
– Coder
yesterday
Be more specific.
– RudiC
yesterday
let us say file 1 has 100 columns and file 2 has 20.
– Coder
23 hours ago
While in principle that might be doable it will be quite some typing effort. Did you consider acutapproach?
– RudiC
18 hours ago
add a comment |
up vote
1
down vote
Try also
join -t, -o1.1,1.2,1.3,1.4,1.5,2.2 <(sort file1) <(sort file2)
answered yesterday
RudiC
3,0411211
It will work. But, when I have more columns, it has to be something else. Can you guide?
– Coder
yesterday
Be more specific.
– RudiC
yesterday
let us say file 1 has 100 columns and file 2 has 20.
– Coder
23 hours ago
While in principle that might be doable it will be quite some typing effort. Did you consider acutapproach?
– RudiC
18 hours ago
add a comment |
up vote
1
down vote
up vote
1
down vote
Try also
join -t, -o1.1,1.2,1.3,1.4,1.5,2.2 <(sort file1) <(sort file2)
answered yesterday
RudiC
3,0411211
Try also
join -t, -o1.1,1.2,1.3,1.4,1.5,2.2 <(sort file1) <(sort file2)
answered yesterday
RudiC
3,0411211
answered yesterday
RudiC
3,0411211
answered yesterday
RudiC
3,0411211
answered yesterday
RudiC
3,0411211
3,0411211
It will work. But, when I have more columns, it has to be something else. Can you guide?
– Coder
yesterday
Be more specific.
– RudiC
yesterday
let us say file 1 has 100 columns and file 2 has 20.
– Coder
23 hours ago
While in principle that might be doable it will be quite some typing effort. Did you consider acutapproach?
– RudiC
18 hours ago
add a comment |
It will work. But, when I have more columns, it has to be something else. Can you guide?
– Coder
yesterday
Be more specific.
– RudiC
yesterday
let us say file 1 has 100 columns and file 2 has 20.
– Coder
23 hours ago
While in principle that might be doable it will be quite some typing effort. Did you consider acutapproach?
– RudiC
18 hours ago
It will work. But, when I have more columns, it has to be something else. Can you guide?
– Coder
yesterday
It will work. But, when I have more columns, it has to be something else. Can you guide?
– Coder
yesterday
Be more specific.
– RudiC
yesterday
Be more specific.
– RudiC
yesterday
let us say file 1 has 100 columns and file 2 has 20.
– Coder
23 hours ago
let us say file 1 has 100 columns and file 2 has 20.
– Coder
23 hours ago
While in principle that might be doable it will be quite some typing effort. Did you consider a
cut approach?– RudiC
18 hours ago
While in principle that might be doable it will be quite some typing effort. Did you consider a
cut approach?– RudiC
18 hours ago
add a comment |
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%2f481855%2fjoining-two-csv-files-on-common-column-and-removing-the-second-last-column%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
What did you try? Post your own efforts to the question
– Inian
yesterday
@Inian My try was this: (1) sort two files based on the common column, (2) apply join on this column (3) then awk all the columns except the second last.
– Coder
yesterday