Get the last line from each of a large number of files, transform them, and write all results to a single new file

Question

I have several hundreds of .csv files that are not logically named (contains numbers, characters, and special characters), all stored in a directory. Each file has large but unequal number of rows and columns. I do not need any information other than the last line of each file.

What would be an efficient way to use Mathematica to get the last line from each file, and summarize them to a new .csv file with the file name as the tag ?

@WReach suggested this, but I am not sure how to proceed, especially given the irregular titles.

Answer 1

A simple way to read the last line of a file uses ReadList to get all lines from the file and then returns the last element of the list:

lastLine[file_] := ReadList[file, String] // Last

This code ignores the possibility of an empty file. If we care about such an eventuality, we can deal with it using a bit more logic:

lastLine[file_] := ReadList[file, String] /. {{} -> $Failed, ls_ :> Last @ ls}

This method reads the whole file to get the last line. An alternate method will be shown below which avoids that, but for now let's look at the rest of the problem.

Here is a function that produces a summary that lists each CSV file in a directory along with the file's last line:

summarizeFiles[directory_] :=
  Module[{files}
  , files = FileNames["*.csv", directory]
  ; {FileNameTake[#, -1], lastLine @ #} & /@ files
  ]

It uses FileNames to get the CSV pathnames. The filename portion of the path is extracted using FileNameTake and then our helper function lastLine is used to get the last line.

We can export the summary as a CSV like this:

$dataDirectory = FileNameJoin @ {NotebookDirectory[], "data"};
$outputFile = FileNameJoin @ {NotebookDirectory[], "result.csv"};

Export[$outputFile, summarizeFiles @ $dataDirectory]

This example assumes that the input data and output file reside in the notebook directory -- change this assumption to suit your taste.

The final result looks like this:

FilePrint @ $outputFile
(*
TI_2000_0.07_508.csv,"x[1] -> 8,x[2] -> 8,x[3] -> 7,x[4] -> 7,x[5] -> 0,x[6] -> 8,x[7] -> 0,x[8] -> 8,x[9] -> 7,x[10] -> 5"
TI_2000_0.13_846.csv,"x[1] -> 8,x[2] -> 7,x[3] -> 7,x[4] -> 7,x[5] -> 7,x[6] -> 7,x[7] -> 0,x[8] -> 7,x[9] -> 6,x[10] -> 5"
...
TI_2300_0.12_104.csv,"x[1] -> 8,x[2] -> 8,x[3] -> 8,x[4] -> 7,x[5] -> 8,x[6] -> 8,x[7] -> 4,x[8] -> 8,x[9] -> 4,x[10] -> 4"
TI_2300_0.1_966.csv,"x[1] -> 8,x[2] -> 8,x[3] -> 8,x[4] -> 8,x[5] -> 4,x[6] -> 4,x[7] -> 8,x[8] -> 8,x[9] -> 4,x[10] -> 4"
*)

Avoid Reading The Whole File

Now we return to the issue of reading the entire file to obtain the last line. The first question is whether this overhead is really a problem. For the sample files, I would suggest that it is not. However, we can optimize if the actual data files are much larger in size, number, or both. The trick is to start reading from somewhere near the end of the file when trying to find the last line. The problem is, from which byte should we start reading given that we do not know the length of the last line?

If we can make some simplifying assumptions, then this is not a difficult problem. The first assumption is that there is a known maximum line length (including the newline delimiter sequence). We can start reading that far from the end of the file. We can ignore any extra lines or line fragments picked up in the subsequent read.

The second assumption is that the file encoding is such that there are no variable-length multibyte character sequences in the file. This is important because otherwise when we start reading from some arbitrary point in the file we might be unlucky enough to start in the middle of a multibyte sequence and read some very strange results. This assumption appears to hold true for the sample data set.

Under these assumptions, the code to skip most of the file and read the last line looks like this:

ClearAll[lastLine]

lastLine[file_, maxLineBytes_:1024] :=
  Module[{s, line}
  , s = OpenRead[file, BinaryFormat -> True]
  ; SetStreamPosition[s, Max[0, FileByteCount@file - maxLineBytes]]
  ; line = ReadList[s, String] /. {{} -> $Failed, ls_ :> Last @ ls}
  ; Close[s]
  ; line
  ]

SetStreamPosition is used to set the read position. FileByteCount and maxLineBytes are used to calculate the starting read point. Adjust maxLineBytes as desired.

If the simplifying assumptions are violated, then the methodology can still be used but the determination of the read starting point becomes more complex. It would involve a trial-and-error process that reads from various candidate start points until the last line is found without any multi-byte errors. It is left as an exercise to the reader to implement that additional logic :)

The use of BinaryFormat -> True is to defeat some undocumented behaviour in OpenRead where it otherwise buffers file contents (at least in some versions of Mathematica, see 182619).

Edit

@librik points out that SetStreamPosition accepts negative positions indicating an offset from the end of the file. Thus, the use of FileByteCount is unnecessary if the SetStreamPosition is changed to:

Quiet[SetStreamPosition[s, -maxLineBytes], SetStreamPosition::stmrng]

Quiet is necessary to guard against the case when the file length is less than maxLineBytes.