How to change repeat dates that occur right after each other

I'm running into some issues with a dataset that I have. So basically, for some reason, the service I used to collect the data is changing some of the dates to be repeats. Here is an example of what I'm talking about

9/13/19

9/14/19

9/16/19

9/16/19

9/17/19

9/18/19

So you will notice in this example that the 9/15 date is missing and that the 9/16/19 date is repeating twice. And you would be right. Through diagnosing the issue, I learned that the first 9/16/19 date is actually supposed to be 9/15/19 and the subject didn't actually submit two prompts that day. This is a very characteristic pattern throughout the dataset where there are a few lines that repeat for whatever reason.

What I want to do is take the first 9/16/19 line and turn it into 9/15/19.

This is of course just an example, but there are many points in the dataset I've attached where this issue recurs. If someone knows a way to fix it using a loop to address all the data in the dataset, please let me know. It'd be very tedious to have to go through linebyline of all my datasets and figure out a fix. Thanks in advance

In the specific dataset I attached, you can see the issue on line 237/238 first, and the next recurrence is at line 249/250 in case you need some examples!

[Walter Roberson]

You need to tell us the rule.

• in any stretch of duplicates, the dates should be projected backwards one day for each entry, even if that causes duplicates? (for example if there was already a 9/15/19, then 9/16/19, 9/16/19 should be modified so the first 9/16/19 is changed to 9/15/19 even though there is already a 9/15/19 ?)
• in any stretch of duplicates, the dates should be shifted backwards to the first unused day, even if that causes the days to be out of order? (For example 9/13/19, 9/15/19, 9/16/19, 9/16/19 should move the first 9/16/19 to the unusued 9/14/19, resulting in 9/13/19, 9/15/19, 9/14/19, 9/16/19) ?
• in any stretch of duplicates, each one should be moved consequatively backwards, changing as many past dates as needed until there are no duplicates or out of order? For example 9/13/19, 9/15/19, 9/16/19, 9/16/19 should move to 9/13/19, 9/14/19, 9/15/19, 9/16/19 ?

[BA]

Thanks for asking, sorry about that. I should probably clarify.

The pattern that I showed in the example is the exact same pattern that repeats throughout the datatable. I will respond to each of your bulletpoints with #'s (first bullet point --> 1.)

The issue I was having was that for some days, the data gets mixed up and instead of there being one datapoint each day on 2 days, we get 2 datapoints on one day and no datapoints on the other day. This is the case with the 9/15/19 example where the data for that day accidentally gets considered as 9/16/19.

1. The way the data is setup, I originally used an outerjoin function so there will be a data point there that should be the one that is replaced. Here is an example of what I'm talking about:

The original script I ran was an outerjoin and basically, I instructed the script to create missing dates since sometimes the subjects don't submit prompts and it causes there to be lots of missing datapoints. OuterJoin assumes that since this data isn't here, I need to create an empty row in this spot with the date to serve as a place holder. So we want to replace this row (in this case 9/15/19) with the 9/16/19 row right after.

2) The way the data is set up, there won't be any unused dates because I used the outerjoin function. So basically, every single day possible that the subject was signed up for that study (say like 8/23 to 9/23), there is a day for. Even for a day like 9/15/19 where the date ends up going into 9/16/19, we have a 9/15/19 date that needs to be replaced by 9/16/19

3) I believe this is also addressed by the outerjoin function so there shouldn't be any missing dates at all in this dataset.

Apologies for the initial mixup, this definitely should've been addressed in the initial post but I'm glad you asked. Here is the part of my overall script that uses outerjoin in case you're interested.

%% Night Prompt
Data_EMA.StudyDate = datetime(Data_EMA.StudyDate, 'Format', 'M/d/yy');

%Load Night Prompt Data
Night_Prompt = readtable([root_folder filesep 'Night_Prompt.xlsx']);
Night_Prompt = sortrows(Night_Prompt,'UserId');

% Rename variables that have the same name across outputs
Night_Prompt.Properties.VariableNames('SurveyStartedDate')={'Night_Prompt_SurveyStartedDate'};
Night_Prompt.Properties.VariableNames('SurveyStartedTime')={'Night_Prompt_SurveyStartedTime'};
Night_Prompt.Properties.VariableNames('SurveySubmittedDate')={'Night_Prompt_SurveySubmittedDate'};
Night_Prompt.Properties.VariableNames('SurveySubmittedTime')={'Night_Prompt_SurveySubmittedTime'};
Night_Prompt.Properties.VariableNames('UserId')={'MetricWireID'};

% Clean up entries so that all entries that should be numberic are. 
for col = 22:size(Night_Prompt,2)  % from column 22 onwards
    x = Night_Prompt{:, col};
    if iscell(x)
        x = cellfun(@(v) sscanf(v, '%f'), x, 'UniformOutput', false);
        Night_Prompt.(Night_Prompt.Properties.VariableNames{col}) = x;        
    end
end

Night_Prompt = addvars(Night_Prompt, Night_Prompt.Night_Prompt_SurveyStartedDate, 'Before', 'Night_Prompt_SurveyStartedTime');

%Outerjoin
Night_Prompt.Properties.VariableNames('Night_Prompt_SurveyStartedDate')={'StudyDate'};
T4 = outerjoin(Data_EMA, Night_Prompt ,'Keys' , {'StudyDate',  'MetricWireID'}, 'MergeKeys', true, 'Type', 'left');
T4 = sortrows(T4, {'MetricWireID'});
T4.Properties.VariableNames('Var8')={'Night_Prompt_SurveyStartedDate'};

