Pandas sort data frame by logical day
1
I have the following resulting pandas DateFrame:
How can I get this to sort properly? For example have the sort so that Day 2 comes after Day 1, not Day 11. As seen in Group 2 below?
python pandas sorting dataframe indexing
edited Nov 24 '18 at 0:31
jpp
96.7k2158109
asked Nov 24 '18 at 0:09
TheCuriouslyCodingFoxahTheCuriouslyCodingFoxah
647
add a comment |
1
I have the following resulting pandas DateFrame:
How can I get this to sort properly? For example have the sort so that Day 2 comes after Day 1, not Day 11. As seen in Group 2 below?
python pandas sorting dataframe indexing
edited Nov 24 '18 at 0:31
jpp
96.7k2158109
asked Nov 24 '18 at 0:09
TheCuriouslyCodingFoxahTheCuriouslyCodingFoxah
647
add a comment |
1
1
1
I have the following resulting pandas DateFrame:
How can I get this to sort properly? For example have the sort so that Day 2 comes after Day 1, not Day 11. As seen in Group 2 below?
python pandas sorting dataframe indexing
edited Nov 24 '18 at 0:31
jpp
96.7k2158109
asked Nov 24 '18 at 0:09
TheCuriouslyCodingFoxahTheCuriouslyCodingFoxah
647
I have the following resulting pandas DateFrame:
How can I get this to sort properly? For example have the sort so that Day 2 comes after Day 1, not Day 11. As seen in Group 2 below?
python pandas sorting dataframe indexing
python pandas sorting dataframe indexing
edited Nov 24 '18 at 0:31
jpp
96.7k2158109
asked Nov 24 '18 at 0:09
TheCuriouslyCodingFoxahTheCuriouslyCodingFoxah
647
edited Nov 24 '18 at 0:31
jpp
96.7k2158109
asked Nov 24 '18 at 0:09
TheCuriouslyCodingFoxahTheCuriouslyCodingFoxah
647
edited Nov 24 '18 at 0:31
jpp
96.7k2158109
edited Nov 24 '18 at 0:31
jpp
96.7k2158109
edited Nov 24 '18 at 0:31
jpp
96.7k2158109
96.7k2158109
asked Nov 24 '18 at 0:09
TheCuriouslyCodingFoxahTheCuriouslyCodingFoxah
647
asked Nov 24 '18 at 0:09
TheCuriouslyCodingFoxahTheCuriouslyCodingFoxah
647
asked Nov 24 '18 at 0:09
TheCuriouslyCodingFoxahTheCuriouslyCodingFoxah
647
647
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
3
set_levels + sort_index
The issue is your strings are being sorted as strings rather than numerically. First convert your first index level to numeric, then sort by index:
# split by whitespace, take last split, convert to integers
new_index_values = df.index.levels[1].str.split().str[-1].astype(int)
# set 'Day' level
df.index = df.index.set_levels(new_index_values, level='Day')
# sort by index
df = df.sort_index()
print(df)
Value
Group Day
A 0 1
2 3
11 2
B 5 5
7 6
10 4
Setup
The above demonstration uses this example setup:
df = pd.DataFrame({'Group': ['A', 'A', 'A', 'B', 'B', 'B'],
'Day': ['Day 0', 'Day 11', 'Day 2', 'Day 10', 'Day 5', 'Day 7'],
'Value': [1, 2, 3, 4, 5, 6]}).set_index(['Group', 'Day'])
print(df)
Value
Group Day
A Day 0 1
Day 11 2
Day 2 3
B Day 10 4
Day 5 5
Day 7 6
answered Nov 24 '18 at 0:29
jppjpp
96.7k2158109
Thanks for the help, I really appreciate it!
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:20
Is there anyway to have the index still return as 'Day 0', 'Day 1', etc. instead of just the integer?
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:27
@TheCuriouslyCodingFoxah, Once you've done the conversion, no. Of course, you can calculate your string index from the integers if you wish. But there's a rarely a need.
– jpp
Nov 26 '18 at 15:31
add a comment |
1
You need to sort integers instead of strings:
import pandas as pd
x = pd.Series([1,2,3,4,6], index=[3,2,1,11,12])
x.sort_index()
1 3
2 2
3 1
11 4
12 6
dtype: int64
y = pd.Series([1,2,3,4,5], index=['3','2','1','11','12'])
y.sort_index()
1 3
11 4
12 5
2 2
3 1
dtype: int64
I would suggest to have only numbers in the column instead of strings 'Day..'.
answered Nov 24 '18 at 0:24
PyJanPyJan
484
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
});
}
});
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%2f53454099%2fpandas-sort-data-frame-by-logical-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
set_levels + sort_index
The issue is your strings are being sorted as strings rather than numerically. First convert your first index level to numeric, then sort by index:
# split by whitespace, take last split, convert to integers
new_index_values = df.index.levels[1].str.split().str[-1].astype(int)
# set 'Day' level
df.index = df.index.set_levels(new_index_values, level='Day')
# sort by index
df = df.sort_index()
print(df)
Value
Group Day
A 0 1
2 3
11 2
B 5 5
7 6
10 4
Setup
The above demonstration uses this example setup:
df = pd.DataFrame({'Group': ['A', 'A', 'A', 'B', 'B', 'B'],
'Day': ['Day 0', 'Day 11', 'Day 2', 'Day 10', 'Day 5', 'Day 7'],
'Value': [1, 2, 3, 4, 5, 6]}).set_index(['Group', 'Day'])
print(df)
Value
Group Day
A Day 0 1
Day 11 2
Day 2 3
B Day 10 4
Day 5 5
Day 7 6
answered Nov 24 '18 at 0:29
jppjpp
96.7k2158109
Thanks for the help, I really appreciate it!
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:20
Is there anyway to have the index still return as 'Day 0', 'Day 1', etc. instead of just the integer?
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:27
@TheCuriouslyCodingFoxah, Once you've done the conversion, no. Of course, you can calculate your string index from the integers if you wish. But there's a rarely a need.
– jpp
Nov 26 '18 at 15:31
add a comment |
3
set_levels + sort_index
The issue is your strings are being sorted as strings rather than numerically. First convert your first index level to numeric, then sort by index:
# split by whitespace, take last split, convert to integers
new_index_values = df.index.levels[1].str.split().str[-1].astype(int)
# set 'Day' level
df.index = df.index.set_levels(new_index_values, level='Day')
# sort by index
df = df.sort_index()
print(df)
Value
Group Day
A 0 1
2 3
11 2
B 5 5
7 6
10 4
Setup
The above demonstration uses this example setup:
df = pd.DataFrame({'Group': ['A', 'A', 'A', 'B', 'B', 'B'],
'Day': ['Day 0', 'Day 11', 'Day 2', 'Day 10', 'Day 5', 'Day 7'],
'Value': [1, 2, 3, 4, 5, 6]}).set_index(['Group', 'Day'])
print(df)
Value
Group Day
A Day 0 1
Day 11 2
Day 2 3
B Day 10 4
Day 5 5
Day 7 6
answered Nov 24 '18 at 0:29
jppjpp
96.7k2158109
Thanks for the help, I really appreciate it!
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:20
Is there anyway to have the index still return as 'Day 0', 'Day 1', etc. instead of just the integer?
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:27
@TheCuriouslyCodingFoxah, Once you've done the conversion, no. Of course, you can calculate your string index from the integers if you wish. But there's a rarely a need.
– jpp
Nov 26 '18 at 15:31
add a comment |
3
3
3
set_levels + sort_index
The issue is your strings are being sorted as strings rather than numerically. First convert your first index level to numeric, then sort by index:
# split by whitespace, take last split, convert to integers
new_index_values = df.index.levels[1].str.split().str[-1].astype(int)
# set 'Day' level
df.index = df.index.set_levels(new_index_values, level='Day')
# sort by index
df = df.sort_index()
print(df)
Value
Group Day
A 0 1
2 3
11 2
B 5 5
7 6
10 4
Setup
The above demonstration uses this example setup:
df = pd.DataFrame({'Group': ['A', 'A', 'A', 'B', 'B', 'B'],
'Day': ['Day 0', 'Day 11', 'Day 2', 'Day 10', 'Day 5', 'Day 7'],
'Value': [1, 2, 3, 4, 5, 6]}).set_index(['Group', 'Day'])
print(df)
Value
Group Day
A Day 0 1
Day 11 2
Day 2 3
B Day 10 4
Day 5 5
Day 7 6
answered Nov 24 '18 at 0:29
jppjpp
96.7k2158109
set_levels + sort_index
The issue is your strings are being sorted as strings rather than numerically. First convert your first index level to numeric, then sort by index:
# split by whitespace, take last split, convert to integers
new_index_values = df.index.levels[1].str.split().str[-1].astype(int)
# set 'Day' level
df.index = df.index.set_levels(new_index_values, level='Day')
# sort by index
df = df.sort_index()
print(df)
Value
Group Day
A 0 1
2 3
11 2
B 5 5
7 6
10 4
Setup
The above demonstration uses this example setup:
df = pd.DataFrame({'Group': ['A', 'A', 'A', 'B', 'B', 'B'],
'Day': ['Day 0', 'Day 11', 'Day 2', 'Day 10', 'Day 5', 'Day 7'],
'Value': [1, 2, 3, 4, 5, 6]}).set_index(['Group', 'Day'])
print(df)
Value
Group Day
A Day 0 1
Day 11 2
Day 2 3
B Day 10 4
Day 5 5
Day 7 6
answered Nov 24 '18 at 0:29
jppjpp
96.7k2158109
answered Nov 24 '18 at 0:29
jppjpp
96.7k2158109
answered Nov 24 '18 at 0:29
jppjpp
96.7k2158109
answered Nov 24 '18 at 0:29
jppjpp
96.7k2158109
96.7k2158109
Thanks for the help, I really appreciate it!
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:20
Is there anyway to have the index still return as 'Day 0', 'Day 1', etc. instead of just the integer?
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:27
@TheCuriouslyCodingFoxah, Once you've done the conversion, no. Of course, you can calculate your string index from the integers if you wish. But there's a rarely a need.
– jpp
Nov 26 '18 at 15:31
add a comment |
Thanks for the help, I really appreciate it!
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:20
Is there anyway to have the index still return as 'Day 0', 'Day 1', etc. instead of just the integer?
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:27
@TheCuriouslyCodingFoxah, Once you've done the conversion, no. Of course, you can calculate your string index from the integers if you wish. But there's a rarely a need.
– jpp
Nov 26 '18 at 15:31
Thanks for the help, I really appreciate it!
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:20
Thanks for the help, I really appreciate it!
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:20
Is there anyway to have the index still return as 'Day 0', 'Day 1', etc. instead of just the integer?
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:27
Is there anyway to have the index still return as 'Day 0', 'Day 1', etc. instead of just the integer?
– TheCuriouslyCodingFoxah
Nov 26 '18 at 15:27
@TheCuriouslyCodingFoxah, Once you've done the conversion, no. Of course, you can calculate your string index from the integers if you wish. But there's a rarely a need.
– jpp
Nov 26 '18 at 15:31
@TheCuriouslyCodingFoxah, Once you've done the conversion, no. Of course, you can calculate your string index from the integers if you wish. But there's a rarely a need.
– jpp
Nov 26 '18 at 15:31
add a comment |
1
You need to sort integers instead of strings:
import pandas as pd
x = pd.Series([1,2,3,4,6], index=[3,2,1,11,12])
x.sort_index()
1 3
2 2
3 1
11 4
12 6
dtype: int64
y = pd.Series([1,2,3,4,5], index=['3','2','1','11','12'])
y.sort_index()
1 3
11 4
12 5
2 2
3 1
dtype: int64
I would suggest to have only numbers in the column instead of strings 'Day..'.
answered Nov 24 '18 at 0:24
PyJanPyJan
484
add a comment |
1
You need to sort integers instead of strings:
import pandas as pd
x = pd.Series([1,2,3,4,6], index=[3,2,1,11,12])
x.sort_index()
1 3
2 2
3 1
11 4
12 6
dtype: int64
y = pd.Series([1,2,3,4,5], index=['3','2','1','11','12'])
y.sort_index()
1 3
11 4
12 5
2 2
3 1
dtype: int64
I would suggest to have only numbers in the column instead of strings 'Day..'.
answered Nov 24 '18 at 0:24
PyJanPyJan
484
add a comment |
1
1
1
You need to sort integers instead of strings:
import pandas as pd
x = pd.Series([1,2,3,4,6], index=[3,2,1,11,12])
x.sort_index()
1 3
2 2
3 1
11 4
12 6
dtype: int64
y = pd.Series([1,2,3,4,5], index=['3','2','1','11','12'])
y.sort_index()
1 3
11 4
12 5
2 2
3 1
dtype: int64
I would suggest to have only numbers in the column instead of strings 'Day..'.
answered Nov 24 '18 at 0:24
PyJanPyJan
484
You need to sort integers instead of strings:
import pandas as pd
x = pd.Series([1,2,3,4,6], index=[3,2,1,11,12])
x.sort_index()
1 3
2 2
3 1
11 4
12 6
dtype: int64
y = pd.Series([1,2,3,4,5], index=['3','2','1','11','12'])
y.sort_index()
1 3
11 4
12 5
2 2
3 1
dtype: int64
I would suggest to have only numbers in the column instead of strings 'Day..'.
answered Nov 24 '18 at 0:24
PyJanPyJan
484
answered Nov 24 '18 at 0:24
PyJanPyJan
484
answered Nov 24 '18 at 0:24
PyJanPyJan
484
answered Nov 24 '18 at 0:24
PyJanPyJan
484
484
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.
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%2f53454099%2fpandas-sort-data-frame-by-logical-day%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
