Fill data from column X to Column Y if Y has NaN using python
I have a column say X with some data. I want to move this data into another Column say Y. I got the code to do it.
This shows column X and Y # in Column Y meaning NAN
The code is as below:
id = df['X'].str.extract(r"(d[8]sd[2])",expand=False).tolist() #extracting values which look like 12345678s12 and i include NaN values
df_new= pd.DataFrame({'Y':id})
wb = load_workbook('text.xlsx')
ws = wb['Sheet1']
for index, row in df_new.iterrows():
cell = 'Y%d' % (index + 2)
ws[cell] = row[0]
wb.save('text.xlsx')
The problem I'm facing is there is some data in Column Y and code overwrites the whole column Y with id.
I don't want this to happen.I want to retain the data in column Y and only if there are NaN values in it, I want those to be replaced by the corresponding value of id.
python excel pandas nan
edited Nov 23 '18 at 11:01
Anubhav Singh
143212
asked Nov 23 '18 at 10:49
Adarsh BhansaliAdarsh Bhansali
84
add a comment |
I have a column say X with some data. I want to move this data into another Column say Y. I got the code to do it.
This shows column X and Y # in Column Y meaning NAN
The code is as below:
id = df['X'].str.extract(r"(d[8]sd[2])",expand=False).tolist() #extracting values which look like 12345678s12 and i include NaN values
df_new= pd.DataFrame({'Y':id})
wb = load_workbook('text.xlsx')
ws = wb['Sheet1']
for index, row in df_new.iterrows():
cell = 'Y%d' % (index + 2)
ws[cell] = row[0]
wb.save('text.xlsx')
The problem I'm facing is there is some data in Column Y and code overwrites the whole column Y with id.
I don't want this to happen.I want to retain the data in column Y and only if there are NaN values in it, I want those to be replaced by the corresponding value of id.
python excel pandas nan
edited Nov 23 '18 at 11:01
Anubhav Singh
143212
asked Nov 23 '18 at 10:49
Adarsh BhansaliAdarsh Bhansali
84
Do you want to replace values fromxtoywhereycontains#?
– Mohamed Thasin ah
Nov 23 '18 at 10:55
Yes thats what is my expected output
– Adarsh Bhansali
Nov 23 '18 at 10:57
add a comment |
I have a column say X with some data. I want to move this data into another Column say Y. I got the code to do it.
This shows column X and Y # in Column Y meaning NAN
The code is as below:
id = df['X'].str.extract(r"(d[8]sd[2])",expand=False).tolist() #extracting values which look like 12345678s12 and i include NaN values
df_new= pd.DataFrame({'Y':id})
wb = load_workbook('text.xlsx')
ws = wb['Sheet1']
for index, row in df_new.iterrows():
cell = 'Y%d' % (index + 2)
ws[cell] = row[0]
wb.save('text.xlsx')
The problem I'm facing is there is some data in Column Y and code overwrites the whole column Y with id.
I don't want this to happen.I want to retain the data in column Y and only if there are NaN values in it, I want those to be replaced by the corresponding value of id.
python excel pandas nan
edited Nov 23 '18 at 11:01
Anubhav Singh
143212
asked Nov 23 '18 at 10:49
Adarsh BhansaliAdarsh Bhansali
84
I have a column say X with some data. I want to move this data into another Column say Y. I got the code to do it.
This shows column X and Y # in Column Y meaning NAN
The code is as below:
id = df['X'].str.extract(r"(d[8]sd[2])",expand=False).tolist() #extracting values which look like 12345678s12 and i include NaN values
df_new= pd.DataFrame({'Y':id})
wb = load_workbook('text.xlsx')
ws = wb['Sheet1']
for index, row in df_new.iterrows():
cell = 'Y%d' % (index + 2)
ws[cell] = row[0]
wb.save('text.xlsx')
The problem I'm facing is there is some data in Column Y and code overwrites the whole column Y with id.
I don't want this to happen.I want to retain the data in column Y and only if there are NaN values in it, I want those to be replaced by the corresponding value of id.
python excel pandas nan
python excel pandas nan
edited Nov 23 '18 at 11:01
Anubhav Singh
143212
asked Nov 23 '18 at 10:49
Adarsh BhansaliAdarsh Bhansali
84
edited Nov 23 '18 at 11:01
Anubhav Singh
143212
asked Nov 23 '18 at 10:49
Adarsh BhansaliAdarsh Bhansali
84
edited Nov 23 '18 at 11:01
Anubhav Singh
143212
edited Nov 23 '18 at 11:01
Anubhav Singh
143212
edited Nov 23 '18 at 11:01
Anubhav Singh
143212
143212
asked Nov 23 '18 at 10:49
Adarsh BhansaliAdarsh Bhansali
84
asked Nov 23 '18 at 10:49
Adarsh BhansaliAdarsh Bhansali
84
asked Nov 23 '18 at 10:49
Adarsh BhansaliAdarsh Bhansali
84
84
Do you want to replace values fromxtoywhereycontains#?
– Mohamed Thasin ah
Nov 23 '18 at 10:55
Yes thats what is my expected output
– Adarsh Bhansali
Nov 23 '18 at 10:57
add a comment |
Do you want to replace values fromxtoywhereycontains#?
– Mohamed Thasin ah
Nov 23 '18 at 10:55
Yes thats what is my expected output
– Adarsh Bhansali
Nov 23 '18 at 10:57
Do you want to replace values from
x to y where y contains #?– Mohamed Thasin ah
Nov 23 '18 at 10:55
Do you want to replace values from
x to y where y contains #?– Mohamed Thasin ah
Nov 23 '18 at 10:55
Yes thats what is my expected output
– Adarsh Bhansali
Nov 23 '18 at 10:57
Yes thats what is my expected output
– Adarsh Bhansali
Nov 23 '18 at 10:57
add a comment |
4 Answers
4
active
oldest
votes
mask
You can mask one series with another:
df['Y'].mask(df['Y'] == '#', df['X'], inplace=True)
Here's a demo with the version which does not work in place:
df = pd.DataFrame({'X': ['A', 'B', 'C', 'D', 'E'],
'Y': ['#', '1', '2', '#', '3']})
df['Y'] = df['Y'].mask(df['Y'] == '#', df['X'])
print(df)
X Y
0 A A
1 B 1
2 C 2
3 D D
4 E 3
answered Nov 23 '18 at 10:56
jppjpp
94.1k2155106
Incase if there was a blank in place of # how would the code change ?
– Adarsh Bhansali
Nov 23 '18 at 11:09
If by blank you mean empty string, usedf['Y'].isin(['#', ''])for your Boolean condition. If you mean null value (NaN), usedf['Y'].isnull() | df['Y'].eq('#').
– jpp
Nov 23 '18 at 11:10
thanks.. but how do i transfer it back to the excel file and in place of df['X'] can i use id so that i can just give the extracted data.
– Adarsh Bhansali
Nov 23 '18 at 11:20
@AdarshBhansali, You are asking at least 3 questions there. If you have another question, please ask a new question.
– jpp
Nov 23 '18 at 11:23
add a comment |
You can use:
df['Y'] = np.where(df['Y']=='#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
JoeJoe
5,89621129
add a comment |
Use np.where
df['Y'] = np.where(df['Y'] == '#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
SociopathSociopath
3,64281635
add a comment |
.loc
Do you want to replace values from x to y where y contains #
If So try this,
df.loc[df['Y']=='#','Y']=df['X']
As your objective is only want to replace records where Y has #, So mask or lock the index where Y has # then assign values from X to Y to only locked index.
If you want to deal with blank then,
df.loc[df['Y'].isnull(),'Y']=df['X']
answered Nov 23 '18 at 10:56
Mohamed Thasin ahMohamed Thasin ah
3,49331239
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%2f53445242%2ffill-data-from-column-x-to-column-y-if-y-has-nan-using-python%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
mask
You can mask one series with another:
df['Y'].mask(df['Y'] == '#', df['X'], inplace=True)
Here's a demo with the version which does not work in place:
df = pd.DataFrame({'X': ['A', 'B', 'C', 'D', 'E'],
'Y': ['#', '1', '2', '#', '3']})
df['Y'] = df['Y'].mask(df['Y'] == '#', df['X'])
print(df)
X Y
0 A A
1 B 1
2 C 2
3 D D
4 E 3
answered Nov 23 '18 at 10:56
jppjpp
94.1k2155106
Incase if there was a blank in place of # how would the code change ?
– Adarsh Bhansali
Nov 23 '18 at 11:09
If by blank you mean empty string, usedf['Y'].isin(['#', ''])for your Boolean condition. If you mean null value (NaN), usedf['Y'].isnull() | df['Y'].eq('#').
– jpp
Nov 23 '18 at 11:10
thanks.. but how do i transfer it back to the excel file and in place of df['X'] can i use id so that i can just give the extracted data.
– Adarsh Bhansali
Nov 23 '18 at 11:20
@AdarshBhansali, You are asking at least 3 questions there. If you have another question, please ask a new question.
– jpp
Nov 23 '18 at 11:23
add a comment |
mask
You can mask one series with another:
df['Y'].mask(df['Y'] == '#', df['X'], inplace=True)
Here's a demo with the version which does not work in place:
df = pd.DataFrame({'X': ['A', 'B', 'C', 'D', 'E'],
'Y': ['#', '1', '2', '#', '3']})
df['Y'] = df['Y'].mask(df['Y'] == '#', df['X'])
print(df)
X Y
0 A A
1 B 1
2 C 2
3 D D
4 E 3
answered Nov 23 '18 at 10:56
jppjpp
94.1k2155106
Incase if there was a blank in place of # how would the code change ?
– Adarsh Bhansali
Nov 23 '18 at 11:09
If by blank you mean empty string, usedf['Y'].isin(['#', ''])for your Boolean condition. If you mean null value (NaN), usedf['Y'].isnull() | df['Y'].eq('#').
– jpp
Nov 23 '18 at 11:10
thanks.. but how do i transfer it back to the excel file and in place of df['X'] can i use id so that i can just give the extracted data.
– Adarsh Bhansali
Nov 23 '18 at 11:20
@AdarshBhansali, You are asking at least 3 questions there. If you have another question, please ask a new question.
– jpp
Nov 23 '18 at 11:23
add a comment |
mask
You can mask one series with another:
df['Y'].mask(df['Y'] == '#', df['X'], inplace=True)
Here's a demo with the version which does not work in place:
df = pd.DataFrame({'X': ['A', 'B', 'C', 'D', 'E'],
'Y': ['#', '1', '2', '#', '3']})
df['Y'] = df['Y'].mask(df['Y'] == '#', df['X'])
print(df)
X Y
0 A A
1 B 1
2 C 2
3 D D
4 E 3
answered Nov 23 '18 at 10:56
jppjpp
94.1k2155106
mask
You can mask one series with another:
df['Y'].mask(df['Y'] == '#', df['X'], inplace=True)
Here's a demo with the version which does not work in place:
df = pd.DataFrame({'X': ['A', 'B', 'C', 'D', 'E'],
'Y': ['#', '1', '2', '#', '3']})
df['Y'] = df['Y'].mask(df['Y'] == '#', df['X'])
print(df)
X Y
0 A A
1 B 1
2 C 2
3 D D
4 E 3
answered Nov 23 '18 at 10:56
jppjpp
94.1k2155106
answered Nov 23 '18 at 10:56
jppjpp
94.1k2155106
answered Nov 23 '18 at 10:56
jppjpp
94.1k2155106
answered Nov 23 '18 at 10:56
jppjpp
94.1k2155106
94.1k2155106
Incase if there was a blank in place of # how would the code change ?
– Adarsh Bhansali
Nov 23 '18 at 11:09
If by blank you mean empty string, usedf['Y'].isin(['#', ''])for your Boolean condition. If you mean null value (NaN), usedf['Y'].isnull() | df['Y'].eq('#').
– jpp
Nov 23 '18 at 11:10
thanks.. but how do i transfer it back to the excel file and in place of df['X'] can i use id so that i can just give the extracted data.
– Adarsh Bhansali
Nov 23 '18 at 11:20
@AdarshBhansali, You are asking at least 3 questions there. If you have another question, please ask a new question.
– jpp
Nov 23 '18 at 11:23
add a comment |
Incase if there was a blank in place of # how would the code change ?
– Adarsh Bhansali
Nov 23 '18 at 11:09
If by blank you mean empty string, usedf['Y'].isin(['#', ''])for your Boolean condition. If you mean null value (NaN), usedf['Y'].isnull() | df['Y'].eq('#').
– jpp
Nov 23 '18 at 11:10
thanks.. but how do i transfer it back to the excel file and in place of df['X'] can i use id so that i can just give the extracted data.
– Adarsh Bhansali
Nov 23 '18 at 11:20
@AdarshBhansali, You are asking at least 3 questions there. If you have another question, please ask a new question.
– jpp
Nov 23 '18 at 11:23
Incase if there was a blank in place of # how would the code change ?
– Adarsh Bhansali
Nov 23 '18 at 11:09
Incase if there was a blank in place of # how would the code change ?
– Adarsh Bhansali
Nov 23 '18 at 11:09
If by blank you mean empty string, use
df['Y'].isin(['#', '']) for your Boolean condition. If you mean null value (NaN), use df['Y'].isnull() | df['Y'].eq('#').– jpp
Nov 23 '18 at 11:10
If by blank you mean empty string, use
df['Y'].isin(['#', '']) for your Boolean condition. If you mean null value (NaN), use df['Y'].isnull() | df['Y'].eq('#').– jpp
Nov 23 '18 at 11:10
thanks.. but how do i transfer it back to the excel file and in place of df['X'] can i use id so that i can just give the extracted data.
– Adarsh Bhansali
Nov 23 '18 at 11:20
thanks.. but how do i transfer it back to the excel file and in place of df['X'] can i use id so that i can just give the extracted data.
– Adarsh Bhansali
Nov 23 '18 at 11:20
@AdarshBhansali, You are asking at least 3 questions there. If you have another question, please ask a new question.
– jpp
Nov 23 '18 at 11:23
@AdarshBhansali, You are asking at least 3 questions there. If you have another question, please ask a new question.
– jpp
Nov 23 '18 at 11:23
add a comment |
You can use:
df['Y'] = np.where(df['Y']=='#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
JoeJoe
5,89621129
add a comment |
You can use:
df['Y'] = np.where(df['Y']=='#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
JoeJoe
5,89621129
add a comment |
You can use:
df['Y'] = np.where(df['Y']=='#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
JoeJoe
5,89621129
You can use:
df['Y'] = np.where(df['Y']=='#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
JoeJoe
5,89621129
answered Nov 23 '18 at 10:58
JoeJoe
5,89621129
answered Nov 23 '18 at 10:58
JoeJoe
5,89621129
answered Nov 23 '18 at 10:58
JoeJoe
5,89621129
5,89621129
add a comment |
add a comment |
Use np.where
df['Y'] = np.where(df['Y'] == '#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
SociopathSociopath
3,64281635
add a comment |
Use np.where
df['Y'] = np.where(df['Y'] == '#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
SociopathSociopath
3,64281635
add a comment |
Use np.where
df['Y'] = np.where(df['Y'] == '#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
SociopathSociopath
3,64281635
Use np.where
df['Y'] = np.where(df['Y'] == '#', df['X'], df['Y'])
answered Nov 23 '18 at 10:58
SociopathSociopath
3,64281635
answered Nov 23 '18 at 10:58
SociopathSociopath
3,64281635
answered Nov 23 '18 at 10:58
SociopathSociopath
3,64281635
answered Nov 23 '18 at 10:58
SociopathSociopath
3,64281635
3,64281635
add a comment |
add a comment |
.loc
Do you want to replace values from x to y where y contains #
If So try this,
df.loc[df['Y']=='#','Y']=df['X']
As your objective is only want to replace records where Y has #, So mask or lock the index where Y has # then assign values from X to Y to only locked index.
If you want to deal with blank then,
df.loc[df['Y'].isnull(),'Y']=df['X']
answered Nov 23 '18 at 10:56
Mohamed Thasin ahMohamed Thasin ah
3,49331239
add a comment |
.loc
Do you want to replace values from x to y where y contains #
If So try this,
df.loc[df['Y']=='#','Y']=df['X']
As your objective is only want to replace records where Y has #, So mask or lock the index where Y has # then assign values from X to Y to only locked index.
If you want to deal with blank then,
df.loc[df['Y'].isnull(),'Y']=df['X']
answered Nov 23 '18 at 10:56
Mohamed Thasin ahMohamed Thasin ah
3,49331239
add a comment |
.loc
Do you want to replace values from x to y where y contains #
If So try this,
df.loc[df['Y']=='#','Y']=df['X']
As your objective is only want to replace records where Y has #, So mask or lock the index where Y has # then assign values from X to Y to only locked index.
If you want to deal with blank then,
df.loc[df['Y'].isnull(),'Y']=df['X']
answered Nov 23 '18 at 10:56
Mohamed Thasin ahMohamed Thasin ah
3,49331239
.loc
Do you want to replace values from x to y where y contains #
If So try this,
df.loc[df['Y']=='#','Y']=df['X']
As your objective is only want to replace records where Y has #, So mask or lock the index where Y has # then assign values from X to Y to only locked index.
If you want to deal with blank then,
df.loc[df['Y'].isnull(),'Y']=df['X']
answered Nov 23 '18 at 10:56
Mohamed Thasin ahMohamed Thasin ah
3,49331239
edited Nov 23 '18 at 11:10
answered Nov 23 '18 at 10:56
Mohamed Thasin ahMohamed Thasin ah
3,49331239
answered Nov 23 '18 at 10:56
Mohamed Thasin ahMohamed Thasin ah
3,49331239
answered Nov 23 '18 at 10:56
Mohamed Thasin ahMohamed Thasin ah
3,49331239
3,49331239
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%2f53445242%2ffill-data-from-column-x-to-column-y-if-y-has-nan-using-python%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
Do you want to replace values from
xtoywhereycontains#?– Mohamed Thasin ah
Nov 23 '18 at 10:55
Yes thats what is my expected output
– Adarsh Bhansali
Nov 23 '18 at 10:57