how to print two columns of xls files on screen?

Question

I asked a question here how can I read each line of a xls file with pausing and the answer is right as below

This solution goes row wise . for example if I have 3 values at first row. It prints first cell then second cell then third cell etc until the first row finish and then goes to second row.

What I want is to print all cells with a distance at the same time for example print the first row then print the second row etc

As an example, if I have in my xls file two rows as follows

row 1 : 1 2 3 (there are three values in three cell) row 2: 5 6 7

I want it prints it like

value :::  1 ------> 2------> 3 
value ::: 5-------> 6------> 7





import time
import pandas as pd
import os
import xlrd
# at first I try to know how many rows and how many columns I have 
workbook = xlrd.open_workbook('myfile.xls')
for sheet in workbook.sheets():
    for row in range(sheet.nrows):
        for column in range(sheet.ncols):
            os.system('clear')
            print "value::: ", sheet.cell(row,column).value
            time.sleep(5.5)    # pause 5.5 seconds

this shows only one column of xls but if I have two or three columns it ignore the second and the third etc and only print the first one

how can I amend this to be able to print as many cell as I want in each epoch ?

Answer 1

This is relatively easy to do with pandas.

import time
import pandas as pd
import os

frame = pd.read_excel('myfile.xls')

This will give you a pandas DataFrame with only the first sheet of that excel file.

pandas dataframes have an inbuilt method to iterate over all rows:

for row in frame.iterrows():
    print row
    time.sleep(5.5)
    os.system('clear')

Please also refer to the documentation of pandas.read_excel (this is for pandas version 0.19.1).

Update:

Well, not properly testing this has come around to bite me in the bottom. It would appear that iterating over single rows of the DataFrame has unexpected side effects on formatting. To make sure this does not happen, the following code transforms the frame into a string via the to_string method that the DataFrame class features. We do not want to get the row index printed out as well so we set the parameter index to false.

To be able to iterate over this data row-wise, we need to split our string at each newline, this is what the splitlines function does. It is an inbuilt function of python strings. It turns our single string containing the entire data frame into a list of strings, each containing only a single line.

pandas separates columns by two spaces, so finally we replace each occurrence of double spaces with the wanted delimiter via the builtin replace function.

import time
import pandas as pd
import os

frame = pd.read_excel('data.xls')

for row in frame.to_string(index=False).splitlines():
    os.system('clear')
    print 'value ::: ' + row.replace('  ', '------>')
    time.sleep(5.5)

os.system('clear')

For reference, also see the python manual on built in types, it lists the methods available for strings. Naturally, these are extremely helpful when trying to manipulate strings.

Update 2:

This is starting to get off topic, but here is a colored version as per your comment.

import time
import pandas as pd
import os

frame = pd.read_excel('data.xls')

blue = '\033[94m'
green = '\033[92m'
yellow = '\033[93m'
plain = '\033[0m'    
colormap = [blue, green, yellow]    
delimiter = '------>'

for row in frame.to_string(index=False).splitlines():
    os.system('clear')
    line = 'value ::: '
    for idx, column in enumerate(row.split('  ')):
        if idx > 0:
            line += delimiter
        line += colormap[idx % 3] + column + plain

    print line
    time.sleep(5.5)

os.system('clear')