If you try to run this script, it might not work for you because it uses another dataset "Data_EMA" so if you want to try to run it, I've also attached it as a download to this comment. Let me know if there's anything else I need to clarify or if something is confusing in here

[Walter Roberson]

I would recommend fixing the dates before doing the join.

[BA]

Yeah that's a good way to approach it.

for sub=1:length(Night_Prompt.MetricWireID)
    for dates=1:length(SubjectDates)
        TableRepeatIdentifiers = ...
    end
end

This is sort of what I started with but I'm kind of lost on how to define a function that would say

if next date is a repeat
    then replace it with 1-date
end

[Cris LaPierre]

Can you share the raw data? The Night_Prompt.xlsx file you shared has already been modified by the code you have shared.

[BA]

Here you go! This should be it

Answer 1

[Cris LaPierre]

Perhaps not the most straightforward way, but this appears to work for me on your processed Nightly_Prompt.xlsx data. Basically, it finds any locations where the data is the same and replaces the date with the date immediately preceeding it. It then deletes the row immediately preceeding it.

ind = hours(diff(Night_Prompt.StudyDate))==0;
Night_Prompt.StudyDate([ind; false]) = Night_Prompt.StudyDate([ind(2:end); false; false]);
Night_Prompt([ind(2:end); false; false],:) = []

You could probably follow a similar approach with the raw data.

[Cris LaPierre]

You may find the convertvars function more useful for converting table variable data types.

I also don't understand why you are using addvars. The variable already exists in the table. Perhaps you want movevars? However, at least in the file you have shared, it is already in the desired location, too.

[BA]

Thanks a lot for doing this, really appreciate it! This was really helpful. I just had one question, did you run this function after my outerjoin script or was it run before? I'm running into a few issues

One thing that I was running into an issue with in the code you wrote is that it is only deleting the repeat date, it isn't replacing the repeat date with the date preceding it. What the code is actually doing is it is taking it 2 days back, so for example:

This is the table before you run the code. Notice the times that are associated with each date as they will show you that the repeated dates aren't lost, but there is a new row that is created.

Now here is after the code is run:

Now there are a few things you will notice here. The first is what I mentioned earlier, which is that the date that is created is actually going 2 days back instead of just becoming the preceding day. The second thing is that we're actually losing data (line 172) which becomes the 28 Sep date but we don't have another 28-Sep that was in the original data. You can look at the times in the very next column to look at which data points are kept/deleted.

Not sure if you know what's going on but if you do, it'd be really helpful if you could let me know.

As for the addvars thing, I had to do it because when I do an outerjoin, the variable it uses (StudyDate) is actually being lost because there is an identical variable in the other table and the way I have the function setup, the outerjoin merges the key variables I use. Now you might be thinking, why wouldn't I just use the StudyDate from the Data_EMA because it's literally the exact same thing. And that is a good question that I don't have the answer to lol, so I guess I'll be deleting that.

Thanks again!

[Cris LaPierre]

This was run on the processed file, which, for example, has data for the 17th, and empty row for the 18th, and then two rows of data for the 18th.

Here's an explanation of the code that changes the date. Note that I made several assumptions.

1. the data is sorted first by uiserId, and then by StudyDate
2. The last StudyDate for one userId is never the same as the first date for the next userId
3. All users have at leaset 2 rows of data
4. The duplicate date is never the first date for a user
5. The duplicate datees are always preceded by an 'empty' row with the 'missing' StudyDate

% Find the first rows of any duplicate dates
ind = hours(diff(Night_Prompt.StudyDate))==0;
% Replace the first rows with the date of the preceding row
Night_Prompt.StudyDate([ind; false]) = Night_Prompt.StudyDate([ind(2:end); false; false]);
% Delete the preceding row
Night_Prompt([ind(2:end); false; false],:) = []

As your images show, these assumptions don't all hold for the raw data, so a different approach would be needed. The challenge I found is that, in your raw data, one user has duplicate submissions 3 times in a row. I am unsure how to fix that. It looks like you could just make it 7/1-7/6, but note that the data for 7/7 is a different UserId.

Anyway, identifying the duplicates is similar as before, only now I would subtract 1 day from the first rows. No need to delete rows.

% Find duplicate rows *for the same UserId*
ind = hours(diff(Night_Prompt.Night_Prompt_SurveyStartedDate))==0 & ...
    Night_Prompt.MetricWireID(1:end-1)==Night_Prompt.MetricWireID(2:end);
% Adjust the date of the first row by subtracting 1 day
Night_Prompt.Night_Prompt_SurveyStartedDate([ind; false]) = ...
    Night_Prompt.Night_Prompt_SurveyStartedDate([ind; false])-caldays(1);

[BA]

Thanks so much for explaining it too, I really appreciate it. When you ran the code, did it run for you? I'm in 2022a and for some reason, I'm getting an error code:

"Operator '==' is not supported for operands of type 'cell'."

This is the code I'm running

%% Create Data_EMA
% Subject ID and Dates

DX = readtable([root_folder filesep 'EMA_Study_1.xlsx']);
DX = sortrows(DX,{'SubID'});

