0

I have two large csv files that share the same first column and first row (header) as follow: etc means more values with same pattern.

file1.csv

names,text1,text2,text3,etc
A,1,4,3 ...
B,5,2,8 ...
C,3,7,4 ...
D,9,1,3 ...
etc

file2.csv

names,text1,test2,text3,etc
A,7,2,9 ...
B,3,0,6 ...
C,8,7,2 ...
D,1,5,6 ...
etc

I would like a code/script to combine columns (with matching headers or column #)from file1.csv and file2.csv in addition to the first column from file1.csv and output them in new files named based on the header.

So it would give the following output files and so on:

text1.csv

names,text1,text1
A,1,7
B,5,3
C,3,8
D,9,1
etc

text2.csv

names,text2,text2
A,4,2
B,2,0
C,7,7
D,1,5
etc

text3.csv

names,text3,text3
A,3,9
B,8,6
C,4,2
D,3,6
etc

4 Answers 4

2

Assuming both files are sorted (excluding the header line), as per your examples, the following should work:

column=2
until [[ $column > $(awk -F, '{ print NF; exit }' file1.csv) ]] ; do 
  join -t , -o 1.1,1.$column,2.$column file1.csv file2.csv > $(awk -F, '{ print $'$column'; exit }' file1.csv).csv 
  ((column++))
done

(Borrowed a snippet from @janos in this reply.)

Most of the magic here is done by join; output files will be written in current directory.

2

This is similar to miguelsvieira’s answer, but using Bash to count the columns and get the headers:

IFS=, read -a headers < file1.csv
column=0
for h in "${headers[@]}"
do
        if [ "$((++column))" = 1 ]
        then
                continue
        fi
        join -t, -o "0,1.$column,2.$column" file1.csv file2.csv > "$h".csv
done

Output:

text1.csv

names,text1,text1
A,1,7
B,5,3
C,3,8
D,9,1

text2.csv

names,text2,test2
A,4,2
B,2,0
C,7,7
D,1,5

text3.csv

names,text3,text3
A,3,9
B,8,6
C,4,2
D,3,6

Notes:

  • read -a reads the first line of the file into an array.
  • The column variable takes on the values 1, 2, 3, 4, …  Do no processing when column is 1 because we don’t want to create a names.csv output file.
  • For the data columns, run join, joining on the first column (the default), outputting that column and the column number column from each file.  This part is almost identical to miguelsvieira’s answer.
  • Use the headers array (i.e., the fields of the first line of file1.csv) to name the output files.

As with miguelsvieira’s answer and RudiC’s answer, this assumes

  • the files have the same number of columns (ideally, they should match)
  • the files have the same number of lines (rows) (they should also match)

and works best if the files are sorted (but it might work if they aren’t, provided they are in the same order).  And, again, this must be done in Bash.

1

Try also

join -t, --header  file[12] | awk -F, -vOFS=, '
        {D = (NF-1)/2
         for (i=2; i<=D+1; i++) {if (FNR==1) FN[i] = $i
                                 print $1, $i, $(i+D)  >  FN[i] ".csv"
                                }
        }
'
cf te*

---------- text1.csv: ----------

names,text1,text1
A,1,7
B,5,3
C,3,8
D,9,1

---------- text2.csv: ----------

names,text2,test2
A,4,2
B,2,0
C,7,7
D,1,5

---------- text3.csv: ----------

names,text3,text3
A,3 ...,9 ...
B,8 ...,6 ...
C,4 ...,2 ...
D,3 ...,6 ...

This depends on input files having the same count of lines and fields, so join can do its part correctly (given it offers the --header option), and awk can calculate the number of iterations. Then it's just a matter of looping through the fields and print them to the relevant files, whose names have been captured in the first line.

2
  • What is cf? Commented Apr 1, 2022 at 3:13
  • @G-Man Says 'Reinstate Monica: an alias to "cat files".
    – RudiC
    Commented Apr 1, 2022 at 7:08
-1
awk -F "," 'NR==FNR{a[FNR]=$2;b[$1]++;next}($1 in b){print $1,a[FNR],$2}' file1 file2

output

file1
names text1 text1
A 1 7
B 5 3
C 3 8
D 9 1
awk -F "," 'NR==FNR{a[FNR]=$3;b[$1]++;next}($1 in b){print $1,a[FNR],$3}' file1 file2

output

file2
names text2 test2
A 4 2
B 2 0
C 7 7
D 1 5
 awk -F "," 'NR==FNR{a[FNR]=$4;b[$1]++;next}($1 in b){print $1,a[FNR],$4}' file1 file2

output

file3
names text3 text3
A 3  9
B 8 6
C 4 2
D 3 6

You must log in to answer this question.

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