Read CSV with yyyyMMddhhmmss and group months

Hello! Matlab newbie, so I apologize if this is a simple question.

I've got a 5000 by 1 CSV file filled with numbers in the yyyyMMddhhmmss format. I'm simply trying to group each line by month.

[Stephen23]

"Read CSV with yyyyMMddhhmmss and group months"

Do NOT use outdated, deprecated functions like DATEVEC.

Import the file data using READTABLE, importing the datestamp data as DATETIME objects. These you can group using e.g. FINDGROUPS. If you upload a sample file by clicking the paperclip button then someone can show you how.

[Lauren]

I've attached a sample. One problem I have is that I'm working on an old version of Matlab, so when running "which datetime" nothing is found.

[Stephen23]

@Lauren: what version of MATLAB are you using?

" I'm simply trying to group each line by month."

Which of these to you want?:

1. group by month only (so you will get twelve groups, i.e. 2021-03 is in the same group as 2019-03)
2. group by month of every year (i.e. 2021-03 is in a different group from 2019-03).

What do you want to occur with missing data? For example, such as here:

Note that your description does not match the uploaded file:

yyyyMMddhhmmss % your description.
202009090029   % actually in the file (no seconds).

[Lauren]

Great points. I want your option 1: to group by month so there are 12 groups.

Yes, you are correct. There are not seconds. It shoulud be yyyyMMddhhmm. Mea Culpa!

Answer 1

[Stephen23]

Here is one way to group by month only, ignoring empty lines of the CSV file:

str = fileread('sample.csv');
tkn = regexp(str,'^(\d{4})(\d\d)','tokens','lineanchors');
tkn = vertcat(tkn{:})

tkn = 5091×2 cell array
    {'2020'}    {'09'}
    {'2020'}    {'09'}
    {'2019'}    {'08'}
    {'2019'}    {'03'}
    {'2019'}    {'02'}
    {'2020'}    {'06'}
    {'2019'}    {'03'}
    {'2020'}    {'06'}
    {'2020'}    {'06'}
    {'2020'}    {'06'}
    {'2020'}    {'06'}
    {'2020'}    {'06'}
    {'2020'}    {'09'}
    {'2019'}    {'09'}
    {'2020'}    {'06'}
    {'2019'}    {'09'}
    {'2019'}    {'07'}
    {'2020'}    {'06'}
    {'2019'}    {'08'}
    {'2019'}    {'02'}
    {'2019'}    {'03'}
    {'2019'}    {'08'}
    {'2018'}    {'07'}
    {'2018'}    {'08'}
    {'2018'}    {'09'}
    {'2020'}    {'10'}
    {'2019'}    {'07'}
    {'2018'}    {'09'}
    {'2020'}    {'08'}
    {'2020'}    {'09'}

[~,~,grp] = unique(tkn(:,2),'stable')

grp = 5091×1

1 1 2 3 4 5 3 5 5 5

[Lauren]

Works perfect! Thank you!!

Answer 2

[KSSV]

Read about datevec. This will split the date into year, month, days etc.....from this you can apply the function unique and get them grouped.