PyQt: How to sort QTableView columns of a excel file(strings and numericals and datetype)

Question

This is a follow up question to: PyQt: How to sort QTableView columns(strings and numericals)

Now I am planning to do the same sorting for the excel files

Here is my Code:

self.Upload = QtGui.QPushButton()
self.Upload.clicked.connect(self.showOpenDialog)
self.Table = QtGui.QPushButton()
self.table.clicked.connect(self.LoadTable)


def showOpenDialog(self):
    fileName = QtGui.QFileDialog.getOpenFileName(self, 'Open file', '/home')
    if (".xls" or ".xml" or ".xlsx" or ".xlsm") in fileName:
        with open(fileName, 'rb') as drate:
            self.Datas = pd.read_excel(drate, index_col=0)
        self.Loaded_File.clear()
        self.Loaded_File.append(fileName)
        colll = self.Datas.dtypes.index
        col_names = np.array(colll)


def LoadTable(self):
    tab_table_view = QtGui.QWidget()
    self.Tab.insertTab(0, tab_table_view, self.File_Name)
    self.tableView = QtGui.QTableView(tab_table_view)
    self.tableView.setGeometry(QtCore.QRect(0, 0, 721, 571))
    self.model = QtGui.QStandardItemModel(self)
    file_name_temp = self.File_Name
    self.tableView.setModel(self.model)
    colll = self.Datas.dtypes.index
    col_names = np.array(colll)
    col_names = np.insert(col_names, 0, self.Datas.index.name)
    self.model.setHorizontalHeaderLabels(col_names)
    self.tableView.hideRow(0)
    self.tableView.setSortingEnabled(True)

            if (".xls" or ".xml" or ".xlsx" or ".xlsm") in self.File_Name:
        from itertools import chain
        ii = 1
        book = xlrd.open_workbook(self.File_Name)
        sheet = book.sheet_by_index(0)
        num_col = len(self.Datas.columns)
        num_row = len(self.Datas)
        print(num_col,num_row)
        self.Matrix = [[0 for x in range(num_col+1)] for y in range(num_row)]
        for i in range(num_row):
            self.Matrix[i][0]=self.Datas.index.values[i]
            for j in range(num_col):
                self.Matrix[i][j+1] = self.Datas.iat[i,j]
        print(self.Matrix)
        for i in range(num_row):
            items = []
            for j in range(num_col):
                field = self.Matrix[i][j]
                item = QtGui.QStandardItem(field)
                for numtype in (int, float):
                    try:
                        data = numtype(field)
                        break
                    except (ValueError, OverflowError):
                        pass
                else:
                    print('Not a number: %r' % field)
                    data = field
                item.setData(data, QtCore.Qt.UserRole)
            items.append(item)
            self.model.appendRow(items)

The output will be:

Not a number: 'Government'
Not a number: 'Canada'
Not a number: 'Carretera'
Not a number: 'None'
Traceback (most recent call last):
  File "Main_File.py", line 1535, in loadTable
    item = QtGui.QStandardItem(field)
TypeError: arguments did not match any overloaded call:
  QStandardItem(): too many arguments
  QStandardItem(str): argument 1 has unexpected type 'Timestamp'
  QStandardItem(QIcon, str): argument 1 has unexpected type 'Timestamp'
  QStandardItem(int, int columns=1): argument 1 has unexpected type 'Timestamp'
  QStandardItem(QStandardItem): argument 1 has unexpected type 'Timestamp'

It is fine if the datatype is str in the file(upto "None") and when the first int or float comes it generates some overloaded call.

Please someone help me.

Answer 1

Below is a demo script which reads both csv and excel files, and should sort dates, ints, floats, and strings correctly.

import sys
import pandas as pd, numpy as np
from PyQt4 import QtCore, QtGui

class Window(QtGui.QWidget):
    def __init__(self):
        super(Window, self).__init__()
        self.model = QtGui.QStandardItemModel(self)
        self.model.setSortRole(QtCore.Qt.UserRole)
        self.tableView = QtGui.QTableView()
        self.tableView.setSortingEnabled(True)
        self.tableView.setModel(self.model)
        self.button = QtGui.QPushButton('Open CSV', self)
        self.button.clicked.connect(self.handleButton)
        layout = QtGui.QVBoxLayout(self)
        layout.addWidget(self.tableView)
        layout.addWidget(self.button)

    def handleButton(self):
        filters = (
            'CSV files (*.csv *.txt)',
            'Excel Files (*.xls *.xml *.xlsx *.xlsm)',
            )
        path, filter = QtGui.QFileDialog.getOpenFileNameAndFilter(
            self, 'Open File', '', ';;'.join(filters))
        if path:
            csv = filter.startswith('CSV')
            if csv:
                dataframe = pd.read_csv(path)
            else:
                dataframe = pd.read_excel(path)
            self.model.setRowCount(0)
            dateformat = '%m/%d/%Y'
            rows, columns = dataframe.shape
            for row in range(rows):
                items = []
                for column in range(columns):
                    field = dataframe.iat[row, column]
                    if csv and isinstance(field, str):
                        try:
                            field = pd.to_datetime(field, format=dateformat)
                        except ValueError:
                            pass
                    if isinstance(field, pd.tslib.Timestamp):
                        text = field.strftime(dateformat)
                        data = field.timestamp()
                    else:
                        text = str(field)
                        if isinstance(field, np.number):
                            data = field.item()
                        else:
                            data = text
                    item = QtGui.QStandardItem(text)
                    item.setData(data, QtCore.Qt.UserRole)
                    items.append(item)
                self.model.appendRow(items)

if __name__ == '__main__':

    app = QtGui.QApplication(sys.argv)
    window = Window()
    window.setGeometry(500, 150, 600, 400)
    window.show()
    sys.exit(app.exec_())

Answer 2

As Ekhumoro suggests, sorting of Excel data is easiest done with Pandas. I have tried similar operations with openpyxl, but it's too limited. Pandas can save you many lines of code.

A small example:

from PyQt4 import QtGui , QtCore
import pandas as pd
import numpy as np
import time
import sys

# open excel file
sales = pd.read_excel ("Sales.xlsx" , parse_dates=['Data'] )
# select columns
purchase_patterns = sales [['Total','Date']]
# choose date index
purchase_patterns = purchase_patterns.set_index("Date")
# regroup / resample by week and get subtotals by sum 
resample = purchase_patterns.resample ('W' , how = sum)

Here are some nice links:

• http://pbpython.com/excel-pandas-comp.html
• http://chrisalbon.com/python/pandas_indexing_selecting.html
• http://chrisalbon.com/python/pandas_dropping_column_and_rows.html
• http://chris.friedline.net/2015-12-15-rutgers/lessons/python2/02-index-slice-subset.html