Help Changing Class/Type In A Table

Hi y'all,

How do I change the class/type of values in a column into datetime? They were added as datetime going in. When I go to access the file after and check the class, it indicates the variables are catigorical. Why does this happen?

Thank you!

[Walter Roberson]

Please show your code of inserting the datetime values into the table, and your code of checking the class of the values.

[Kristine]

The code being used to create tables in datetime is shown below. The code is doing more than just adding datetime, but I added all potentially relavant code I think @Walter Roberson Is this helpful. I believe it should create columns with datetime, and I'm not sure why it isn't so.

% Adding datetime columns - using the file name for context. 
function [output] = add_datetime_column(reference_file, filename) 
    [filepath, name, extention] = fileparts(filename); % access file info to extract name of file
    parts_of_name               = extract(name, digitsPattern) ; % extract only the numbers in the name  

    date_only_cell     = parts_of_name(1) ; % define the first set of extacted numbers as date 
    date_and_time_cell = strcat(date_only_cell, time_only_cell) ; % combine the date and time cells into one 
    
    in_format_datetime = 'yyyyMMddHHmmss' ; % tell MATLAB how the cell data is formated, and it's convert to string
    convert_datetime   = datetime(date_and_time_cell, 'InputFormat', in_format_datetime) ; % convert cell data into datetime format/type/class
    dt_datetime       = datetime(convert_datetime, 'Format', 'MM-dd-yyyy hh:mm:ss') ;
    dt_date_only      = datetime(convert_datetime, 'Format', 'MM-dd-yyy') ; 

    repeat_number           = height(reference_file) ; % define # of rows in file to print datetime in a column that same number of times
    reference_file.Datetime = repmat(dt_datetime,  repeat_number, 1); % add column with datetime informaion 
    reference_file.DateOnly = repmat(dt_date_only, repeat_number, 1) ;
    
    output                  = reference_file ; 
end

% Runs multiple functions on each file. 
% Not All Defined Functions In This Function Are Shown Above
function []  = each_day_table(folder_referenced, output_folder_location)
   dimention = size(folder_referenced) ; % returns a row vector whose elements are the lengths of the corresponding dimensions 

   % for loop to process each individual file in the folder instead of going through all the files at once
   for i = 1:dimention(2) % (2) is referencing the second value given by size, which is the length (number of rows) ∴ number of files
       % accessing each file in the folder/diractory 
       datastore_result = datastore(folder_referenced(i)) ; % accesses files 
       original_data    = readall(datastore_result) ; % interprets files % making everything into one gant file, that's why it's so slow
       
       with_datetime    = add_datetime_column(original_data, folder_referenced(i)) ; 
       
       new_table        = new_table_useful_data(with_datetime) ; 
       no_zeroz         = remove_zeros(new_table) ;
       no_ESD_over_150  = remove_ESD_greater_150(no_zeroz) ;
      
       [~, name, ext] = fileparts(folder_referenced(i)) ; % ~ is used since that field is not references, so MATLAB knows to skip it
       output_filename = fullfile(output_folder_location, "new_" + name + ext) ; % defining path to directory, and how the file should be named
       writetable(no_ESD_over_150, output_filename) % Saving files 
   end 
end

