Mixing
find maximum value in col C in pandas dataframe while group by both col A and B
0
I have a pandas dataframe like this:
df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'],'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95]})
RT Quality Name Similarity
0 9 70 a 0.98
1 10 60 a 0.97
2 10 50 b 0.97
3 11 60 c 0.95
4 11 80 b 0.95
5 11 70 c 0.95
6 11 80 b 0.95
The values in the column Similarity has the same group-by with column RT
I want to group column RT and find the maximum column Quality value and group by column Name.
For example:
In column RT value 11,which have column Name value c and b, sum each of the column Quality values, then get c = 130, b =160, and sort the maximum 160, b
then get
RT Quality Name Similarity
0 9 70 a 0.98
1 10 60 a 0.97
2 10 50 b 0.97
3 11 160 b 0.95
4 11 130 c 0.95
python pandas
edited Nov 20 '18 at 7:33
ssemilla
3,077424
asked Nov 20 '18 at 2:27
X.tangX.tang
113
add a comment |
0
I have a pandas dataframe like this:
df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'],'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95]})
RT Quality Name Similarity
0 9 70 a 0.98
1 10 60 a 0.97
2 10 50 b 0.97
3 11 60 c 0.95
4 11 80 b 0.95
5 11 70 c 0.95
6 11 80 b 0.95
The values in the column Similarity has the same group-by with column RT
I want to group column RT and find the maximum column Quality value and group by column Name.
For example:
In column RT value 11,which have column Name value c and b, sum each of the column Quality values, then get c = 130, b =160, and sort the maximum 160, b
then get
RT Quality Name Similarity
0 9 70 a 0.98
1 10 60 a 0.97
2 10 50 b 0.97
3 11 160 b 0.95
4 11 130 c 0.95
python pandas
edited Nov 20 '18 at 7:33
ssemilla
3,077424
asked Nov 20 '18 at 2:27
X.tangX.tang
113
add a comment |
0
0
0
I have a pandas dataframe like this:
df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'],'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95]})
RT Quality Name Similarity
0 9 70 a 0.98
1 10 60 a 0.97
2 10 50 b 0.97
3 11 60 c 0.95
4 11 80 b 0.95
5 11 70 c 0.95
6 11 80 b 0.95
The values in the column Similarity has the same group-by with column RT
I want to group column RT and find the maximum column Quality value and group by column Name.
For example:
In column RT value 11,which have column Name value c and b, sum each of the column Quality values, then get c = 130, b =160, and sort the maximum 160, b
then get
RT Quality Name Similarity
0 9 70 a 0.98
1 10 60 a 0.97
2 10 50 b 0.97
3 11 160 b 0.95
4 11 130 c 0.95
python pandas
edited Nov 20 '18 at 7:33
ssemilla
3,077424
asked Nov 20 '18 at 2:27
X.tangX.tang
113
I have a pandas dataframe like this:
df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'],'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95]})
RT Quality Name Similarity
0 9 70 a 0.98
1 10 60 a 0.97
2 10 50 b 0.97
3 11 60 c 0.95
4 11 80 b 0.95
5 11 70 c 0.95
6 11 80 b 0.95
The values in the column Similarity has the same group-by with column RT
I want to group column RT and find the maximum column Quality value and group by column Name.
For example:
In column RT value 11,which have column Name value c and b, sum each of the column Quality values, then get c = 130, b =160, and sort the maximum 160, b
then get
RT Quality Name Similarity
0 9 70 a 0.98
1 10 60 a 0.97
2 10 50 b 0.97
3 11 160 b 0.95
4 11 130 c 0.95
python pandas
python pandas
edited Nov 20 '18 at 7:33
ssemilla
3,077424
asked Nov 20 '18 at 2:27
X.tangX.tang
113
edited Nov 20 '18 at 7:33
ssemilla
3,077424
asked Nov 20 '18 at 2:27
X.tangX.tang
113
edited Nov 20 '18 at 7:33
ssemilla
3,077424
edited Nov 20 '18 at 7:33
ssemilla
3,077424
edited Nov 20 '18 at 7:33
ssemilla
3,077424
3,077424
asked Nov 20 '18 at 2:27
X.tangX.tang
113
asked Nov 20 '18 at 2:27
X.tangX.tang
113
asked Nov 20 '18 at 2:27
X.tangX.tang
113
113
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
you can use groupby with agg:
use lambda to return all Similarities or max to return the max
df.groupby(['RT','Name']).agg({'Quality':'sum', 'Similarity':lambda x:x.unique()})
Quality Similarity
RT Name
9 a 70 0.98
10 a 60 0.97
b 50 0.97
11 b 160 0.95
c 130 0.95
answered Nov 20 '18 at 2:36
ChrisChris
2,0161318
thank you very much~
– X.tang
Nov 21 '18 at 11:49
add a comment |
0
You may not need agg
df.groupby(['RT','Similarity','Name'],as_index=False)['Quality'].sum()
Out[150]:
RT Similarity Name Quality
0 9 0.98 a 70
1 10 0.97 a 60
2 10 0.97 b 50
3 11 0.95 b 160
4 11 0.95 c 130
answered Nov 20 '18 at 4:26
W-BW-B
104k73165
Thank you for your reply, what is the difference betweenaggandas_index? and if I have some irrelevant column,how to keep those column use your method .df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'], 'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95],"samples":[13,2,4,5,6,6,7,]})such as keep the columnsamplesalong with column['RT','Similarity','Name']
– X.tang
Nov 21 '18 at 11:47
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%2f53385348%2ffind-maximum-value-in-col-c-in-pandas-dataframe-while-group-by-both-col-a-and-b%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
0
you can use groupby with agg:
use lambda to return all Similarities or max to return the max
df.groupby(['RT','Name']).agg({'Quality':'sum', 'Similarity':lambda x:x.unique()})
Quality Similarity
RT Name
9 a 70 0.98
10 a 60 0.97
b 50 0.97
11 b 160 0.95
c 130 0.95
answered Nov 20 '18 at 2:36
ChrisChris
2,0161318
thank you very much~
– X.tang
Nov 21 '18 at 11:49
add a comment |
0
you can use groupby with agg:
use lambda to return all Similarities or max to return the max
df.groupby(['RT','Name']).agg({'Quality':'sum', 'Similarity':lambda x:x.unique()})
Quality Similarity
RT Name
9 a 70 0.98
10 a 60 0.97
b 50 0.97
11 b 160 0.95
c 130 0.95
answered Nov 20 '18 at 2:36
ChrisChris
2,0161318
thank you very much~
– X.tang
Nov 21 '18 at 11:49
add a comment |
0
0
0
you can use groupby with agg:
use lambda to return all Similarities or max to return the max
df.groupby(['RT','Name']).agg({'Quality':'sum', 'Similarity':lambda x:x.unique()})
Quality Similarity
RT Name
9 a 70 0.98
10 a 60 0.97
b 50 0.97
11 b 160 0.95
c 130 0.95
answered Nov 20 '18 at 2:36
ChrisChris
2,0161318
you can use groupby with agg:
use lambda to return all Similarities or max to return the max
df.groupby(['RT','Name']).agg({'Quality':'sum', 'Similarity':lambda x:x.unique()})
Quality Similarity
RT Name
9 a 70 0.98
10 a 60 0.97
b 50 0.97
11 b 160 0.95
c 130 0.95
answered Nov 20 '18 at 2:36
ChrisChris
2,0161318
edited Nov 20 '18 at 2:56
answered Nov 20 '18 at 2:36
ChrisChris
2,0161318
answered Nov 20 '18 at 2:36
ChrisChris
2,0161318
answered Nov 20 '18 at 2:36
ChrisChris
2,0161318
2,0161318
thank you very much~
– X.tang
Nov 21 '18 at 11:49
add a comment |
thank you very much~
– X.tang
Nov 21 '18 at 11:49
thank you very much~
– X.tang
Nov 21 '18 at 11:49
thank you very much~
– X.tang
Nov 21 '18 at 11:49
add a comment |
0
You may not need agg
df.groupby(['RT','Similarity','Name'],as_index=False)['Quality'].sum()
Out[150]:
RT Similarity Name Quality
0 9 0.98 a 70
1 10 0.97 a 60
2 10 0.97 b 50
3 11 0.95 b 160
4 11 0.95 c 130
answered Nov 20 '18 at 4:26
W-BW-B
104k73165
Thank you for your reply, what is the difference betweenaggandas_index? and if I have some irrelevant column,how to keep those column use your method .df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'], 'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95],"samples":[13,2,4,5,6,6,7,]})such as keep the columnsamplesalong with column['RT','Similarity','Name']
– X.tang
Nov 21 '18 at 11:47
add a comment |
0
You may not need agg
df.groupby(['RT','Similarity','Name'],as_index=False)['Quality'].sum()
Out[150]:
RT Similarity Name Quality
0 9 0.98 a 70
1 10 0.97 a 60
2 10 0.97 b 50
3 11 0.95 b 160
4 11 0.95 c 130
answered Nov 20 '18 at 4:26
W-BW-B
104k73165
Thank you for your reply, what is the difference betweenaggandas_index? and if I have some irrelevant column,how to keep those column use your method .df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'], 'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95],"samples":[13,2,4,5,6,6,7,]})such as keep the columnsamplesalong with column['RT','Similarity','Name']
– X.tang
Nov 21 '18 at 11:47
add a comment |
0
0
0
You may not need agg
df.groupby(['RT','Similarity','Name'],as_index=False)['Quality'].sum()
Out[150]:
RT Similarity Name Quality
0 9 0.98 a 70
1 10 0.97 a 60
2 10 0.97 b 50
3 11 0.95 b 160
4 11 0.95 c 130
answered Nov 20 '18 at 4:26
W-BW-B
104k73165
You may not need agg
df.groupby(['RT','Similarity','Name'],as_index=False)['Quality'].sum()
Out[150]:
RT Similarity Name Quality
0 9 0.98 a 70
1 10 0.97 a 60
2 10 0.97 b 50
3 11 0.95 b 160
4 11 0.95 c 130
answered Nov 20 '18 at 4:26
W-BW-B
104k73165
answered Nov 20 '18 at 4:26
W-BW-B
104k73165
answered Nov 20 '18 at 4:26
W-BW-B
104k73165
answered Nov 20 '18 at 4:26
W-BW-B
104k73165
104k73165
Thank you for your reply, what is the difference betweenaggandas_index? and if I have some irrelevant column,how to keep those column use your method .df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'], 'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95],"samples":[13,2,4,5,6,6,7,]})such as keep the columnsamplesalong with column['RT','Similarity','Name']
– X.tang
Nov 21 '18 at 11:47
add a comment |
Thank you for your reply, what is the difference betweenaggandas_index? and if I have some irrelevant column,how to keep those column use your method .df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'], 'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95],"samples":[13,2,4,5,6,6,7,]})such as keep the columnsamplesalong with column['RT','Similarity','Name']
– X.tang
Nov 21 '18 at 11:47
Thank you for your reply, what is the difference between
agg and as_index? and if I have some irrelevant column,how to keep those column use your method . df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'], 'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95],"samples":[13,2,4,5,6,6,7,]}) such as keep the column samples along with column ['RT','Similarity','Name']– X.tang
Nov 21 '18 at 11:47
Thank you for your reply, what is the difference between
agg and as_index? and if I have some irrelevant column,how to keep those column use your method . df = pd.DataFrame({"RT":[9,10,10,11,11,11,11],"Quality":[70,60,50,60,80,70,80],'Name' :['a','a','b','c','b','c','b'], 'Similarity':[0.98,0.97,0.97,0.95,0.95,0.95,0.95],"samples":[13,2,4,5,6,6,7,]}) such as keep the column samples along with column ['RT','Similarity','Name']– X.tang
Nov 21 '18 at 11:47
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%2f53385348%2ffind-maximum-value-in-col-c-in-pandas-dataframe-while-group-by-both-col-a-and-b%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
