I want to read similar column headers and standardize their names

Let's say I have a few csv files with column headers that are similar, but they actually represent the same data (the file header names (VariableNames) change per data file and I can't change this). How can I standardize the filenames so I can use detectImportOptions?

Here are the two column headers:

Data1= {'AnnotatedSequence','x_ProteinGroups','x_Proteins','x_MissedCleavages', ...
        'Charge','m_z_Da_','MH__Da_','DeltaM_ppm_','Deltam_z_Da_', ...
        'IonInjectTime_ms_', 'IsolationInterference___','RT_min_','PercolatorPEP'}
Data2 ={'AnnotatedSequence','Modifications','NumberOfProteinGroups', ...
        'NumberOfProteins', 'NumberOfMissedCleavages','Charge','mzInDa', ...
        'MHplusInDa','TheoMHplusInDa', 'DeltaMInPpm','DeltaMzInDa', ...
        'Intensity','IsolationInterferenceInPercent', 'IonInjectTimeInMs', ...
        'RTInMin','ApexRTInMin','PercolatorQ_Value','PercolatorPEP', ...
        'PercolatorSVMScore'}

As you can see, some column headers in Data1 (e.g. x_ProteinGroups) contain the same type of data as Data2 (e.g. NumberOfProteinGroups). How can I standardize the name of the header so detectImportOptions can read any of these files?

I tried using strtok(Data1,'_') but that just removes the underscore from the files and I still have other string components that need to be removed. strcmpi would be useful if it didn't have a size requirement.

Your help is greatly appreciated.

[dpb]

Is the following how you would match the two?

Data2=                                     Data1=      
  1   {'AnnotatedSequence'; ...             1  'AnnotatedSequence'; ...
  2    'Modifications'; ...
  3    'NumberOfProteinGroups'; ...         2  'x_ProteinGroups'; 
  4    'NumberOfProteins'; ...              3  'x_Proteins'; ...
  5    'NumberOfMissedCleavages'; ...       4  'x_MissedCleavages'; ...
  6    'Charge'; ...                        5  'Charge'; ...
  7    'mzInDa'; ...                        6  'm_z_Da_'; ...
  8    'MHplusInDa'; ...                    7  'MH__Da_'; ...
  9    'TheoMHplusInDa'; ... 
 10    'DeltaMInPpm'; ...                   8  'DeltaM_ppm_'; ...
 11    'DeltaMzInDa'; ...                   9  'Deltam_z_Da_'; ... 
 12    'Intensity'; ...
 13    'IsolationInterferenceInPercent';   11  'IsolationInterference___'; ...
 14    'IonInjectTimeInMs'; ...            10  'IonInjectTime_ms_'; ...
 15    'RTInMin'; ...                      12  'RT_min_'; ...
 16    'ApexRTInMin'; ...
 17    'PercolatorQ_Value'; ...
 18    'PercolatorPEP'; ...                13  'PercolatorPEP'}
 19    'PercolatorSVMScore'}

What would you choose as the preferred nomenclature?

I, personally, detest extremely long names so I'd probably shorten them all significantly, but whatever you pick can work...the problem will be if there are any number of other perturbations, then it gets harder.

[Amanda Figueroa]

Hi dbp!

Thanks for the quick response! The shortest version of the name is okay with me, I really do hate these long names as well :/ Yes, the order you set above is fine with me :)

Thanks for your help :) Things are moving along well on my end.

[dpb]

I've had pressing other business...did you get the picture from the above of at least one way to proceed would be once have decided which names belong to each other, find the key substrings within each to do search and destroy/replace missions upon?

Again, if you only have these two subsets, "not so bad"; if there are a number of other perturbations possible, it gets to be more painful to either enumerate all the cases or to write robust regular expressions for the more general solution.

[Amanda Figueroa]

Hi again,

I'm assuming you want me to index the cell array based on position. However, there may be situations where users will not have the same column position for that header. I tried seeing if I could index a cell by its contents, but it seems like I can't do that. The idea would be to say:

Data1.SelectedVariableNames('NumberofProteins') = 'x_Proteins'

That line does not work though. Otherwise, modifying all the code and plots using "if" statements is going to be a pain...

I'm trying to work this out somehow. Please let me know if this is what you suggested or if you think of anything useful to try.

Amanda

[Adam Danz]

