I want to visualize and filter data from a pandas dataframe in PyQt. I already managed to visualize the data and to open up a menu by clicking on the header. The idea is, that you can choose the elements in the menu which you want to keep in the table and filter the data that way (like in Excel).
Excel example of filtering
As in Excel I want to limit the height of the menu and make it scrollable instead. Although I set the style to QMenu { menu-scrollable: 1; } it won't allow for any scrolling if their is not enough unique values in the column.
Menu not showing any scrolling options
If there is a certain number of unique values (more than the physical screen size would allow) it allows for scrolling. In this case it shows everything correct. But if I try to scroll the menu, the menu instead moves up until it reaches the top of the screen and only then starts scrolling.
Scrollable Menu with the right height and position
After scrolling the menu moves up until it reaches the top
In the pictures above you can see, that it shows the scrollable menu, but if I am trying to scroll, the menu just moves up. Only if the menu reaches the top, it allows me to scroll through the values.
class CustomProxyModel(QtCore.QSortFilterProxyModel):
def __init__(self, parent=None):
super().__init__(parent)
self._filters = dict()
@property
def filters(self):
return self._filters
def setFilter(self, expresion, column):
if expresion:
self.filters[column] = expresion
elif column in self.filters:
del self.filters[column]
self.invalidateFilter()
def filterAcceptsRow(self, source_row, source_parent):
for column, expresion in self.filters.items():
text = self.sourceModel().index(source_row, column, source_parent).data()
regex = QtCore.QRegExp(
expresion, QtCore.Qt.CaseInsensitive, QtCore.QRegExp.RegExp
)
if regex.indexIn(text) == -1:
return False
return True
class PandasModel(QtCore.QAbstractTableModel):
def __init__(self, df=pandas.DataFrame(), parent=None):
QtCore.QAbstractTableModel.__init__(self, parent=parent)
self._df = df.copy()
def toDataFrame(self):
return self._df.copy()
def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
if role != QtCore.Qt.DisplayRole:
return QtCore.QVariant()
if orientation == QtCore.Qt.Horizontal:
try:
return self._df.columns.tolist()[section]
except (IndexError, ):
return QtCore.QVariant()
elif orientation == QtCore.Qt.Vertical:
try:
return self._df.index.tolist()[section]
except (IndexError, ):
return QtCore.QVariant()
def data(self, index, role=QtCore.Qt.DisplayRole):
if role != QtCore.Qt.DisplayRole:
return QtCore.QVariant()
if not index.isValid():
return QtCore.QVariant()
return QtCore.QVariant(str(self._df.iloc[index.row(), index.column()]))
def setData(self, index, value, role):
row = self._df.index[index.row()]
col = self._df.columns[index.column()]
if hasattr(value, 'toPyObject'):
# PyQt4 gets a QVariant
value = value.toPyObject()
else:
# PySide gets an unicode
dtype = self._df[col].dtype
if dtype != object:
value = None if value == '' else dtype.type(value)
self._df.set_value(row, col, value)
return True
def rowCount(self, parent=QtCore.QModelIndex()):
return len(self._df.index)
def columnCount(self, parent=QtCore.QModelIndex()):
return len(self._df.columns)
def sort(self, column, order):
colname = self._df.columns.tolist()[column]
self.layoutAboutToBeChanged.emit()
self._df.sort_values(colname, ascending=order == QtCore.Qt.AscendingOrder, inplace=True)
self._df.reset_index(inplace=True, drop=True)
self.layoutChanged.emit()
class Ui(QtWidgets.QMainWindow):
def __init__(self):
super(Ui, self).__init__() # Call the inherited classes __init__ method
uic.loadUi('../test.ui', self) # Load the .ui file
button = self.findChild(QtWidgets.QPushButton, 'button_load')
button.clicked.connect(self.read)
self.view = self.findChild(QtWidgets.QTableView, 'tableView')
self.view.setSelectionMode(QtWidgets.QAbstractItemView.SingleSelection)
self.horizontalHeader = self.view.horizontalHeader()
self.horizontalHeader.sectionClicked.connect(self.on_view_horizontalHeader_sectionClicked)
self.show() # Show the GUI
def read(self):
proxy = CustomProxyModel(self)
self.model = PandasModel(pandas.read_csv('C:/Users/Sergey/Downloads/10000 Sales Records.csv'))
proxy.setSourceModel(self.model)
self.view.setModel(proxy)
self.view.resizeColumnsToContents()
def on_view_horizontalHeader_sectionClicked(self, index):
menu = QtWidgets.QMenu(self.view)
signalMapper = QtCore.QSignalMapper(self)
valuesUnique = self.model._df.iloc[:, index].unique().astype(str)
actionAll = QtWidgets.QAction("All", self)
# actionAll.triggered.connect(self.on_actionAll_triggered)
menu.addAction(actionAll)
menu.addSeparator()
for actionNumber, actionName in enumerate(sorted(list(valuesUnique))):
action = QtWidgets.QAction(actionName, self)
action.setCheckable(True)
action.setChecked(True)
signalMapper.setMapping(action, actionNumber)
# action.triggered.connect(signalMapper.map)
menu.addAction(action)
# self.signalMapper.mapped.connect(self.on_signalMapper_mapped)
headerPos = self.view.mapToGlobal(self.horizontalHeader.pos())
posX = headerPos.x() + self.horizontalHeader.sectionPosition(index)
posY = headerPos.y() + self.horizontalHeader.height()
menu.setStyleSheet("QMenu { menu-scrollable: 1; }")
menu.setMaximumHeight(155)
menu.popup(QtCore.QPoint(posX, posY))
menu.move(posX, posY)
app = QtWidgets.QApplication(sys.argv) # Create an instance of QtWidgets.QApplication
window = Ui() # Create an instance of our class
app.exec_() # Start the application
test.ui
<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
<class>MainWindow</class>
<widget class="QMainWindow" name="MainWindow">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>800</width>
<height>600</height>
</rect>
</property>
<property name="windowTitle">
<string>MainWindow</string>
</property>
<widget class="QWidget" name="centralwidget">
<widget class="QPushButton" name="button_load">
<property name="geometry">
<rect>
<x>350</x>
<y>440</y>
<width>75</width>
<height>23</height>
</rect>
</property>
<property name="text">
<string>Load</string>
</property>
</widget>
<widget class="QTableView" name="tableView">
<property name="geometry">
<rect>
<x>80</x>
<y>30</y>
<width>621</width>
<height>351</height>
</rect>
</property>
</widget>
</widget>
<widget class="QMenuBar" name="menubar">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>800</width>
<height>21</height>
</rect>
</property>
</widget>
<widget class="QStatusBar" name="statusbar"/>
</widget>
<resources/>
<connections/>
</ui>
Example data set: http://eforexcel.com/wp/wp-content/uploads/2017/07/10000-Sales-Records.zip
