pandas merge columns to create new column with comma separated values
My dataframe has four columns with colors. I want to combine them into one column called "Colors" and use commas to separate the values.
For example, I'm trying to combine into a Colors column like this :
ID Black Red Blue Green Colors
120 NaN red NaN green red, green
121 black Nan blue NaN black, blue
My code is:
df['Colors'] = df[['Black, 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x), axis=1)
But the output for ID 120 is:
, red, , green
And the output for ID 121 is:
black, , blue,
FOUND MY PROBLEM!
Earlier in my code, I replaced "None" with " " instead of NaN. Upon making the change, plus incorporating feedback to insert [x.notnull()], it works!
df['Black'].replace('None', np.nan, inplace=True)
df['Colors'] = df[['Black, 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x[x.notnull()]), axis=1)
python pandas merge multiple-columns comma
asked Jan 1 at 21:35
KRDavisKRDavis
204
add a comment |
My dataframe has four columns with colors. I want to combine them into one column called "Colors" and use commas to separate the values.
For example, I'm trying to combine into a Colors column like this :
ID Black Red Blue Green Colors
120 NaN red NaN green red, green
121 black Nan blue NaN black, blue
My code is:
df['Colors'] = df[['Black, 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x), axis=1)
But the output for ID 120 is:
, red, , green
And the output for ID 121 is:
black, , blue,
FOUND MY PROBLEM!
Earlier in my code, I replaced "None" with " " instead of NaN. Upon making the change, plus incorporating feedback to insert [x.notnull()], it works!
df['Black'].replace('None', np.nan, inplace=True)
df['Colors'] = df[['Black, 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x[x.notnull()]), axis=1)
python pandas merge multiple-columns comma
asked Jan 1 at 21:35
KRDavisKRDavis
204
add a comment |
My dataframe has four columns with colors. I want to combine them into one column called "Colors" and use commas to separate the values.
For example, I'm trying to combine into a Colors column like this :
ID Black Red Blue Green Colors
120 NaN red NaN green red, green
121 black Nan blue NaN black, blue
My code is:
df['Colors'] = df[['Black, 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x), axis=1)
But the output for ID 120 is:
, red, , green
And the output for ID 121 is:
black, , blue,
FOUND MY PROBLEM!
Earlier in my code, I replaced "None" with " " instead of NaN. Upon making the change, plus incorporating feedback to insert [x.notnull()], it works!
df['Black'].replace('None', np.nan, inplace=True)
df['Colors'] = df[['Black, 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x[x.notnull()]), axis=1)
python pandas merge multiple-columns comma
asked Jan 1 at 21:35
KRDavisKRDavis
204
My dataframe has four columns with colors. I want to combine them into one column called "Colors" and use commas to separate the values.
For example, I'm trying to combine into a Colors column like this :
ID Black Red Blue Green Colors
120 NaN red NaN green red, green
121 black Nan blue NaN black, blue
My code is:
df['Colors'] = df[['Black, 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x), axis=1)
But the output for ID 120 is:
, red, , green
And the output for ID 121 is:
black, , blue,
FOUND MY PROBLEM!
Earlier in my code, I replaced "None" with " " instead of NaN. Upon making the change, plus incorporating feedback to insert [x.notnull()], it works!
df['Black'].replace('None', np.nan, inplace=True)
df['Colors'] = df[['Black, 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x[x.notnull()]), axis=1)
python pandas merge multiple-columns comma
python pandas merge multiple-columns comma
asked Jan 1 at 21:35
KRDavisKRDavis
204
asked Jan 1 at 21:35
KRDavisKRDavis
204
edited Jan 2 at 1:43
KRDavis
asked Jan 1 at 21:35
KRDavisKRDavis
204
asked Jan 1 at 21:35
KRDavisKRDavis
204
asked Jan 1 at 21:35
KRDavisKRDavis
204
204
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You just need to handle NaNs
df['Colors'] = df[['Black', 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x[x.notnull()]), axis = 1)
ID Black Red Blue Green Colors
0 120 NaN red NaN green red, green
1 121 black NaN blue NaN black, blue
answered Jan 1 at 21:40
VaishaliVaishali
21.4k41335
Happy new year ~ :-)
– Wen-Ben
Jan 1 at 22:04
@W-B, wish you a very happy new year :)
– Vaishali
Jan 2 at 4:31
add a comment |
Using dot
s=df.iloc[:,1:]
s.notnull()
Black Red Blue Green
0 False True False True
1 True True True False
s.notnull().dot(s.columns+',').str[:-1]
0 Red,Green
1 Black,Red,Blue
dtype: object
df['color']=s.notnull().dot(s.columns+',').str[:-1]
answered Jan 1 at 22:04
Wen-BenWen-Ben
113k83368
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%2f53999131%2fpandas-merge-columns-to-create-new-column-with-comma-separated-values%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
You just need to handle NaNs
df['Colors'] = df[['Black', 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x[x.notnull()]), axis = 1)
ID Black Red Blue Green Colors
0 120 NaN red NaN green red, green
1 121 black NaN blue NaN black, blue
answered Jan 1 at 21:40
VaishaliVaishali
21.4k41335
Happy new year ~ :-)
– Wen-Ben
Jan 1 at 22:04
@W-B, wish you a very happy new year :)
– Vaishali
Jan 2 at 4:31
add a comment |
You just need to handle NaNs
df['Colors'] = df[['Black', 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x[x.notnull()]), axis = 1)
ID Black Red Blue Green Colors
0 120 NaN red NaN green red, green
1 121 black NaN blue NaN black, blue
answered Jan 1 at 21:40
VaishaliVaishali
21.4k41335
Happy new year ~ :-)
– Wen-Ben
Jan 1 at 22:04
@W-B, wish you a very happy new year :)
– Vaishali
Jan 2 at 4:31
add a comment |
You just need to handle NaNs
df['Colors'] = df[['Black', 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x[x.notnull()]), axis = 1)
ID Black Red Blue Green Colors
0 120 NaN red NaN green red, green
1 121 black NaN blue NaN black, blue
answered Jan 1 at 21:40
VaishaliVaishali
21.4k41335
You just need to handle NaNs
df['Colors'] = df[['Black', 'Red', 'Blue', 'Green']].apply(lambda x: ', '.join(x[x.notnull()]), axis = 1)
ID Black Red Blue Green Colors
0 120 NaN red NaN green red, green
1 121 black NaN blue NaN black, blue
answered Jan 1 at 21:40
VaishaliVaishali
21.4k41335
answered Jan 1 at 21:40
VaishaliVaishali
21.4k41335
answered Jan 1 at 21:40
VaishaliVaishali
21.4k41335
answered Jan 1 at 21:40
VaishaliVaishali
21.4k41335
21.4k41335
Happy new year ~ :-)
– Wen-Ben
Jan 1 at 22:04
@W-B, wish you a very happy new year :)
– Vaishali
Jan 2 at 4:31
add a comment |
Happy new year ~ :-)
– Wen-Ben
Jan 1 at 22:04
@W-B, wish you a very happy new year :)
– Vaishali
Jan 2 at 4:31
Happy new year ~ :-)
– Wen-Ben
Jan 1 at 22:04
Happy new year ~ :-)
– Wen-Ben
Jan 1 at 22:04
@W-B, wish you a very happy new year :)
– Vaishali
Jan 2 at 4:31
@W-B, wish you a very happy new year :)
– Vaishali
Jan 2 at 4:31
add a comment |
Using dot
s=df.iloc[:,1:]
s.notnull()
Black Red Blue Green
0 False True False True
1 True True True False
s.notnull().dot(s.columns+',').str[:-1]
0 Red,Green
1 Black,Red,Blue
dtype: object
df['color']=s.notnull().dot(s.columns+',').str[:-1]
answered Jan 1 at 22:04
Wen-BenWen-Ben
113k83368
add a comment |
Using dot
s=df.iloc[:,1:]
s.notnull()
Black Red Blue Green
0 False True False True
1 True True True False
s.notnull().dot(s.columns+',').str[:-1]
0 Red,Green
1 Black,Red,Blue
dtype: object
df['color']=s.notnull().dot(s.columns+',').str[:-1]
answered Jan 1 at 22:04
Wen-BenWen-Ben
113k83368
add a comment |
Using dot
s=df.iloc[:,1:]
s.notnull()
Black Red Blue Green
0 False True False True
1 True True True False
s.notnull().dot(s.columns+',').str[:-1]
0 Red,Green
1 Black,Red,Blue
dtype: object
df['color']=s.notnull().dot(s.columns+',').str[:-1]
answered Jan 1 at 22:04
Wen-BenWen-Ben
113k83368
Using dot
s=df.iloc[:,1:]
s.notnull()
Black Red Blue Green
0 False True False True
1 True True True False
s.notnull().dot(s.columns+',').str[:-1]
0 Red,Green
1 Black,Red,Blue
dtype: object
df['color']=s.notnull().dot(s.columns+',').str[:-1]
answered Jan 1 at 22:04
Wen-BenWen-Ben
113k83368
answered Jan 1 at 22:04
Wen-BenWen-Ben
113k83368
answered Jan 1 at 22:04
Wen-BenWen-Ben
113k83368
answered Jan 1 at 22:04
Wen-BenWen-Ben
113k83368
113k83368
add a comment |
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%2f53999131%2fpandas-merge-columns-to-create-new-column-with-comma-separated-values%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
