Module 2 - Day 5

Login to Lab using your credentials. There is a notebook with name 2-5.ipynb already created for you. Open that and use it for today’s training.

Shut down all previous notebooks.

Assignments

%load_problem text-in-a-box

Problem: Text in a Box

Write a program box.py that takes word as a command-line argument and prints the word in a box as shown below.

$ python box.py python
+--------+
| python |
+--------+

Please note that there should be exactly one space on either side of the text in the box.

You can verify your solution using:

%verify_problem text-in-a-box

%%file box.py
# your code here
import sys

def box(text):
    width = len(text) + 2
    edge = "+" + "-"*width + "+"
    print(edge)
    print("|",text,"|")
    print(edge)

box(sys.argv[1])

Overwriting box.py

!python box.py hello

+-------+
| hello |
+-------+

!python box.py "joy of programming"

+--------------------+
| joy of programming |
+--------------------+

%verify_problem text-in-a-box

Found 3 checks
✓ python box.py python
✓ python box.py Joy
✓ python box.py "Joy of Programming"
🎉 Congratulations! You have successfully solved problem text-in-a-box!!

%%file cat.py

import sys
def cat(filename):
    with open(filename) as f:
        print(f.read())


filename = sys.argv[1]
cat(filename)

Overwriting cat.py

%%file testfiles.txt
this is test file
for testing cat program

Writing testfiles.txt

!python cat.py testfiles.txt

this is test file
for testing cat program

%%file box.py
# your code here
import sys

def box(text):
    width = len(text) + 2
    edge = "+" + "-"*width + "+"
    print(edge)
    print("|",text,"|")
    print(edge)

text = input("INput your text")
box(text)

Overwriting box.py

def box(text):
    width = len(text) + 2
    edge = "+" + "-"*width + "+"
    print(edge)
    print("|",text,"|")
    print(edge)

text = input("INput your text") # do not input any where in assignments
box(text)

INput your text test

+------+
| test |
+------+

Writing Excel files using python

We will use third party library xlsxwriter for this

install the library using pip module and restart the kernel

!python3 -m pip install xlsxwriter

Defaulting to user installation because normal site-packages is not writeable
Collecting xlsxwriter
  Downloading XlsxWriter-3.2.0-py3-none-any.whl.metadata (2.6 kB)