Data_EMA = table;
for i=1:length(DX.SubID)
    Temp = cellstr(datestr([datetime(DX.StartDate(i)):datetime(DX.EndDate(i))]','mm/dd/yy'));
    Temp2 = [repmat(DX(i,:),length(Temp),1) array2table(Temp,'VariableName',{'StudyDate'})];
    Data_EMA = [Data_EMA; Temp2];
end 

Data_EMA = sortrows(Data_EMA,{'SubID'});

StudyDay = days(datetime(cellstr(datestr(Data_EMA.StudyDate,'mm/dd/yy'))) - ...
    datetime(cellstr(datestr(Data_EMA.StartDate,'mm/dd/yy'))))+1;

Data_EMA = [Data_EMA(:,[1:4]) array2table(StudyDay,'VariableNames',{'StudyDay'}) Data_EMA(:,[5:end])];

%% NightPrompt
Data_EMA.StudyDate = datetime(Data_EMA.StudyDate, 'Format', 'M/d/yy');

%Load Night Prompt Data
Night_Prompt = readtable([root_folder filesep 'Night_Prompt.xlsx']);
Night_Prompt = sortrows(Night_Prompt,'UserId');

% Rename variables that have the same name across outputs
Night_Prompt.Properties.VariableNames('SurveyStartedDate')={'Night_Prompt_SurveyStartedDate'};
Night_Prompt.Properties.VariableNames('SurveyStartedTime')={'Night_Prompt_SurveyStartedTime'};
Night_Prompt.Properties.VariableNames('SurveySubmittedDate')={'Night_Prompt_SurveySubmittedDate'};
Night_Prompt.Properties.VariableNames('SurveySubmittedTime')={'Night_Prompt_SurveySubmittedTime'};
Night_Prompt.Properties.VariableNames('UserId')={'MetricWireID'};
Night_Prompt.Properties.VariableNames('TriggerDate')={'Night_Prompt_TriggerDate'};
Night_Prompt.Properties.VariableNames('TriggerTime')={'Night_Prompt_TriggerTime'};
Night_Prompt.Properties.VariableNames('HowMuchMoney_sWorthOfCocaineDidYouHave_')={'Night_Prompt_HowMuchMoney_sWorthOfCocaineDidYouHave_'};
Night_Prompt.Properties.VariableNames('HowManyDrinksDidYouHave__OneDrinkIsACanOfBeer_AGlassOfWine_ASho')={'Night_Prompt_HowManyDrinksDidYouHave_'};
Night_Prompt.Properties.VariableNames('HowManyJointsDidYouHave_')={'Night_Prompt_HowManyJointsDidYouHave_'};
Night_Prompt.Properties.VariableNames('WhatDidYouUse_')={'Night_Prompt_WhatDidYouUse_'};
Night_Prompt.Properties.VariableNames('HowManyBagsOrPillsDidYouUse_')={'Night_Prompt_BagsPills'};
Night_Prompt.Properties.VariableNames('HowManyBenzodiazepine_orBenzos_PillsDidYouHave_')={'Night_Prompt_HowManyBenzodiazepine_orBenzos_PillsDidYouHave_'};


% Clean up entries so that all entries that should be numberic are. 
for col = 22:size(Night_Prompt,2)  % from column 22 onwards
    x = Night_Prompt{:, col};
    if iscell(x)
        x = cellfun(@(v) sscanf(v, '%f'), x, 'UniformOutput', false);
        Night_Prompt.(Night_Prompt.Properties.VariableNames{col}) = x;        
    end
end

Night_Prompt = addvars(Night_Prompt, Night_Prompt.Night_Prompt_SurveyStartedDate, 'Before', 'Night_Prompt_SurveyStartedTime');

%Outerjoin
Night_Prompt.Properties.VariableNames('Night_Prompt_SurveyStartedDate')={'StudyDate'};
T4 = outerjoin(Data_EMA, Night_Prompt ,'Keys' , {'StudyDate',  'MetricWireID'}, 'MergeKeys', true, 'Type', 'left');
T4 = sortrows(T4, {'MetricWireID'});
T4.Properties.VariableNames('Var8')={'Night_Prompt_SurveyStartedDate'};

% Find duplicate rows *for the same UserId*
ind = hours(diff(Night_Prompt.Night_Prompt_SurveyStartedDate))==0 & ...
    Night_Prompt.MetricWireID(1:end-1)==Night_Prompt.MetricWireID(2:end);
% Adjust the date of the first row by subtracting 1 day
Night_Prompt.Night_Prompt_SurveyStartedDate([ind; false]) = ...
    Night_Prompt.Night_Prompt_SurveyStartedDate([ind; false])-caldays(1);

[Cris LaPierre]

I did. Here is the code I ran. I think the trick is that I specity the 'TextType' as 'string'.

opts = detectImportOptions("Data_EMA.xlsx");
opts = setvartype(opts,"StudyDate","datetime");
opts = setvaropts(opts,"StudyDate",'InputFormat','MM/dd/yy');
Data_EMA = readtable('Data_EMA.xlsx',opts)

Data_EMA = 2223×8 table
     StartDate       EndDate        SubID       Group     StudyDay            MetricWireID            Protocol    StudyDate
    ___________    ___________    _________    _______    ________    ____________________________    ________    _________

    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}        1       {'60a4073a4a311034d36fa37c'}       2        05/19/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}        2       {'60a4073a4a311034d36fa37c'}       2        05/20/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}        3       {'60a4073a4a311034d36fa37c'}       2        05/21/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}        4       {'60a4073a4a311034d36fa37c'}       2        05/22/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}        5       {'60a4073a4a311034d36fa37c'}       2        05/23/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}        6       {'60a4073a4a311034d36fa37c'}       2        05/24/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}        7       {'60a4073a4a311034d36fa37c'}       2        05/25/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}        8       {'60a4073a4a311034d36fa37c'}       2        05/26/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}        9       {'60a4073a4a311034d36fa37c'}       2        05/27/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}       10       {'60a4073a4a311034d36fa37c'}       2        05/28/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}       11       {'60a4073a4a311034d36fa37c'}       2        05/29/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}       12       {'60a4073a4a311034d36fa37c'}       2        05/30/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}       13       {'60a4073a4a311034d36fa37c'}       2        05/31/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}       14       {'60a4073a4a311034d36fa37c'}       2        06/01/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}       15       {'60a4073a4a311034d36fa37c'}       2        06/02/21 
    19-May-2021    15-Jun-2021    2.176e+07    {'OUD'}       16       {'60a4073a4a311034d36fa37c'}       2        06/03/21 

