Importing a string matrix into Lazarus - problem only ONE column

[Mickel]

Found the issues and the program works perfectly now, changed the real into integer, because the output also had a +E03. Just to keep you updated, the final code:

function change(matrix2: TKUStringMatrix): TKUIntegerMatrix;
var matrix1: TKUIntegerMatrix;
var i,j: Integer;

begin
  matrix1 := TKUIntegerMatrix.Create(matrix2.NrRows, matrix2.NrCols);

   for i := 1 to matrix1.NrRows do
      for j := 1 to matrix1.NrCols do
        if matrix2.Cell[i, j] <> '' then
          matrix1.Cell[i, j] := StrToInt(matrix2.Cell[i, j]);

   result:= matrix1
end;


function assets(matrix1: TKUIntegerMatrix): TKUIntegerMatrix;
  var i, j, k : Integer;
  begin

  k:=1;

  for i:=1 to matrix1.NrRows do  //119533
  begin
       for j:=k to matrix1.NrRows do
        begin

             if matrix1[i,1] = matrix1[j,1] then    // gck the same
                begin
                if matrix1[j,2] = matrix1[i,2] - 1 then
                   begin
                   writeln(i);
                   matrix1.SetCell(i,4,matrix1[j,3]);
                   k:=j;
                   end

                end

             end;
  end;

  if i= 40 then
        writeln('40');
     //if i=100 then
       // writeln('100');
     if i= 50000 then
        writeln('halfweg');
     if i= 85000 then
        writeln('bijna daar');


  matrix1.Save('Compustat v009.txt');
  writeln('file saved');


  end;



procedure LinkdProc();
  //import gegevens
  var  matrix2: TKUStringMatrix;
  var matrix1: TKUIntegerMatrix;

  begin
       matrix2 := TKUStringMatrix.Create('Compustat v008.csv',';');

       writeln();
       //matrix2.show();

       writeln('Import bestanden gelukt');
       matrix1:=change(matrix2);

       assets(matrix1);

       Readln;
  end;

  begin
  LinkdProc();
  end.             

Thank you for your help again !

[wp]

@Mickel:
For your next posting: The readability of code is improved if you write "[ code=Pascal]" before the first line and "[ /code]" after the last line (without the quotation characters, and without the space after the opening bracket which I have to add here to avoid activating the highlighter).

[rvk]