Are these 2 lists the only possibilities or do other csv files contain alternative variable names? If these 2 lists are the only possibilities, proceed with dpb's solution.

Regular expressions will come in handy.

If you're dealing with a wide variety of variable names that need grouped, their values might be helpful. For example, if variable x is the only variable with values in the 10,000s, that will be a good way to identify variable x. If you have categorical variables, they might be identifiable too.

The good news is that once your data are clean, your task will be 60% complete (on avg).

[Amanda Figueroa]

Thanks Adam,

I am working with data that was given to me and the column headers change for some odd reason. Therefore, I have to assume that these will change further in the future. Eventually I want to get to analyze big datasets and there will be various column header names. I am working on an alternative solution, if not, then I will proceed with dbp's suggestion.

Amanda

[dpb]

Well, you can't solve a problem without a clear definition of what the problem really is... :)

If it were me, I'd start with simple and only get more complex as it was demonstrated necessary to be so.

I'd also go complain to the source of the data to have them provide a roadmap if not consistency when they generate/supply the data.

What I was really suggesting (and used the rearranging of the supplied data to illustrate) is that there are matching patterns between the two sets that are unique for certain variables on a 1:1 basis for a subset of the variables, then another correlation for another subset, and so on...I just left the indices with the two to ensure you were in agreement that I had identified the match correctly to make further reference more simple to a given label.

The simple-minded way is to itemize those patterns on each side in lookup tables and just do substitution of each to the other to whatever is chosen as the final substring for that pattern combination to use in the final variable name.

In some cases just a simple startswith() will be enough; in some others will take some thinking about regular expressions to hit the "In"<-->"_" places where it's embedded in the string and to ensure you don't inadvertently find another string match beside the ones you're really looking for.

[Adam Danz]

It sounds like a mess but it's not uncommon. Often times data comes from different sources with different formats and the cleaning stage is inevitable.

You'll probably need to use a mix of strategies all arranged in a set of well thought-out conditionals. For example, start by search for embedded key words. If there are no matches, you might be able to search by matching expected values in the data. If there is more than one match, you'll need a second conditional to eliminate the incorrect match.

Some data might be integers (categorical data, counts, etc). Some data might have expected values between 0 and 1. Some data might be expected to be sparse, filled with mostly 0s. All of these are testable and you should include lots of sanity checks in this process. Here's an example sanity check

%These data should all be positive, non-zero integers.  
if mod(x, 1) ~= 0 | x <=0 
    error('Sanity Check Failure.  Data are not positive integers.')
end

[Amanda Figueroa]

Hi dbp,

Yeah, I get your drift and I did write these down on paper and found the patterns. The problem is that many variables start and/or end the same ('DeltaMInPpm' , 'DeltaMInDa' , 'mzInDa','DeltaScore')... So I keep hitting these roadblocks.

I know the problem is the naming convention. I can talk to the company to fix this moving forward, but the data I have is already stored with these column names (countless experiments). I need to analyze these files that are already finished.

Thanks for the help, I will keep working on fixing this issue.

Answer 1

[dpb]

Picking up where left off above with two string arrays of quoted string column labels from the two shown-to-be-different files...

>> [lia,lib]=ismember(s3,s1);        % see who does match (shorter file first this time)
>> whos li*
  Name       Size            Bytes  Class      Attributes

  lia       31x1                31  logical              
  lib       31x1               248  double               

>> s3(lia)                          % the list of who does match between the two...
ans = 
  19×1 string array
    "Checked"
    "Confidence"
    "Identifying Node"
    "PSM Ambiguity"
    "Annotated Sequence"
    "Modifications"
    "Master Protein Accessions"
    "Protein Accessions"
    "Charge"
    "Rank"
    "Search Engine Rank"
    "Activation Type"
    "MS Order"
    "First Scan"
    "Spectrum File"
    "Ions Matched"
    "XCorr"
    "Percolator q-Value"
    "Percolator PEP"
>>
Now see who doesn't match from this file...

>> s3(~lia)
ans = 
  12×1 string array
    "# Protein Groups"
    "# Proteins"
    "# Missed Cleavages"
    "DeltaScore"
    "DeltaCn"
    "m/z [Da]"
    "MH+ [Da]"
    "DeltaM [ppm]"
    "Deltam/z [Da]"
    "Isolation Interference [%]"
    "Ion Inject Time [ms]"
    "RT [min]"
