Mapping column values of one DataFrame to another DataFrame using a key with different header names
1
$begingroup$
I have two data frames df1 and df2 which look something like this.
cat1 cat2 cat3
0 10 25 12
1 11 22 14
2 12 30 15
all_cats cat_codes
0 10 A
1 11 B
2 12 C
3 25 D
4 22 E
5 30 F
6 14 G
I would like a DataFrame where each column in df1 is created but replaced with cat_codes. Column header names are different. I have tried join and merge but my number of rows are inconsistent. I am dealing with huge number of samples (100,000). My output should ideally be this:
cat1 cat2 cat3
0 A D C
1 B E Y
2 C F Z
The resulting columns should be appended to df1.
python pandas dataframe
asked Oct 16 '18 at 15:50
DannyDanny
1998
$endgroup$
bumped to the homepage by Community♦ 15 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
1
$begingroup$
I have two data frames df1 and df2 which look something like this.
cat1 cat2 cat3
0 10 25 12
1 11 22 14
2 12 30 15
all_cats cat_codes
0 10 A
1 11 B
2 12 C
3 25 D
4 22 E
5 30 F
6 14 G
I would like a DataFrame where each column in df1 is created but replaced with cat_codes. Column header names are different. I have tried join and merge but my number of rows are inconsistent. I am dealing with huge number of samples (100,000). My output should ideally be this:
cat1 cat2 cat3
0 A D C
1 B E Y
2 C F Z
The resulting columns should be appended to df1.
python pandas dataframe
asked Oct 16 '18 at 15:50
DannyDanny
1998
$endgroup$
bumped to the homepage by Community♦ 15 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
1
1
1
$begingroup$
I have two data frames df1 and df2 which look something like this.
cat1 cat2 cat3
0 10 25 12
1 11 22 14
2 12 30 15
all_cats cat_codes
0 10 A
1 11 B
2 12 C
3 25 D
4 22 E
5 30 F
6 14 G
I would like a DataFrame where each column in df1 is created but replaced with cat_codes. Column header names are different. I have tried join and merge but my number of rows are inconsistent. I am dealing with huge number of samples (100,000). My output should ideally be this:
cat1 cat2 cat3
0 A D C
1 B E Y
2 C F Z
The resulting columns should be appended to df1.
python pandas dataframe
asked Oct 16 '18 at 15:50
DannyDanny
1998
$endgroup$
I have two data frames df1 and df2 which look something like this.
cat1 cat2 cat3
0 10 25 12
1 11 22 14
2 12 30 15
all_cats cat_codes
0 10 A
1 11 B
2 12 C
3 25 D
4 22 E
5 30 F
6 14 G
I would like a DataFrame where each column in df1 is created but replaced with cat_codes. Column header names are different. I have tried join and merge but my number of rows are inconsistent. I am dealing with huge number of samples (100,000). My output should ideally be this:
cat1 cat2 cat3
0 A D C
1 B E Y
2 C F Z
The resulting columns should be appended to df1.
python pandas dataframe
python pandas dataframe
asked Oct 16 '18 at 15:50
DannyDanny
1998
asked Oct 16 '18 at 15:50
DannyDanny
1998
asked Oct 16 '18 at 15:50
DannyDanny
1998
asked Oct 16 '18 at 15:50
DannyDanny
1998
asked Oct 16 '18 at 15:50
DannyDanny
1998
1998
bumped to the homepage by Community♦ 15 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 15 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
$begingroup$
You can convert df2 to a dictionary and use that to replace the values in df1
cat_1 = [10, 11, 12]
cat_2 = [25, 22, 30]
cat_3 = [12, 14, 15]
df1 = pd.DataFrame({'cat1':cat_1, 'cat2':cat_2, 'cat3':cat_3})
all_cats = [10, 11, 12, 25, 22, 30, 15]
cat_codes = ['A', 'B', 'C', 'D', 'E', 'F', 'G']
df2 = pd.DataFrame({'all_cats':all_cats, 'cat_codes':cat_codes})
rename_dict = df2.set_index('all_cats').to_dict()['cat_codes']
df1 = df1.replace(rename_dict)
If you still have some values that aren't in your dictionary and want to replace them with Z, you can use a regex to replace them.
df1.astype('str').replace({'d+': 'Z'}, regex=True)
answered Oct 16 '18 at 16:10
Stephen WitkowskiStephen Witkowski
715
$endgroup$
$begingroup$
Thank you for your response. I want to create columns but not replace them and these data frames are of high cardinality which means cat_1,cat_2 and cat_3 are not the only columns in the data frame. Of course, I can convert these columns into lists and use your solution but I am looking for an elegant way of doing this. Do you think 'joins' would help?
$endgroup$
– Danny
Oct 17 '18 at 8:44
$begingroup$
Just to be clear, you wouldn't need to convert these columns into lists. You're simply changingdf2into a dictionary and using that to replace values in the data frame. I have a question: do you have other values in this dataframe that you don't want to replace, but take the same value as something inall_cats? For example, do you only want to replacecat_1,cat_2, andcat_3, but want to leavecat_4alone? If so, is any value incat_4equal to any value inall_cats? Let me know if I'm not making sense...
$endgroup$
– Stephen Witkowski
Oct 17 '18 at 12:22
$begingroup$
Yes. You are right. I want to leave the other columns alone but the other columns may or may not match the values inall_cats.
$endgroup$
– Danny
Oct 17 '18 at 12:39
add a comment |
0
$begingroup$
df3 = pd.merge(df1,df2,left_on=['cat'+str(i)], right_on = ['cat_codes'], how = 'left')
I would iterate this for cat1,cat2 and cat3. This does not replace the existing column values but appends new columns.
answered Dec 19 '18 at 13:44
DannyDanny
1998
$endgroup$
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
});
});
}, "mathjax-editing");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "557"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdatascience.stackexchange.com%2fquestions%2f39773%2fmapping-column-values-of-one-dataframe-to-another-dataframe-using-a-key-with-dif%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
$begingroup$
You can convert df2 to a dictionary and use that to replace the values in df1
cat_1 = [10, 11, 12]
cat_2 = [25, 22, 30]
cat_3 = [12, 14, 15]
df1 = pd.DataFrame({'cat1':cat_1, 'cat2':cat_2, 'cat3':cat_3})
all_cats = [10, 11, 12, 25, 22, 30, 15]
cat_codes = ['A', 'B', 'C', 'D', 'E', 'F', 'G']
df2 = pd.DataFrame({'all_cats':all_cats, 'cat_codes':cat_codes})
rename_dict = df2.set_index('all_cats').to_dict()['cat_codes']
df1 = df1.replace(rename_dict)
If you still have some values that aren't in your dictionary and want to replace them with Z, you can use a regex to replace them.
df1.astype('str').replace({'d+': 'Z'}, regex=True)
answered Oct 16 '18 at 16:10
Stephen WitkowskiStephen Witkowski
715
$endgroup$
$begingroup$
Thank you for your response. I want to create columns but not replace them and these data frames are of high cardinality which means cat_1,cat_2 and cat_3 are not the only columns in the data frame. Of course, I can convert these columns into lists and use your solution but I am looking for an elegant way of doing this. Do you think 'joins' would help?
$endgroup$
– Danny
Oct 17 '18 at 8:44
$begingroup$
Just to be clear, you wouldn't need to convert these columns into lists. You're simply changingdf2into a dictionary and using that to replace values in the data frame. I have a question: do you have other values in this dataframe that you don't want to replace, but take the same value as something inall_cats? For example, do you only want to replacecat_1,cat_2, andcat_3, but want to leavecat_4alone? If so, is any value incat_4equal to any value inall_cats? Let me know if I'm not making sense...
$endgroup$
– Stephen Witkowski
Oct 17 '18 at 12:22
$begingroup$
Yes. You are right. I want to leave the other columns alone but the other columns may or may not match the values inall_cats.
$endgroup$
– Danny
Oct 17 '18 at 12:39
add a comment |
0
$begingroup$
You can convert df2 to a dictionary and use that to replace the values in df1
cat_1 = [10, 11, 12]
cat_2 = [25, 22, 30]
cat_3 = [12, 14, 15]
df1 = pd.DataFrame({'cat1':cat_1, 'cat2':cat_2, 'cat3':cat_3})
all_cats = [10, 11, 12, 25, 22, 30, 15]
cat_codes = ['A', 'B', 'C', 'D', 'E', 'F', 'G']
df2 = pd.DataFrame({'all_cats':all_cats, 'cat_codes':cat_codes})
rename_dict = df2.set_index('all_cats').to_dict()['cat_codes']
df1 = df1.replace(rename_dict)
If you still have some values that aren't in your dictionary and want to replace them with Z, you can use a regex to replace them.
df1.astype('str').replace({'d+': 'Z'}, regex=True)
answered Oct 16 '18 at 16:10
Stephen WitkowskiStephen Witkowski
715
$endgroup$
$begingroup$
Thank you for your response. I want to create columns but not replace them and these data frames are of high cardinality which means cat_1,cat_2 and cat_3 are not the only columns in the data frame. Of course, I can convert these columns into lists and use your solution but I am looking for an elegant way of doing this. Do you think 'joins' would help?
$endgroup$
– Danny
Oct 17 '18 at 8:44
$begingroup$
Just to be clear, you wouldn't need to convert these columns into lists. You're simply changingdf2into a dictionary and using that to replace values in the data frame. I have a question: do you have other values in this dataframe that you don't want to replace, but take the same value as something inall_cats? For example, do you only want to replacecat_1,cat_2, andcat_3, but want to leavecat_4alone? If so, is any value incat_4equal to any value inall_cats? Let me know if I'm not making sense...
$endgroup$
– Stephen Witkowski
Oct 17 '18 at 12:22
$begingroup$
Yes. You are right. I want to leave the other columns alone but the other columns may or may not match the values inall_cats.
$endgroup$
– Danny
Oct 17 '18 at 12:39
add a comment |
0
0
0
$begingroup$
You can convert df2 to a dictionary and use that to replace the values in df1
cat_1 = [10, 11, 12]
cat_2 = [25, 22, 30]
cat_3 = [12, 14, 15]
df1 = pd.DataFrame({'cat1':cat_1, 'cat2':cat_2, 'cat3':cat_3})
all_cats = [10, 11, 12, 25, 22, 30, 15]
cat_codes = ['A', 'B', 'C', 'D', 'E', 'F', 'G']
df2 = pd.DataFrame({'all_cats':all_cats, 'cat_codes':cat_codes})
rename_dict = df2.set_index('all_cats').to_dict()['cat_codes']
df1 = df1.replace(rename_dict)
If you still have some values that aren't in your dictionary and want to replace them with Z, you can use a regex to replace them.
df1.astype('str').replace({'d+': 'Z'}, regex=True)
answered Oct 16 '18 at 16:10
Stephen WitkowskiStephen Witkowski
715
$endgroup$
You can convert df2 to a dictionary and use that to replace the values in df1
cat_1 = [10, 11, 12]
cat_2 = [25, 22, 30]
cat_3 = [12, 14, 15]
df1 = pd.DataFrame({'cat1':cat_1, 'cat2':cat_2, 'cat3':cat_3})
all_cats = [10, 11, 12, 25, 22, 30, 15]
cat_codes = ['A', 'B', 'C', 'D', 'E', 'F', 'G']
df2 = pd.DataFrame({'all_cats':all_cats, 'cat_codes':cat_codes})
rename_dict = df2.set_index('all_cats').to_dict()['cat_codes']
df1 = df1.replace(rename_dict)
If you still have some values that aren't in your dictionary and want to replace them with Z, you can use a regex to replace them.
df1.astype('str').replace({'d+': 'Z'}, regex=True)
answered Oct 16 '18 at 16:10
Stephen WitkowskiStephen Witkowski
715
answered Oct 16 '18 at 16:10
Stephen WitkowskiStephen Witkowski
715
answered Oct 16 '18 at 16:10
Stephen WitkowskiStephen Witkowski
715
answered Oct 16 '18 at 16:10
Stephen WitkowskiStephen Witkowski
715
715
$begingroup$
Thank you for your response. I want to create columns but not replace them and these data frames are of high cardinality which means cat_1,cat_2 and cat_3 are not the only columns in the data frame. Of course, I can convert these columns into lists and use your solution but I am looking for an elegant way of doing this. Do you think 'joins' would help?
$endgroup$
– Danny
Oct 17 '18 at 8:44
$begingroup$
Just to be clear, you wouldn't need to convert these columns into lists. You're simply changingdf2into a dictionary and using that to replace values in the data frame. I have a question: do you have other values in this dataframe that you don't want to replace, but take the same value as something inall_cats? For example, do you only want to replacecat_1,cat_2, andcat_3, but want to leavecat_4alone? If so, is any value incat_4equal to any value inall_cats? Let me know if I'm not making sense...
$endgroup$
– Stephen Witkowski
Oct 17 '18 at 12:22
$begingroup$
Yes. You are right. I want to leave the other columns alone but the other columns may or may not match the values inall_cats.
$endgroup$
– Danny
Oct 17 '18 at 12:39
add a comment |
$begingroup$
Thank you for your response. I want to create columns but not replace them and these data frames are of high cardinality which means cat_1,cat_2 and cat_3 are not the only columns in the data frame. Of course, I can convert these columns into lists and use your solution but I am looking for an elegant way of doing this. Do you think 'joins' would help?
$endgroup$
– Danny
Oct 17 '18 at 8:44
$begingroup$
Just to be clear, you wouldn't need to convert these columns into lists. You're simply changingdf2into a dictionary and using that to replace values in the data frame. I have a question: do you have other values in this dataframe that you don't want to replace, but take the same value as something inall_cats? For example, do you only want to replacecat_1,cat_2, andcat_3, but want to leavecat_4alone? If so, is any value incat_4equal to any value inall_cats? Let me know if I'm not making sense...
$endgroup$
– Stephen Witkowski
Oct 17 '18 at 12:22
$begingroup$
Yes. You are right. I want to leave the other columns alone but the other columns may or may not match the values inall_cats.
$endgroup$
– Danny
Oct 17 '18 at 12:39
$begingroup$
Thank you for your response. I want to create columns but not replace them and these data frames are of high cardinality which means cat_1,cat_2 and cat_3 are not the only columns in the data frame. Of course, I can convert these columns into lists and use your solution but I am looking for an elegant way of doing this. Do you think 'joins' would help?
$endgroup$
– Danny
Oct 17 '18 at 8:44
$begingroup$
Thank you for your response. I want to create columns but not replace them and these data frames are of high cardinality which means cat_1,cat_2 and cat_3 are not the only columns in the data frame. Of course, I can convert these columns into lists and use your solution but I am looking for an elegant way of doing this. Do you think 'joins' would help?
$endgroup$
– Danny
Oct 17 '18 at 8:44
$begingroup$
Just to be clear, you wouldn't need to convert these columns into lists. You're simply changing
df2 into a dictionary and using that to replace values in the data frame. I have a question: do you have other values in this dataframe that you don't want to replace, but take the same value as something in all_cats? For example, do you only want to replace cat_1, cat_2, and cat_3, but want to leave cat_4 alone? If so, is any value in cat_4 equal to any value in all_cats? Let me know if I'm not making sense...$endgroup$
– Stephen Witkowski
Oct 17 '18 at 12:22
$begingroup$
Just to be clear, you wouldn't need to convert these columns into lists. You're simply changing
df2 into a dictionary and using that to replace values in the data frame. I have a question: do you have other values in this dataframe that you don't want to replace, but take the same value as something in all_cats? For example, do you only want to replace cat_1, cat_2, and cat_3, but want to leave cat_4 alone? If so, is any value in cat_4 equal to any value in all_cats? Let me know if I'm not making sense...$endgroup$
– Stephen Witkowski
Oct 17 '18 at 12:22
$begingroup$
Yes. You are right. I want to leave the other columns alone but the other columns may or may not match the values in
all_cats.$endgroup$
– Danny
Oct 17 '18 at 12:39
$begingroup$
Yes. You are right. I want to leave the other columns alone but the other columns may or may not match the values in
all_cats.$endgroup$
– Danny
Oct 17 '18 at 12:39
add a comment |
0
$begingroup$
df3 = pd.merge(df1,df2,left_on=['cat'+str(i)], right_on = ['cat_codes'], how = 'left')
I would iterate this for cat1,cat2 and cat3. This does not replace the existing column values but appends new columns.
answered Dec 19 '18 at 13:44
DannyDanny
1998
$endgroup$
add a comment |
0
$begingroup$
df3 = pd.merge(df1,df2,left_on=['cat'+str(i)], right_on = ['cat_codes'], how = 'left')
I would iterate this for cat1,cat2 and cat3. This does not replace the existing column values but appends new columns.
answered Dec 19 '18 at 13:44
DannyDanny
1998
$endgroup$
add a comment |
0
0
0
$begingroup$
df3 = pd.merge(df1,df2,left_on=['cat'+str(i)], right_on = ['cat_codes'], how = 'left')
I would iterate this for cat1,cat2 and cat3. This does not replace the existing column values but appends new columns.
answered Dec 19 '18 at 13:44
DannyDanny
1998
$endgroup$
df3 = pd.merge(df1,df2,left_on=['cat'+str(i)], right_on = ['cat_codes'], how = 'left')
I would iterate this for cat1,cat2 and cat3. This does not replace the existing column values but appends new columns.
answered Dec 19 '18 at 13:44
DannyDanny
1998
answered Dec 19 '18 at 13:44
DannyDanny
1998
answered Dec 19 '18 at 13:44
DannyDanny
1998
answered Dec 19 '18 at 13:44
DannyDanny
1998
1998
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Data Science Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fdatascience.stackexchange.com%2fquestions%2f39773%2fmapping-column-values-of-one-dataframe-to-another-dataframe-using-a-key-with-dif%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
