Removing Rows from Array Based on Date/Time Value in Cell

2020 7 29
2 个回答
回答已采纳
更新时间：2020 7 30
7 次查看（30 天）

0 个投票

I am trying to remove all lines of data for whom the "last order date" (row 17 of my CSV file) is after 8/31/2019. My code was weeding out some, but there were a few later dates left behind. I switched all the date values to Serial Date Numbers in hopes that would simplify the process, but it's still not weeding out anything after 7183 (the serial date number for 8/31/19). I am out of ideas and would greatly appreciate any feedback.
Code:
segment = readtable('myFile.csv');
a = 1;
rows = height(segment);
newDate = [];
for f = 1:rows
addDate = datenum(segment{f, 17});
newDate(f) = addDate;
end
cutoffDateOG = datetime('08/31/0019','InputFormat','MM/dd/uuuu', 'Format', 'preserveinput');
cutoffDate = datenum(cutoffDateOG);
while a < rows
if newDate(a)>cutoffDate || isnat(segment{a,17}) == 1
segment(a,:) = [];
rows = rows-1;
end
a = a+1;
end
end

请先登录，再回答此问题。

 采纳的回答

Cris LaPierre
Cris LaPierre 2020-7-30

0 个投票

Ok, I'll take you through the steps I'd follow. I'll be sharing videos from our Exploratory Data Analysis specialization on Coursera. If you have time, you might find it a great way to learn the latest features in MATLAB for data analysis. You can access all but the graded content for free.
You'll have to get the data into MATLAB first. I suggest using the Import Tool. This video may be helpful for that. If you prefer, you can generate the code used to import the data from the Import Tool. This video on generating and reusing code created by the import tool shows you how.
Make sure to set the data type of the variables you want to use (each column of a table is a variable). Most important is to set the Last Order Date to datetime. You can learn more about working with dates and times here.
Then you'll want to access specific variables from the table for your comparison. This video will explain how to do this. And since you are actually wanting to access a subset of the data in your table, perhaps this video will also be helpful.
% Import data setting date formats
opts = detectImportOptions("MatLab Test Data.csv");
opts = setvaropts(opts,{'x_LastOrderDate','x_FirstShowAttendedDate','x_LastShowAttendedDate','x_NextShowAttendingDate','x_LastCompShowDate'},'InputFormat',"MM/dd/yy");
opts = setvaropts(opts,'DateCreated','InputFormat','MM/dd/yy HH:mm');
segment = readtable("MatLab Test Data.csv",opts);
% This is the part that removes the rows of data with Last Order Date after cutoff date
% Specify the cutoff date
cutoffDate = datetime(2019,08,13);
% Remove rows where Date is after cut off date
segment(segment.x_LastOrderDate>cutoffDate,:)=[];
% Remove rows where Dates has a value of NaT
segment=rmmissing(segment,"DataVariables","x_LastOrderDate")

1 个评论
显示 -1更早的评论 隐藏 -1更早的评论

Liza Miller
Liza Miller 2020-7-30
Not only did this perfectly do what I needed, I REALLY appreicate the resources for further learning.Thank you so much!

请先登录，再进行评论。

更多回答（1 个）

Cris LaPierre
Cris LaPierre 2020-7-29

0 个投票

Don't use datenums. I would suggest converting your dates to datetimes (segment{f,17}). Then you can just use regular conditional expressions (>,<.==,etc.).

4 个评论
显示 2更早的评论 隐藏 2更早的评论

Cris LaPierre
Cris LaPierre 2020-7-29
It is likely we can be of more help if you attach your data file.
Liza Miller
Liza Miller 2020-7-29
编辑：Liza Miller 2020-7-29
Hi Chris! Thank you so much for responding. I can't upload my data, since it contains customer information, but there are 500 rows, and the columns are all in different classes (text, numbers, and date/time). I originally used datetime in this code instead of datenum, but it didn't work for me.
Here is what that code looked like:
newDate = [];
for f = 1:rows
current = segment{f,17};
addDate = datetime(current);
newDate = [addDate,newDate];
end
cutoffDate = datetime('08/31/0019','InputFormat','MM/dd/uuuu', 'Format', 'preserveinput');
while a < rows
if newDate(a)>cutoffDate || isnat(segment{a,17}) == 1
segment(a,:) = [];
rows = rows-1;
end
a = a+1;
end
I have it print out each date at the end of the code just to test, and here's a sample from that:
09/13/0019
10/24/0014
04/22/0016
02/03/0020
08/04/0016
10/17/0018
06/21/0018
11/07/0015
It catches 58 cases where the date is bigger than 8/31/19, but there are still just 6 dates it leaves behind (like the 2/3/2020 above).
Cris LaPierre
Cris LaPierre 2020-7-29
编辑：Cris LaPierre 2020-7-29
You may be making this more complicated than it needs to be. Without a representative data set, we can only guess, but I suspect something like this should work. Ignore the first few lines. I'm creating a dummy table of various data types for the example.
% Dummy data set including a range of dates including NaT entries.
Dates = [datetime(2019,01,01):days(1):datetime('today') repmat(NaT,1,24)];
segment = table(Dates',zeros(length(Dates),1),zeros(length(Dates),1,'single'),cell(length(Dates),1),strings(length(Dates),1));
segment.Properties.VariableNames=["Dates","Double","Single","Cell","String"];
% Specify the cutoff date
cutoffDate = datetime(2019,08,13);
% Remove rows where Dates has a value of NaT
segment=rmmissing(segment,"DataVariables","Dates")
% Remove rows where Date is after cut off date
segment(segment.Dates>cutoffDate,:)=[];
Since I start Dates on the first day of 2019, and since 8/13/2019 is the 225th day of the year (in 2019), the resulting size of segment should be a table with 225 rows and 5 columns.
day(cutoffDate,'dayofyear')
ans =
225
Liza Miller
Liza Miller 2020-7-30
编辑：Liza Miller 2020-7-30
Thank you so much; you've been so helpful. I should've prefaced this by saying, I worked with MatLab a little in school, but I'm trying to implement it into my current role as a marketing analyst. I am a super beginner, so after your example I do see how it would be imperative to see the actual data. Here's the spredsheet I'm working off of. The dates I'm trying to sift through is row 17 (last order date). rmmissing is a godsend and is much simpler than what I was doing, but I'm having some trouble translating the row variable name aspect, considering how many rows I have. I tried using a variable for the 17th column of my data, but that didn't work.
You've been so incredibly helpful, and if don't have time/are sick of answering any more I very much understand, but I'd love to be able to utilize this awesome code you've provided, and hopefully implement MatLab into my company!

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by