Extending dates for values in a dataframe Python





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







0















I have data that looks like:



Year      Month       Region       Value1       Value2
2016 1 west 2 3
2016 1 east 4 5
2016 1 north 5 3
2016 2 west 6 4
2016 2 east 7 3
.
.
2016 12 west 2 3
2016 12 east 3 7
2016 12 north 6 8
2017 1 west 2 3
.
.
2018 7 west 1 1
2018 7 east 9 9
2018 7 north 5 1


I want to extend my values into Year 2021 for each Month but keep the previous values from the final month in the set (Month 7 of Year 2018).



The desired output would be attached to the ends of each set by Region, Month, and Year like:



2018        7         west         1            1
2018 7 east 9 9
2018 7 north 5 1
2018 8 west 1 1
2018 8 east 9 9
2018 8 north 5 1
2018 9 west 1 1
2018 9 east 9 9
2018 9 north 5 1
.
.
2019 7 west 1 1
2019 7 east 9 9
2019 7 north 5 1
.
.
2021 7 west 1 1
2021 7 east 9 9
2021 7 north 5 1


What would the best way to approach this be?










share|improve this question





























    0















    I have data that looks like:



    Year      Month       Region       Value1       Value2
    2016 1 west 2 3
    2016 1 east 4 5
    2016 1 north 5 3
    2016 2 west 6 4
    2016 2 east 7 3
    .
    .
    2016 12 west 2 3
    2016 12 east 3 7
    2016 12 north 6 8
    2017 1 west 2 3
    .
    .
    2018 7 west 1 1
    2018 7 east 9 9
    2018 7 north 5 1


    I want to extend my values into Year 2021 for each Month but keep the previous values from the final month in the set (Month 7 of Year 2018).



    The desired output would be attached to the ends of each set by Region, Month, and Year like:



    2018        7         west         1            1
    2018 7 east 9 9
    2018 7 north 5 1
    2018 8 west 1 1
    2018 8 east 9 9
    2018 8 north 5 1
    2018 9 west 1 1
    2018 9 east 9 9
    2018 9 north 5 1
    .
    .
    2019 7 west 1 1
    2019 7 east 9 9
    2019 7 north 5 1
    .
    .
    2021 7 west 1 1
    2021 7 east 9 9
    2021 7 north 5 1


    What would the best way to approach this be?










    share|improve this question

























      0












      0








      0


      0






      I have data that looks like:



      Year      Month       Region       Value1       Value2
      2016 1 west 2 3
      2016 1 east 4 5
      2016 1 north 5 3
      2016 2 west 6 4
      2016 2 east 7 3
      .
      .
      2016 12 west 2 3
      2016 12 east 3 7
      2016 12 north 6 8
      2017 1 west 2 3
      .
      .
      2018 7 west 1 1
      2018 7 east 9 9
      2018 7 north 5 1


      I want to extend my values into Year 2021 for each Month but keep the previous values from the final month in the set (Month 7 of Year 2018).



      The desired output would be attached to the ends of each set by Region, Month, and Year like:



      2018        7         west         1            1
      2018 7 east 9 9
      2018 7 north 5 1
      2018 8 west 1 1
      2018 8 east 9 9
      2018 8 north 5 1
      2018 9 west 1 1
      2018 9 east 9 9
      2018 9 north 5 1
      .
      .
      2019 7 west 1 1
      2019 7 east 9 9
      2019 7 north 5 1
      .
      .
      2021 7 west 1 1
      2021 7 east 9 9
      2021 7 north 5 1


      What would the best way to approach this be?










      share|improve this question














      I have data that looks like:



      Year      Month       Region       Value1       Value2
      2016 1 west 2 3
      2016 1 east 4 5
      2016 1 north 5 3
      2016 2 west 6 4
      2016 2 east 7 3
      .
      .
      2016 12 west 2 3
      2016 12 east 3 7
      2016 12 north 6 8
      2017 1 west 2 3
      .
      .
      2018 7 west 1 1
      2018 7 east 9 9
      2018 7 north 5 1


      I want to extend my values into Year 2021 for each Month but keep the previous values from the final month in the set (Month 7 of Year 2018).



      The desired output would be attached to the ends of each set by Region, Month, and Year like:



      2018        7         west         1            1
      2018 7 east 9 9
      2018 7 north 5 1
      2018 8 west 1 1
      2018 8 east 9 9
      2018 8 north 5 1
      2018 9 west 1 1
      2018 9 east 9 9
      2018 9 north 5 1
      .
      .
      2019 7 west 1 1
      2019 7 east 9 9
      2019 7 north 5 1
      .
      .
      2021 7 west 1 1
      2021 7 east 9 9
      2021 7 north 5 1


      What would the best way to approach this be?







      python python-3.x pandas python-2.7 dataframe






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 21 '18 at 23:23









      HelloToEarthHelloToEarth

      532215




      532215
























          1 Answer
          1






          active

          oldest

          votes


















          1














          I would create a function that uses pd.date_range with a freq of months:



          This functions assumes that you have three regions but can be modified for more.



          def myFunction(df, periods, freq='M'):
          # find the last date in the df
          last = pd.to_datetime(df.Year*10000+df.Month*100+1,format='%Y%m%d').max()

          # create new date range based on n periods with a freq of months
          newDates = pd.date_range(start=last, periods=periods+1, freq=freq)
          newDates = newDates[newDates>last]
          newDates = newDates[:periods+1]
          new_df = pd.DataFrame({'Date':newDates})[1:]

          # convert Date to year and month columns
          new_df['Year'] = new_df['Date'].dt.year
          new_df['Month'] = new_df['Date'].dt.month
          new_df.drop(columns='Date', inplace=True)

          # add your three regions and ffill values
          west = df[:-2].append([new_df], sort=False, ignore_index=True).ffill()
          east = df[:-1].append([new_df], sort=False, ignore_index=True).ffill()
          north = df.append([new_df], sort=False, ignore_index=True).ffill()

          # append you three region dfs and drop duplicates
          new = west.append([east,north], sort=False, ignore_index=True).drop_duplicates()
          return new.sort_values(['Year', 'Month']).reset_index().drop(columns='index')

          myFunction(df,3)


          with periods set to three this will return the next three months...



              Year    Month   Region  Value1  Value2
          0 2016 1 west 2.0 3.0
          1 2016 1 east 4.0 5.0
          2 2016 1 north 5.0 3.0
          3 2016 2 west 6.0 4.0
          4 2016 2 east 7.0 3.0
          5 2016 12 west 2.0 3.0
          6 2016 12 east 3.0 7.0
          7 2016 12 north 6.0 8.0
          8 2017 1 west 2.0 3.0
          9 2018 7 west 1.0 1.0
          10 2018 7 east 9.0 9.0
          11 2018 7 north 5.0 1.0
          12 2018 8 west 1.0 1.0
          13 2018 8 east 9.0 9.0
          14 2018 8 north 5.0 1.0
          15 2018 9 west 1.0 1.0
          16 2018 9 east 9.0 9.0
          17 2018 9 north 5.0 1.0
          18 2018 10 west 1.0 1.0
          19 2018 10 east 9.0 9.0
          20 2018 10 north 5.0 1.0





          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%2f53421843%2fextending-dates-for-values-in-a-dataframe-python%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









            1














            I would create a function that uses pd.date_range with a freq of months:



            This functions assumes that you have three regions but can be modified for more.



            def myFunction(df, periods, freq='M'):
            # find the last date in the df
            last = pd.to_datetime(df.Year*10000+df.Month*100+1,format='%Y%m%d').max()

            # create new date range based on n periods with a freq of months
            newDates = pd.date_range(start=last, periods=periods+1, freq=freq)
            newDates = newDates[newDates>last]
            newDates = newDates[:periods+1]
            new_df = pd.DataFrame({'Date':newDates})[1:]

            # convert Date to year and month columns
            new_df['Year'] = new_df['Date'].dt.year
            new_df['Month'] = new_df['Date'].dt.month
            new_df.drop(columns='Date', inplace=True)

            # add your three regions and ffill values
            west = df[:-2].append([new_df], sort=False, ignore_index=True).ffill()
            east = df[:-1].append([new_df], sort=False, ignore_index=True).ffill()
            north = df.append([new_df], sort=False, ignore_index=True).ffill()

            # append you three region dfs and drop duplicates
            new = west.append([east,north], sort=False, ignore_index=True).drop_duplicates()
            return new.sort_values(['Year', 'Month']).reset_index().drop(columns='index')

            myFunction(df,3)


            with periods set to three this will return the next three months...



                Year    Month   Region  Value1  Value2
            0 2016 1 west 2.0 3.0
            1 2016 1 east 4.0 5.0
            2 2016 1 north 5.0 3.0
            3 2016 2 west 6.0 4.0
            4 2016 2 east 7.0 3.0
            5 2016 12 west 2.0 3.0
            6 2016 12 east 3.0 7.0
            7 2016 12 north 6.0 8.0
            8 2017 1 west 2.0 3.0
            9 2018 7 west 1.0 1.0
            10 2018 7 east 9.0 9.0
            11 2018 7 north 5.0 1.0
            12 2018 8 west 1.0 1.0
            13 2018 8 east 9.0 9.0
            14 2018 8 north 5.0 1.0
            15 2018 9 west 1.0 1.0
            16 2018 9 east 9.0 9.0
            17 2018 9 north 5.0 1.0
            18 2018 10 west 1.0 1.0
            19 2018 10 east 9.0 9.0
            20 2018 10 north 5.0 1.0





            share|improve this answer




























              1














              I would create a function that uses pd.date_range with a freq of months:



              This functions assumes that you have three regions but can be modified for more.



              def myFunction(df, periods, freq='M'):
              # find the last date in the df
              last = pd.to_datetime(df.Year*10000+df.Month*100+1,format='%Y%m%d').max()

              # create new date range based on n periods with a freq of months
              newDates = pd.date_range(start=last, periods=periods+1, freq=freq)
              newDates = newDates[newDates>last]
              newDates = newDates[:periods+1]
              new_df = pd.DataFrame({'Date':newDates})[1:]

              # convert Date to year and month columns
              new_df['Year'] = new_df['Date'].dt.year
              new_df['Month'] = new_df['Date'].dt.month
              new_df.drop(columns='Date', inplace=True)

              # add your three regions and ffill values
              west = df[:-2].append([new_df], sort=False, ignore_index=True).ffill()
              east = df[:-1].append([new_df], sort=False, ignore_index=True).ffill()
              north = df.append([new_df], sort=False, ignore_index=True).ffill()

              # append you three region dfs and drop duplicates
              new = west.append([east,north], sort=False, ignore_index=True).drop_duplicates()
              return new.sort_values(['Year', 'Month']).reset_index().drop(columns='index')

              myFunction(df,3)


              with periods set to three this will return the next three months...



                  Year    Month   Region  Value1  Value2
              0 2016 1 west 2.0 3.0
              1 2016 1 east 4.0 5.0
              2 2016 1 north 5.0 3.0
              3 2016 2 west 6.0 4.0
              4 2016 2 east 7.0 3.0
              5 2016 12 west 2.0 3.0
              6 2016 12 east 3.0 7.0
              7 2016 12 north 6.0 8.0
              8 2017 1 west 2.0 3.0
              9 2018 7 west 1.0 1.0
              10 2018 7 east 9.0 9.0
              11 2018 7 north 5.0 1.0
              12 2018 8 west 1.0 1.0
              13 2018 8 east 9.0 9.0
              14 2018 8 north 5.0 1.0
              15 2018 9 west 1.0 1.0
              16 2018 9 east 9.0 9.0
              17 2018 9 north 5.0 1.0
              18 2018 10 west 1.0 1.0
              19 2018 10 east 9.0 9.0
              20 2018 10 north 5.0 1.0





              share|improve this answer


























                1












                1








                1







                I would create a function that uses pd.date_range with a freq of months:



                This functions assumes that you have three regions but can be modified for more.



                def myFunction(df, periods, freq='M'):
                # find the last date in the df
                last = pd.to_datetime(df.Year*10000+df.Month*100+1,format='%Y%m%d').max()

                # create new date range based on n periods with a freq of months
                newDates = pd.date_range(start=last, periods=periods+1, freq=freq)
                newDates = newDates[newDates>last]
                newDates = newDates[:periods+1]
                new_df = pd.DataFrame({'Date':newDates})[1:]

                # convert Date to year and month columns
                new_df['Year'] = new_df['Date'].dt.year
                new_df['Month'] = new_df['Date'].dt.month
                new_df.drop(columns='Date', inplace=True)

                # add your three regions and ffill values
                west = df[:-2].append([new_df], sort=False, ignore_index=True).ffill()
                east = df[:-1].append([new_df], sort=False, ignore_index=True).ffill()
                north = df.append([new_df], sort=False, ignore_index=True).ffill()

                # append you three region dfs and drop duplicates
                new = west.append([east,north], sort=False, ignore_index=True).drop_duplicates()
                return new.sort_values(['Year', 'Month']).reset_index().drop(columns='index')

                myFunction(df,3)


                with periods set to three this will return the next three months...



                    Year    Month   Region  Value1  Value2
                0 2016 1 west 2.0 3.0
                1 2016 1 east 4.0 5.0
                2 2016 1 north 5.0 3.0
                3 2016 2 west 6.0 4.0
                4 2016 2 east 7.0 3.0
                5 2016 12 west 2.0 3.0
                6 2016 12 east 3.0 7.0
                7 2016 12 north 6.0 8.0
                8 2017 1 west 2.0 3.0
                9 2018 7 west 1.0 1.0
                10 2018 7 east 9.0 9.0
                11 2018 7 north 5.0 1.0
                12 2018 8 west 1.0 1.0
                13 2018 8 east 9.0 9.0
                14 2018 8 north 5.0 1.0
                15 2018 9 west 1.0 1.0
                16 2018 9 east 9.0 9.0
                17 2018 9 north 5.0 1.0
                18 2018 10 west 1.0 1.0
                19 2018 10 east 9.0 9.0
                20 2018 10 north 5.0 1.0





                share|improve this answer













                I would create a function that uses pd.date_range with a freq of months:



                This functions assumes that you have three regions but can be modified for more.



                def myFunction(df, periods, freq='M'):
                # find the last date in the df
                last = pd.to_datetime(df.Year*10000+df.Month*100+1,format='%Y%m%d').max()

                # create new date range based on n periods with a freq of months
                newDates = pd.date_range(start=last, periods=periods+1, freq=freq)
                newDates = newDates[newDates>last]
                newDates = newDates[:periods+1]
                new_df = pd.DataFrame({'Date':newDates})[1:]

                # convert Date to year and month columns
                new_df['Year'] = new_df['Date'].dt.year
                new_df['Month'] = new_df['Date'].dt.month
                new_df.drop(columns='Date', inplace=True)

                # add your three regions and ffill values
                west = df[:-2].append([new_df], sort=False, ignore_index=True).ffill()
                east = df[:-1].append([new_df], sort=False, ignore_index=True).ffill()
                north = df.append([new_df], sort=False, ignore_index=True).ffill()

                # append you three region dfs and drop duplicates
                new = west.append([east,north], sort=False, ignore_index=True).drop_duplicates()
                return new.sort_values(['Year', 'Month']).reset_index().drop(columns='index')

                myFunction(df,3)


                with periods set to three this will return the next three months...



                    Year    Month   Region  Value1  Value2
                0 2016 1 west 2.0 3.0
                1 2016 1 east 4.0 5.0
                2 2016 1 north 5.0 3.0
                3 2016 2 west 6.0 4.0
                4 2016 2 east 7.0 3.0
                5 2016 12 west 2.0 3.0
                6 2016 12 east 3.0 7.0
                7 2016 12 north 6.0 8.0
                8 2017 1 west 2.0 3.0
                9 2018 7 west 1.0 1.0
                10 2018 7 east 9.0 9.0
                11 2018 7 north 5.0 1.0
                12 2018 8 west 1.0 1.0
                13 2018 8 east 9.0 9.0
                14 2018 8 north 5.0 1.0
                15 2018 9 west 1.0 1.0
                16 2018 9 east 9.0 9.0
                17 2018 9 north 5.0 1.0
                18 2018 10 west 1.0 1.0
                19 2018 10 east 9.0 9.0
                20 2018 10 north 5.0 1.0






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 22 '18 at 3:19









                ChrisChris

                3,2082523




                3,2082523
































                    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.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421843%2fextending-dates-for-values-in-a-dataframe-python%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







                    Popular posts from this blog

                    Guess what letter conforming each word

                    Port of Spain

                    Run scheduled task as local user group (not BUILTIN)