@Mickel
I might have some further optimization for you (I now kinda see what you're trying to do). You can loop the rows with your outer loop. For your inner loop you only need to loop from the same place as your outer loop until you find your year or until you find another value. In this case you won't need the extra k variable and the loop is terminated much faster in comparison to your code.

(From your Example.csv the inner loop for rows 1004 only has to loop from current row until the column 1 changes to something else than 1004.)

Code: Pascal  [Select][+][-]

1.     for i := 1 to matrix1.NrRows do  //119533
2.     begin
3.       for j := i to matrix1.NrRows do   // <-- you can begin at row i
4.       begin
5.         if matrix1[i, 1] = matrix1[j, 1] then    // gck the same
6.         begin
7.           if matrix1[j, 2] = matrix1[i, 2] - 1 then
8.           begin
9.             writeln(i);
10.             matrix1.SetCell(i, 4, matrix1[j, 3]);
11.             break;
12.           end;
13.         end
14.         else
15.           break; // you can break here because you've reached another value
16.       end;
17.     end;

About your conversion to integermatrix.... How do you read those real values. Or have you changed the source file to cents instead of euros?

If you still have euros you can't read anything other then whole euros.

I also found it strange you got +E03 because I saw this line in the source:

Code: Pascal  [Select][+][-]

1. Str(PDouble(@value)^:5:2, text);

which should have resulted in a number like 12345.12
(So maybe it's better to concentrate on fixing that than going for integermatrix)

But if you integermatrix works for you it's ok (but if you're dealing with currency I still think you'll need the realmatrix).

One final note... besides the suggestion wp made about placing the code in the code-tags... you also might want to use correct and easy to read indentation. It was the incorrect indentation that got you in problems with your previous assets() procedure. If the indentation is correct, errors are much easier to spot.

If you have Lazarus you can install the package jclidelazarus. After that you can use Ctrl+D to run a code-formatter which does the indentation for you. It's really easy and convenient.

[Mickel]

Yes, the numbers are in thousands of euros. So I multiplied the original dataset by 1000. However I encountered a problem when I wanted to test it on a second dataset, where the numbers are in millions. Then for example the number 2573126000 is too big to be an integer and it will fail to import again. But some numbers need to be multiplied by 1000 to have no decimals.


So I guess I need to keep the real one. The output is similar to:

1.00400000000000E+003    2.01300000000000E+003    2.19950000000000E+003    2.13690000000000E+003
 1.00400000000000E+003    2.01200000000000E+003    2.13690000000000E+003    2.19565300000000E+003
 1.00400000000000E+003    2.01100000000000E+003    2.19565300000000E+003    1.70372700000000E+003
 1.00400000000000E+003    2.01000000000000E+003    1.70372700000000E+003    1.50104200000000E+003
 1.00400000000000E+003    2.00900000000000E+003    1.50104200000000E+003    1.37751100000000E+003
 1.00400000000000E+003    2.00800000000000E+003    1.37751100000000E+003    1.36201000000000E+003
 1.00400000000000E+003    2.00700000000000E+003    1.36201000000000E+003    1.06763300000000E+003
 1.00400000000000E+003    2.00600000000000E+003    1.06763300000000E+003    9.78819000000000E+002
 1.00400000000000E+003    2.00500000000000E+003    9.78819000000000E+002    7.32230000000000E+002

So you are right we should focus on fixing this issue with:

Code: Pascal  [Select][+][-]

1.  Str(PDouble(@value)^:5:2, text);

[rvk]

O wait...
The value:5:2 is only used for the matrix1.show;
If you do matrix1.show it will show 123.12 correctly.

The matrix1.save has this

Code: Pascal  [Select][+][-]

1. write(DataFile, self.GetCell(r, c));

So there is no formatting of the cells when you write the file.
And without formatting a real is written with +E notations.

But does this really need to be fixed, though??
Reading +E notation back into a realmatrix should work too.
(But in that case you shouldn't use the stringmatrix and convert but directly the realmatrix.create.)

For example... doing this... reading the Compustat v009.txt back and showing it, works:

Code: Pascal  [Select][+][-]

1.     assets(matrix1);
2.     Readln;
3.     matrix3 := TKURealMatrix.Create('Compustat v009.txt');
4.     matrix3.show;
5.     Readln;

gives this for my Example.csv:

Code: [Select]

      |     1 |     2 |     3 |     4
------+-------+-------+-------+------
    1 | 1004.00 | 2013.00 | 1116.90 | 1033.70
    2 | 1004.00 | 2012.00 | 1033.70 | 1063.27
    3 | 1004.00 | 2011.00 | 1063.27 | 913.99
    4 | 1004.00 | 2010.00 | 913.99 | 863.43
    5 | 1004.00 | 2009.00 | 863.43 | 851.31
    6 | 1004.00 | 2008.00 | 851.31 | 783.43
    7 | 1004.00 | 2007.00 | 783.43 | 645.72
    8 | 1004.00 | 2006.00 | 645.72 | 624.45
    9 | 1004.00 | 2005.00 | 624.45 | 474.54
   10 | 1004.00 | 2004.00 | 474.54 | 432.20
   11 | 1004.00 | 2003.00 | 432.20 | 396.41
   12 | 1004.00 | 2002.00 | 396.41 | 436.66
   13 | 1004.00 | 2001.00 | 436.66 | 485.86
   14 | 1004.00 | 2000.00 | 485.86 | 511.27
   15 | 1004.00 | 1999.00 | 511.27 | 508.19
   16 | 1004.00 | 1998.00 | 508.19 | 468.40
   17 | 1004.00 | 1997.00 | 468.40 | 414.10
   18 | 1004.00 | 1996.00 | 414.10 | 338.01
   19 | 1004.00 | 1995.00 | 338.01 | 321.63
   20 | 1004.00 | 1994.00 | 321.63 | 307.72
   21 | 1004.00 | 1993.00 | 307.72 | 265.04
   22 | 1004.00 | 1992.00 | 265.04 |  0.00
   23 | 1045.00 | 2014.00 | 12112.00 |  0.00
   24 | 1045.00 | 2011.00 | 6757.00 | 6838.00
   25 | 1045.00 | 2010.00 | 6838.00 | 6642.00
   26 | 1045.00 | 2009.00 | 6642.00 | 5935.00
   27 | 1045.00 | 2008.00 | 5935.00 | 7229.00
   28 | 1045.00 | 2007.00 | 7229.00 | 6902.00
   29 | 1045.00 | 2006.00 | 6902.00 | 6164.00
   30 | 1045.00 | 2005.00 | 6164.00 | 4971.00
   31 | 1045.00 | 2004.00 | 4971.00 | 4682.00
   32 | 1045.00 | 2003.00 | 4682.00 |  0.00


[Mickel]

Oh I didn't know that the write function didn't have any formatting, nice that you spotted it!

It doesn't have to be fixed if I know how to export these show results. My lazarus executes everything in cmd, I'm not able to copy the output from there.

[rvk]

It doesn't have to be fixed if I know how to export these show results. My lazarus executes everything in cmd, I'm not able to copy the output from there.

Yes, you can. I did too (to copy it to my post)

When you have your console-screen and it is on the readln you can right click the title-bar (of the console) and choose Edit > Mark. After that you can select the entire output with your mouse. One press on Enter will copy that text to you clipboard. If your console-screen doesn't have all the data (i.e. there is no scroll-bar) you could set the buffer in settings: Right-click title-bar -> Properties > Layout and increase Buffer Size height.

But there is an easier (programmatic) way. The matrix1.show is just ONE statement:

Code: Pascal  [Select][+][-]

1. procedure TKUMatrix.Show();
2. begin
3.      write(self.ToStr());
4. end;

So if you need the pretty output you can just save matrix1.ToStr() to a string and write that string to a file. (The ToStr is a public function so that's not a problem)

[Mickel]

The first step doesn't work, because of the maximum buffer size height of 9999. I need at least 100k entries, a bummer. So I tried the 2nd code, but not sure if i'm following you there.

first try:
 

Code: Pascal  [Select][+][-]

1. matrix1.ToStr();
2.   matrix1.Save('Compustat v009.txt');    

2nd try:
 

Code: Pascal  [Select][+][-]

1.   matrixSt:= matrix1.ToStr();
2.   matrixSt.Save('Compustat v009.txt');    

First has the same result, second doesn't work even if I define matrixSt as a KUStringMatrix.

[rvk]

First has the same result, second doesn't work even if I define matrixSt as a KUStringMatrix.

No... you need to do something like this:

Code: Pascal  [Select][+][-]

1. var
2.   s: string;
3. begin
4.   s:= matrix1.toStr();
5.   // and now save s to a file.

But the easiest way to save a string to a file would be to use TStringList:

Code: Pascal  [Select][+][-]

1. assets(matrix1);
2. with TStringList.Create do
3. try
4.   Add(matrix1.toStr());        // <-- add the string from matrix1.toStr() to the stringlist
5.   SaveToFile('filename.txt');  // <-- save the stringlist to a file
6. finally
7.   Free;
8. end;
9. Writeln('saved to filename.txt');
10. Readln;

[Mickel]

Works like a charm, thank you rvk