%Load Night Prompt Data
Night_Prompt = readtable('Night_Prompt.xlsx','TextType','string');  % <------- SET HERE
Night_Prompt = sortrows(Night_Prompt,'UserId');

% Rename variables that have the same name across outputs
Night_Prompt.Properties.VariableNames('SurveyStartedDate')={'Night_Prompt_SurveyStartedDate'};
Night_Prompt.Properties.VariableNames('SurveyStartedTime')={'Night_Prompt_SurveyStartedTime'};
Night_Prompt.Properties.VariableNames('SurveySubmittedDate')={'Night_Prompt_SurveySubmittedDate'};
Night_Prompt.Properties.VariableNames('SurveySubmittedTime')={'Night_Prompt_SurveySubmittedTime'};
Night_Prompt.Properties.VariableNames('UserId')={'MetricWireID'}

Night_Prompt = 1391×58 table
            ResponseId            ResponseType           MetricWireID                    StudyId                       SurveyId               SurveyName      Night_Prompt_SurveyStartedDate    Night_Prompt_SurveyStartedTime    Night_Prompt_SurveySubmittedDate    Night_Prompt_SurveySubmittedTime    TimeZone            TriggerId             TriggerDate    TriggerTime       TriggerName        TriggerIndex    DeviceOS    DeviceOSVersion     AppVersion     SubmissionLocation    DidYouDoAnyOfTheFollowingToday_TookYourOpioidMedication_methado    DidYouDoAnyOfTheFollowingToday_SpokeTo_orSaw_YourTherapist_    DidYouDoAnyOfTheFollowingToday_AttendedGroupTherapy_meeting_    DidYouDoAnyOfTheFollowingTodaySinceYourLastPrompt_    HowManyBagsOrPillsDidYouUse_    HowMuchMoney_sWorthOfCocaineDidYouHave_    HowManyDrinksDidYouHave__OneDrinkIsACanOfBeer_AGlassOfWine_ASho    HowManyJointsDidYouHave_    HowManyBenzodiazepine_orBenzos_PillsDidYouHave_    WhatDidYouUse_    DidYouDoAnyOfTheFollowingToday_DidSomethingDangerousAsAWayToTem    DidYouDoAnyOfTheFollowingToday_ShareNeedlesOrWorks_    DidYouDoAnyOfTheFollowingToday_HadUnprotectedSex_    DidYouDoAnyOfTheFollowingToday_TookADrugFromANewOrUnknownSource    DidYouDoAnyOfTheFollowingToday_UsedMoreThanIIntended_    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourFinancialSi    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourLoveLife_    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourRelationshi    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourRelations_1    HowMuchStressDidYouHaveInTheFollowingAreasToday_School_work_    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourLegalSituat    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourHealth_    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourDayOverall_    ThroughTheDayToday_HowMuchDidYouFeel_SupportedByThePeopleInYour    HowMuchDoYouBelieveThisStatement_TomorrowWillBeABetterDayThanTo    HowMuchDoYouBelieveThisStatement_MyLifeIsMeaningful_    TodayOverallIHadPleasant_positive_SocialInteractions_      IsThereAnythingElseOfNoteThatHappenedToday_GoodOrBad_      IfYouHaveBeenPrescribedOtherMedications_DidYouTakeThoseOtherMed    DELETED_QUESTION    DELETED_QUESTION_1    DELETED_QUESTION_2    DELETED_QUESTION_3    DELETED_QUESTION_4    DELETED_QUESTION_5    DELETED_QUESTION_6    DELETED_QUESTION_7    DELETED_QUESTION_8
    __________________________    ____________    __________________________    __________________________    __________________________    ______________    ______________________________    ______________________________    ________________________________    ________________________________    ________    __________________________    ___________    ___________    __________________    ____________    ________    _______________    ____________    __________________    _______________________________________________________________    ___________________________________________________________    ____________________________________________________________    __________________________________________________    ____________________________    _______________________________________    _______________________________________________________________    ________________________    _______________________________________________    ______________    _______________________________________________________________    ___________________________________________________    _________________________________________________    _______________________________________________________________    _____________________________________________________    _______________________________________________________________    _____________________________________________________________    _______________________________________________________________    _______________________________________________________________    ____________________________________________________________    _______________________________________________________________    ___________________________________________________________    _______________________________________________________________    _______________________________________________________________    _______________________________________________________________    ____________________________________________________    _____________________________________________________    _________________________________________________________    _______________________________________________________________    ________________    __________________    __________________    __________________    __________________    __________________    __________________    __________________    __________________

    "62295c789ce0ce50d1ebcd61"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             09-Mar-2022                        "21:00:08"                        09-Mar-2022                          "21:03:36"               "-5:00"     "5f5159955e42834858a38f2e"    09-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          1          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "Yes"                                                           NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "Yes"                                                        "No"                                                  "No"                                                        "No"                                                          "No"                                                           73                                                               "62"                                                        "19"                                                                     "55"                                                              "72"                                                       "55"                                                                    52                                                                54                                                                 61                                                                 52                                                           59                                                     "Yes"                            "no"                                                                                    <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "622aadb257ab90e887b0c829"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             10-Mar-2022                        "21:00:14"                        10-Mar-2022                          "21:02:25"               "-5:00"     "5f5159955e42834858a38f2e"    10-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          2          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "Yes"                                                           NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           65                                                               "59"                                                        "18"                                                                     "49"                                                              "45"                                                       "15"                                                                    38                                                                44                                                                 49                                                                 68                                                           55                                                     "Yes"                            "couples counseling started"                                                            <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "622bff1de2720180db350ac6"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             11-Mar-2022                        "21:00:07"                        11-Mar-2022                          "21:02:04"               "-5:00"     "5f5159955e42834858a38f2e"    11-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          3          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "No"                                                             "No"                                                            NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           70                                                               "17"                                                        "14"                                                                     "35"                                                              "73"                                                       "23"                                                                    53                                                                53                                                                 54                                                                 28                                                           31                                                     "Yes"                            "n/a"                                                                                   <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "622d51d9a87238437f8f05fb"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             12-Mar-2022                        "21:03:14"                        12-Mar-2022                          "21:04:55"               "-5:00"     "5f5159955e42834858a38f2e"    12-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          4          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "No"                                                             "No"                                                            NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           73                                                               "53"                                                        "18"                                                                     "25"                                                              "80"                                                       "6"                                                                     25                                                               NaN                                                                 48                                                                 22                                                           33                                                     "Yes"                            "miss clinic which means I miss Saturday and Sunday dose"                               <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "622fc93ceb2b032e050e82db"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             13-Mar-2022                        "21:15:07"                        13-Mar-2022                          "21:16:41"               "-4:00"     "5f5159955e42834858a38f2e"    13-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          5          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "No"                                                             "No"                                                            NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           45                                                               "52"                                                        "61"                                                                     "53"                                                              "55"                                                       "62"                                                                    56                                                                56                                                                 59                                                                 59                                                           60                                                     "Yes"                            "didn’t feel well"                                                                      <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "622fe95d668d4360b2527222"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             14-Mar-2022                        "21:15:15"                        14-Mar-2022                          "21:18:20"               "-4:00"     "5f5159955e42834858a38f2e"    14-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          6          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "No"                                                             "Yes"                                                           NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           58                                                               "31"                                                        "46"                                                                     "36"                                                              "73"                                                       "13"                                                                    64                                                                58                                                                 70                                                                 39                                                           48                                                     "Yes"                            "clinic drama"                                                                          <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "62313de36450cf99ddac2885"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             15-Mar-2022                        "21:15:17"                        15-Mar-2022                          "21:31:14"               "-4:00"     "5f5159955e42834858a38f2e"    15-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          7          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "Yes"                                                           NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           76                                                               "25"                                                        "99"                                                                     "46"                                                              "77"                                                       "18"                                                                    35                                                                54                                                                 56                                                                 31                                                           24                                                     "Yes"                            "clinic reinstated"                                                                     <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "6233500e141c1df0920ab8a4"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             16-Mar-2022                        "21:00:06"                        16-Mar-2022                          "21:02:13"               "-4:00"     "5f5159955e42834858a38f2e"    16-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          8          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "Yes"                                                           NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           40                                                               "38"                                                        "35"                                                                     "19"                                                              "45"                                                       "18"                                                                    45                                                                18                                                                 42                                                                 38                                                           32                                                     "Yes"                            "ema payment"                                                                           <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "6233db4ce9f1cf1c2094badf"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             17-Mar-2022                        "21:05:08"                        17-Mar-2022                          "21:07:01"               "-4:00"     "5f5159955e42834858a38f2e"    17-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          9          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "No"                                                            NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "Yes"                                                         "No"                                                           76                                                               "23"                                                        "21"                                                                     "49"                                                              "30"                                                       "9"                                                                     77                                                                71                                                                 69                                                                 52                                                           29                                                     "Yes"                            "cleaned whole house"                                                                   <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "62352b6bf6d02cf4e23cf823"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             18-Mar-2022                        "21:00:17"                        18-Mar-2022                          "21:01:30"               "-4:00"     "5f5159955e42834858a38f2e"    18-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         10          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "Yes"                                                           NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           58                                                               "62"                                                        "50 - Moderately"                                                        "54"                                                              "58"                                                       "35"                                                                    34                                                                49                                                                 30                                                                 41                                                           54                                                     "Yes"                            "n/a"                                                                                   <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "6237ce618a28d169ba509415"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             20-Mar-2022                        "21:00:10"                        20-Mar-2022                          "21:01:21"               "-4:00"     "5f5159955e42834858a38f2e"    20-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         12          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "No"                                                             "No"                                                            NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           78                                                               "23"                                                        "53"                                                                     "56"                                                              "74"                                                       "58"                                                                    52                                                                45                                                                 54                                                                NaN                                                           55                                                     "No"                             "none"                                                                                  <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "6239248308460b617d0a3a1b"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             21-Mar-2022                        "21:15:06"                        21-Mar-2022                          "21:20:28"               "-4:00"     "5f5159955e42834858a38f2e"    21-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         13          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "No"                                                            NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           14                                                               "64"                                                        "25"                                                                     "53"                                                              "27"                                                       "68"                                                                    22                                                                49                                                                 41                                                                 51                                                           48                                                     "Yes"                            "none"                                                                                  <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "623a71dda6be9a84d555a9bc"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             22-Mar-2022                        "21:00:43"                        22-Mar-2022                          "21:03:25"               "-4:00"     "5f5159955e42834858a38f2e"    22-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         14          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "Yes"                                                           NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           69                                                               "32"                                                        "56"                                                                     "49"                                                              "69"                                                       "24"                                                                    49                                                                26                                                                NaN                                                                 51                                                           38                                                     "Yes"                            "nothing"                                                                               <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "623bc87600f3ac8298e02095"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             23-Mar-2022                        "21:24:14"                        23-Mar-2022                          "21:25:09"               "-4:00"     "5f5159955e42834858a38f2e"    23-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         15          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "No"                                                            NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           26                                                               "55"                                                        "24"                                                                     "61"                                                              "17"                                                       "50 - Moderately"                                                       25                                                                48                                                                 35                                                                 58                                                           33                                                     "Yes"                            "none"                                                                                  <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "623d1548beb4cdaefb69b394"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             24-Mar-2022                        "21:03:34"                        24-Mar-2022                          "21:05:10"               "-4:00"     "5f5159955e42834858a38f2e"    24-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         16          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "No"                                                            NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           56                                                               "31"                                                        "52"                                                                     "41"                                                              "67"                                                       "15"                                                                    56                                                                51                                                                 51                                                                 44                                                           58                                                     "Yes"                            "work reprimand"                                                                        <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        
    "623e6983107aac8029c259ee"    "Submission"    "5e617d2afddfbe07f6e838ca"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"             25-Mar-2022                        "21:15:12"                        25-Mar-2022                          "21:16:50"               "-4:00"     "5f5159955e42834858a38f2e"    25-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         17          "iOS"            NaN          "04.09.0008"    "0.00000,0.00000"                                  "Yes"                                                            "No"                                                            NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                       "No"                                                         "No"                                                  "No"                                                        "No"                                                          "No"                                                           69                                                               "22"                                                        "53"                                                                     "42"                                                              "61"                                                       "13"                                                                    55                                                                58                                                                 49                                                                 39                                                           43                                                     "Yes"                            "none"                                                                                  <missing>                                  <missing>            <missing>             <missing>             <missing>             <missing>             <missing>             <missing>             <missing>                NaN        

