hi guys , i want to read a text file line by line and remove the lines which have NA and the duplicated columns

d = fopen('COADREAD_methylation.txt','r');
this_line=0;
all={};
while this_line~=-1
 % C= textscan( d, '%f%s'  ) ;
    this_line=fgetl(d);
   if this_line~=-1
       all=[all;this_line];
   end
end

fclose(d);

[Stephen23]

See also earlier question:

https://www.mathworks.com/matlabcentral/answers/325333-split-a-row-into-2-rows

Answer 1

[dpb]

Well, 'NA' is easy, not sure what defines the repeated columns; not enough time at present to try to parse that input file to figure out what is/isn't unique without a description being supplied...

fid = fopen('COADREAD_methylation.txt','r');
data={};
while ~feof(fid)
  l=fgetl(fid);
  if isempty(strfind(l,'NA')), data=[data;{l}]; end
end
fid=fclose(fid);

If the presence of 'NA' is all that's needed to get all the offending records, then you're done; otherwise need more details on how to tell so folks here don't have to try to work it out on their own.

[chocho]

thank you , but it doesn't work! Error using isempty Too many input arguments.

Error in textfile (line 6) if isempty(l,'NA'), all=[all;l]; end

[dpb]

Huh....somehow missed the strfind call. Fixed in Answer

Also got rid of the variable named all, all is a very important builtin function in Matlab; don't alias it or trouble is sure to ensue. Granted, it's hard to know what is/isn't supplied but the editor will give you hints.

[chocho]

Hybridization REF TCGA-A6-2672-11A-01D-1551-05 TCGA-A6-2672-11A-01D-1551-05 TCGA-A6-2672-11A-01D-1551-05

Composite Element REF Beta_value Gene_Symbol Chromosome Genomic_Coordinate Beta_value

cg00000292 0.511852232819811 ATP2A1 16 28890100 0.787687855895422 ATP2A1 16 28890100 0.51208122605745

cg00002426 0.519102187746053 SLMAP 3 57743543 0.932889308560864 SLMAP 3 57743543 0.167545040501881

[chocho]

as you see on here there 4 columns on the 3rd and 4rd columns there are sme columns repeated like Gene symbol, chromosome and genomic coordinates are repeated in each line but not beta_value so i want to delete all the Chromosome and Genomic coordinates columns and i want to keep for every :cg ...... gene symbol's column(only one column ) and all the Beta_values columns (which i need to take the average between them). for rows i just need to delete each row or line having NA (i don't need them anymore ) i tried your code but doesn't work plz ,i hope you can help me and i will be very thankful

[chocho]

thanks a lot Now is perfect sorry but i have another issue:now after delete the NA rows i have some rows which have 2 genes like this:

cg00008493  0.987979722052904  "COX8C;KIAA1409"

this line have COX8C and KIAA1409 so i want to divide it into 2 rows instead of only one output should be like this:

cg00008493  0.987979722052904  COX8C
cg00008493  0.987979722052904  KIAA1409

[dpb]

Are such rows the only ones remaining that have a semi-colon in them? If so, finding them is the same as finding the 'NA' string except you now want to process the ones containing, not missing the found target instead of skipping them.

Breaking up the lines is again some pretty simple string processing; simply locate the semi-colon and the last tab-delimiter between the fields to retain the same input format.

>> l='cg00008493  0.987979722052904  "COX8C;KIAA1409"';
>> l=strrep(l,'"','');  % remove the superfluous "
>> idx=strfind(l,';');  % locate the semi-colon separator
>> itab=find(l==char(9),1,'last');  % and the last \t before 
>> l=[{l(1:ix-1)}; {[l(1:itab) l(ix+1:end)]}];  % build two lines from one
>> l
l = 
  'cg00008493  0.987979722052904  COX8C'
  'cg00008493  0.987979722052904  KIAA1409'
>>

NB: the result is a cellstr array as the lengths of the two substrings aren't the same; this is same "trick" used earlier in concatenating the lines while removing the unwanted lines.

