Filtering QTableview with QSortFilterProxyModel very slow

Johnson9070

Hi All,

I want to do some filtering on a pandas dataframe displayd in a QTableview. There about 4500 rows and 15 columns.
When i type the results are filtered immediately. Pretty fast for 200 rows but very slow for large datasets.
Any idea how to improve the results ?

Here is the code:

class PandasTableModel(QAbstractTableModel):
    def __init__(self, data, parent=None):
        QAbstractItemModel.__init__(self, parent)
        self._data = data
        self.colors = dict()

    def rowCount(self, parent=None):
        return self._data.index.size
    def columnCount(self, parent=None):
        return self._data.columns.size

    def setData(self, index, value, role):

        if role == Qt.EditRole:
            # Set the value into the frame.
            self._data.iloc[index.row(), index.column()] = value
            return True

    def data(self, index, role=Qt.DisplayRole):
        if index.isValid():
            if role == Qt.DisplayRole:
                return str(self._data.iloc[index.row(), index.column()])
            if role == Qt.EditRole:
                return str(self._data.iloc[index.row(), index.column()])
            if role == Qt.BackgroundRole:
                color = self.colors.get((index.row(), index.column()))
                if color is not None:
                    return color
        return None

    def headerData(self, rowcol, orientation, role):
        if orientation == Qt.Horizontal and role == Qt.DisplayRole:
            return self._data.columns[rowcol]
        if orientation == Qt.Vertical and role == Qt.DisplayRole:
            return self._data.index[rowcol]
        return None

    def change_color(self, row, column, color):
        ix = self.index(row, column)
        self.colors[(row, column)] = color
        self.dataChanged.emit(ix, ix, (Qt.BackgroundRole,))

class TableViewer(QtWidgets.QMainWindow):

    def __init__(self):
        super(TableViewer, self).__init__()

        self.ui = loadUi("QTableViewForm.ui", self)
        self.ui.cmdRun1.clicked.connect(self.RunFunction1)
        self.ui.cmdRun2.clicked.connect(self.RunFunction2)
        self.ui.inputFilter.textChanged.connect(self.SetFilteredView)
        self.showdata()

    def showdata(self):
        start = timeit.default_timer()
        print("Start LoadData")

        data = pd.read_pickle("productdata.pkl")

        self.model = PandasTableModel(data)
        self.ui.tableData.setModel(self.model)
        self.proxy_model = QSortFilterProxyModel()
        self.proxy_model.setFilterKeyColumn(-1)  # Search all columns.
        self.proxy_model.setSourceModel(self.model)
        self.proxy_model.sort(0, Qt.AscendingOrder)
        self.proxy_model.setFilterCaseSensitivity(False)
        self.ui.tableData.setModel(self.proxy_model)

        print("Stop LoadData")
        end = timeit.default_timer()
        print("Process Time: ", (end - start))

    def SetFilteredView(self):

        print("Start set_filter")
        filter_text = self.ui.inputFilter.text()
        self.proxy_model.setFilterFixedString(filter_text)
        filter_result = self.proxy_model.rowCount()
        self.ui.lblResult.setText("(" + str(filter_result) + " records)")
        print("Stop setFilteredView")
if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    win = TableViewer()
    win.show()
    sys.exit(app.exec_())

SGaist

Hi,

Do you really need to filter on all columns ?

That said, since you have a pandas data frame, did you consider using its filtering capabilities ? It might better suit your needs.

Johnson9070

Hi SGaist , thx for your reply.

The filter on all columns is exactly wat i need ;-)
But not at this speed.

The code below works great and filters really fast (>4500rows) but when i display the data using the "data" function of the model it becomes slow again. And i need the "data" function to do some formatting on the data in the table.
I need to be able to filter semi-large tables (10000rows) at real time using the direct filter function.
Here is the fast code:

class PandasTableModel(QtGui.QStandardItemModel):

    def __init__(self, data, parent=None):

        QtGui.QStandardItemModel.__init__(self, parent)
        self._data = data

        for col in data.columns:
            data_col = [QtGui.QStandardItem("{}".format(x)) for x in data[col].values]
            self.appendColumn(data_col)

        return

    def rowCount(self, parent=None):
        return len(self._data.values)

    def columnCount(self, parent=None):
        return self._data.columns.size

    def headerData(self, x, orientation, role):

        if orientation == Qt.Horizontal and role == Qt.DisplayRole:
            return self._data.columns[x]
        if orientation == Qt.Vertical and role == Qt.DisplayRole:
            return self._data.index[x]

    def flags(self, index):
        if not index.isValid():
            return Qt.ItemIsEnabled

        return super().flags(index) | Qt.ItemIsEditable  # add editable flag.

    def setData(self, index, value, role):

        if role == Qt.EditRole:
            # Set the value into the frame.
            self._data.iloc[index.row(), index.column()] = value
            return True

        return False

class TableViewer(QtWidgets.QMainWindow):

    def __init__(self):
        super(TableViewer, self).__init__()

        self.ui = loadUi("QTableViewForm.ui", self)
        self.ui.cmdRun1.clicked.connect(self.RunFunction1)
        self.ui.cmdRun2.clicked.connect(self.RunFunction2)
        self.ui.inputFilter.textChanged.connect(self.SetFilteredView)

        self.showdata()

    def showdata(self):
        start = timeit.default_timer()
        print("Start LoadData")

        data = pd.read_pickle("productdata.pkl")

        self.model = PandasTableModel(data)
        self.ui.tableData.setModel(self.model)
        self.proxy_model = QSortFilterProxyModel()
        self.proxy_model.setFilterKeyColumn(-1)  # Search all columns.
        self.proxy_model.setSourceModel(self.model)
        self.proxy_model.sort(0, Qt.AscendingOrder)
        self.proxy_model.setFilterCaseSensitivity(False)
        self.ui.tableData.setModel(self.proxy_model)

        print("Stop LoadData")
        end = timeit.default_timer()
        print("Process Time: ", (end - start))

    def SetFilteredView(self):

        # print("Start set_filter")
        filter_text = self.ui.inputFilter.text()
        self.proxy_model.setFilterFixedString(filter_text)
        filter_result = self.proxy_model.rowCount()
        self.ui.lblResult.setText("(" + str(filter_result) + " records)")


if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    win = TableViewer()
    win.show()
    sys.exit(app.exec_())

SGaist

Do you need to do a lot of data type conversions ?

Johnson9070

@SGaist The idea is that i reed in a data file (csv , xls , ...) containing : url , string , date , email , phone numbers , .... so a bit of a mix. For the moment i have data showing 10.0 instead of 10 and 324758855252.0 instead of 0032.... or +32.....
Also i would like to add some icons and so. I use face reco in this application so for example a user with a trained profile would have the faceId icon next to his name for example.

Johnson9070

SGaist : Can i control the decorationrole through a function after a button click ?

SGaist

You can update the model as you wish, as long as you call the corresponding changed signal it will trigger the UI update.