% Clean up entries so that all entries that should be numberic are. 
Night_Prompt = convertvars(Night_Prompt,23:width(Night_Prompt),'double');

% MY CODE - Preview duplicates
ind = hours(diff(Night_Prompt.Night_Prompt_SurveyStartedDate))==0 & Night_Prompt.MetricWireID(1:end-1)==Night_Prompt.MetricWireID(2:end);
[Night_Prompt.Night_Prompt_SurveyStartedDate([ind(2:end); false; false]), Night_Prompt.Night_Prompt_SurveyStartedDate([ind; false]), Night_Prompt.Night_Prompt_SurveyStartedDate([false; ind])]

ans = 5×3 datetime array
   17-Sep-2020   19-Sep-2020   19-Sep-2020
   28-Sep-2020   30-Sep-2020   30-Sep-2020
   30-Jun-2021   02-Jul-2021   02-Jul-2021
   02-Jul-2021   03-Jul-2021   03-Jul-2021
   03-Jul-2021   04-Jul-2021   04-Jul-2021

% Change dates
Night_Prompt.Night_Prompt_SurveyStartedDate([ind; false]) = Night_Prompt.Night_Prompt_SurveyStartedDate([ind; false])-caldays(1);

%Outerjoin
Night_Prompt.Properties.VariableNames('Night_Prompt_SurveyStartedDate')={'StudyDate'};
T4 = outerjoin(Data_EMA, Night_Prompt ,'Keys' , {'StudyDate',  'MetricWireID'}, 'MergeKeys', true, 'Type', 'left');
T4 = sortrows(T4, {'MetricWireID'})

