python - 在 PyQt5 的 TableView 中显示 pandas DataFrame，其中列设置为索引
问题描述
我在 Qt 设计器中设计了一个 UI，它由 1 个按钮和 1 个组成QTableView。该按钮用于浏览 excel，根据我的代码对其进行操作，并将结果显示在TableView. 这是我的代码：
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtGui import QIcon
import numpy as np
import pandas as pd
import glob
import os
import csv
from itertools import combinations
from PyQt5.QtWidgets import QDialog, QApplication
class DataFrameModel(QtCore.QAbstractTableModel):
DtypeRole = QtCore.Qt.UserRole + 1000
ValueRole = QtCore.Qt.UserRole + 1001
def __init__(self, df=pd.DataFrame(), parent=None):
super(DataFrameModel, self).__init__(parent)
self._dataframe = df
def setDataFrame(self, dataframe):
self.beginResetModel()
self._dataframe = dataframe.copy()
self.endResetModel()
def dataFrame(self):
return self._dataframe
dataFrame = QtCore.pyqtProperty(pd.DataFrame, fget=dataFrame, fset=setDataFrame)
@QtCore.pyqtSlot(int, QtCore.Qt.Orientation, result=str)
def headerData(self, section: int, orientation: QtCore.Qt.Orientation, role: int = QtCore.Qt.DisplayRole):
if role == QtCore.Qt.DisplayRole:
if orientation == QtCore.Qt.Horizontal:
return self._dataframe.columns[section]
else:
return str(self._dataframe.index[section])
return QtCore.QVariant()
def rowCount(self, parent=QtCore.QModelIndex()):
if parent.isValid():
return 0
return len(self._dataframe.index)
def columnCount(self, parent=QtCore.QModelIndex()):
if parent.isValid():
return 0
return self._dataframe.columns.size
def data(self, index, role=QtCore.Qt.DisplayRole):
if not index.isValid() or not (0 <= index.row() < self.rowCount() \
and 0 <= index.column() < self.columnCount()):
return QtCore.QVariant()
row = self._dataframe.index[index.row()]
col = self._dataframe.columns[index.column()]
dt = self._dataframe[col].dtype
val = self._dataframe.iloc[row][col]
if role == QtCore.Qt.DisplayRole:
return str(val)
elif role == DataFrameModel.ValueRole:
return val
if role == DataFrameModel.DtypeRole:
return dt
return QtCore.QVariant()
def roleNames(self):
roles = {
QtCore.Qt.DisplayRole: b'display',
DataFrameModel.DtypeRole: b'dtype',
DataFrameModel.ValueRole: b'value'
}
return roles
class Ui_Rulepriority(object):
def setupUi(self, Rulepriority):
Rulepriority.setObjectName("Rulepriority")
Rulepriority.resize(820, 480)
self.verticalLayout = QtWidgets.QVBoxLayout(Rulepriority)
self.verticalLayout.setObjectName("verticalLayout")
self.OpenCsv = QtWidgets.QPushButton(Rulepriority)
self.OpenCsv.setObjectName("OpenCsv")
self.verticalLayout.addWidget(self.OpenCsv)
self.OpenCsv.clicked.connect(self.file_open)
self.tableView = QtWidgets.QTableView(Rulepriority)
self.tableView.setSizeAdjustPolicy(QtWidgets.QAbstractScrollArea.AdjustToContents)
self.tableView.setObjectName("tableView")
self.verticalLayout.addWidget(self.tableView)
self.retranslateUi(Rulepriority)
self.OpenCsv.clicked.connect(self.tableView.show)
QtCore.QMetaObject.connectSlotsByName(Rulepriority)
def retranslateUi(self, Rulepriority):
_translate = QtCore.QCoreApplication.translate
Rulepriority.setWindowTitle(_translate("Rulepriority", "Violation Solving Prioritization tool"))
self.OpenCsv.setText(_translate("Rulepriority", "Browse excel and get solving probability"))
Rulepriority.setWindowIcon(QtGui.QIcon('favicon.ico'))
def file_open(self):
path, _ = QtWidgets.QFileDialog.getOpenFileName(Rulepriority, 'Open csv', QtCore.QDir.rootPath(),
'Data_*.xlsx')
df1 = pd.read_excel(path)
df2 = df1.set_index(['Priority', 'RID to solve', 'Probability', 'RID per probability', 'Remarks']).sort_values(by=['Priority'],
ascending=True)
model = DataFrameModel(df2)
self.tableView.setModel(model)
if __name__ == "__main__":
import sys
app = QtWidgets.QApplication(sys.argv)
Rulepriority = QtWidgets.QDialog()
ui = Ui_Rulepriority()
ui.setupUi(Rulepriority)
Rulepriority.show()
sys.exit(app.exec_())
这里def file_open(self)是用来浏览excel表格的。为了在 中显示 pandas DataFrame QTableView，我使用的是class DataFrameModel(QtCore.QAbstractTableModel)从这个答案中借来的How to display a Pandas data frame with PyQt5/PySide2
这是我浏览的数据的样子：
我希望上面的数据看起来像这样：
为此，我正在使用以下代码：
df2 = df1.set_index(['Priority', 'RID to solve', 'Probability', 'RID per probability', 'Remarks']).sort_values(by=['Priority'],
ascending=True)
但是当我尝试在前端显示相同的结果时，我得到逗号分隔的结果，如下所示：
如果你们能帮我得到上面显示的格式，我将不胜感激。
解决方案
解决了这个问题。我发现异常索引导致了问题，所以，
代替：
df2 = df1.set_index(['Priority', 'RID to solve', 'Probability', 'RID per probability', 'Remarks']).sort_values(by=['Priority'],
ascending=True)
我用这个：
cols = ['Priority','RID to solve']
dm = df1[cols].apply(lambda x: x.duplicated())
df1[cols]= df1[cols].mask(m, '')
这段代码所做的是，首先用缺失值替换重复值，然后在数据框列上屏蔽它们以获得如下结果：
这是具有上述审核的完整代码：
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtGui import QIcon
import numpy as np
import pandas as pd
import glob
import os
import csv
from itertools import combinations
from PyQt5.QtWidgets import QDialog, QApplication
class DataFrameModel(QtCore.QAbstractTableModel):
DtypeRole = QtCore.Qt.UserRole + 1000
ValueRole = QtCore.Qt.UserRole + 1001
def __init__(self, df=pd.DataFrame(), parent=None):
super(DataFrameModel, self).__init__(parent)
self._dataframe = df
def setDataFrame(self, dataframe):
self.beginResetModel()
self._dataframe = dataframe.copy()
self.endResetModel()
def dataFrame(self):
return self._dataframe
dataFrame = QtCore.pyqtProperty(pd.DataFrame, fget=dataFrame, fset=setDataFrame)
@QtCore.pyqtSlot(int, QtCore.Qt.Orientation, result=str)
def headerData(self, section: int, orientation: QtCore.Qt.Orientation, role: int = QtCore.Qt.DisplayRole):
if role == QtCore.Qt.DisplayRole:
if orientation == QtCore.Qt.Horizontal:
return self._dataframe.columns[section]
else:
return str(self._dataframe.index[section])
return QtCore.QVariant()
def rowCount(self, parent=QtCore.QModelIndex()):
if parent.isValid():
return 0
return len(self._dataframe.index)
def columnCount(self, parent=QtCore.QModelIndex()):
if parent.isValid():
return 0
return self._dataframe.columns.size
def data(self, index, role=QtCore.Qt.DisplayRole):
if not index.isValid() or not (0 <= index.row() < self.rowCount() \
and 0 <= index.column() < self.columnCount()):
return QtCore.QVariant()
row = self._dataframe.index[index.row()]
col = self._dataframe.columns[index.column()]
dt = self._dataframe[col].dtype
val = self._dataframe.iloc[row][col]
if role == QtCore.Qt.DisplayRole:
return str(val)
elif role == DataFrameModel.ValueRole:
return val
if role == DataFrameModel.DtypeRole:
return dt
return QtCore.QVariant()
def roleNames(self):
roles = {
QtCore.Qt.DisplayRole: b'display',
DataFrameModel.DtypeRole: b'dtype',
DataFrameModel.ValueRole: b'value'
}
return roles
class Ui_Rulepriority(object):
def setupUi(self, Rulepriority):
Rulepriority.setObjectName("Rulepriority")
Rulepriority.resize(820, 480)
self.verticalLayout = QtWidgets.QVBoxLayout(Rulepriority)
self.verticalLayout.setObjectName("verticalLayout")
self.OpenCsv = QtWidgets.QPushButton(Rulepriority)
self.OpenCsv.setObjectName("OpenCsv")
self.verticalLayout.addWidget(self.OpenCsv)
self.OpenCsv.clicked.connect(self.file_open)
self.tableView = QtWidgets.QTableView(Rulepriority)
self.tableView.setSizeAdjustPolicy(QtWidgets.QAbstractScrollArea.AdjustToContents)
self.tableView.setObjectName("tableView")
self.verticalLayout.addWidget(self.tableView)
QtCore.QFileSystemWatcher()
self.retranslateUi(Rulepriority)
self.OpenCsv.clicked.connect(self.tableView.show)
QtCore.QMetaObject.connectSlotsByName(Rulepriority)
def retranslateUi(self, Rulepriority):
_translate = QtCore.QCoreApplication.translate
Rulepriority.setWindowTitle(_translate("Rulepriority", "Violation Solving Prioritization tool"))
self.OpenCsv.setText(_translate("Rulepriority", "Browse excel and get solving probability"))
Rulepriority.setWindowIcon(QtGui.QIcon('favicon.ico'))
def file_open(self):
path, _ = QtWidgets.QFileDialog.getOpenFileName(Rulepriority, 'Open csv', QtCore.QDir.rootPath(),
'Data_*.xlsx')
df1 = pd.read_excel(path)
cols = ['Priority', 'RID to solve']
dm = df1[cols].apply(lambda x: x.duplicated())
df1[cols] = df1[cols].mask(dm, '')
model = DataFrameModel(df1)
self.tableView.setModel(model)
if __name__ == "__main__":
import sys
app = QtWidgets.QApplication(sys.argv)
Rulepriority = QtWidgets.QDialog()
ui = Ui_Rulepriority()
ui.setupUi(Rulepriority)
Rulepriority.show()
sys.exit(app.exec_())
干杯:)
推荐阅读
- jenkins - 在詹金斯中止时不要立即终止进程
- ibm-cloud - 如何在 Watson 助手之外使用多个条件响应？
- hadoop - 获取从 CDH Hadoop 集群连接到 S3 的“peer not authenticated”异常
- python - 使用 python 将原始文件加载到 SQL Server 数据库中
- json - 发回 JSON 对象
- sql-server - 执行数据类型的存储过程错误消息
- tensorflow - horovod do summary_op 发生“一个或多个张量被提交以减少”
- c - strcmp 无法比较链表中的字符串
- python - 无法为 Slack bot 中发短信的人打印用户名
- regex - Go：用逗号分割字符串，但忽略双引号内的逗号