Python to save needed rows in Excel contents

Question

I am using Windows 7 + Python 2.76.

I am trying to save specific contents of xls files into new files.

The original contents looks like:

What I want to do is to save all the rows with “UK” (2nd column) in new files.

What I am doing is below:

old_file = open_workbook('C:\\1.xls',formatting_info=True)
old_sheet = old_file.sheet_by_index(0)

new_file = xlwt.Workbook(encoding='utf-8', style_compression = 0)
new_sheet = new_file.add_sheet('Sheet1', cell_overwrite_ok = True)

contents = []

for row in range(old_sheet.nrows):
    a = old_sheet.cell(row,0).value
    b = old_sheet.cell(row,1).value
    c = old_sheet.cell(row,2).value

    if "UK" in b:
        contents.append(a)
        contents.append(b)
        contents.append(c)

for c, content in enumerate(contents):
    new_sheet.write(0, c, content)


new_file.save('C:\\file_1.xls')

However it only puts all the results in 1 row. I think it’s because I put all the contents into 1 list and write them into 1 row.

But what’s the right way to put them? (as the number of needed rows is uncertain).

Answer 1

With pandas:

import pandas as pd
orig_df = pd.read_excel(orig_excel_path, sheetname=sheetname)
orig_df[orig_df['Visited'] == 'UK'].to_excel(new_excel_path, sheet_name=new_sheetname)

Breaking it down:

orig_df['Visited'] == 'UK' returns a list of True or False for each row if the Visited column is 'UK'. In this case [False, True, False, True]. Passing this list back to the original dataframe will give us only the rows in the indexes corresponding to those with True.

Answer 2

to append 1 row with 3 columns, use a "2D array" data structure, e.g. a list of lists:

contents.append([a, b, c])