change the sheetname to the duplicated column value
0
I have multiple sheets, each sheet having similar columns. I want to change the sheet name to the one of the duplicated column values.
For example:
A B C D
1 X1 5 ftr
2 X1 3 nbg
3 X1 2 nhg
4 X1 3 ght
5 X1 2 fgt
I have converted the multiple dataframes to excel using the below answer:
List of multiple dataframes to separate Excel sheets.
I want to replace sheet name to the 'B' column value (X1).
I want to remove the sheets if 'B' column values are empty.
I would like to know is there any possibilities.
Pleased to hear some suggestions.
python pandas dataframe
asked Nov 13 '18 at 13:04
aliali
527
add a comment |
0
I have multiple sheets, each sheet having similar columns. I want to change the sheet name to the one of the duplicated column values.
For example:
A B C D
1 X1 5 ftr
2 X1 3 nbg
3 X1 2 nhg
4 X1 3 ght
5 X1 2 fgt
I have converted the multiple dataframes to excel using the below answer:
List of multiple dataframes to separate Excel sheets.
I want to replace sheet name to the 'B' column value (X1).
I want to remove the sheets if 'B' column values are empty.
I would like to know is there any possibilities.
Pleased to hear some suggestions.
python pandas dataframe
asked Nov 13 '18 at 13:04
aliali
527
add a comment |
0
0
0
I have multiple sheets, each sheet having similar columns. I want to change the sheet name to the one of the duplicated column values.
For example:
A B C D
1 X1 5 ftr
2 X1 3 nbg
3 X1 2 nhg
4 X1 3 ght
5 X1 2 fgt
I have converted the multiple dataframes to excel using the below answer:
List of multiple dataframes to separate Excel sheets.
I want to replace sheet name to the 'B' column value (X1).
I want to remove the sheets if 'B' column values are empty.
I would like to know is there any possibilities.
Pleased to hear some suggestions.
python pandas dataframe
asked Nov 13 '18 at 13:04
aliali
527
I have multiple sheets, each sheet having similar columns. I want to change the sheet name to the one of the duplicated column values.
For example:
A B C D
1 X1 5 ftr
2 X1 3 nbg
3 X1 2 nhg
4 X1 3 ght
5 X1 2 fgt
I have converted the multiple dataframes to excel using the below answer:
List of multiple dataframes to separate Excel sheets.
I want to replace sheet name to the 'B' column value (X1).
I want to remove the sheets if 'B' column values are empty.
I would like to know is there any possibilities.
Pleased to hear some suggestions.
python pandas dataframe
python pandas dataframe
asked Nov 13 '18 at 13:04
aliali
527
asked Nov 13 '18 at 13:04
aliali
527
edited Nov 14 '18 at 12:43
ali
asked Nov 13 '18 at 13:04
aliali
527
asked Nov 13 '18 at 13:04
aliali
527
asked Nov 13 '18 at 13:04
aliali
527
527
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
First Find the duplicate column and store its value by below code:
df1= pd.DataFrame('A': [1,1,1,1,1],
'C': np.random.randn(5))
nunique = df1.apply(pd.Series.nunique)
value_col = nunique[nunique == 1] ## Value_col will have "1". In your case it will be "X1"
Now Import openpyxl to rename the sheet
import openpyxl
file_name=openpyxl.load_workbook("sample.xlsx")
sheet_name = file_name.get_sheet_by_name('Sheet')
if len(value_col) == 1:
sheet_name.title = value_col[0]
else:
file_name.remove_sheet(sheet_name)
ss.save("sample.xlsx")
answered Nov 13 '18 at 13:19
Rahul AgarwalRahul Agarwal
2,17551027
I have an empty sheets which I would like to remove it by checking the value_col, if the value is empty. Can you please suggest me.
– ali
Nov 14 '18 at 12:17
You mean if the value ofvalue_colcomes null or whatever meaning there is no column with duplicate values. You need to delete that sheet. Correct?
– Rahul Agarwal
Nov 14 '18 at 12:19
Yes, exactly. As I mentioned in my question I use the 'sheetName_'.format(i) where i is the number of sheets. Now, I want to remove the sheets with no column.
– ali
Nov 14 '18 at 12:21
1
Updated the answer...just manage your sheet_name logic...rest all will work
– Rahul Agarwal
Nov 14 '18 at 12:43
1
get_sheet_by_namegives you the name of particular sheet, if you want to get all the sheet names it will befile_name.get_sheet_names()orfile_name.sheetnames
– Rahul Agarwal
Nov 15 '18 at 10:39
|
show 9 more comments
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%2f53281640%2fchange-the-sheetname-to-the-duplicated-column-value%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
1
First Find the duplicate column and store its value by below code:
df1= pd.DataFrame('A': [1,1,1,1,1],
'C': np.random.randn(5))
nunique = df1.apply(pd.Series.nunique)
value_col = nunique[nunique == 1] ## Value_col will have "1". In your case it will be "X1"
Now Import openpyxl to rename the sheet
import openpyxl
file_name=openpyxl.load_workbook("sample.xlsx")
sheet_name = file_name.get_sheet_by_name('Sheet')
if len(value_col) == 1:
sheet_name.title = value_col[0]
else:
file_name.remove_sheet(sheet_name)
ss.save("sample.xlsx")
answered Nov 13 '18 at 13:19
Rahul AgarwalRahul Agarwal
2,17551027
I have an empty sheets which I would like to remove it by checking the value_col, if the value is empty. Can you please suggest me.
– ali
Nov 14 '18 at 12:17
You mean if the value ofvalue_colcomes null or whatever meaning there is no column with duplicate values. You need to delete that sheet. Correct?
– Rahul Agarwal
Nov 14 '18 at 12:19
Yes, exactly. As I mentioned in my question I use the 'sheetName_'.format(i) where i is the number of sheets. Now, I want to remove the sheets with no column.
– ali
Nov 14 '18 at 12:21
1
Updated the answer...just manage your sheet_name logic...rest all will work
– Rahul Agarwal
Nov 14 '18 at 12:43
1
get_sheet_by_namegives you the name of particular sheet, if you want to get all the sheet names it will befile_name.get_sheet_names()orfile_name.sheetnames
– Rahul Agarwal
Nov 15 '18 at 10:39
|
show 9 more comments
1
First Find the duplicate column and store its value by below code:
df1= pd.DataFrame('A': [1,1,1,1,1],
'C': np.random.randn(5))
nunique = df1.apply(pd.Series.nunique)
value_col = nunique[nunique == 1] ## Value_col will have "1". In your case it will be "X1"
Now Import openpyxl to rename the sheet
import openpyxl
file_name=openpyxl.load_workbook("sample.xlsx")
sheet_name = file_name.get_sheet_by_name('Sheet')
if len(value_col) == 1:
sheet_name.title = value_col[0]
else:
file_name.remove_sheet(sheet_name)
ss.save("sample.xlsx")
answered Nov 13 '18 at 13:19
Rahul AgarwalRahul Agarwal
2,17551027
I have an empty sheets which I would like to remove it by checking the value_col, if the value is empty. Can you please suggest me.
– ali
Nov 14 '18 at 12:17
You mean if the value ofvalue_colcomes null or whatever meaning there is no column with duplicate values. You need to delete that sheet. Correct?
– Rahul Agarwal
Nov 14 '18 at 12:19
Yes, exactly. As I mentioned in my question I use the 'sheetName_'.format(i) where i is the number of sheets. Now, I want to remove the sheets with no column.
– ali
Nov 14 '18 at 12:21
1
Updated the answer...just manage your sheet_name logic...rest all will work
– Rahul Agarwal
Nov 14 '18 at 12:43
1
get_sheet_by_namegives you the name of particular sheet, if you want to get all the sheet names it will befile_name.get_sheet_names()orfile_name.sheetnames
– Rahul Agarwal
Nov 15 '18 at 10:39
|
show 9 more comments
1
1
1
First Find the duplicate column and store its value by below code:
df1= pd.DataFrame('A': [1,1,1,1,1],
'C': np.random.randn(5))
nunique = df1.apply(pd.Series.nunique)
value_col = nunique[nunique == 1] ## Value_col will have "1". In your case it will be "X1"
Now Import openpyxl to rename the sheet
import openpyxl
file_name=openpyxl.load_workbook("sample.xlsx")
sheet_name = file_name.get_sheet_by_name('Sheet')
if len(value_col) == 1:
sheet_name.title = value_col[0]
else:
file_name.remove_sheet(sheet_name)
ss.save("sample.xlsx")
answered Nov 13 '18 at 13:19
Rahul AgarwalRahul Agarwal
2,17551027
First Find the duplicate column and store its value by below code:
df1= pd.DataFrame('A': [1,1,1,1,1],
'C': np.random.randn(5))
nunique = df1.apply(pd.Series.nunique)
value_col = nunique[nunique == 1] ## Value_col will have "1". In your case it will be "X1"
Now Import openpyxl to rename the sheet
import openpyxl
file_name=openpyxl.load_workbook("sample.xlsx")
sheet_name = file_name.get_sheet_by_name('Sheet')
if len(value_col) == 1:
sheet_name.title = value_col[0]
else:
file_name.remove_sheet(sheet_name)
ss.save("sample.xlsx")
answered Nov 13 '18 at 13:19
Rahul AgarwalRahul Agarwal
2,17551027
edited Nov 14 '18 at 12:41
answered Nov 13 '18 at 13:19
Rahul AgarwalRahul Agarwal
2,17551027
answered Nov 13 '18 at 13:19
Rahul AgarwalRahul Agarwal
2,17551027
answered Nov 13 '18 at 13:19
Rahul AgarwalRahul Agarwal
2,17551027
2,17551027
I have an empty sheets which I would like to remove it by checking the value_col, if the value is empty. Can you please suggest me.
– ali
Nov 14 '18 at 12:17
You mean if the value ofvalue_colcomes null or whatever meaning there is no column with duplicate values. You need to delete that sheet. Correct?
– Rahul Agarwal
Nov 14 '18 at 12:19
Yes, exactly. As I mentioned in my question I use the 'sheetName_'.format(i) where i is the number of sheets. Now, I want to remove the sheets with no column.
– ali
Nov 14 '18 at 12:21
1
Updated the answer...just manage your sheet_name logic...rest all will work
– Rahul Agarwal
Nov 14 '18 at 12:43
1
get_sheet_by_namegives you the name of particular sheet, if you want to get all the sheet names it will befile_name.get_sheet_names()orfile_name.sheetnames
– Rahul Agarwal
Nov 15 '18 at 10:39
|
show 9 more comments
I have an empty sheets which I would like to remove it by checking the value_col, if the value is empty. Can you please suggest me.
– ali
Nov 14 '18 at 12:17
You mean if the value ofvalue_colcomes null or whatever meaning there is no column with duplicate values. You need to delete that sheet. Correct?
– Rahul Agarwal
Nov 14 '18 at 12:19
Yes, exactly. As I mentioned in my question I use the 'sheetName_'.format(i) where i is the number of sheets. Now, I want to remove the sheets with no column.
– ali
Nov 14 '18 at 12:21
1
Updated the answer...just manage your sheet_name logic...rest all will work
– Rahul Agarwal
Nov 14 '18 at 12:43
1
get_sheet_by_namegives you the name of particular sheet, if you want to get all the sheet names it will befile_name.get_sheet_names()orfile_name.sheetnames
– Rahul Agarwal
Nov 15 '18 at 10:39
I have an empty sheets which I would like to remove it by checking the value_col, if the value is empty. Can you please suggest me.
– ali
Nov 14 '18 at 12:17
I have an empty sheets which I would like to remove it by checking the value_col, if the value is empty. Can you please suggest me.
– ali
Nov 14 '18 at 12:17
You mean if the value of
value_col comes null or whatever meaning there is no column with duplicate values. You need to delete that sheet. Correct?– Rahul Agarwal
Nov 14 '18 at 12:19
You mean if the value of
value_col comes null or whatever meaning there is no column with duplicate values. You need to delete that sheet. Correct?– Rahul Agarwal
Nov 14 '18 at 12:19
Yes, exactly. As I mentioned in my question I use the 'sheetName_'.format(i) where i is the number of sheets. Now, I want to remove the sheets with no column.
– ali
Nov 14 '18 at 12:21
Yes, exactly. As I mentioned in my question I use the 'sheetName_'.format(i) where i is the number of sheets. Now, I want to remove the sheets with no column.
– ali
Nov 14 '18 at 12:21
1
1
Updated the answer...just manage your sheet_name logic...rest all will work
– Rahul Agarwal
Nov 14 '18 at 12:43
Updated the answer...just manage your sheet_name logic...rest all will work
– Rahul Agarwal
Nov 14 '18 at 12:43
1
1
get_sheet_by_name gives you the name of particular sheet, if you want to get all the sheet names it will be file_name.get_sheet_names() or file_name.sheetnames– Rahul Agarwal
Nov 15 '18 at 10:39
get_sheet_by_name gives you the name of particular sheet, if you want to get all the sheet names it will be file_name.get_sheet_names() or file_name.sheetnames– Rahul Agarwal
Nov 15 '18 at 10:39
|
show 9 more comments
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%2f53281640%2fchange-the-sheetname-to-the-duplicated-column-value%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
