Python: How can I drop the first 5 minutes of each day in my time serie?












3














I have a dataframe with columns: Date of a transaction , Time of the transaction and Price. I want to drop the first and last 5 minutes in each day.



Here is an example:



----------------------------------------
Date | Time | Price
----------------------------------------
03/03/2014 | 09:36:36.814 | 43.90
---------------------------------------
03/03/2014 | 09:37:02.381 | 43.40
---------------------------------------
03/03/2014 | 09:41:02.381 | 43.40
---------------------------------------
03/03/2014 | 09:50:02.381 | 43.40
---------------------------------------


I want to get this output:



----------------------------------------
Date | Time | Price
---------------------------------------
03/03/2014 | 09:50:02.381 | 43.40
---------------------------------------


I need to do this for each day of time serie.
I tried this code:



  trades14081.insert(2,'DateTime',pd.to_datetime(trades14081['Date']+trades14081['Time'], format = "%d/%m/%Y%H:%M:%S.%f" ))
delta=datetime.timedelta(minutes=5)
i=0
j=0
start=
end=
while trades14081['Date'][i]==trades14081['Date'][j] and j<len(trades14081):
if trades14081['DateTime'][j]-trades14081['DateTime'][i]<delta:
j=j+1
else:
start.append(i)
end.append(j)
j=j+1
while trades14081['Date'][i]==trades14081['Date'][j] and j<len(trades14081):
j=j+1
i=j
for i in range(len(start)):
trades14081=trades14081.drop(trades14081.index[start[i]:end[i]])


But I keep on getting this error:




KeyError: 19996




>      12             l.append(j)
> 13 j=j+1
> ---> 14 while trades14081['Date'][i]==trades14081['Date'][j]:
> 15 j=j+1
> 16 i=j


19996 is the length of my dataframe trades14081.



Any ideas?










share|improve this question
























  • Can you share sample input and expected output? That'll make it clearer for us.
    – Mayank Porwal
    Nov 11 at 11:00










  • This problem domain looks like it's more suited to something like Observables. RxJS/Marble diagrams rxmarbles.com - egghead.io and angular university have good course around this if you were coming at it from a JS perspective. Perhaps github.com/ReactiveX/RxPY would work - have never used it..
    – JGFMK
    Nov 11 at 11:02
















3














I have a dataframe with columns: Date of a transaction , Time of the transaction and Price. I want to drop the first and last 5 minutes in each day.



Here is an example:



----------------------------------------
Date | Time | Price
----------------------------------------
03/03/2014 | 09:36:36.814 | 43.90
---------------------------------------
03/03/2014 | 09:37:02.381 | 43.40
---------------------------------------
03/03/2014 | 09:41:02.381 | 43.40
---------------------------------------
03/03/2014 | 09:50:02.381 | 43.40
---------------------------------------


I want to get this output:



----------------------------------------
Date | Time | Price
---------------------------------------
03/03/2014 | 09:50:02.381 | 43.40
---------------------------------------


I need to do this for each day of time serie.
I tried this code:



  trades14081.insert(2,'DateTime',pd.to_datetime(trades14081['Date']+trades14081['Time'], format = "%d/%m/%Y%H:%M:%S.%f" ))
delta=datetime.timedelta(minutes=5)
i=0
j=0
start=
end=
while trades14081['Date'][i]==trades14081['Date'][j] and j<len(trades14081):
if trades14081['DateTime'][j]-trades14081['DateTime'][i]<delta:
j=j+1
else:
start.append(i)
end.append(j)
j=j+1
while trades14081['Date'][i]==trades14081['Date'][j] and j<len(trades14081):
j=j+1
i=j
for i in range(len(start)):
trades14081=trades14081.drop(trades14081.index[start[i]:end[i]])


But I keep on getting this error:




KeyError: 19996




>      12             l.append(j)
> 13 j=j+1
> ---> 14 while trades14081['Date'][i]==trades14081['Date'][j]:
> 15 j=j+1
> 16 i=j


19996 is the length of my dataframe trades14081.



Any ideas?










share|improve this question
























  • Can you share sample input and expected output? That'll make it clearer for us.
    – Mayank Porwal
    Nov 11 at 11:00










  • This problem domain looks like it's more suited to something like Observables. RxJS/Marble diagrams rxmarbles.com - egghead.io and angular university have good course around this if you were coming at it from a JS perspective. Perhaps github.com/ReactiveX/RxPY would work - have never used it..
    – JGFMK
    Nov 11 at 11:02














