1

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
2
  • What did you try? Post your own efforts to the question
    – Inian
    Commented Nov 15, 2018 at 6:35
  • @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
    Commented Nov 15, 2018 at 8:57

2 Answers 2

1

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
1
  • 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
    Commented Nov 15, 2018 at 9:56
1

Try also

join -t, -o1.1,1.2,1.3,1.4,1.5,2.2 <(sort file1) <(sort file2)
4
  • It will work. But, when I have more columns, it has to be something else. Can you guide?
    – Coder
    Commented Nov 15, 2018 at 10:45
  • Be more specific.
    – RudiC
    Commented Nov 15, 2018 at 10:48
  • let us say file 1 has 100 columns and file 2 has 20.
    – Coder
    Commented Nov 16, 2018 at 2:56
  • While in principle that might be doable it will be quite some typing effort. Did you consider a cut approach?
    – RudiC
    Commented Nov 16, 2018 at 8:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .