how to create a column hierarchical index in pandas data frame












0















I have two data frames which look like following



Datframe A:



dataDate  name prediction       
2018-09-30 A 2.30
2018-10-01 A 1.51
2018-10-02 A 2.08
2018-10-03 A 1.82
2018-09-30 B 0.96
2018-10-01 B 6.52
2018-10-02 B 9.21
2018-10-03 B 17.43
2018-09-30 C 6.89
2018-10-01 C 6.10
2018-10-02 C 5.53
2018-10-03 C 1.91


I want to transform my data frame while creating a hierarchical index in the columns so that I can access a couple of columns at the same time. So, for example:-



Dataframe B:



dataDate   prediction       
Group pred
2018-09-30 A 2.30
2018-10-01 A 1.51
2018-10-02 A 2.08
2018-10-03 A 1.82
2018-09-30 B 0.96
2018-10-01 B 6.52
2018-10-02 B 9.21
2018-10-03 B 17.43
2018-09-30 C 6.89
2018-10-01 C 6.10
2018-10-02 C 5.53
2018-10-03 C 1.91


Data frame B has just two columns 'dataDate' and 'prediction' and prediction has then 'level 1' two columns 'Group' and 'pred'. Now I can access them by just prediction.



Please help me to transform Dataframe B from Dataframe A and vice-versa with pandas?










