Pandas - split columns and count occurences
It's info for some purchases made by clients on phone accessories, my real data would look something like this:
Abstract Model 1 ~Samsung S6 | Sold: 4
I've got a dataset that looks something like this:
item sold
Design1 ~Model1 1
Design2 ~Model1 2
Design1 ~Model2 3
Design2 ~Model2 1
I want to break the item column into 2 columns , design and model, and count each time a design has been sold, and a model has been sold, individually, based on the selling data of design+model combinations in the input.
My expected output, based on the first dataset, would look something like this:
expected output:
design design_sold model model_sold
Design1 4 Model1 3
Design2 3 Model2 4
Thank you for your help
pandas count
asked Nov 20 '18 at 10:49
remus2232remus2232
424
add a comment |
It's info for some purchases made by clients on phone accessories, my real data would look something like this:
Abstract Model 1 ~Samsung S6 | Sold: 4
I've got a dataset that looks something like this:
item sold
Design1 ~Model1 1
Design2 ~Model1 2
Design1 ~Model2 3
Design2 ~Model2 1
I want to break the item column into 2 columns , design and model, and count each time a design has been sold, and a model has been sold, individually, based on the selling data of design+model combinations in the input.
My expected output, based on the first dataset, would look something like this:
expected output:
design design_sold model model_sold
Design1 4 Model1 3
Design2 3 Model2 4
Thank you for your help
pandas count
asked Nov 20 '18 at 10:49
remus2232remus2232
424
add a comment |
It's info for some purchases made by clients on phone accessories, my real data would look something like this:
Abstract Model 1 ~Samsung S6 | Sold: 4
I've got a dataset that looks something like this:
item sold
Design1 ~Model1 1
Design2 ~Model1 2
Design1 ~Model2 3
Design2 ~Model2 1
I want to break the item column into 2 columns , design and model, and count each time a design has been sold, and a model has been sold, individually, based on the selling data of design+model combinations in the input.
My expected output, based on the first dataset, would look something like this:
expected output:
design design_sold model model_sold
Design1 4 Model1 3
Design2 3 Model2 4
Thank you for your help
pandas count
asked Nov 20 '18 at 10:49
remus2232remus2232
424
It's info for some purchases made by clients on phone accessories, my real data would look something like this:
Abstract Model 1 ~Samsung S6 | Sold: 4
I've got a dataset that looks something like this:
item sold
Design1 ~Model1 1
Design2 ~Model1 2
Design1 ~Model2 3
Design2 ~Model2 1
I want to break the item column into 2 columns , design and model, and count each time a design has been sold, and a model has been sold, individually, based on the selling data of design+model combinations in the input.
My expected output, based on the first dataset, would look something like this:
expected output:
design design_sold model model_sold
Design1 4 Model1 3
Design2 3 Model2 4
Thank you for your help
pandas count
pandas count
asked Nov 20 '18 at 10:49
remus2232remus2232
424
asked Nov 20 '18 at 10:49
remus2232remus2232
424
asked Nov 20 '18 at 10:49
remus2232remus2232
424
asked Nov 20 '18 at 10:49
remus2232remus2232
424
asked Nov 20 '18 at 10:49
remus2232remus2232
424
424
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
try this,
df[['Design','Model']]=df['item'].str.split(' ~',expand=True)
print pd.concat([df.groupby('Design',as_index=False)['sold'].sum().rename(columns={'sold':'Desgin Sold'}),df.groupby('Model',as_index=False)['sold'].sum().rename(columns={'sold':'Model Sold'})],axis=1)
Output:
Design Desgin Sold Model Model Sold
0 Design1 4 Model1 3
1 Design2 3 Model2 4
Explanation:'
1. .str.split() used to split your series into frame.
groupbymodel and design and performsumon grouped object.renamethe column andconcatyour dataframe.
answered Nov 20 '18 at 10:52
Mohamed Thasin ahMohamed Thasin ah
3,86831840
Hello, Mohamed; Thank you for your reply; I'm currently getting a syntax error on the last line, trying to figure it out exactly where it is, as we speak
– remus2232
Nov 21 '18 at 17:08
1
This above code works nicely to me, try your self let me know if you still stuck... Happy coding 😊
– Mohamed Thasin ah
Nov 21 '18 at 17:11
1
It did work, I had to assign the result to the Dataframe instead of printing it. Thank you so much for this, man, you're the best!
– remus2232
Nov 21 '18 at 17:12
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%2f53391347%2fpandas-split-columns-and-count-occurences%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
try this,
df[['Design','Model']]=df['item'].str.split(' ~',expand=True)
print pd.concat([df.groupby('Design',as_index=False)['sold'].sum().rename(columns={'sold':'Desgin Sold'}),df.groupby('Model',as_index=False)['sold'].sum().rename(columns={'sold':'Model Sold'})],axis=1)
Output:
Design Desgin Sold Model Model Sold
0 Design1 4 Model1 3
1 Design2 3 Model2 4
Explanation:'
1. .str.split() used to split your series into frame.
groupbymodel and design and performsumon grouped object.renamethe column andconcatyour dataframe.
answered Nov 20 '18 at 10:52
Mohamed Thasin ahMohamed Thasin ah
3,86831840
Hello, Mohamed; Thank you for your reply; I'm currently getting a syntax error on the last line, trying to figure it out exactly where it is, as we speak
– remus2232
Nov 21 '18 at 17:08
1
This above code works nicely to me, try your self let me know if you still stuck... Happy coding 😊
– Mohamed Thasin ah
Nov 21 '18 at 17:11
1
It did work, I had to assign the result to the Dataframe instead of printing it. Thank you so much for this, man, you're the best!
– remus2232
Nov 21 '18 at 17:12
add a comment |
try this,
df[['Design','Model']]=df['item'].str.split(' ~',expand=True)
print pd.concat([df.groupby('Design',as_index=False)['sold'].sum().rename(columns={'sold':'Desgin Sold'}),df.groupby('Model',as_index=False)['sold'].sum().rename(columns={'sold':'Model Sold'})],axis=1)
Output:
Design Desgin Sold Model Model Sold
0 Design1 4 Model1 3
1 Design2 3 Model2 4
Explanation:'
1. .str.split() used to split your series into frame.
groupbymodel and design and performsumon grouped object.renamethe column andconcatyour dataframe.
answered Nov 20 '18 at 10:52
Mohamed Thasin ahMohamed Thasin ah
3,86831840
Hello, Mohamed; Thank you for your reply; I'm currently getting a syntax error on the last line, trying to figure it out exactly where it is, as we speak
– remus2232
Nov 21 '18 at 17:08
1
This above code works nicely to me, try your self let me know if you still stuck... Happy coding 😊
– Mohamed Thasin ah
Nov 21 '18 at 17:11
1
It did work, I had to assign the result to the Dataframe instead of printing it. Thank you so much for this, man, you're the best!
– remus2232
Nov 21 '18 at 17:12
add a comment |
try this,
df[['Design','Model']]=df['item'].str.split(' ~',expand=True)
print pd.concat([df.groupby('Design',as_index=False)['sold'].sum().rename(columns={'sold':'Desgin Sold'}),df.groupby('Model',as_index=False)['sold'].sum().rename(columns={'sold':'Model Sold'})],axis=1)
Output:
Design Desgin Sold Model Model Sold
0 Design1 4 Model1 3
1 Design2 3 Model2 4
Explanation:'
1. .str.split() used to split your series into frame.
groupbymodel and design and performsumon grouped object.renamethe column andconcatyour dataframe.
answered Nov 20 '18 at 10:52
Mohamed Thasin ahMohamed Thasin ah
3,86831840
try this,
df[['Design','Model']]=df['item'].str.split(' ~',expand=True)
print pd.concat([df.groupby('Design',as_index=False)['sold'].sum().rename(columns={'sold':'Desgin Sold'}),df.groupby('Model',as_index=False)['sold'].sum().rename(columns={'sold':'Model Sold'})],axis=1)
Output:
Design Desgin Sold Model Model Sold
0 Design1 4 Model1 3
1 Design2 3 Model2 4
Explanation:'
1. .str.split() used to split your series into frame.
groupbymodel and design and performsumon grouped object.renamethe column andconcatyour dataframe.
answered Nov 20 '18 at 10:52
Mohamed Thasin ahMohamed Thasin ah
3,86831840
edited Nov 20 '18 at 10:59
answered Nov 20 '18 at 10:52
Mohamed Thasin ahMohamed Thasin ah
3,86831840
answered Nov 20 '18 at 10:52
Mohamed Thasin ahMohamed Thasin ah
3,86831840
answered Nov 20 '18 at 10:52
Mohamed Thasin ahMohamed Thasin ah
3,86831840
3,86831840
Hello, Mohamed; Thank you for your reply; I'm currently getting a syntax error on the last line, trying to figure it out exactly where it is, as we speak
– remus2232
Nov 21 '18 at 17:08
1
This above code works nicely to me, try your self let me know if you still stuck... Happy coding 😊
– Mohamed Thasin ah
Nov 21 '18 at 17:11
1
It did work, I had to assign the result to the Dataframe instead of printing it. Thank you so much for this, man, you're the best!
– remus2232
Nov 21 '18 at 17:12
add a comment |
Hello, Mohamed; Thank you for your reply; I'm currently getting a syntax error on the last line, trying to figure it out exactly where it is, as we speak
– remus2232
Nov 21 '18 at 17:08
1
This above code works nicely to me, try your self let me know if you still stuck... Happy coding 😊
– Mohamed Thasin ah
Nov 21 '18 at 17:11
1
It did work, I had to assign the result to the Dataframe instead of printing it. Thank you so much for this, man, you're the best!
– remus2232
Nov 21 '18 at 17:12
Hello, Mohamed; Thank you for your reply; I'm currently getting a syntax error on the last line, trying to figure it out exactly where it is, as we speak
– remus2232
Nov 21 '18 at 17:08
Hello, Mohamed; Thank you for your reply; I'm currently getting a syntax error on the last line, trying to figure it out exactly where it is, as we speak
– remus2232
Nov 21 '18 at 17:08
1
1
This above code works nicely to me, try your self let me know if you still stuck... Happy coding 😊
– Mohamed Thasin ah
Nov 21 '18 at 17:11
This above code works nicely to me, try your self let me know if you still stuck... Happy coding 😊
– Mohamed Thasin ah
Nov 21 '18 at 17:11
1
1
It did work, I had to assign the result to the Dataframe instead of printing it. Thank you so much for this, man, you're the best!
– remus2232
Nov 21 '18 at 17:12
It did work, I had to assign the result to the Dataframe instead of printing it. Thank you so much for this, man, you're the best!
– remus2232
Nov 21 '18 at 17:12
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%2f53391347%2fpandas-split-columns-and-count-occurences%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