>>

Then reverse the seearch longer array first...
    
>> [lia,lib]=ismember(s1,s3);
>> s1(~lia)             % we alread knows who matcches
ans = 
  31×1 string array
    "Identifying Node Type"
    "Search ID"
    "Identifying Node No"
    "Sequence"
    "Number of Protein Groups"
    "Number of Proteins"
    "Protein Descriptions"
    "Number of Missed Cleavages"
    "Original Precursor Charge"
    "Delta Score"
    "Delta Cn"
    "Concatenated Rank"
    "mz in Da"
    "MHplus in Da"
    "Theo MHplus in Da"
    "Delta M in ppm"
    "Delta mz in Da"
    "Matched Ions"
    "Total Ions"
    "Intensity"
    "Isolation Interference in Percent"
    "Ion Inject Time in ms"
    "RT in min"
    "Last Scan"
    "Master Scans"
    "Annotation"
    "Peptides Matched"
    "Area"
    "Apex RT in min"
    "Percolator SVMScore"
    "Precursor Quan Result ID"
>> 

there are the two lists and where the matches/mismtches are located between the two files. All you need to doo is to line those back up again and use the string of one to find the matching and replace both with the wanted name going forward.

I'd probably replace all the blanks in the names with underscores as the first step before the anything else but that's just one way to do it.

Answer 2

[Amanda Figueroa]

I found out what happened. The software output has the exact same column names (which I discovered by dragging each type of file into the workspace.

I import these files into matlab with detectImportOptions and it seems like detectImportOptions is what adds the "_" values

I've been working on fixing this for a week now to find out that it is a function. Lol

[dpb]

The underscores get added when variable names have embedded blanks that aren't valid Matlab variable names; I really doubt the in sequence is being added; it looks like that is probably real to me.

Attach two files that illustrate the issue...there will be something different between the two if detectImportOptions returns something other than the same result for each.

You need to inspect the files themselves with just a plain text editor to see what each contains and if there's an issue there (as seems must), then making the fixup first to the raw file is likely much more the simpler fix.

[Amanda Figueroa]

Based on the pictures and your comments, I found out the x_ comes from "# Proteins". I attached pictures where I compare three text files that originally have the same column headers:

And here's what happens when I use detectImportOptions

[Amanda Figueroa]

Following up on how to fix the column headers, it seems like the automated import tool gets rid of the # and empty spaces and assigns the column headers as they should be shown.

[dpb]

I never use the import tool so don't know what it does, specifically.

We can't do anything with the images; attach the files (or the first few lines with the header if they're huge) so somebody can actually see what it is you have to work with and have a chance at it..

[Amanda Figueroa]

Here are the three sample files with the column headers.

Thanks

[dpb]

fid=fopen('buffer.txt','r');
l=fgetl(fid);
fid=fclose(fid);
l=textscan(l,'%q');
s1=string(l{:});

fid=fopen('DMSO.txt','r');
l=fgetl(fid);
fid=fclose(fid);
l=textscan(l,'%q');
s2=string(l{:});

fid=fopen('NBA.txt','r');
l=fgetl(fid);
title3=textscan(l,'%q')
fid=fclose(fid);
l=textscan(l,'%q');
s3=string(l{:});

Preliminaries out of way having read the header lines, let's see something about what we got...

>> all(ismember(s1,s2))==1
ans =
       1
>>

So, the first two files are identical with 50 columns. The third is the odd man out as it only had 30 columns, but we proved the other two are actually the same, not just happen to have the same number of columns.

>>  [s1(1:30) s3(1:30)]

displays the first 30 of the one and all the third and illustrates that the problem is there are blanks in the column names and differences in what they are labelled besides.

Given the limited number of mismatches, I'd just build the manual lookup table of one to the other as outlined above and write a little routine that reads the headers and matches them up as wanted similarly as to how the above does and then use ismember or other set functions to grab the ones wanted and match 'em up. Simplest would probably be to go to table and create a shorter mnemonic name for all variables than these.

If this is the set of types of files, it shouldn't be hard at all and no point in getting too complicated...even if there are others that show up eventually, it probably still will be faster to just follow the same idea with each than trying to write totally generic code.

[Amanda Figueroa]

Thanks! This is very helpful.