share|improve this question





























    0















    I have two data frames which look like following



    Datframe A:



    dataDate  name prediction       
    2018-09-30 A 2.30
    2018-10-01 A 1.51
    2018-10-02 A 2.08
    2018-10-03 A 1.82
    2018-09-30 B 0.96
    2018-10-01 B 6.52
    2018-10-02 B 9.21
    2018-10-03 B 17.43
    2018-09-30 C 6.89
    2018-10-01 C 6.10
    2018-10-02 C 5.53
    2018-10-03 C 1.91


    I want to transform my data frame while creating a hierarchical index in the columns so that I can access a couple of columns at the same time. So, for example:-



    Dataframe B:



    dataDate   prediction       
    Group pred
    2018-09-30 A 2.30
    2018-10-01 A 1.51
    2018-10-02 A 2.08
    2018-10-03 A 1.82
    2018-09-30 B 0.96
    2018-10-01 B 6.52
    2018-10-02 B 9.21
    2018-10-03 B 17.43
    2018-09-30 C 6.89
    2018-10-01 C 6.10
    2018-10-02 C 5.53
    2018-10-03 C 1.91


    Data frame B has just two columns 'dataDate' and 'prediction' and prediction has then 'level 1' two columns 'Group' and 'pred'. Now I can access them by just prediction.



    Please help me to transform Dataframe B from Dataframe A and vice-versa with pandas?










    share|improve this question



























      0












      0








      0








      I have two data frames which look like following



      Datframe A:



      dataDate  name prediction       
      2018-09-30 A 2.30
      2018-10-01 A 1.51
      2018-10-02 A 2.08
      2018-10-03 A 1.82
      2018-09-30 B 0.96
      2018-10-01 B 6.52
      2018-10-02 B 9.21
      2018-10-03 B 17.43
      2018-09-30 C 6.89
      2018-10-01 C 6.10
      2018-10-02 C 5.53
      2018-10-03 C 1.91


      I want to transform my data frame while creating a hierarchical index in the columns so that I can access a couple of columns at the same time. So, for example:-



      Dataframe B:



      dataDate   prediction       
      Group pred
      2018-09-30 A 2.30
      2018-10-01 A 1.51
      2018-10-02 A 2.08
      2018-10-03 A 1.82
      2018-09-30 B 0.96
      2018-10-01 B 6.52
      2018-10-02 B 9.21
      2018-10-03 B 17.43
      2018-09-30 C 6.89
      2018-10-01 C 6.10
      2018-10-02 C 5.53
      2018-10-03 C 1.91


      Data frame B has just two columns 'dataDate' and 'prediction' and prediction has then 'level 1' two columns 'Group' and 'pred'. Now I can access them by just prediction.



      Please help me to transform Dataframe B from Dataframe A and vice-versa with pandas?










      share|improve this question
















      I have two data frames which look like following



      Datframe A:



      dataDate  name prediction       
      2018-09-30 A 2.30
      2018-10-01 A 1.51
      2018-10-02 A 2.08
      2018-10-03 A 1.82
      2018-09-30 B 0.96
      2018-10-01 B 6.52
      2018-10-02 B 9.21
      2018-10-03 B 17.43
      2018-09-30 C 6.89
      2018-10-01 C 6.10
      2018-10-02 C 5.53
      2018-10-03 C 1.91


      I want to transform my data frame while creating a hierarchical index in the columns so that I can access a couple of columns at the same time. So, for example:-



      Dataframe B:



      dataDate   prediction       
      Group pred
      2018-09-30 A 2.30
      2018-10-01 A 1.51
      2018-10-02 A 2.08
      2018-10-03 A 1.82
      2018-09-30 B 0.96
      2018-10-01 B 6.52
      2018-10-02 B 9.21
      2018-10-03 B 17.43
      2018-09-30 C 6.89
      2018-10-01 C 6.10
      2018-10-02 C 5.53
      2018-10-03 C 1.91


      Data frame B has just two columns 'dataDate' and 'prediction' and prediction has then 'level 1' two columns 'Group' and 'pred'. Now I can access them by just prediction.



      Please help me to transform Dataframe B from Dataframe A and vice-versa with pandas?







      python pandas pivot






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 2 at 12:15







      Manu Sharma

















      asked Jan 2 at 10:28









      Manu SharmaManu Sharma

      354517




      354517
























          1 Answer
          1






          active

          oldest

          votes


















          3














          Use set_index for first column to DatatimeIndex if necessary and then assign new MultiIndex by MultiIndex.from_product:



          df = df.set_index('dataDate')
          df.columns = pd.MultiIndex.from_product([['prediction'],['Group','pred']])
          #alternative
          #df.columns = [['prediction'] * len(df.columns),['Group','pred']]
          print (df)
          prediction
          Group pred
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91


          For converting I suggest not change columns names in first level, only define new level Group:



          df1 = df.set_index('dataDate')
          df1.columns = pd.MultiIndex.from_product([['Group'],df1.columns])
          print (df1)
          Group
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91

          df = df1['Group'].copy()
          print (df)
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91





          share|improve this answer


























          • Thank you so much. Also pls help me with, if I have a structure of data frame B, which is 'df' in your answer, how can I transform it in the initial data frame

            – Manu Sharma
            Jan 2 at 12:18











          • @ManuSharma - Please check edit, is possible create MultiIndex this new way?

            – jezrael
            Jan 2 at 12:19






          • 1





            It Worked like a charm. Thanks

            – Manu Sharma
            Jan 2 at 15:27











          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%2f54004687%2fhow-to-create-a-column-hierarchical-index-in-pandas-data-frame%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









          3














          Use set_index for first column to DatatimeIndex if necessary and then assign new MultiIndex by MultiIndex.from_product:



          df = df.set_index('dataDate')
          df.columns = pd.MultiIndex.from_product([['prediction'],['Group','pred']])
          #alternative
          #df.columns = [['prediction'] * len(df.columns),['Group','pred']]
          print (df)
          prediction
          Group pred
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91


          For converting I suggest not change columns names in first level, only define new level Group:



          df1 = df.set_index('dataDate')
          df1.columns = pd.MultiIndex.from_product([['Group'],df1.columns])
          print (df1)
          Group
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91

          df = df1['Group'].copy()
          print (df)
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91





          share|improve this answer


























          • Thank you so much. Also pls help me with, if I have a structure of data frame B, which is 'df' in your answer, how can I transform it in the initial data frame

            – Manu Sharma
            Jan 2 at 12:18











          • @ManuSharma - Please check edit, is possible create MultiIndex this new way?

            – jezrael
            Jan 2 at 12:19






          • 1





            It Worked like a charm. Thanks

            – Manu Sharma
            Jan 2 at 15:27
















          3














          Use set_index for first column to DatatimeIndex if necessary and then assign new MultiIndex by MultiIndex.from_product:



          df = df.set_index('dataDate')
          df.columns = pd.MultiIndex.from_product([['prediction'],['Group','pred']])
          #alternative
          #df.columns = [['prediction'] * len(df.columns),['Group','pred']]
          print (df)
          prediction
          Group pred
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91


          For converting I suggest not change columns names in first level, only define new level Group:



          df1 = df.set_index('dataDate')
          df1.columns = pd.MultiIndex.from_product([['Group'],df1.columns])
          print (df1)
          Group
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91

          df = df1['Group'].copy()
          print (df)
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91





          share|improve this answer


























          • Thank you so much. Also pls help me with, if I have a structure of data frame B, which is 'df' in your answer, how can I transform it in the initial data frame

            – Manu Sharma
            Jan 2 at 12:18











          • @ManuSharma - Please check edit, is possible create MultiIndex this new way?

            – jezrael
            Jan 2 at 12:19






          • 1





            It Worked like a charm. Thanks

            – Manu Sharma
            Jan 2 at 15:27














          3












          3








          3







          Use set_index for first column to DatatimeIndex if necessary and then assign new MultiIndex by MultiIndex.from_product:



          df = df.set_index('dataDate')
          df.columns = pd.MultiIndex.from_product([['prediction'],['Group','pred']])
          #alternative
          #df.columns = [['prediction'] * len(df.columns),['Group','pred']]
          print (df)
          prediction
          Group pred
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91


          For converting I suggest not change columns names in first level, only define new level Group:



          df1 = df.set_index('dataDate')
          df1.columns = pd.MultiIndex.from_product([['Group'],df1.columns])
          print (df1)
          Group
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91

          df = df1['Group'].copy()
          print (df)
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91





          share|improve this answer















          Use set_index for first column to DatatimeIndex if necessary and then assign new MultiIndex by MultiIndex.from_product:



          df = df.set_index('dataDate')
          df.columns = pd.MultiIndex.from_product([['prediction'],['Group','pred']])
          #alternative
          #df.columns = [['prediction'] * len(df.columns),['Group','pred']]
          print (df)
          prediction
          Group pred
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91


          For converting I suggest not change columns names in first level, only define new level Group:



          df1 = df.set_index('dataDate')
          df1.columns = pd.MultiIndex.from_product([['Group'],df1.columns])
          print (df1)
          Group
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91

          df = df1['Group'].copy()
          print (df)
          name prediction
          dataDate
          2018-09-30 A 2.30
          2018-10-01 A 1.51
          2018-10-02 A 2.08
          2018-10-03 A 1.82
          2018-09-30 B 0.96
          2018-10-01 B 6.52
          2018-10-02 B 9.21
          2018-10-03 B 17.43
          2018-09-30 C 6.89
          2018-10-01 C 6.10
          2018-10-02 C 5.53
          2018-10-03 C 1.91






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jan 2 at 12:19

























          answered Jan 2 at 10:31









          jezraeljezrael

          345k25300371




          345k25300371













          • Thank you so much. Also pls help me with, if I have a structure of data frame B, which is 'df' in your answer, how can I transform it in the initial data frame

            – Manu Sharma
            Jan 2 at 12:18











          • @ManuSharma - Please check edit, is possible create MultiIndex this new way?

            – jezrael
            Jan 2 at 12:19






          • 1





            It Worked like a charm. Thanks

            – Manu Sharma
            Jan 2 at 15:27



















          • Thank you so much. Also pls help me with, if I have a structure of data frame B, which is 'df' in your answer, how can I transform it in the initial data frame

            – Manu Sharma
            Jan 2 at 12:18











          • @ManuSharma - Please check edit, is possible create MultiIndex this new way?

            – jezrael
            Jan 2 at 12:19






          • 1





            It Worked like a charm. Thanks

            – Manu Sharma
            Jan 2 at 15:27

















          Thank you so much. Also pls help me with, if I have a structure of data frame B, which is 'df' in your answer, how can I transform it in the initial data frame

          – Manu Sharma
          Jan 2 at 12:18





          Thank you so much. Also pls help me with, if I have a structure of data frame B, which is 'df' in your answer, how can I transform it in the initial data frame

          – Manu Sharma
          Jan 2 at 12:18













          @ManuSharma - Please check edit, is possible create MultiIndex this new way?

          – jezrael
          Jan 2 at 12:19





          @ManuSharma - Please check edit, is possible create MultiIndex this new way?

          – jezrael
          Jan 2 at 12:19




          1




          1





          It Worked like a charm. Thanks

          – Manu Sharma
          Jan 2 at 15:27





          It Worked like a charm. Thanks

          – Manu Sharma
          Jan 2 at 15:27




















          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%2f54004687%2fhow-to-create-a-column-hierarchical-index-in-pandas-data-frame%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

          Monofisismo

          Angular Downloading a file using contenturl with Basic Authentication

          Olmecas