T4 = 2225×64 table
     StartDate       EndDate        SubID       Group     StudyDay           MetricWireID           Protocol    StudyDate            ResponseId            ResponseType             StudyId                       SurveyId               SurveyName      Night_Prompt_SurveyStartedTime    Night_Prompt_SurveySubmittedDate    Night_Prompt_SurveySubmittedTime    TimeZone             TriggerId             TriggerDate    TriggerTime       TriggerName        TriggerIndex    DeviceOS     DeviceOSVersion     AppVersion     SubmissionLocation    DidYouDoAnyOfTheFollowingToday_TookYourOpioidMedication_methado    DidYouDoAnyOfTheFollowingToday_SpokeTo_orSaw_YourTherapist_    DidYouDoAnyOfTheFollowingToday_AttendedGroupTherapy_meeting_    DidYouDoAnyOfTheFollowingTodaySinceYourLastPrompt_    HowManyBagsOrPillsDidYouUse_    HowMuchMoney_sWorthOfCocaineDidYouHave_    HowManyDrinksDidYouHave__OneDrinkIsACanOfBeer_AGlassOfWine_ASho    HowManyJointsDidYouHave_    HowManyBenzodiazepine_orBenzos_PillsDidYouHave_    WhatDidYouUse_    DidYouDoAnyOfTheFollowingToday_DidSomethingDangerousAsAWayToTem    DidYouDoAnyOfTheFollowingToday_ShareNeedlesOrWorks_    DidYouDoAnyOfTheFollowingToday_HadUnprotectedSex_    DidYouDoAnyOfTheFollowingToday_TookADrugFromANewOrUnknownSource    DidYouDoAnyOfTheFollowingToday_UsedMoreThanIIntended_    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourFinancialSi    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourLoveLife_    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourRelationshi    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourRelations_1    HowMuchStressDidYouHaveInTheFollowingAreasToday_School_work_    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourLegalSituat    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourHealth_    HowMuchStressDidYouHaveInTheFollowingAreasToday_YourDayOverall_    ThroughTheDayToday_HowMuchDidYouFeel_SupportedByThePeopleInYour    HowMuchDoYouBelieveThisStatement_TomorrowWillBeABetterDayThanTo    HowMuchDoYouBelieveThisStatement_MyLifeIsMeaningful_    TodayOverallIHadPleasant_positive_SocialInteractions_    IsThereAnythingElseOfNoteThatHappenedToday_GoodOrBad_    IfYouHaveBeenPrescribedOtherMedications_DidYouTakeThoseOtherMed    DELETED_QUESTION    DELETED_QUESTION_1    DELETED_QUESTION_2    DELETED_QUESTION_3    DELETED_QUESTION_4    DELETED_QUESTION_5    DELETED_QUESTION_6    DELETED_QUESTION_7    DELETED_QUESTION_8
    ___________    ___________    _________    _______    ________    __________________________    ________    _________    __________________________    ____________    __________________________    __________________________    ______________    ______________________________    ________________________________    ________________________________    _________    __________________________    ___________    ___________    __________________    ____________    _________    _______________    ____________    __________________    _______________________________________________________________    ___________________________________________________________    ____________________________________________________________    __________________________________________________    ____________________________    _______________________________________    _______________________________________________________________    ________________________    _______________________________________________    ______________    _______________________________________________________________    ___________________________________________________    _________________________________________________    _______________________________________________________________    _____________________________________________________    _______________________________________________________________    _____________________________________________________________    _______________________________________________________________    _______________________________________________________________    ____________________________________________________________    _______________________________________________________________    ___________________________________________________________    _______________________________________________________________    _______________________________________________________________    _______________________________________________________________    ____________________________________________________    _____________________________________________________    _____________________________________________________    _______________________________________________________________    ________________    __________________    __________________    __________________    __________________    __________________    __________________    __________________    __________________

    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}        1       "5e617d2afddfbe07f6e838ca"       2        03/09/22     "62295c789ce0ce50d1ebcd61"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:00:08"                        09-Mar-2022                          "21:03:36"               "-5:00"      "5f5159955e42834858a38f2e"    09-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          1         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "Yes"                                                             NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            73                                                                62                                                                19                                                                 55                                                               72                                                                55                                                               52                                                               54                                                                 61                                                                 52                                                           59                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}        2       "5e617d2afddfbe07f6e838ca"       2        03/10/22     "622aadb257ab90e887b0c829"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:00:14"                        10-Mar-2022                          "21:02:25"               "-5:00"      "5f5159955e42834858a38f2e"    10-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          2         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "Yes"                                                             NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            65                                                                59                                                                18                                                                 49                                                               45                                                                15                                                               38                                                               44                                                                 49                                                                 68                                                           55                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}        3       "5e617d2afddfbe07f6e838ca"       2        03/11/22     "622bff1de2720180db350ac6"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:00:07"                        11-Mar-2022                          "21:02:04"               "-5:00"      "5f5159955e42834858a38f2e"    11-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          3         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "No"                                                             "No"                                                              NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            70                                                                17                                                                14                                                                 35                                                               73                                                                23                                                               53                                                               53                                                                 54                                                                 28                                                           31                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}        4       "5e617d2afddfbe07f6e838ca"       2        03/12/22     "622d51d9a87238437f8f05fb"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:03:14"                        12-Mar-2022                          "21:04:55"               "-5:00"      "5f5159955e42834858a38f2e"    12-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          4         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "No"                                                             "No"                                                              NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            73                                                                53                                                                18                                                                 25                                                               80                                                                 6                                                               25                                                              NaN                                                                 48                                                                 22                                                           33                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}        5       "5e617d2afddfbe07f6e838ca"       2        03/13/22     "622fc93ceb2b032e050e82db"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:15:07"                        13-Mar-2022                          "21:16:41"               "-4:00"      "5f5159955e42834858a38f2e"    13-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          5         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "No"                                                             "No"                                                              NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            45                                                                52                                                                61                                                                 53                                                               55                                                                62                                                               56                                                               56                                                                 59                                                                 59                                                           60                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}        6       "5e617d2afddfbe07f6e838ca"       2        03/14/22     "622fe95d668d4360b2527222"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:15:15"                        14-Mar-2022                          "21:18:20"               "-4:00"      "5f5159955e42834858a38f2e"    14-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          6         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "No"                                                             "Yes"                                                             NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            58                                                                31                                                                46                                                                 36                                                               73                                                                13                                                               64                                                               58                                                                 70                                                                 39                                                           48                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}        7       "5e617d2afddfbe07f6e838ca"       2        03/15/22     "62313de36450cf99ddac2885"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:15:17"                        15-Mar-2022                          "21:31:14"               "-4:00"      "5f5159955e42834858a38f2e"    15-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          7         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "Yes"                                                             NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            76                                                                25                                                                99                                                                 46                                                               77                                                                18                                                               35                                                               54                                                                 56                                                                 31                                                           24                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}        8       "5e617d2afddfbe07f6e838ca"       2        03/16/22     "6233500e141c1df0920ab8a4"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:00:06"                        16-Mar-2022                          "21:02:13"               "-4:00"      "5f5159955e42834858a38f2e"    16-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          8         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "Yes"                                                             NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            40                                                                38                                                                35                                                                 19                                                               45                                                                18                                                               45                                                               18                                                                 42                                                                 38                                                           32                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}        9       "5e617d2afddfbe07f6e838ca"       2        03/17/22     "6233db4ce9f1cf1c2094badf"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:05:08"                        17-Mar-2022                          "21:07:01"               "-4:00"      "5f5159955e42834858a38f2e"    17-Mar-2022    "21:00:00"     "SCHEDULE Trigger"          9         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "No"                                                              NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            76                                                                23                                                                21                                                                 49                                                               30                                                                 9                                                               77                                                               71                                                                 69                                                                 52                                                           29                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}       10       "5e617d2afddfbe07f6e838ca"       2        03/18/22     "62352b6bf6d02cf4e23cf823"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:00:17"                        18-Mar-2022                          "21:01:30"               "-4:00"      "5f5159955e42834858a38f2e"    18-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         10         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "Yes"                                                             NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            58                                                                62                                                               NaN                                                                 54                                                               58                                                                35                                                               34                                                               49                                                                 30                                                                 41                                                           54                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}       11       "5e617d2afddfbe07f6e838ca"       2        03/19/22     <missing>                     <missing>       <missing>                     <missing>                     <missing>                   <missing>                                 NaT                          <missing>                <missing>    <missing>                             NaT    <missing>      <missing>                 NaN         <missing>          NaN          <missing>       <missing>                                        <missing>                                                        <missing>                                                         NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                           NaN                                                               NaN                                                               NaN                                                                NaN                                                              NaN                                                               NaN                                                              NaN                                                              NaN                                                                NaN                                                                NaN                                                          NaN                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}       12       "5e617d2afddfbe07f6e838ca"       2        03/20/22     "6237ce618a28d169ba509415"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:00:10"                        20-Mar-2022                          "21:01:21"               "-4:00"      "5f5159955e42834858a38f2e"    20-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         12         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "No"                                                             "No"                                                              NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            78                                                                23                                                                53                                                                 56                                                               74                                                                58                                                               52                                                               45                                                                 54                                                                NaN                                                           55                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}       13       "5e617d2afddfbe07f6e838ca"       2        03/21/22     "6239248308460b617d0a3a1b"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:15:06"                        21-Mar-2022                          "21:20:28"               "-4:00"      "5f5159955e42834858a38f2e"    21-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         13         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "No"                                                              NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            14                                                                64                                                                25                                                                 53                                                               27                                                                68                                                               22                                                               49                                                                 41                                                                 51                                                           48                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}       14       "5e617d2afddfbe07f6e838ca"       2        03/22/22     "623a71dda6be9a84d555a9bc"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:00:43"                        22-Mar-2022                          "21:03:25"               "-4:00"      "5f5159955e42834858a38f2e"    22-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         14         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "Yes"                                                             NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            69                                                                32                                                                56                                                                 49                                                               69                                                                24                                                               49                                                               26                                                                NaN                                                                 51                                                           38                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}       15       "5e617d2afddfbe07f6e838ca"       2        03/23/22     "623bc87600f3ac8298e02095"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:24:14"                        23-Mar-2022                          "21:25:09"               "-4:00"      "5f5159955e42834858a38f2e"    23-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         15         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "No"                                                              NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            26                                                                55                                                                24                                                                 61                                                               17                                                               NaN                                                               25                                                               48                                                                 35                                                                 58                                                           33                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        
    09-Mar-2022    06-Apr-2022    2.176e+07    {'OUD'}       16       "5e617d2afddfbe07f6e838ca"       2        03/24/22     "623d1548beb4cdaefb69b394"    "Submission"    "5df13e7726139735c684868b"    "5df13f3826139735c6848801"    "Night Prompt"              "21:03:34"                        24-Mar-2022                          "21:05:10"               "-4:00"      "5f5159955e42834858a38f2e"    24-Mar-2022    "21:00:00"     "SCHEDULE Trigger"         16         "iOS"              NaN          "04.09.0008"    "0.00000,0.00000"                                "Yes"                                                            "No"                                                              NaN                                                        NaN                                        NaN                                   NaN                                                    NaN                                            NaN                                     NaN                               NaN                                        NaN                                                          NaN                                                   NaN                                                         NaN                                                           NaN                                                            56                                                                31                                                                52                                                                 41                                                               67                                                                15                                                               56                                                               51                                                                 51                                                                 44                                                           58                                                      NaN                                                      NaN                                                           NaN                                        NaN                  NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN                   NaN        

[BA]

Wow this is great. Thank you so much for your help! I can't thank you enough for doing this. Really really appreciate it!