% Create list of files to loop through
folder_with_files = dir(input_folder_location) ; % define directory where files are 
filenames         = fullfile({folder_with_files.folder}, {folder_with_files.name}) ; % access file path and name information  
csvFiles          = endsWith(filenames, '.csv') ;  % use logi → determining which values end in .csv (fullfile provides axtra info we don't want)
filenames         = filenames(csvFiles) ; % create cell array with all file names

% Running the functions.
each_day_table(filenames, output_folder_location) ; 

[Walter Roberson]

    date_and_time_cell = strcat(date_only_cell, time_only_cell) ; % combine the date and time cells into one 

time_only_cell does not appear to be defined at that point in the code.

[Walter Roberson]

    convert_datetime   = datetime(date_and_time_cell, 'InputFormat', in_format_datetime) ; % convert cell data into datetime format/type/class
    dt_datetime       = datetime(convert_datetime, 'Format', 'MM-dd-yyyy hh:mm:ss') ;
    dt_date_only      = datetime(convert_datetime, 'Format', 'MM-dd-yyy') ; 

The output of convert_datetime is a datetime object.

It is not valid to pass a datetime object as the first parameter of datetime()

[Walter Roberson]

    convert_datetime   = datetime(date_and_time_cell, 'InputFormat', in_format_datetime) ; % convert cell data into datetime format/type/class
    dt_datetime       = datetime(convert_datetime, 'Format', 'MM-dd-yyyy hh:mm:ss') ;
    dt_date_only      = datetime(convert_datetime, 'Format', 'MM-dd-yyy') ; 

If that worked at all, it would replicate the datetime from the filename, and merely adjust the format in two different ways. Setting the Format two different ways on datetime information changes how the datetime information displays but does not change the underlying information . In particular setting the Format on datetime does not remove the hours minutes second information, just sets the h m s to not be displayed. If you want just the date portion, then you should

dateshift(convert_datetime, 'start', 'day')

[Kristine]

@Walter Roberson

"time_only_cell" was a line accidently removed. It defined the part of the title that gave the time.

And if "convert_datetime" is a datetime object, why doesn't it exist as datetime when inseted into the table? If I delete everything else and just put that code into the table it is still expressed as a category.

I don't need the underlying information to change, just how it is displayed. When it is displayed in a column with just dates, I have an easier time graphing things based on dates. But I can't graph anything now, because everything is showing up as a category instead of datetime, and the code I set up needs datetime inputs.

[Cris LaPierre]

new_table        = new_table_useful_data(with_datetime) ; 
no_zeroz         = remove_zeros(new_table) ;
no_ESD_over_150  = remove_ESD_greater_150(no_zeroz) ;

These 3 data processing functions are also not defined in the shared code either.

[Kristine]

These were other functions that remove rows of data that are unrelated to adding datetime columns to the files.

But I did resolve my own issue by switching "MM-dd-yyyy" to "MM/dd/yyyy". I changed nothing else. Now the columns are interpreted as having datetime values. A frustratingly simple solution, though I still don't understand why my code didn't work this way before.

Answer 1

[Cris LaPierre]

The best solution would be to fix how your data is imported. Then you don't hvae to convert it.

You could use convertvars, or just use datetime directly on the table variable

T.A = datetime(T.A,'InputFormat',infmt)

infmt should be set to match the current data. Since your data is categorical, you likely first need to convert it to a string or numeric data type. We could be more specific if you shared your data and code.

[Kristine]

@Cris LaPierre , I have shared my code under the comment above.

[Cris LaPierre]

@Kristine could you also share a file contianing your data? You can attach it using the paperclip icon.

[Kristine]

Unfortunately the files I'm processing are too big to share, but I've edited one to make it small and easy to upload.

[Cris LaPierre]

That's fine. We don't need the full file. Just a representative example.

To that end, I'm having difficulty identifying which column should be a datetime. The shared file has 4 columns labeled: roi_number Area Biovolume EquivDiameter

Please share one that includes the column you want to convert to a datetime. If there is any ambiguity to the format, please also let us know what the expected result should be for at least 1 value.

[Walter Roberson]

The posted function add_datetime_column extracts datetime from the file name, and repeats it once for each row in reference_file

[Cris LaPierre]

Thank you. I missed that.

Admittedly, this is greatly simplified, but I think that makes it easier to understand the code.

filename = "D20240603T131852_IFCB196_fea_v2.csv" ; 

% read all files
fds = fileDatastore(filename,"ReadFcn",@myFcn,"UniformRead",true);
T = readall(fds)

% Write to a new file
[~, name, ext] = fileparts(filename); % access file info to extract name of file
writetable(T, "new" + name + ext) % Saving files


function out = myFcn(filename)
out = readtable(filename);
[filepath, name, extention] = fileparts(filename); % access file info to extract name of file
T = extractBetween(name,"D","_");
out.Datetime(:) = datetime(T,"InputFormat",'yyyyMMdd''T''HHmmss','Format', 'MM-dd-yyyy hh:mm:ss');
out.DateOnly = out.Datetime;
out.DateOnly.Format = 'MM-dd-yyy';
end

[Kristine]

@Cris LaPierre This is just a simplification on my code, but still results in a column of categorical values instead of datetime for me.

[Cris LaPierre]

This code does not create categorical values. The code out.DateOnly.Format would throw an error. Are you sure the values are categoricals?

Can you identify where your code diverges from this example? Better yet, share your implementation of this approach.