Python Forum
How to make a 3D List of Excel Spreadsheets?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to make a 3D List of Excel Spreadsheets?
#1
Question 
Hi-
I've been failing greatly trying to get different ideas working, or finding how to do this.

Is there a snippet of code I may please use (using something like XLRD, Pandas or OpenPyxl) to allow me to make a 3D-List of multiple Excel spreadsheets in an XLSX file, pls?

i.e.: I'd like to read in multiple Excel tabs/worksheets in an XLSX file, such as:

-----TAB#1-----
data-1a data-1b
data-2a data-2b

-----TAB#2-----
data-3a data-3b
data-4a data-4b

...and turn it into a 3D List like:

[ [ 'TAB#1', ['data-1a', 'data-1b'], ['data-2a', 'data-2b'] ] [ 'TAB#2', ['data-3a', 'data-3b'], ['data-4a', 'data-4b'] ] ]

Thank you so much,
CG
Reply
#2
So you want a list of spreadsheets?
data[sheet][row][column]?

Sheet 0    Sheet 1    Sheet 2
1, 2, 3,   9, 8, 7,   a, b, c,
4, 5, 6,   6, 5, 4,   d, e, f,
7, 8, 9    3, 2, 1    g, h, i
data[0][2][2] == 9
data[1][0][0] == 9
data[2][1][1] == e
Reply
#3
Going by the example you gave, please if possible, for myList to be populated similar to:

myList = [ [ 'Sheet 0', ['1','2','3'], ['4','5','6'], ['7','8','9'] ], [ 'Sheet 1', ['9','8','7'], ['6','5','4'], ['3','2','1'] ], [ 'Sheet 2', ['a','b','c'], ['d','e','f'], ['g','h','i'] ] ]

Thanks much in advance
CG
Reply
#4
(Jan-23-2021, 11:04 PM)deanhystad Wrote: So you want a list of spreadsheets?
data[sheet][row][column]?

Sheet 0    Sheet 1    Sheet 2
1, 2, 3,   9, 8, 7,   a, b, c,
4, 5, 6,   6, 5, 4,   d, e, f,
7, 8, 9    3, 2, 1    g, h, i
data[0][2][2] == 9
data[1][0][0] == 9
data[2][1][1] == e

Hi Dean-

Ohh wow---Although my response in reply #3 above, would be ideal, I re-read your reply and that would also work great! Thank you!!

If it's done via the way you've mentioned:
1) Is there a line(or lines) of python w/ a module that currently populates the "data" list the way you mention?
2) If so, is there a way of deciphering the sheet name of "0", "1", and "2" in the left-most list, in your example above?

Thanks again!
CG
Reply
#5
Your "desired" data structure does not make sense at all. Using heterogeneous lists only complicates access later on. Using a dict of lists of lists may be a bit better but still unnecessary when you can work for example with Workbook/Worksheet objects. What would you do with this data structure? It smells like XY problem to me.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Combining Spreadsheets Py3_14 4 525 Feb-07-2024, 04:58 PM
Last Post: deanhystad
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  trouble reading string/module from excel as a list popular_dog 0 386 Oct-04-2023, 01:07 PM
Last Post: popular_dog
  Why do I have to repeat items in list slices in order to make this work? Pythonica 7 1,259 May-22-2023, 10:39 PM
Last Post: ICanIBB
  help me to make my password list in python >>> Oktay34riza 0 552 Dec-23-2022, 12:38 PM
Last Post: Oktay34riza
  list from excel devilonline 4 1,337 Jun-22-2022, 11:00 PM
Last Post: devilonline
  Compare two Excel sheets with Python and list diffenrences dmkfon 1 14,491 Oct-09-2021, 03:30 PM
Last Post: Larz60+
  Merging spreadsheets with the same columns and extracting rows with matching entries johnbernard 3 8,397 Aug-19-2021, 03:08 PM
Last Post: johnbernard
  Make Groups with the List Elements quest 2 1,936 Jul-11-2021, 09:58 AM
Last Post: perfringo
  Undo interation to make a single list? DustinKlent 2 2,133 Nov-29-2020, 03:41 AM
Last Post: DustinKlent

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020