Combine .csv-files with different amount of lines

Question

Here I asked already for a similar problem.

Now I have 3 .csv-files but one of them has only 1 line.

file1.csv

dog
cats
mouse

file2.csv

001a
002a
003c

file3.csv

WORD

The output should be

dog,001a,WORD
cats,002a,WORD
mouse,003c,WORD

One solution (from my previous question) is:

paste -d, file1.csv file2.csv | awk -F, '{print $1 "," $2 ",WORD"}'

But "WORD" is here just written and not read from file3.csv.

Is there a way to tell paste to fill every line with file3.csv? Or maybe to save file3.csv as a variable and give it to awk?

canupseq · Accepted Answer · 2023-02-12 22:27:48Z

2

Here is another simple method:

$ paste  -d, file1 file2 file3 | awk 'BEGIN{FS=OFS=","} {if(NR==1){w=$3} else {$3=w}}1'
dog,001a,WORD
cats,002a,WORD
mouse,003c,WORD

edited Feb 12, 2023 at 22:27

answered Feb 12, 2023 at 21:46

canupseq

2,0041 gold badge6 silver badges21 bronze badges

2

FWIW {if(NR==1){w=$3} else {$3=w}} could be written as NR==1{w=$3} {$3=w}

Ed Morton
– Ed Morton

2023-02-13 13:34:31 +00:00
Commented Feb 13, 2023 at 13:34

Add a comment |

Ed Morton · Accepted Answer · 2023-02-13 13:33:29Z

0

$ paste -d, file1.csv file2.csv |
    awk -v OFS=',' 'NR==FNR{w=$0; next} {print $0, w}' file3.csv -
dog,001a,WORD
cats,002a,WORD
mouse,003c,WORD

answered Feb 13, 2023 at 13:33

Ed Morton

36k6 gold badges25 silver badges60 bronze badges

Add a comment |

Kusalananda · Accepted Answer · 2023-02-25 18:20:40Z

Using the fill-down sub-command of Miller (mlr) we may fill all the empty fields with values from the immediately preceding record's corresponding field regardless of how long the files are.

The following command assumes that the data in each file is header-less CSV, and it will perform the fill-down operation on all three fields in each record:

paste -d, file1 file2 file3 | mlr -N --csv fill-down -f 1,2,3

Example run (note that I'm using a shorter second file):

$ cat file1
dog
cats
mouse
$ cat file2
001a
002a
$ cat file3
WORD

$ paste -d, file1 file2 file3
dog,001a,WORD
cats,002a,
mouse,,

$ paste -d, file1 file2 file3 | mlr -N --csv fill-down -f 1,2,3
dog,001a,WORD
cats,002a,WORD
mouse,002a,WORD

A variant which uses paste to create a TSV data set for Miller (by simply not setting a non-default delimiter) and which then converts the TSV to CSV utilising the --t2c option (a shortcut for the combination of the two options --itsv and --ocsv):

paste file1 file2 file3 | mlr -N --t2c fill-down -f 1,2,3

Stack Exchange Network

Combine .csv-files with different amount of lines

3 Answers 3

You must log in to answer this question.

Linked

Hot Network Questions

Combine .csv-files with different amount of lines

3 Answers 3

You must log in to answer this question.

Linked

Related

Hot Network Questions