how to create a function that takes a txt file with a lot of columns and saves a new file (while keeping the old one) with 3 specific columns from the original ..

Hey guys!

I have a tooon of txt files that I'd like to get 3 columns from each txt file and save them in the same folder. (the old txt file has 2932 columns, I want to select 3 of those 2932 and save that into a new file -- format doesn't matter... txt and excel are both okay) is there a way to automate this to do this for all my txt files? I am still rather new to matlab..

thank you!

Answer 1

[Peter Jarosi]

You should have been more specific, so I could have given more accurate answer. For instance, a list of your filenames, names of your three columns, structure of your data (maybe these are in csv or xlsx format). Anyway, I hope that the following will help you:

myThreeCols = {'colName1', 'colName2', 'colName3'};
listOfFiles = {'myFile1.csv', 'myFile2.csv', 'myFile3.csv', 'myFile4.csv'};
numOfFiles = size(listOfFiles, 2);
for i = 1 : numOfFiles
    myTable = readtable(listOfFiles{1,i}, 'ReadVariableNames', true); % Reading your file
    myTable = myTable(:, myThreeCols); % Select your three columns
    newFile = strcat('new_', listOfFiles{1,i}); % Insert prefix 'new_' in filename
    writetable(myTable, newFile, 'WriteVariableNames', true); % Writing your file
end

If you upload some of your files I could be more specific. Perhaps your filenames could be iterable, that makes life easier.

[Yoanna Ivanova]

I have a lot of txt files -- a lot a lot. And those txt files have more information than what I need. The only columns I need from each txt file are called Date, Time, o2 concentration but they don't always have the headings , sometimes they do sometimes they don't. They're found in columns A, B and AB in the txt file , regardless of whether the heading is present or not. I can open these txt files with excel normally and that is how I have been dealing with the data so far.

What I want to eventually do -- in a sense is to create these new files with these new 3 columns, then consolidate the data because I have 5 sec data for the o2 concentration but I need it in minute values (so average it out per minute). I've been doing this with excel thus far (deleting the columns I don't need, then removing blank rows, then consolidating the data per minute -- keep in mind that I also have the second data present in the Time column so I first had to split the columns, then delete the seconds, then finally consolidate) but it is so repetitive and it takes so long to process that I wonder if there is another way of doing this -- perhaps with matlab.

I tried your code adjusting it for my variables but it doesn't really work..

[Peter Jarosi]

Hi Yoanna,

I understand your problem, dont' be afraid, it is normal and happens very frequently in data science. Your explanation is very detailed but not enough, hundreds of sentences wouldn't be enough. Could you please upload some examples of your files, so that I might help you.

Peter

[Yoanna Ivanova]

Here is an example --

[Peter Jarosi]

The following example doesn't work perfectly, because your example file is a little bit messy: unnecessary empty rows, and the length of your header (28) doesn't match with the length of your data row (56).

myThreeCols = {'Vstpd1', 'Temp1', 'Pambient'}; % Select your three columns
listOfFiles = {'example.txt'};
numOfFiles = size(listOfFiles, 2);
for i = 1 : numOfFiles
    myTable = readtable(listOfFiles{1,i}, 'ReadVariableNames', true,...
        'Delimiter', 'tab'); % Reading your file
    newTable = myTable(:, myThreeCols); 
    newFile = strcat('new_', listOfFiles{1,i}); % Insert prefix 'new_' in filename
    writetable(newTable, newFile, 'WriteVariableNames', true,...
        'Delimiter', 'tab'); % Writing your file
    newExcelFile = strrep(newFile, '.txt', '.xlsx');
    writetable(newTable, newExcelFile, 'WriteVariableNames', true);
end

I don't know which three columns you want, so I picked up three of them randomly. You can change the first line of my code if you want to.

I am thinking about how we can fix your example file easily.

The following code fixes your data problems more or less:

myThreeCols = {'Vstpd1', 'Temp1', 'Pambient'}; % Select your three columns
listOfFiles = {'example.txt'};
numOfFiles = size(listOfFiles, 2);
for i = 1 : numOfFiles
    myTable = readtable(listOfFiles{1,i}, 'ReadVariableNames', true,...
        'Delimiter', 'tab'); % Reading your file
    myTable = myTable(~strcmp(myTable.DATE, ''), :);
    myTable.Properties.VariableNames{'Var30'} = 'TIME_3'; 
    newTable = myTable(:, myThreeCols); 
    newFile = strcat('new_', listOfFiles{1,i}); % Insert prefix 'new_' in filename
    writetable(newTable, newFile, 'WriteVariableNames', true,...
        'Delimiter', 'tab'); % Writing your file
    newExcelFile = strrep(newFile, '.txt', '.xlsx');
    writetable(newTable, newExcelFile, 'WriteVariableNames', true);
end

Notes:

1. This program line deletes empty data rows.

myTable = myTable(~strcmp(myTable.DATE, ''), :);

2. This program line renames column 30 to TIME_3

myTable.Properties.VariableNames{'Var30'} = 'TIME_3';

You may also want to rename other columns.

3. Runnig the code above you will get a warning message: 'Warning: Variable names were modified to make them valid MATLAB identifiers.' It means that some of your variable names in your header are not valid variable names in Matlab, so Matlab a little bit renamed them automatically according to the following rules. https://www.mathworks.com/help/matlab/ref/isvarname.html

[Yoanna Ivanova]

Hi Peter,

Thank you so much for your effort!! However, the code isn't really working for me. I ran your code but when I do, I only get this:

I actually need the full column, not just the first row, and I needed the Date, Time and o2 concentration. I need to average out the data per minute and save that new file with the minute data. perhaps this is indeed a bit too complicated to do with matlab?

[Peter Jarosi]

I've tested my code on your file example.txt and gotten many rows not only one. Most likely your original file is different from your example significantly, perhaps there is an unintended eof character somewhere.

In my opinion it is not too complicated 'to average out the data per minute and save' in Matlab but can be a little bit more inconvenient than in other programming languages. For instance, using Python Pandas dataframes would be much easier for this problem. Matlab did a pretty good job with developing and improving table object in the last decade and I can do almost everything with Matlab tables what I did in Pandas but sometimes it's painful.

[Yoanna Ivanova]

Thanks Peter! Yes, now it works okay -- I removed a couple of rows in the beginning. Thank you!!

[Peter Jarosi]

You're very welcome!