Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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.

share|improve this question
3  
take WReaches module: myFunction[file_]:=Module[...] then make a list of your files: ls = FileNames["*.csv", "/path/to/files", 2] and then map that list onto your function to get the last line from all files. Then you can save this to a new files. You can also join the files names to the output so that each last line is associated with its filename. –  Mike Honeychurch Jul 22 at 5:51
    
You may also refer to this. –  xzczd Jul 22 at 7:46
    
You can even make a palette! (OK I guess it won't work with hundreds of files, but self-promotion is self-promotion) –  acl Jul 22 at 11:20

1 Answer 1

up vote 5 down vote accepted

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]
  ; 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 :)

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.

share|improve this answer
    
Excellent response! +1 –  brama Jul 22 at 15:33
    
SetStreamPosition will take a negative number, meaning "position backwards from the end." You don't have to call FileByteCount to compute it. –  librik Jul 29 at 8:41
    
@librik Thanks, that never occurred to me. I've added a note to that effect to my response. –  WReach Jul 29 at 13:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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