Pandas: dataframe transformation using pivot
up vote
0
down vote
favorite
I have a data frame in the below format:
Date Id A B C D E
2018-01-28 5937.0 11.000000 11.000000 10.000000 10.000000 10.000000
2018-01-21 5937.0 10.000000 10.000000 10.000000 10.000000 10.000000
I want to change the data into the below format:
Id 2018-01-28 2018-01-21
A 5937.0 11.000000 10.000000
B 5937.0 11.000000 10.000000
C 5937.0 10.000000 10.000000
D 5937.0 10.000000 10.000000
E 5937.0 10.000000 10.000000
What is the best method to carry out following transformation. I have been using pivot but its not working(I am not very good with pivot)
python pandas pivot transformation
asked Nov 12 at 8:57
apoorv parmar
317
add a comment |
up vote
0
down vote
favorite
I have a data frame in the below format:
Date Id A B C D E
2018-01-28 5937.0 11.000000 11.000000 10.000000 10.000000 10.000000
2018-01-21 5937.0 10.000000 10.000000 10.000000 10.000000 10.000000
I want to change the data into the below format:
Id 2018-01-28 2018-01-21
A 5937.0 11.000000 10.000000
B 5937.0 11.000000 10.000000
C 5937.0 10.000000 10.000000
D 5937.0 10.000000 10.000000
E 5937.0 10.000000 10.000000
What is the best method to carry out following transformation. I have been using pivot but its not working(I am not very good with pivot)
python pandas pivot transformation
asked Nov 12 at 8:57
apoorv parmar
317
You can check this stackoverflow.com/questions/41861846/…. It can help.
– Rishi Bansal
Nov 12 at 9:17
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a data frame in the below format:
Date Id A B C D E
2018-01-28 5937.0 11.000000 11.000000 10.000000 10.000000 10.000000
2018-01-21 5937.0 10.000000 10.000000 10.000000 10.000000 10.000000
I want to change the data into the below format:
Id 2018-01-28 2018-01-21
A 5937.0 11.000000 10.000000
B 5937.0 11.000000 10.000000
C 5937.0 10.000000 10.000000
D 5937.0 10.000000 10.000000
E 5937.0 10.000000 10.000000
What is the best method to carry out following transformation. I have been using pivot but its not working(I am not very good with pivot)
python pandas pivot transformation
asked Nov 12 at 8:57
apoorv parmar
317
I have a data frame in the below format:
Date Id A B C D E
2018-01-28 5937.0 11.000000 11.000000 10.000000 10.000000 10.000000
2018-01-21 5937.0 10.000000 10.000000 10.000000 10.000000 10.000000
I want to change the data into the below format:
Id 2018-01-28 2018-01-21
A 5937.0 11.000000 10.000000
B 5937.0 11.000000 10.000000
C 5937.0 10.000000 10.000000
D 5937.0 10.000000 10.000000
E 5937.0 10.000000 10.000000
What is the best method to carry out following transformation. I have been using pivot but its not working(I am not very good with pivot)
python pandas pivot transformation
python pandas pivot transformation
asked Nov 12 at 8:57
apoorv parmar
317
asked Nov 12 at 8:57
apoorv parmar
317
asked Nov 12 at 8:57
apoorv parmar
317
asked Nov 12 at 8:57
apoorv parmar
317
asked Nov 12 at 8:57
apoorv parmar
317
317
You can check this stackoverflow.com/questions/41861846/…. It can help.
– Rishi Bansal
Nov 12 at 9:17
add a comment |
You can check this stackoverflow.com/questions/41861846/…. It can help.
– Rishi Bansal
Nov 12 at 9:17
You can check this stackoverflow.com/questions/41861846/…. It can help.
– Rishi Bansal
Nov 12 at 9:17
You can check this stackoverflow.com/questions/41861846/…. It can help.
– Rishi Bansal
Nov 12 at 9:17
add a comment |
3 Answers
3
active
oldest
votes
up vote
2
down vote
accepted
Use set_index followed by stack and unstack with reset_index:
df1 = df.set_index(['Date','Id']).stack().unstack(0).reset_index(0)
print(df1)
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
df1=df.set_index(['Date','Id']).stack().unstack(0).reset_index(0).rename_axis(None,1)
print(df1)
Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:33
Sandeep Kadapa
5,642427
add a comment |
up vote
1
down vote
I would do this using melt and pivot_table:
(df.melt(['Date', 'Id'])
.pivot_table(index=['variable', 'Id'], columns='Date', values='value')
.reset_index())
Date variable Id 2018-01-21 2018-01-28
0 A 5937.0 10.0 11.0
1 B 5937.0 10.0 11.0
2 C 5937.0 10.0 10.0
3 D 5937.0 10.0 10.0
4 E 5937.0 10.0 10.0
answered Nov 12 at 9:41
coldspeed
115k18106185
add a comment |
up vote
1
down vote
Using pivot:
(df.pivot_table(values=["A", "B", "C", "D", "E"], columns=["Id", "Date"])
.unstack()
.reset_index(1) # Multi-index level 1 = Id
.rename_axis(None, 1)) # Set columns name to None (not Date)
Output:
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:58
Edgar R. Mondragón
1,4061619
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',
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
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%2f53258706%2fpandas-dataframe-transformation-using-pivot%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
Use set_index followed by stack and unstack with reset_index:
df1 = df.set_index(['Date','Id']).stack().unstack(0).reset_index(0)
print(df1)
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
df1=df.set_index(['Date','Id']).stack().unstack(0).reset_index(0).rename_axis(None,1)
print(df1)
Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:33
Sandeep Kadapa
5,642427
add a comment |
up vote
2
down vote
accepted
Use set_index followed by stack and unstack with reset_index:
df1 = df.set_index(['Date','Id']).stack().unstack(0).reset_index(0)
print(df1)
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
df1=df.set_index(['Date','Id']).stack().unstack(0).reset_index(0).rename_axis(None,1)
print(df1)
Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:33
Sandeep Kadapa
5,642427
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
Use set_index followed by stack and unstack with reset_index:
df1 = df.set_index(['Date','Id']).stack().unstack(0).reset_index(0)
print(df1)
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
df1=df.set_index(['Date','Id']).stack().unstack(0).reset_index(0).rename_axis(None,1)
print(df1)
Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:33
Sandeep Kadapa
5,642427
Use set_index followed by stack and unstack with reset_index:
df1 = df.set_index(['Date','Id']).stack().unstack(0).reset_index(0)
print(df1)
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
df1=df.set_index(['Date','Id']).stack().unstack(0).reset_index(0).rename_axis(None,1)
print(df1)
Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:33
Sandeep Kadapa
5,642427
edited Nov 12 at 9:47
answered Nov 12 at 9:33
Sandeep Kadapa
5,642427
answered Nov 12 at 9:33
Sandeep Kadapa
5,642427
answered Nov 12 at 9:33
Sandeep Kadapa
5,642427
5,642427
add a comment |
add a comment |
up vote
1
down vote
I would do this using melt and pivot_table:
(df.melt(['Date', 'Id'])
.pivot_table(index=['variable', 'Id'], columns='Date', values='value')
.reset_index())
Date variable Id 2018-01-21 2018-01-28
0 A 5937.0 10.0 11.0
1 B 5937.0 10.0 11.0
2 C 5937.0 10.0 10.0
3 D 5937.0 10.0 10.0
4 E 5937.0 10.0 10.0
answered Nov 12 at 9:41
coldspeed
115k18106185
add a comment |
up vote
1
down vote
I would do this using melt and pivot_table:
(df.melt(['Date', 'Id'])
.pivot_table(index=['variable', 'Id'], columns='Date', values='value')
.reset_index())
Date variable Id 2018-01-21 2018-01-28
0 A 5937.0 10.0 11.0
1 B 5937.0 10.0 11.0
2 C 5937.0 10.0 10.0
3 D 5937.0 10.0 10.0
4 E 5937.0 10.0 10.0
answered Nov 12 at 9:41
coldspeed
115k18106185
add a comment |
up vote
1
down vote
up vote
1
down vote
I would do this using melt and pivot_table:
(df.melt(['Date', 'Id'])
.pivot_table(index=['variable', 'Id'], columns='Date', values='value')
.reset_index())
Date variable Id 2018-01-21 2018-01-28
0 A 5937.0 10.0 11.0
1 B 5937.0 10.0 11.0
2 C 5937.0 10.0 10.0
3 D 5937.0 10.0 10.0
4 E 5937.0 10.0 10.0
answered Nov 12 at 9:41
coldspeed
115k18106185
I would do this using melt and pivot_table:
(df.melt(['Date', 'Id'])
.pivot_table(index=['variable', 'Id'], columns='Date', values='value')
.reset_index())
Date variable Id 2018-01-21 2018-01-28
0 A 5937.0 10.0 11.0
1 B 5937.0 10.0 11.0
2 C 5937.0 10.0 10.0
3 D 5937.0 10.0 10.0
4 E 5937.0 10.0 10.0
answered Nov 12 at 9:41
coldspeed
115k18106185
answered Nov 12 at 9:41
coldspeed
115k18106185
answered Nov 12 at 9:41
coldspeed
115k18106185
answered Nov 12 at 9:41
coldspeed
115k18106185
115k18106185
add a comment |
add a comment |
up vote
1
down vote
Using pivot:
(df.pivot_table(values=["A", "B", "C", "D", "E"], columns=["Id", "Date"])
.unstack()
.reset_index(1) # Multi-index level 1 = Id
.rename_axis(None, 1)) # Set columns name to None (not Date)
Output:
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:58
Edgar R. Mondragón
1,4061619
add a comment |
up vote
1
down vote
Using pivot:
(df.pivot_table(values=["A", "B", "C", "D", "E"], columns=["Id", "Date"])
.unstack()
.reset_index(1) # Multi-index level 1 = Id
.rename_axis(None, 1)) # Set columns name to None (not Date)
Output:
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:58
Edgar R. Mondragón
1,4061619
add a comment |
up vote
1
down vote
up vote
1
down vote
Using pivot:
(df.pivot_table(values=["A", "B", "C", "D", "E"], columns=["Id", "Date"])
.unstack()
.reset_index(1) # Multi-index level 1 = Id
.rename_axis(None, 1)) # Set columns name to None (not Date)
Output:
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:58
Edgar R. Mondragón
1,4061619
Using pivot:
(df.pivot_table(values=["A", "B", "C", "D", "E"], columns=["Id", "Date"])
.unstack()
.reset_index(1) # Multi-index level 1 = Id
.rename_axis(None, 1)) # Set columns name to None (not Date)
Output:
Date Id 2018-01-21 2018-01-28
A 5937.0 10.0 11.0
B 5937.0 10.0 11.0
C 5937.0 10.0 10.0
D 5937.0 10.0 10.0
E 5937.0 10.0 10.0
answered Nov 12 at 9:58
Edgar R. Mondragón
1,4061619
answered Nov 12 at 9:58
Edgar R. Mondragón
1,4061619
answered Nov 12 at 9:58
Edgar R. Mondragón
1,4061619
answered Nov 12 at 9:58
Edgar R. Mondragón
1,4061619
1,4061619
add a comment |
add a comment |
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
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%2f53258706%2fpandas-dataframe-transformation-using-pivot%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
You can check this stackoverflow.com/questions/41861846/…. It can help.
– Rishi Bansal
Nov 12 at 9:17