[chocho]

that's great now how could i make a relation between the previous code and this part after removing the NA rows now i have to split the rows bu using the code which you put in last (i'm working in the same text file )

fid = fopen('COADREAD_methylation.txt','r');
data={};
while ~feof(fid)
  l=fgetl(fid);
  if isempty(strfind(l,'NA')), data=[data;{l}]; end
end
fid=fclose(fid); 
l='cg00008493  0.987979722052904  "COX8C;KIAA1409"';
>> l=strrep(l,'"','');  % remove the superfluous "
>> idx=strfind(l,';');  % locate the semi-colon separator
>> itab=find(l==char(9),1,'last');  % and the last \t before 
>> l=[{l(1:ix-1)}; {[l(1:itab) l(ix+1:end)]}];  % build two lines from one
>> l

[chocho]

sorry for the low efficiency

[dpb]

Why not do it on the fly while you're reading the file and already have the line in memory before creating the file with the undesired record format?

Surely a phd candidate can figure that out... :)

ADDENDUM

HINT: What do you think might occur if you were to place the above in the if clause prior to appending onto the data cell array?

[chocho]

thanks a lot, you are right !

[dpb]

I knew you could do it!!! :)

Glad to help out; hope picked up some tidbits for the future along the way...

[chocho]

hi friend, i want to make this code like this format

Note: i want to get every line and check if it has a NA remove it and get the second line, if not ckeck the columns of this line and see which column have ';' split this column and make 2 rows

fid = fopen('COADREAD_methylation.txt','r');
data={};

while ~feof(fid)
  l=fgetl(fid);   %get the lines
    if isempty(strfind(l,'NA')),  %remove NA rows
    else 
        %read next line
      idx=regexp(l,'\t','split');   %split the colmuns of this line which don't have NA and look for ';' in every column and split it 
      [nrow,ncol]=size(idx);  
           for i=1:ncol  
                 if idx(i)==';'  %look for columns which have ';'and split it 
                     split this column into 2 columns and put the second column
                     into a new row
                      %D = regexp(idx,';','split')
                      %l=[{l(1:idx-1)}; {[l(1:itab) l(idx+1:end)]}]; %split the line into 2
                 end
                     i=i+1;
           end

            save this line % this line will have no NA and if have ; will be splitted
      end
  end
  fid=fclose(fid);

[chocho]

inputs:

Hybridization REF  TCGA-A6-2672-11A-01D-1551-05  TCGA-A6-2672-11A-01D-1551-05  TCGA-A6-2672-11A-01D-1551-05

Composite Element REF  Beta_value  Gene_Symbol  Chromosome  Genomic_Coordinate  Beta_value    Gene_Symbol
cg00000292  0.511852232819811  ATP2A1   16  28890100  0.787687855895422  ATP2A1
cg00002426  0.519102187746053  SLMAP    3  57743543  0.932889308560864  SLMAP
cg00006414  NA  "ZNF425;ZNF398"  7  148822837  NA  "ZNF425;ZNF398"  
cg00008493  0.987979722052904  "COX8C;KIAA1409"  14  93813777  0.986128428295584      "COX8C;KIAA1409"  
cg00011459  0.922491239231445  "TMEM186;PMM2"  16  8890425  0.961124285303233  "TMEM186;PMM2"

outputs:

Hybridization REF  TCGA-A6-2672-11A-01D-1551-05  TCGA-A6-2672-11A-01D-1551-05  TCGA-A6-2672-11A-01D-1551-05
cg00000292  0.511852232819811  ATP2A1   0.787687855895422  
cg00002426  0.519102187746053  SLMAP       0.932889308560864  
cg00008493  0.987979722052904  COX8C     0.986128428295584      
cg00008493  0.987979722052904  KIAA1409  0.986128428295584        
cg00011459  0.922491239231445  TMEM186  0.961124285303233  
cg00011459  0.922491239231445  PMM2                0.961124285303233

appreciate your help !