how to create a column hierarchical index in pandas data frame
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
add a comment |
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
add a comment |
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
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
python pandas pivot
edited Jan 2 at 12:15
Manu Sharma
asked Jan 2 at 10:28
Manu SharmaManu Sharma
354517
354517
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
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 createMultiIndex
this new way?
– jezrael
Jan 2 at 12:19
1
It Worked like a charm. Thanks
– Manu Sharma
Jan 2 at 15:27
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
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 createMultiIndex
this new way?
– jezrael
Jan 2 at 12:19
1
It Worked like a charm. Thanks
– Manu Sharma
Jan 2 at 15:27
add a comment |
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
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 createMultiIndex
this new way?
– jezrael
Jan 2 at 12:19
1
It Worked like a charm. Thanks
– Manu Sharma
Jan 2 at 15:27
add a comment |
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
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
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 createMultiIndex
this new way?
– jezrael
Jan 2 at 12:19
1
It Worked like a charm. Thanks
– Manu Sharma
Jan 2 at 15:27
add a comment |
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 createMultiIndex
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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