1

I have a directory with 1000s of files from a data recording system, and each file can have as many as 40,000 or more rows. The challenge is that sometimes data is not recorded from one or more sensors and thus will be missing eg

File1:

A,B,C,D,F

10,20,10,20,5

File2:

B,C,D,E,F

20,10,20,5,10

File3:

D,E,F


10,30,20

The desired result has all the files merged/joined with a single header. Where an input file is missing a column (because the sensor was broken) that portion is replaced with a null

A,B,C,D,E,F

10,20,10,20,,5

,20,10,20,5,10

,,,10,30,20

The last column, F, is always present because that's the date/time stamp.

I found this answer, however it assumes all headers/columns are the same across all the files

Paste multiple large csv files with different header order

I also found this question Merging multiple CSV files for matching and non matching columns but the answer is not sufficiently complete for me to use it.

Thanks

  • 2
    Are there really blank lines mixed in with the data lines of your CSVs? If not then please fix your example. – Ed Morton Aug 2 '19 at 4:16
2

If you want to try an alternative and very clean and easy tool (https://github.com/johnkerl/miller), starting from a folder where you have your input CSV files, using this command

mlr --csv unsparsify *.csv >out.csv

you will have

A,B,C,D,F,E
10,20,10,20,5,
,20,10,20,10,5
,,,10,20,30

If you want to have F a the end, the command is

mlr --csv unsparsify then reorder -e -f F *.csv

If you a lot of files you could do it in two steps:

mlr --icsv cat *.csv >tmp.txt
mlr --ocsv unsparsify tmp.txt >out.csv
  • 1
    That works really well! I downloaded the OSX binary mlr.osx fixed up the permissions and ran the command you suggest. Looks like its best that the first, lowest numbered file, is complete which then keeps the columns in order. – Steve Shiny Aug 5 '19 at 9:13
  • @SteveShiny I have edited the reply and added an option to put F at the end. If it's the right reply for you, please select it as the best answer – aborruso Aug 5 '19 at 15:23
  • Thanks @aborruse. I am trying to merge ~2100 files with over 10,000,000 lines between them. On my first go my machine locked up and I power cycled, but now I think maybe I should have waited longer than 10 minutes. The machine has 8 physical cores and 24Gb of RAM. I can use a VM which is bigger - should I have waited longer, or should I get a bigger machine? – Steve Shiny Aug 7 '19 at 1:47
  • @SteveShiny I have edited the reply adding a process in two steps. Please try it – aborruso Aug 7 '19 at 7:18
1
BEGIN {
        OFS = FS = ","

        # Parse given column headers and remeber their order.

        # nf will be the number of fields we'd want in the output.
        nf = split(pick, header)
        for (i = 1; i <= nf; ++i)
                order[header[i]] = i

        # Output headers.
        print pick
}

FNR == 1 {
        # Parse column headers from input file.

        delete reorder

        for (i = 1; i <= NF; ++i)
                # If the current header is one that we'd like to pick...
                if ($i in order)
                        # ... record what column it is located in.
                        reorder[order[$i]] = i

        next
}

{
        # Process data fields from input file.

        # We build a new output record, so explicitly split the current record
        # and save it in the field array, then empty the record and rebuild.
        split($0, field)
        $0 = ""

        for (i = 1; i <= nf; ++i)
                # If reorder[i] is zero, it's a column that is not available in the
                # current file.
                $i = (reorder[i] == 0 ? "" : field[reorder[i]])

        print
}

The above awk script would take a selection of columns that you'd like to extract (in some particular order) as an argument, and would extract these from each of its input files.

Examples with the data that you show in the question:

$ awk -v pick='A,B,C,D,E,F' -f script.awk file*.csv
A,B,C,D,E,F
10,20,10,20,,5
,20,10,20,5,10
,,,10,30,20
$ awk -v pick='F,B,A' -f script.awk file*.csv
F,B,A
5,20,10
10,20,
20,,
1

Assuming there aren't really blank lines between data lines and using GNU awk for sorted_in:

$ cat tst.awk
BEGIN { FS=OFS="," }
FNR==1 {
    delete f
    for (i=1; i<=NF; i++) {
        f[$i] = i
        flds[$i]
    }
    numFiles++
    next
}
{
    for (tag in f) {
        val[numFiles,tag] = $(f[tag])
    }
}
END {
    PROCINFO["sorted_in"] = "@val_str_asc"
    sep = ""
    for (tag in flds) {
        printf "%s%s", sep, tag
        sep = OFS
    }
    print ""
    for (fileNr=1; fileNr<=numFiles; fileNr++) {
        sep = ""
        for (tag in flds) {
            printf "%s%s", sep, val[fileNr,tag]
            sep = OFS
        }
        print ""
    }
}

.

$ awk -f tst.awk file{1..3}
A,B,C,D,E,F
10,20,10,20,,5
,20,10,20,5,10
,,,10,30,20
  • Thanks. If my files are not numbered file1, file2, file3 etc, can I do *.txt? – Steve Shiny Aug 2 '19 at 5:36
  • Of course, why not try it? – Ed Morton Aug 2 '19 at 5:45
  • It nearly works... I don't think this will paste properly... A,B,C,D,E,F,BEGIN { FS=OFS="," } 10,20,10,20,,5,, ,20,10,20,5,10,, ,,,10,30,20,, ,,,,,,}, – Steve Shiny Aug 2 '19 at 7:32
  • BTW I'm using a Mac – Steve Shiny Aug 2 '19 at 7:34
  • Please clarify what "it nearly works" means if you'd like help. On a Mac or otherwise you'd need to use GNU awk to get the columns sorted as I show. – Ed Morton Aug 2 '19 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.