Pivoting data with date as a row in Python










2















I have data that I've left in a format that will allow me to pivot on dates that look like:



Region 0 1 2 3
Date 2005-01-01 2005-02-01 2005-03-01 ....
East South Central 400 500 600
Pacific 100 200 150
.
.
Mountain 500 600 450


I need to pivot this table so it looks like:



0 Date Region value
1 2005-01-01 East South Central 400
2 2005-02-01 East South Central 500
3 2005-03-01 East South Central 600
.
.
4 2005-03-01 Pacific 100
4 2005-03-01 Pacific 200
4 2005-03-01 Pacific 150
.
.


Since both Date and Region are under one another I'm not sure how to melt or pivot around these strings so that I can get my desired output.



How can I go about this?










share|improve this question


























    2















    I have data that I've left in a format that will allow me to pivot on dates that look like:



    Region 0 1 2 3
    Date 2005-01-01 2005-02-01 2005-03-01 ....
    East South Central 400 500 600
    Pacific 100 200 150
    .
    .
    Mountain 500 600 450


    I need to pivot this table so it looks like:



    0 Date Region value
    1 2005-01-01 East South Central 400
    2 2005-02-01 East South Central 500
    3 2005-03-01 East South Central 600
    .
    .
    4 2005-03-01 Pacific 100
    4 2005-03-01 Pacific 200
    4 2005-03-01 Pacific 150
    .
    .


    Since both Date and Region are under one another I'm not sure how to melt or pivot around these strings so that I can get my desired output.



    How can I go about this?










    share|improve this question
























      2












      2








      2








      I have data that I've left in a format that will allow me to pivot on dates that look like:



      Region 0 1 2 3
      Date 2005-01-01 2005-02-01 2005-03-01 ....
      East South Central 400 500 600
      Pacific 100 200 150
      .
      .
      Mountain 500 600 450


      I need to pivot this table so it looks like:



      0 Date Region value
      1 2005-01-01 East South Central 400
      2 2005-02-01 East South Central 500
      3 2005-03-01 East South Central 600
      .
      .
      4 2005-03-01 Pacific 100
      4 2005-03-01 Pacific 200
      4 2005-03-01 Pacific 150
      .
      .


      Since both Date and Region are under one another I'm not sure how to melt or pivot around these strings so that I can get my desired output.



      How can I go about this?










      share|improve this question














      I have data that I've left in a format that will allow me to pivot on dates that look like:



      Region 0 1 2 3
      Date 2005-01-01 2005-02-01 2005-03-01 ....
      East South Central 400 500 600
      Pacific 100 200 150
      .
      .
      Mountain 500 600 450


      I need to pivot this table so it looks like:



      0 Date Region value
      1 2005-01-01 East South Central 400
      2 2005-02-01 East South Central 500
      3 2005-03-01 East South Central 600
      .
      .
      4 2005-03-01 Pacific 100
      4 2005-03-01 Pacific 200
      4 2005-03-01 Pacific 150
      .
      .


      Since both Date and Region are under one another I'm not sure how to melt or pivot around these strings so that I can get my desired output.



      How can I go about this?







      python python-3.x pandas dataframe pivot-table






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 15 '18 at 0:27









      HelloToEarthHelloToEarth

      504214




      504214






















          1 Answer
          1






          active

          oldest

          votes


















          1














          I think this is the solution you are looking for. Shown by example.



          import pandas as pd
          import numpy as np
          N=100
          regions = list('abcdef')
          df = pd.DataFrame([[i for i in range(N)], ['2016-'.format(i) for i in range(N)],
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N)),
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N))])
          df.index = ['Region', 'Date', 'a', 'b', 'c', 'd']
          print(df)


          This gives



           0 1 2 3 4 5 6 7 
          Region 0 1 2 3 4 5 6 7
          Date 2016-0 2016-1 2016-2 2016-3 2016-4 2016-5 2016-6 2016-7
          a 96 432 181 64 87 355 339 314
          b 360 23 162 98 450 78 114 109
          c 143 375 420 493 321 277 208 317
          d 371 144 207 108 163 67 465 130


          And the solution to pivot this into the form you want is



          df.transpose().melt(id_vars=['Date'], value_vars=['a', 'b', 'c', 'd'])


          which gives



           Date variable value
          0 2016-0 a 96
          1 2016-1 a 432
          2 2016-2 a 181
          3 2016-3 a 64
          4 2016-4 a 87
          5 2016-5 a 355
          6 2016-6 a 339
          7 2016-7 a 314
          8 2016-8 a 111
          9 2016-9 a 121
          10 2016-10 a 124
          11 2016-11 a 383
          12 2016-12 a 424
          13 2016-13 a 453
          ...
          393 2016-93 d 176
          394 2016-94 d 277
          395 2016-95 d 256
          396 2016-96 d 174
          397 2016-97 d 349
          398 2016-98 d 414
          399 2016-99 d 132





          share|improve this answer























          • Obviously you will need to replace ['a', 'b', 'c', 'd'] in the melt transform with a list of your regions, but you could get this list by using list(df.index)[2:]. Indexing from 2 to skip past the date and Region rows.

            – James Fulton
            Nov 15 '18 at 0:51










          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%2f53310781%2fpivoting-data-with-date-as-a-row-in-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 think this is the solution you are looking for. Shown by example.



          import pandas as pd
          import numpy as np
          N=100
          regions = list('abcdef')
          df = pd.DataFrame([[i for i in range(N)], ['2016-'.format(i) for i in range(N)],
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N)),
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N))])
          df.index = ['Region', 'Date', 'a', 'b', 'c', 'd']
          print(df)


          This gives



           0 1 2 3 4 5 6 7 
          Region 0 1 2 3 4 5 6 7
          Date 2016-0 2016-1 2016-2 2016-3 2016-4 2016-5 2016-6 2016-7
          a 96 432 181 64 87 355 339 314
          b 360 23 162 98 450 78 114 109
          c 143 375 420 493 321 277 208 317
          d 371 144 207 108 163 67 465 130


          And the solution to pivot this into the form you want is



          df.transpose().melt(id_vars=['Date'], value_vars=['a', 'b', 'c', 'd'])


          which gives



           Date variable value
          0 2016-0 a 96
          1 2016-1 a 432
          2 2016-2 a 181
          3 2016-3 a 64
          4 2016-4 a 87
          5 2016-5 a 355
          6 2016-6 a 339
          7 2016-7 a 314
          8 2016-8 a 111
          9 2016-9 a 121
          10 2016-10 a 124
          11 2016-11 a 383
          12 2016-12 a 424
          13 2016-13 a 453
          ...
          393 2016-93 d 176
          394 2016-94 d 277
          395 2016-95 d 256
          396 2016-96 d 174
          397 2016-97 d 349
          398 2016-98 d 414
          399 2016-99 d 132





          share|improve this answer























          • Obviously you will need to replace ['a', 'b', 'c', 'd'] in the melt transform with a list of your regions, but you could get this list by using list(df.index)[2:]. Indexing from 2 to skip past the date and Region rows.

            – James Fulton
            Nov 15 '18 at 0:51















          1














          I think this is the solution you are looking for. Shown by example.



          import pandas as pd
          import numpy as np
          N=100
          regions = list('abcdef')
          df = pd.DataFrame([[i for i in range(N)], ['2016-'.format(i) for i in range(N)],
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N)),
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N))])
          df.index = ['Region', 'Date', 'a', 'b', 'c', 'd']
          print(df)


          This gives



           0 1 2 3 4 5 6 7 
          Region 0 1 2 3 4 5 6 7
          Date 2016-0 2016-1 2016-2 2016-3 2016-4 2016-5 2016-6 2016-7
          a 96 432 181 64 87 355 339 314
          b 360 23 162 98 450 78 114 109
          c 143 375 420 493 321 277 208 317
          d 371 144 207 108 163 67 465 130


          And the solution to pivot this into the form you want is



          df.transpose().melt(id_vars=['Date'], value_vars=['a', 'b', 'c', 'd'])


          which gives



           Date variable value
          0 2016-0 a 96
          1 2016-1 a 432
          2 2016-2 a 181
          3 2016-3 a 64
          4 2016-4 a 87
          5 2016-5 a 355
          6 2016-6 a 339
          7 2016-7 a 314
          8 2016-8 a 111
          9 2016-9 a 121
          10 2016-10 a 124
          11 2016-11 a 383
          12 2016-12 a 424
          13 2016-13 a 453
          ...
          393 2016-93 d 176
          394 2016-94 d 277
          395 2016-95 d 256
          396 2016-96 d 174
          397 2016-97 d 349
          398 2016-98 d 414
          399 2016-99 d 132





          share|improve this answer























          • Obviously you will need to replace ['a', 'b', 'c', 'd'] in the melt transform with a list of your regions, but you could get this list by using list(df.index)[2:]. Indexing from 2 to skip past the date and Region rows.

            – James Fulton
            Nov 15 '18 at 0:51













          1












          1








          1







          I think this is the solution you are looking for. Shown by example.



          import pandas as pd
          import numpy as np
          N=100
          regions = list('abcdef')
          df = pd.DataFrame([[i for i in range(N)], ['2016-'.format(i) for i in range(N)],
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N)),
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N))])
          df.index = ['Region', 'Date', 'a', 'b', 'c', 'd']
          print(df)


          This gives



           0 1 2 3 4 5 6 7 
          Region 0 1 2 3 4 5 6 7
          Date 2016-0 2016-1 2016-2 2016-3 2016-4 2016-5 2016-6 2016-7
          a 96 432 181 64 87 355 339 314
          b 360 23 162 98 450 78 114 109
          c 143 375 420 493 321 277 208 317
          d 371 144 207 108 163 67 465 130


          And the solution to pivot this into the form you want is



          df.transpose().melt(id_vars=['Date'], value_vars=['a', 'b', 'c', 'd'])


          which gives



           Date variable value
          0 2016-0 a 96
          1 2016-1 a 432
          2 2016-2 a 181
          3 2016-3 a 64
          4 2016-4 a 87
          5 2016-5 a 355
          6 2016-6 a 339
          7 2016-7 a 314
          8 2016-8 a 111
          9 2016-9 a 121
          10 2016-10 a 124
          11 2016-11 a 383
          12 2016-12 a 424
          13 2016-13 a 453
          ...
          393 2016-93 d 176
          394 2016-94 d 277
          395 2016-95 d 256
          396 2016-96 d 174
          397 2016-97 d 349
          398 2016-98 d 414
          399 2016-99 d 132





          share|improve this answer













          I think this is the solution you are looking for. Shown by example.



          import pandas as pd
          import numpy as np
          N=100
          regions = list('abcdef')
          df = pd.DataFrame([[i for i in range(N)], ['2016-'.format(i) for i in range(N)],
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N)),
          list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N))])
          df.index = ['Region', 'Date', 'a', 'b', 'c', 'd']
          print(df)


          This gives



           0 1 2 3 4 5 6 7 
          Region 0 1 2 3 4 5 6 7
          Date 2016-0 2016-1 2016-2 2016-3 2016-4 2016-5 2016-6 2016-7
          a 96 432 181 64 87 355 339 314
          b 360 23 162 98 450 78 114 109
          c 143 375 420 493 321 277 208 317
          d 371 144 207 108 163 67 465 130


          And the solution to pivot this into the form you want is



          df.transpose().melt(id_vars=['Date'], value_vars=['a', 'b', 'c', 'd'])


          which gives



           Date variable value
          0 2016-0 a 96
          1 2016-1 a 432
          2 2016-2 a 181
          3 2016-3 a 64
          4 2016-4 a 87
          5 2016-5 a 355
          6 2016-6 a 339
          7 2016-7 a 314
          8 2016-8 a 111
          9 2016-9 a 121
          10 2016-10 a 124
          11 2016-11 a 383
          12 2016-12 a 424
          13 2016-13 a 453
          ...
          393 2016-93 d 176
          394 2016-94 d 277
          395 2016-95 d 256
          396 2016-96 d 174
          397 2016-97 d 349
          398 2016-98 d 414
          399 2016-99 d 132






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 15 '18 at 0:49









          James FultonJames Fulton

          1825




          1825












          • Obviously you will need to replace ['a', 'b', 'c', 'd'] in the melt transform with a list of your regions, but you could get this list by using list(df.index)[2:]. Indexing from 2 to skip past the date and Region rows.

            – James Fulton
            Nov 15 '18 at 0:51

















          • Obviously you will need to replace ['a', 'b', 'c', 'd'] in the melt transform with a list of your regions, but you could get this list by using list(df.index)[2:]. Indexing from 2 to skip past the date and Region rows.

            – James Fulton
            Nov 15 '18 at 0:51
















          Obviously you will need to replace ['a', 'b', 'c', 'd'] in the melt transform with a list of your regions, but you could get this list by using list(df.index)[2:]. Indexing from 2 to skip past the date and Region rows.

          – James Fulton
          Nov 15 '18 at 0:51





          Obviously you will need to replace ['a', 'b', 'c', 'd'] in the melt transform with a list of your regions, but you could get this list by using list(df.index)[2:]. Indexing from 2 to skip past the date and Region rows.

          – James Fulton
          Nov 15 '18 at 0:51



















          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%2f53310781%2fpivoting-data-with-date-as-a-row-in-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

          Top Tejano songwriter Luis Silva dead of heart attack at 64

          政党

          天津地下鉄3号線