Looping through excel sheets - array index out of rangeHow to break out of multiple loops in Python?Is there a NumPy function to return the first index of something in an array?Accessing the index in 'for' loops?Breaking out of nested loopsIterating through a range of dates in PythonLoop through each row of a range in ExcelIndexError: list assignment index out of rangeWhat is the most efficient way to loop through dataframes with pandas?How do I get a DataFrame Index / Series column as an array or list?How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
Can a Warlock become Neutral Good?
Can a German sentence have two subjects?
GPS Rollover on Android Smartphones
Mains transformer blew up amplifier, incorrect description in wiring instructions?
Why don't electromagnetic waves interact with each other?
N.B. ligature in Latex
Has the BBC provided arguments for saying Brexit being cancelled is unlikely?
Japan - Plan around max visa duration
How is the claim "I am in New York only if I am in America" the same as "If I am in New York, then I am in America?
How much RAM could one put in a typical 80386 setup?
Writing rule which states that two causes for the same superpower is bad writing
Show that if two triangles built on parallel lines, with equal bases have the same perimeter only if they are congruent.
Email Account under attack (really) - anything I can do?
Approximately how much travel time was saved by the opening of the Suez Canal in 1869?
Is it possible to rebuild the bike frame (to make it lighter) by welding aluminum tubes
Prevent a directory in /tmp from being deleted
Dragon forelimb placement
Theorems that impeded progress
What is the offset in a seaplane's hull?
Is the language p and n are natural numbers and there's no prime number in [p,p+n] belongs to NP class?
If I cast Expeditious Retreat, can I Dash as a bonus action on the same turn?
Is it possible to make sharp wind that can cut stuff from afar?
Smoothness of finite-dimensional functional calculus
Did Shadowfax go to Valinor?
Looping through excel sheets - array index out of range
How to break out of multiple loops in Python?Is there a NumPy function to return the first index of something in an array?Accessing the index in 'for' loops?Breaking out of nested loopsIterating through a range of dates in PythonLoop through each row of a range in ExcelIndexError: list assignment index out of rangeWhat is the most efficient way to loop through dataframes with pandas?How do I get a DataFrame Index / Series column as an array or list?How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have an excel with 17 sheets and I am running a loop to extract the data from all the sheets but the first one. All the sheets are identically structured, yet something wrong happens when my i = 6 as I get the following error:
---------------------------------------------------------------------------
IndexError Traceback (most recent call last)
<ipython-input-244-ea6f45e973a4> in <module>()
4 df_1 = pd.read_excel(r'C:Usersfilippo.sebastioOneDrive - ELEVATETargetTarget Download 28 FebQuantitative dataSCHAEFER_Putian ZhangShengzhangsheng -- RSAP Factory Metrics Tool- Hardcopy Form draft to publish 2018 12.xlsx', i , header = 4, index_col=1)
5 worksheet_1 = workbook.sheet_by_index(i)
----> 6 month = worksheet_1.cell(5,4).value
7 df_1 = df_1.drop(df_1.index[0])
8 df_1 = df_1.drop(df_1.index[-1])
~Anaconda3libsite-packagesxlrdsheet.py in cell(self, rowx, colx)
406 xfx = None
407 return Cell(
--> 408 self._cell_types[rowx][colx],
409 self._cell_values[rowx][colx],
410 xfx,
IndexError: array index out of range
This is my loop
frame = pd.DataFrame()
list_ = []
for i in range(1,17):
df_1 = pd.read_excel(r'C:Usersfilippo.sebastioOneDrive - ELEVATETargetTarget Download 28 FebQuantitative dataSCHAEFER_Putian ZhangShengzhangsheng -- RSAP Factory Metrics Tool- Hardcopy Form draft to publish 2018 12.xlsx', i , header = 4, index_col=1)
worksheet_1 = workbook.sheet_by_index(i)
month = worksheet_1.cell(5,4).value
df_1 = df_1.drop(df_1.index[0])
df_1 = df_1.drop(df_1.index[-1])
df_1 = df_1.drop(df_1.columns[0], axis=1)
df_1 = df_1.dropna(axis=1, how='all')
for col in df_1.columns[0:3]:
df_1[col] = pd.to_numeric(df_1[col], errors='coerce')
df_1['mean'] = df_1.iloc[:, 0:3].mean(axis=1)
df_1 = df_1[[ 'mean']]
df_1_t = df_1.T
df_1_t['Month'] = month
df_1_t['Factory'] = worksheet_1.cell(3,2).value
df_1_t['Factory_id'] = worksheet_0.cell(3,2 ).value
df_1_t['Country'] = worksheet_0.cell(4,2 ).value
df_1_t['Consultant'] = worksheet_0.cell(5,2 ).value
list_.append(df_1_t)
list_
frame = pd.concat(list_)
frame
I checked and the particular cell in the sheet is not empty (all the sheets are the same and similarly populated). Also, the loop works fine throughout all the other sheets - (1 to 5 and 7 to 17). What could be the problem?
python excel pandas
asked Mar 9 at 3:29
Filippo SebastioFilippo Sebastio
18619
|
show 8 more comments
I have an excel with 17 sheets and I am running a loop to extract the data from all the sheets but the first one. All the sheets are identically structured, yet something wrong happens when my i = 6 as I get the following error:
---------------------------------------------------------------------------
IndexError Traceback (most recent call last)
<ipython-input-244-ea6f45e973a4> in <module>()
4 df_1 = pd.read_excel(r'C:Usersfilippo.sebastioOneDrive - ELEVATETargetTarget Download 28 FebQuantitative dataSCHAEFER_Putian ZhangShengzhangsheng -- RSAP Factory Metrics Tool- Hardcopy Form draft to publish 2018 12.xlsx', i , header = 4, index_col=1)
5 worksheet_1 = workbook.sheet_by_index(i)
----> 6 month = worksheet_1.cell(5,4).value
7 df_1 = df_1.drop(df_1.index[0])
8 df_1 = df_1.drop(df_1.index[-1])
~Anaconda3libsite-packagesxlrdsheet.py in cell(self, rowx, colx)
406 xfx = None
407 return Cell(
--> 408 self._cell_types[rowx][colx],
409 self._cell_values[rowx][colx],
410 xfx,
IndexError: array index out of range
This is my loop
frame = pd.DataFrame()
list_ = []
for i in range(1,17):
df_1 = pd.read_excel(r'C:Usersfilippo.sebastioOneDrive - ELEVATETargetTarget Download 28 FebQuantitative dataSCHAEFER_Putian ZhangShengzhangsheng -- RSAP Factory Metrics Tool- Hardcopy Form draft to publish 2018 12.xlsx', i , header = 4, index_col=1)
worksheet_1 = workbook.sheet_by_index(i)
month = worksheet_1.cell(5,4).value
df_1 = df_1.drop(df_1.index[0])
df_1 = df_1.drop(df_1.index[-1])
df_1 = df_1.drop(df_1.columns[0], axis=1)
df_1 = df_1.dropna(axis=1, how='all')
for col in df_1.columns[0:3]:
df_1[col] = pd.to_numeric(df_1[col], errors='coerce')
df_1['mean'] = df_1.iloc[:, 0:3].mean(axis=1)
df_1 = df_1[[ 'mean']]
df_1_t = df_1.T
df_1_t['Month'] = month
df_1_t['Factory'] = worksheet_1.cell(3,2).value
df_1_t['Factory_id'] = worksheet_0.cell(3,2 ).value
df_1_t['Country'] = worksheet_0.cell(4,2 ).value
df_1_t['Consultant'] = worksheet_0.cell(5,2 ).value
list_.append(df_1_t)
list_
frame = pd.concat(list_)
frame
I checked and the particular cell in the sheet is not empty (all the sheets are the same and similarly populated). Also, the loop works fine throughout all the other sheets - (1 to 5 and 7 to 17). What could be the problem?
python excel pandas
asked Mar 9 at 3:29
Filippo SebastioFilippo Sebastio
18619
Is there a hidden sheet? Can you try unhiding sheets and see if the 6th sheet is structured differently?
– Baris Tasdelen
Mar 9 at 3:37
I unhid the sheet and a drop down reference sheet came up. I moved the reference sheet to the last sheet, yet, I re-run the loop and I have always the same problem.
– Filippo Sebastio
Mar 9 at 3:41
Is it still failing at i=6 or when i=17?
– Baris Tasdelen
Mar 9 at 3:43
always at i = 6
– Filippo Sebastio
Mar 9 at 3:44
you can put the code in the for loop in a try / except, so that it will run for all the valid sheets, and skip the ones that fail
– Baris Tasdelen
Mar 9 at 3:45
|
show 8 more comments
I have an excel with 17 sheets and I am running a loop to extract the data from all the sheets but the first one. All the sheets are identically structured, yet something wrong happens when my i = 6 as I get the following error:
---------------------------------------------------------------------------
IndexError Traceback (most recent call last)
<ipython-input-244-ea6f45e973a4> in <module>()
4 df_1 = pd.read_excel(r'C:Usersfilippo.sebastioOneDrive - ELEVATETargetTarget Download 28 FebQuantitative dataSCHAEFER_Putian ZhangShengzhangsheng -- RSAP Factory Metrics Tool- Hardcopy Form draft to publish 2018 12.xlsx', i , header = 4, index_col=1)
5 worksheet_1 = workbook.sheet_by_index(i)
----> 6 month = worksheet_1.cell(5,4).value
7 df_1 = df_1.drop(df_1.index[0])
8 df_1 = df_1.drop(df_1.index[-1])
~Anaconda3libsite-packagesxlrdsheet.py in cell(self, rowx, colx)
406 xfx = None
407 return Cell(
--> 408 self._cell_types[rowx][colx],
409 self._cell_values[rowx][colx],
410 xfx,
IndexError: array index out of range
This is my loop
frame = pd.DataFrame()
list_ = []
for i in range(1,17):
df_1 = pd.read_excel(r'C:Usersfilippo.sebastioOneDrive - ELEVATETargetTarget Download 28 FebQuantitative dataSCHAEFER_Putian ZhangShengzhangsheng -- RSAP Factory Metrics Tool- Hardcopy Form draft to publish 2018 12.xlsx', i , header = 4, index_col=1)
worksheet_1 = workbook.sheet_by_index(i)
month = worksheet_1.cell(5,4).value
df_1 = df_1.drop(df_1.index[0])
df_1 = df_1.drop(df_1.index[-1])
df_1 = df_1.drop(df_1.columns[0], axis=1)
df_1 = df_1.dropna(axis=1, how='all')
for col in df_1.columns[0:3]:
df_1[col] = pd.to_numeric(df_1[col], errors='coerce')
df_1['mean'] = df_1.iloc[:, 0:3].mean(axis=1)
df_1 = df_1[[ 'mean']]
df_1_t = df_1.T
df_1_t['Month'] = month
df_1_t['Factory'] = worksheet_1.cell(3,2).value
df_1_t['Factory_id'] = worksheet_0.cell(3,2 ).value
df_1_t['Country'] = worksheet_0.cell(4,2 ).value
df_1_t['Consultant'] = worksheet_0.cell(5,2 ).value
list_.append(df_1_t)
list_
frame = pd.concat(list_)
frame
I checked and the particular cell in the sheet is not empty (all the sheets are the same and similarly populated). Also, the loop works fine throughout all the other sheets - (1 to 5 and 7 to 17). What could be the problem?
python excel pandas
asked Mar 9 at 3:29
Filippo SebastioFilippo Sebastio
18619
I have an excel with 17 sheets and I am running a loop to extract the data from all the sheets but the first one. All the sheets are identically structured, yet something wrong happens when my i = 6 as I get the following error:
---------------------------------------------------------------------------
IndexError Traceback (most recent call last)
<ipython-input-244-ea6f45e973a4> in <module>()
4 df_1 = pd.read_excel(r'C:Usersfilippo.sebastioOneDrive - ELEVATETargetTarget Download 28 FebQuantitative dataSCHAEFER_Putian ZhangShengzhangsheng -- RSAP Factory Metrics Tool- Hardcopy Form draft to publish 2018 12.xlsx', i , header = 4, index_col=1)
5 worksheet_1 = workbook.sheet_by_index(i)
----> 6 month = worksheet_1.cell(5,4).value
7 df_1 = df_1.drop(df_1.index[0])
8 df_1 = df_1.drop(df_1.index[-1])
~Anaconda3libsite-packagesxlrdsheet.py in cell(self, rowx, colx)
406 xfx = None
407 return Cell(
--> 408 self._cell_types[rowx][colx],
409 self._cell_values[rowx][colx],
410 xfx,
IndexError: array index out of range
This is my loop
frame = pd.DataFrame()
list_ = []
for i in range(1,17):
df_1 = pd.read_excel(r'C:Usersfilippo.sebastioOneDrive - ELEVATETargetTarget Download 28 FebQuantitative dataSCHAEFER_Putian ZhangShengzhangsheng -- RSAP Factory Metrics Tool- Hardcopy Form draft to publish 2018 12.xlsx', i , header = 4, index_col=1)
worksheet_1 = workbook.sheet_by_index(i)
month = worksheet_1.cell(5,4).value
df_1 = df_1.drop(df_1.index[0])
df_1 = df_1.drop(df_1.index[-1])
df_1 = df_1.drop(df_1.columns[0], axis=1)
df_1 = df_1.dropna(axis=1, how='all')
for col in df_1.columns[0:3]:
df_1[col] = pd.to_numeric(df_1[col], errors='coerce')
df_1['mean'] = df_1.iloc[:, 0:3].mean(axis=1)
df_1 = df_1[[ 'mean']]
df_1_t = df_1.T
df_1_t['Month'] = month
df_1_t['Factory'] = worksheet_1.cell(3,2).value
df_1_t['Factory_id'] = worksheet_0.cell(3,2 ).value
df_1_t['Country'] = worksheet_0.cell(4,2 ).value
df_1_t['Consultant'] = worksheet_0.cell(5,2 ).value
list_.append(df_1_t)
list_
frame = pd.concat(list_)
frame
I checked and the particular cell in the sheet is not empty (all the sheets are the same and similarly populated). Also, the loop works fine throughout all the other sheets - (1 to 5 and 7 to 17). What could be the problem?
python excel pandas
python excel pandas
asked Mar 9 at 3:29
Filippo SebastioFilippo Sebastio
18619
asked Mar 9 at 3:29
Filippo SebastioFilippo Sebastio
18619
asked Mar 9 at 3:29
Filippo SebastioFilippo Sebastio
18619
asked Mar 9 at 3:29
Filippo SebastioFilippo Sebastio
18619
asked Mar 9 at 3:29
Filippo SebastioFilippo Sebastio
18619
18619
Is there a hidden sheet? Can you try unhiding sheets and see if the 6th sheet is structured differently?
– Baris Tasdelen
Mar 9 at 3:37
I unhid the sheet and a drop down reference sheet came up. I moved the reference sheet to the last sheet, yet, I re-run the loop and I have always the same problem.
– Filippo Sebastio
Mar 9 at 3:41
Is it still failing at i=6 or when i=17?
– Baris Tasdelen
Mar 9 at 3:43
always at i = 6
– Filippo Sebastio
Mar 9 at 3:44
you can put the code in the for loop in a try / except, so that it will run for all the valid sheets, and skip the ones that fail
– Baris Tasdelen
Mar 9 at 3:45
|
show 8 more comments
Is there a hidden sheet? Can you try unhiding sheets and see if the 6th sheet is structured differently?
– Baris Tasdelen
Mar 9 at 3:37
I unhid the sheet and a drop down reference sheet came up. I moved the reference sheet to the last sheet, yet, I re-run the loop and I have always the same problem.
– Filippo Sebastio
Mar 9 at 3:41
Is it still failing at i=6 or when i=17?
– Baris Tasdelen
Mar 9 at 3:43
always at i = 6
– Filippo Sebastio
Mar 9 at 3:44
you can put the code in the for loop in a try / except, so that it will run for all the valid sheets, and skip the ones that fail
– Baris Tasdelen
Mar 9 at 3:45
Is there a hidden sheet? Can you try unhiding sheets and see if the 6th sheet is structured differently?
– Baris Tasdelen
Mar 9 at 3:37
Is there a hidden sheet? Can you try unhiding sheets and see if the 6th sheet is structured differently?
– Baris Tasdelen
Mar 9 at 3:37
I unhid the sheet and a drop down reference sheet came up. I moved the reference sheet to the last sheet, yet, I re-run the loop and I have always the same problem.
– Filippo Sebastio
Mar 9 at 3:41
I unhid the sheet and a drop down reference sheet came up. I moved the reference sheet to the last sheet, yet, I re-run the loop and I have always the same problem.
– Filippo Sebastio
Mar 9 at 3:41
Is it still failing at i=6 or when i=17?
– Baris Tasdelen
Mar 9 at 3:43
Is it still failing at i=6 or when i=17?
– Baris Tasdelen
Mar 9 at 3:43
always at i = 6
– Filippo Sebastio
Mar 9 at 3:44
always at i = 6
– Filippo Sebastio
Mar 9 at 3:44
you can put the code in the for loop in a try / except, so that it will run for all the valid sheets, and skip the ones that fail
– Baris Tasdelen
Mar 9 at 3:45
you can put the code in the for loop in a try / except, so that it will run for all the valid sheets, and skip the ones that fail
– Baris Tasdelen
Mar 9 at 3:45
|
show 8 more comments
0
active
oldest
votes
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%2f55073713%2flooping-through-excel-sheets-array-index-out-of-range%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f55073713%2flooping-through-excel-sheets-array-index-out-of-range%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
Is there a hidden sheet? Can you try unhiding sheets and see if the 6th sheet is structured differently?
– Baris Tasdelen
Mar 9 at 3:37
I unhid the sheet and a drop down reference sheet came up. I moved the reference sheet to the last sheet, yet, I re-run the loop and I have always the same problem.
– Filippo Sebastio
Mar 9 at 3:41
Is it still failing at i=6 or when i=17?
– Baris Tasdelen
Mar 9 at 3:43
always at i = 6
– Filippo Sebastio
Mar 9 at 3:44
you can put the code in the for loop in a try / except, so that it will run for all the valid sheets, and skip the ones that fail
– Baris Tasdelen
Mar 9 at 3:45