3












3








3







I have a dataframe with columns: Date of a transaction , Time of the transaction and Price. I want to drop the first and last 5 minutes in each day.



Here is an example:



----------------------------------------
Date | Time | Price
----------------------------------------
03/03/2014 | 09:36:36.814 | 43.90
---------------------------------------
03/03/2014 | 09:37:02.381 | 43.40
---------------------------------------
03/03/2014 | 09:41:02.381 | 43.40
---------------------------------------
03/03/2014 | 09:50:02.381 | 43.40
---------------------------------------


I want to get this output:



----------------------------------------
Date | Time | Price
---------------------------------------
03/03/2014 | 09:50:02.381 | 43.40
---------------------------------------


I need to do this for each day of time serie.
I tried this code:



  trades14081.insert(2,'DateTime',pd.to_datetime(trades14081['Date']+trades14081['Time'], format = "%d/%m/%Y%H:%M:%S.%f" ))
delta=datetime.timedelta(minutes=5)
i=0
j=0
start=
end=
while trades14081['Date'][i]==trades14081['Date'][j] and j<len(trades14081):
if trades14081['DateTime'][j]-trades14081['DateTime'][i]<delta:
j=j+1
else:
start.append(i)
end.append(j)
j=j+1
while trades14081['Date'][i]==trades14081['Date'][j] and j<len(trades14081):
j=j+1
i=j
for i in range(len(start)):
trades14081=trades14081.drop(trades14081.index[start[i]:end[i]])


But I keep on getting this error:




KeyError: 19996




>      12             l.append(j)
> 13 j=j+1
> ---> 14 while trades14081['Date'][i]==trades14081['Date'][j]:
> 15 j=j+1
> 16 i=j


19996 is the length of my dataframe trades14081.



Any ideas?










share|improve this question















I have a dataframe with columns: Date of a transaction , Time of the transaction and Price. I want to drop the first and last 5 minutes in each day.



Here is an example:



----------------------------------------
Date | Time | Price
----------------------------------------
03/03/2014 | 09:36:36.814 | 43.90
---------------------------------------
03/03/2014 | 09:37:02.381 | 43.40
---------------------------------------
03/03/2014 | 09:41:02.381 | 43.40
---------------------------------------
03/03/2014 | 09:50:02.381 | 43.40
---------------------------------------


I want to get this output:



----------------------------------------
Date | Time | Price
---------------------------------------
03/03/2014 | 09:50:02.381 | 43.40
---------------------------------------


I need to do this for each day of time serie.
I tried this code:



  trades14081.insert(2,'DateTime',pd.to_datetime(trades14081['Date']+trades14081['Time'], format = "%d/%m/%Y%H:%M:%S.%f" ))
delta=datetime.timedelta(minutes=5)
i=0
j=0
start=
end=
while trades14081['Date'][i]==trades14081['Date'][j] and j<len(trades14081):
if trades14081['DateTime'][j]-trades14081['DateTime'][i]<delta:
j=j+1
else:
start.append(i)
end.append(j)
j=j+1
while trades14081['Date'][i]==trades14081['Date'][j] and j<len(trades14081):
j=j+1
i=j
for i in range(len(start)):
trades14081=trades14081.drop(trades14081.index[start[i]:end[i]])


But I keep on getting this error:




KeyError: 19996




>      12             l.append(j)
> 13 j=j+1
> ---> 14 while trades14081['Date'][i]==trades14081['Date'][j]:
> 15 j=j+1
> 16 i=j


19996 is the length of my dataframe trades14081.



Any ideas?







python python-3.x pandas pandas-groupby timedelta






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 11 at 12:05









jpp

90.4k2052101




90.4k2052101










asked Nov 11 at 10:34









Nada Baili

193




193












  • Can you share sample input and expected output? That'll make it clearer for us.
    – Mayank Porwal
    Nov 11 at 11:00










  • This problem domain looks like it's more suited to something like Observables. RxJS/Marble diagrams rxmarbles.com - egghead.io and angular university have good course around this if you were coming at it from a JS perspective. Perhaps github.com/ReactiveX/RxPY would work - have never used it..
    – JGFMK
    Nov 11 at 11:02


















  • Can you share sample input and expected output? That'll make it clearer for us.
    – Mayank Porwal
    Nov 11 at 11:00










  • This problem domain looks like it's more suited to something like Observables. RxJS/Marble diagrams rxmarbles.com - egghead.io and angular university have good course around this if you were coming at it from a JS perspective. Perhaps github.com/ReactiveX/RxPY would work - have never used it..
    – JGFMK
    Nov 11 at 11:02
















