Create and update table into loop

Hello guys!

I would appreciate your expertise on this:

I have a loop that reads and does several things on a series of input data. It goes like this

output_path = ('C:\PhD\Validation Natalia\Output');

obsdir  = 'C:\PhD\Validation Natalia\Meteo data\Daily Data\';
wrfdir = 'C:\PhD\Validation Natalia\WRF\Daily Data\';
list1 = rdir ('C:\PhD\Validation Natalia\Meteo data\Daily Data\'); 
stations = readtable('C:\PhD\Validation Natalia\stations_Natalia.csv'); 

tdaily=datetime(2015,1,1):days(1):datetime(2015,12,31);

for i=1:size(stations,1)-1 
    
  var=('Temperature'); varunits=('C');
    
  name = stations( i, 1 );
  name.(1);
  namestr = char(name.(1)); 
  indx = strfind (list1, namestr)  ; 
    
  for j = 1:size(indx, 1)
    if (indx{j,1}>0)
       if (strfind(list1{j,1}, 'txt') > 0)
                      
         obsfile = [obsdir, 'Daily_Obs_',namestr,'.txt']
         Obs_Data = readtable(obsfile);

         wrffile = [wrfdir, 'Daily_WRF_', namestr,  '.txt'] 
         WRF_Data = readtable(wrffile);
           
         All=table(WRF_Data.Date_UTC, WRF_Data.Temp, Obs_Data.Temp);
         All.Properties.VariableNames{1} = 'Date_UTC'; All.Properties.VariableNames{2} = 'WRF_Temp';
         All.Properties.VariableNames{3} = 'Obs_Temp';
       end
    end
  end
end

As you can see, I have data from 8 stations that are being processed. What I want to do is to store all the All tables into one separate table (e.g. Data_All_Stations), every time the i loop runs again. As a result, Data_All_Stations will have 1 Column titled Date_UTC, and 2 columns for each station (WRF_Temp and Obs_Temp with station name).

Is there a way to do so? thank you in advance!

PS. I am on Matlab 2019a.

Answer 1

[Arjun]

Hi @Daphne PARLIARI,

I understand that you are working with observed and modeled temperature data from 8 stations, and your goal is to consolidate everything into a single master table. This table should have one Date_UTC column and 16 additional columns—two for each station: one for observed temperature and one for WRF-modeled temperature.

However, there are a few inconsistencies in the input files that need to be addressed:

1. Column name mismatch: For example, in Daily_Obs_Heraklion.txt, the date column is labeled DATE_UTC, while in Daily_WRF_Heraklion.txt, it is Date_UTC.
2. Column order mismatch: The order of columns differs between files.
3. Date format inconsistency: Observed data uses dd/MM/yyyy, while WRF data uses dd-MMM-yyyy.

To handle these discrepancies and successfully merge the data, you need to:

1. Standardize column names so both datasets use Date_UTC.
2. Ensure consistent column order before merging.
3. Convert date strings to "datetime" objects using the correct input format for each file.
4. Use MATLAB’s "outerjoin" carefully to merge each station’s data into the master table without duplicating columns.

Kindly refer to the code section below for implementation of the above suggested fix:

output_path = ('C:\PhD\Validation Natalia\Output');
obsdir  = 'C:\PhD\Validation Natalia\Meteo data\Daily Data\';
wrfdir = 'C:\PhD\Validation Natalia\WRF\Daily Data\';
list1 = rdir ('C:\PhD\Validation Natalia\Meteo data\Daily Data\'); 
stations = readtable('C:\PhD\Validation Natalia\stations_Natalia.csv'); 
% Create master table with column vector
tdaily = (datetime(2015,1,1):days(1):datetime(2015,12,31))';
Data_All_Stations = table(tdaily, 'VariableNames', {'Date_UTC'});

for i = 1:size(stations,1)-1
    name = stations{i, 1};
    namestr = char(name);
    indx = strfind(list1, namestr);
    for j = 1:size(indx, 1)
        if indx{j,1} > 0
            if contains(list1{j,1}, 'txt')
                
                obsfile = [obsdir, 'Daily_Obs_', namestr, '.txt'];
                wrffile = [wrfdir, 'Daily_WRF_', namestr, '.txt'];

                Obs_Data = readtable(obsfile);
                WRF_Data = readtable(wrffile);

                % Standardize date column names
                obs_date_col = strcmpi(Obs_Data.Properties.VariableNames, 'DATE_UTC');
                wrf_date_col = strcmpi(WRF_Data.Properties.VariableNames, 'Date_UTC');
                Obs_Data.Properties.VariableNames{obs_date_col} = 'Date_UTC';
                WRF_Data.Properties.VariableNames{wrf_date_col} = 'Date_UTC';

                % Convert date formats
                Obs_Data.Date_UTC = datetime(Obs_Data.Date_UTC, 'InputFormat', 'dd/MM/yyyy');
                WRF_Data.Date_UTC = datetime(WRF_Data.Date_UTC, 'InputFormat', 'dd-MMM-yyyy');


                % Ensure date columns are column vectors
                Obs_Data.Date_UTC = Obs_Data.Date_UTC(:);
                WRF_Data.Date_UTC = WRF_Data.Date_UTC(:);

                % Rename columns with station name
                wrf_col = ['WRF_Temp_', namestr];
                obs_col = ['Obs_Temp_', namestr];

                % Create station-specific table
                All = table(WRF_Data.Date_UTC, WRF_Data.Temp, Obs_Data.Temp, ...
                            'VariableNames', {'Date_UTC', wrf_col, obs_col});

                % Remove any existing columns with the same names
                existingCols = ismember(Data_All_Stations.Properties.VariableNames, {wrf_col, obs_col});
                Data_All_Stations(:, existingCols) = [];

                % Merge with master table
                Data_All_Stations = outerjoin(Data_All_Stations, All, ...
                    'Keys', 'Date_UTC', ...
                    'MergeKeys', true);
            end
        end
    end
end

% Save to Excel
writetable(Data_All_Stations, fullfile(output_path, 'Data_All_Stations.xlsx'));

I generated some dummy data for "Larisa" station and tested the code, following is the screenshot of the output:

Kindly refer to the documentation of "outerjoin" and "datetime":

• "outerjoin": https://www.mathworks.com/help/matlab/ref/table.outerjoin.html
• "datetime": https://www.mathworks.com/help/matlab/ref/datetime.html

I hope this helps!