Downloading XlsxWriter-3.2.0-py3-none-any.whl (159 kB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 159.9/159.9 kB 2.3 MB/s eta 0:00:00 0:00:01
Installing collected packages: xlsxwriter
Successfully installed xlsxwriter-3.2.0

[notice] A new release of pip is available: 24.0 -> 24.1.2
[notice] To update, run: pip install --upgrade pip

import xlsxwriter

simple workbook

import xlsxwriter
workbook = xlsxwriter.Workbook("sample.xlsx") # case sensitive
sheet = workbook.add_worksheet("sample")
sheet.write("A1", "Hello World!") # cell id is just like in excel
workbook.close()

import xlsxwriter
workbook = xlsxwriter.Workbook("sample1.xlsx") # case sensitive
sheet = workbook.add_worksheet("sample")
sheet.write(0,0, "Hello World!") # with row number and col number.. they start with 0
# this cell is equivalent to A1 cell
workbook.close()

Can we write tabular data?

indexdata = [('IBM', 'Monday', 111.71436961893693),
          ('IBM', 'Tuesday', 141.21220022208635),
          ('IBM', 'Wednesday', 112.40571010053796),
          ('IBM', 'Thursday', 137.54133351926248),
          ('IBM', 'Friday', 140.25154281801224),
          ('MICROSOFT', 'Monday', 235.0403622499107),
          ('MICROSOFT', 'Tuesday', 225.0206535036475),
          ('MICROSOFT', 'Wednesday', 216.10342426936444),
          ('MICROSOFT', 'Thursday', 200.38038844494193),
          ('MICROSOFT', 'Friday', 235.80850482793264),
          ('APPLE', 'Monday', 321.49182055844256),
          ('APPLE', 'Tuesday', 340.63612771662815),
          ('APPLE', 'Wednesday', 303.9065277507285),
          ('APPLE', 'Thursday', 338.1350605764038),
          ('APPLE', 'Friday', 318.3912296144338)]

headers = ["symbol", "day", "price"]

import xlsxwriter 


def write_row(sheet, row, rownumber):    
    for c, item in enumerate(row):
        sheet.write(rownumber, c, item)


def write_tabular_data_to_excel(data, headers, filename):
    workbook = xlsxwriter.Workbook(filename)
    sheet = workbook.add_worksheet()

    # write headers
    write_row(sheet, headers, 0)
    
    for r, row in enumerate(data):# we overwrote the header!
        write_row(sheet, row, r)

    workbook.close()

write_tabular_data_to_excel(indexdata, headers, "indexdata.xlsx")

import xlsxwriter 


def write_row(sheet, row, rownumber):    
    for c, item in enumerate(row):
        sheet.write(rownumber, c, item)


def write_tabular_data_to_excel(data, headers, filename):
    workbook = xlsxwriter.Workbook(filename)
    sheet = workbook.add_worksheet()

    # write headers
    write_row(sheet, headers, 0)
    
    for r, row in enumerate(data, start=1):# we overwrote the header!
        write_row(sheet, row, r)

    workbook.close()

write_tabular_data_to_excel(indexdata, headers, "indexdata.xlsx")

tables = [[i*n for i in range(1, 11)]  for n in range(1,8) ]
tables

[[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
 [2, 4, 6, 8, 10, 12, 14, 16, 18, 20],
 [3, 6, 9, 12, 15, 18, 21, 24, 27, 30],
 [4, 8, 12, 16, 20, 24, 28, 32, 36, 40],
 [5, 10, 15, 20, 25, 30, 35, 40, 45, 50],
 [6, 12, 18, 24, 30, 36, 42, 48, 54, 60],
 [7, 14, 21, 28, 35, 42, 49, 56, 63, 70]]

write_tabular_data_to_excel(tables, [""]*10, "tables.xlsx")

def transpose():
    pass

tables # collection of lists, rows

[[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
 [2, 4, 6, 8, 10, 12, 14, 16, 18, 20],
 [3, 6, 9, 12, 15, 18, 21, 24, 27, 30],
 [4, 8, 12, 16, 20, 24, 28, 32, 36, 40],
 [5, 10, 15, 20, 25, 30, 35, 40, 45, 50],
 [6, 12, 18, 24, 30, 36, 42, 48, 54, 60],
 [7, 14, 21, 28, 35, 42, 49, 56, 63, 70]]

transpose will be collection of list # collection of columns

def transpose(data2d):
    colcount = len(data2d[0])
    return [column(data2d, c) for c in range(colcount)]

tables[0] # 0th row

[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

tables[0][0] # 0th row, 0th col

1

tables[1][0] # 1st row , 0th colu

2

tables[2][0] # 2st row , 0th colu

3

tables[3][0] # 3rd row , 0th column

4

def column(data, colnum):
    rowcount = len(data) # data is collection of rows!
    return [data[r][colnum] for r in range(rowcount)]

column(tables, 0)

[1, 2, 3, 4, 5, 6, 7]

transpose(tables)

[[1, 2, 3, 4, 5, 6, 7],
 [2, 4, 6, 8, 10, 12, 14],
 [3, 6, 9, 12, 15, 18, 21],
 [4, 8, 12, 16, 20, 24, 28],
 [5, 10, 15, 20, 25, 30, 35],
 [6, 12, 18, 24, 30, 36, 42],
 [7, 14, 21, 28, 35, 42, 49],
 [8, 16, 24, 32, 40, 48, 56],
 [9, 18, 27, 36, 45, 54, 63],
 [10, 20, 30, 40, 50, 60, 70]]

tables

[[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
 [2, 4, 6, 8, 10, 12, 14, 16, 18, 20],
 [3, 6, 9, 12, 15, 18, 21, 24, 27, 30],
 [4, 8, 12, 16, 20, 24, 28, 32, 36, 40],
 [5, 10, 15, 20, 25, 30, 35, 40, 45, 50],
 [6, 12, 18, 24, 30, 36, 42, 48, 54, 60],
 [7, 14, 21, 28, 35, 42, 49, 56, 63, 70]]

tttables = transpose(tables)
headers = ["one","two","three","four","five","six","seven"]
write_tabular_data_to_excel(tttables, headers, "multitable.xlsx")

Formatting cells

workbook = xlsxwriter.Workbook("bold.xlsx")
sheet = workbook.add_worksheet()
bold = workbook.add_format({"bold":True}) # this bold variable stores the format
                                          # can be used any time in any sheet any cell for 
                                          # this workbook only

sheet.write("A1", "Hello", bold)
sheet.write("B1", "World!", bold)
sheet.write(1, 0, "Some text") # first you give cell id related data, celid or rownum and colnum.
workbook.close()

import xlsxwriter 


def write_row(sheet, row, rownumber, format_=None):    
    for c, item in enumerate(row):
        if format_:
            sheet.write(rownumber, c, item, format_)
        else:
            sheet.write(rownumber, c, item)


def write_tabular_data_to_excel(data, headers, filename):
    workbook = xlsxwriter.Workbook(filename)
    sheet = workbook.add_worksheet()
    bold = workbook.add_format({"bold":True})
    
    # write headers
    write_row(sheet, headers, 0, bold)
    
    for r, row in enumerate(data, start=1):# we overwrote the header!
        write_row(sheet, row, r)

    workbook.close()

write_tabular_data_to_excel(indexdata, ["Symbol", "Day", "Price"], "indexbold.xlsx")

workbook = xlsxwriter.Workbook("numformat.xlsx")
sheet = workbook.add_worksheet()
numformat = workbook.add_format({"num_format":"$#,##0"}) 

sheet.write("B1", 1000, numformat)
sheet.write(1, 0, "Some text") # first you give cell id related data, celid or rownum and colnum.
workbook.close()

Formula

workbook = xlsxwriter.Workbook("sum.xlsx")
s = workbook.add_worksheet()

for i in range(10):
    s.write(i, 0, i)

s.write(10, 0, "=SUM(A1:A10)")

workbook.close()

Library homepage

For more help you see the library documentation