Can you share sample input and expected output? That'll make it clearer for us.
– Mayank Porwal
Nov 11 at 11:00




Can you share sample input and expected output? That'll make it clearer for us.
– Mayank Porwal
Nov 11 at 11:00












This problem domain looks like it's more suited to something like Observables. RxJS/Marble diagrams rxmarbles.com - egghead.io and angular university have good course around this if you were coming at it from a JS perspective. Perhaps github.com/ReactiveX/RxPY would work - have never used it..
– JGFMK
Nov 11 at 11:02




This problem domain looks like it's more suited to something like Observables. RxJS/Marble diagrams rxmarbles.com - egghead.io and angular university have good course around this if you were coming at it from a JS perspective. Perhaps github.com/ReactiveX/RxPY would work - have never used it..
– JGFMK
Nov 11 at 11:02












1 Answer
1






active

oldest

votes


















2















groupby + Boolean indexing



You can and should avoid Python-level loops. Here you can use groupby:



# convert strings to timedelta
df['Time'] = pd.to_timedelta(df['Time'])

# define offset from start to omit
offset = pd.Timedelta(minutes=5)

# apply Boolean filter to dataframe
res = df.loc[df['Time'] > df.groupby('Date')['Time'].transform('min') + offset]

print(res)

Date Time Price
4 03/03/2014 09:40:00 41
5 03/03/2014 09:46:00 42





share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53247879%2fpython-how-can-i-drop-the-first-5-minutes-of-each-day-in-my-time-serie%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2















    groupby + Boolean indexing



    You can and should avoid Python-level loops. Here you can use groupby:



    # convert strings to timedelta
    df['Time'] = pd.to_timedelta(df['Time'])

    # define offset from start to omit
    offset = pd.Timedelta(minutes=5)

    # apply Boolean filter to dataframe
    res = df.loc[df['Time'] > df.groupby('Date')['Time'].transform('min') + offset]

    print(res)

    Date Time Price
    4 03/03/2014 09:40:00 41
    5 03/03/2014 09:46:00 42





    share|improve this answer


























      2















      groupby + Boolean indexing



      You can and should avoid Python-level loops. Here you can use groupby:



      # convert strings to timedelta
      df['Time'] = pd.to_timedelta(df['Time'])

      # define offset from start to omit
      offset = pd.Timedelta(minutes=5)

      # apply Boolean filter to dataframe
      res = df.loc[df['Time'] > df.groupby('Date')['Time'].transform('min') + offset]

      print(res)

      Date Time Price
      4 03/03/2014 09:40:00 41
      5 03/03/2014 09:46:00 42





      share|improve this answer
























        2












        2








        2







        groupby + Boolean indexing



        You can and should avoid Python-level loops. Here you can use groupby:



        # convert strings to timedelta
        df['Time'] = pd.to_timedelta(df['Time'])

        # define offset from start to omit
        offset = pd.Timedelta(minutes=5)

        # apply Boolean filter to dataframe
        res = df.loc[df['Time'] > df.groupby('Date')['Time'].transform('min') + offset]

        print(res)

        Date Time Price
        4 03/03/2014 09:40:00 41
        5 03/03/2014 09:46:00 42





        share|improve this answer













        groupby + Boolean indexing



        You can and should avoid Python-level loops. Here you can use groupby:



        # convert strings to timedelta
        df['Time'] = pd.to_timedelta(df['Time'])

        # define offset from start to omit
        offset = pd.Timedelta(minutes=5)

        # apply Boolean filter to dataframe
        res = df.loc[df['Time'] > df.groupby('Date')['Time'].transform('min') + offset]

        print(res)

        Date Time Price
        4 03/03/2014 09:40:00 41
        5 03/03/2014 09:46:00 42






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 11 at 12:02









        jpp

        90.4k2052101




        90.4k2052101






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53247879%2fpython-how-can-i-drop-the-first-5-minutes-of-each-day-in-my-time-serie%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            Xamarin.form Move up view when keyboard appear

            Post-Redirect-Get with Spring WebFlux and Thymeleaf

            Anylogic : not able to use stopDelay()