Extracting column and specific sorted row data from a large text file

Hello everybody,

I have a large text file of 4Gb. I wanted to import the file into MATLAB and extract certain column and row.

It is required to extract the data corresponding to Time=86400. For e.g., Line 5 should be skipped and Line 17 should be included.

In Line 17, the cell number (1) and slope angle (14.6) along with Z corresponding to the lowest positive value of P (in several cases, a set of both positive and negative values of P are seen) should be selected.

I have attached a sample file.

The final output will look like:

Cell_number Slope_Angle Z P

1 14.6 0.10000E-02 0.93631E-03

2 14.4 0.10000E-02 0.93631E-03

Thanks a lot !!

Answer 1

[Andrei Bobrov]

[EDIT]

f = fopen('FILE_MATLAB.txt');
c = textscan(f,'%s','delimiter','\n');
fclose(f);

c = c{:};
cw = c(find(~cellfun(@isempty,regexp(c,'86400.000'))) + (0:11))';
d = regexp(cw,'-?\d+(\.\d+)?(E[+-]\d+)?','match');
N = cell2mat(cellfun(@(x)str2double(x(1:2)),d,'un',0));
[m,n] = size(N);
D = N(2:end,2:2:end);
D(D < 0) = nan;
[~,ii] = min(D);
out = reshape(permute(reshape(N([ones(1,n);kron(ii+1,[1 1])] + m*(0:n-1)),2,2,[]),[2,1,3]),4,[])';

OTHER variant (for data from your file FILE_MATLAB.txt) 

f = fopen('FILE_MATLAB.txt');
c0 = textscan(f,repmat('%16.6f ',1,6),'Headerlines',4,'CollectOutput',1);
fclose(f);

c = c0{:};
t = c(:,4)==86400;
ii = (find(t) + (1:11))';

c2 = c(ii + size(c,1));
c2(c2<0) = nan;
[~,jj] = min(c2);
out = [c(t,1:2),c(ii(1,:) + jj - 1,1:2)];

[NIKIL]

Thanks a lot Andrei!

I removed the previous comment since the error occurred while running in MacOS. In windows its running fine.

The code ran extremely fast on the 4 gb file.

However, the code couldnt extract the lowest positive value for P. Instead it is extracting only the uppermost value.

For e.g.: when the P values are as shown below (new file attached), it will extract 0.93631 instead of 8.20E-05

p

-0.93631

0.28183

8.20E-05

0.84361

1.1245

1.4054

-1.6863

-1.9672

2.2481

2.529

2.8099

Is there a way to solve this?

[Andrei Bobrov]

I'm fixed.

[NIKIL]

Thank you Andrei. The application of code to a 4 gb file worked within 34.69 mins.

Answer 2

[Are Mjaavatten]

fid = fopen('FILE_MATLAB.txt');
Cell_number   = [];
Slope_Angle  = [];
Z = [];
P = [];
for i = 1:4   % Skip header lines
    tline = fgetl(fid);
end
while ~feof(fid)
    s = textscan(fid,'%f',4);
    d = textscan(fid,'%f %f %f %f %f %f',11,'CollectOutput', 1);
    time = s{1}(4);
    if abs(time-86400) < 1e-5  
        Cell_number = [Cell_number;s{1}(1)];
        data = d{1};
        [~,rowno] = min(data(:,2));
        Slope_Angle = [Slope_Angle;s{1}(2)];
        Z = [Z;data(rowno,1)];
        P = [P;data(rowno,5)];
    end
end
fclose(fid);
fprintf('%12s %12s %12s %12s\n','Cell_number','Slope_Angle','Z','P');
for i = 1:length(Z)
    fprintf('%12d %12g %12.2e %12.4e\n',Cell_number(i),Slope_Angle(i),...
        Z(i),P(i));
end

[NIKIL]

Thank you very much for the help. Currently i am running the code on 4 gb file. However, its been running for more than 14hrs now. :(