Posts: 12
Threads: 6
Joined: Jul 2020
Jan-23-2021, 10:50 PM
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
Posts: 6,347
Threads: 17
Joined: Feb 2020
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
Posts: 12
Threads: 6
Joined: Jul 2020
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
Posts: 12
Threads: 6
Joined: Jul 2020
Jan-24-2021, 04:44 AM
(This post was last modified: Jan-24-2021, 04:44 AM by chatguy.)
(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
Posts: 8,094
Threads: 154
Joined: Sep 2016
Jan-24-2021, 05:24 AM
(This post was last modified: Jan-24-2021, 05:24 AM by buran.)
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.
|