Python read xlsx as Csv

Question

I'm using xlrd to read a xlsx file as a csv. For this purpose I'm using the following code:

workbook = xlrd.open_workbook("170519_taxonomy_in_qiime.xlsx")
sheet = workbook.sheet_by_index(0)
source_data = [sheet.row_values(rowx) for rowx in range(sheet.nrows)]

Which gives me this example result

[[225145.0, 'k__Bacteria', ' p__ZB3', ' c__Rs-J96', ' o__', ' f__', ' g__', ' s__'], [2916972.0, 'k__Bacteria', ' p__ZB3', ' c__Rs-J96', 'o__', ' f__', ' g__', ' s__']]

But I need my result to look like this:

[['225145.0, k__Bacteria,  p__ZB3,  c__Rs-J96,  o__,  f__,  g__,  s__'], ['2916972.0, k__Bacteria,  p__ZB3,  c__Rs-J96,  o__,  f__,  g__,  s__']]

Any Idea how can do this?

How is the question related to the title? Just join the inner list to obtain the result... — Serge Ballesta
– Serge Ballesta, Commented Mar 31, 2020 at 8:21
If you're trying to convert your xlsx into a csv this might answer your question: stackoverflow.com/questions/22688477/… — yvesonline
– yvesonline, Commented Mar 31, 2020 at 8:28

Dakshin · Accepted Answer · 2020-03-31 08:28:56Z

2

Each item in source_data is a list of values. You are trying to produce a single string containing each value in the list.

You can use the str.join function for this. However, note that the first element in the list is a float value and not a string, so you first need to convert that to a string before using the join function.

For example:

source_data = [' '.join(map(str,sheet.row_values(rowx))) for rowx in range(sheet.nrows)]

answered Mar 31, 2020 at 8:28

Dakshin

4691 gold badge7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OO7 · Accepted Answer · 2020-03-31 09:02:41Z

1

x = [[225145.0, 'k__Bacteria', ' p__ZB3', ' c__Rs-J96', ' o__', ' f__', ' g__', ' s__'], [2916972.0, 'k__Bacteria', ' p__ZB3', ' c__Rs-J96', 'o__', ' f__', ' g__', ' s__']]

y = []
for c in x:
    b = ""
    for a in c:
        b =b + "".join(str(a))
    y.append(b)
y

Output

['225145.0k__Bacteria p__ZB3 c__Rs-J96 o__ f__ g__ s__',
 '2916972.0k__Bacteria p__ZB3 c__Rs-J96o__ f__ g__ s__']

answered Mar 31, 2020 at 9:02

OO7

4583 silver badges14 bronze badges

Collectives™ on Stack Overflow

Python read xlsx as Csv

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related