def test_copyDataFrame(copy, operator):
dataFrame = pandas.DataFrame([0], columns=['A'])
model = DataFrameModel(dataFrame, copyDataFrame=copy)
assert operator(id(model.dataFrame()), id(dataFrame))
model.setDataFrame(dataFrame, copyDataFrame=copy)
assert operator(id(model.dataFrame()), id(dataFrame))
class MainWindow(QMainWindow, Ui_MainWindow): ''' Class documentation goes here. ''' def __init__(self, parent=None): ''' Constructor @param parent reference to the parent widget @type QWidget ''' super(MainWindow, self).__init__(parent) self.setupUi(self) '''初始化pandasqt''' widget = self.pandastablewidget widget.resize(600, 500) self.model = DataFrameModel() # 設定新的模型 widget.setViewModel(self.model) self.df = pd.read_excel(r'./data/fund_data.xlsx',encoding='big5') self.df_original = self.df.copy() # 備份原始資料 self.model.setDataFrame(self.df) @pyqtSlot() def on_pushButton_clicked(self): '''初始化pandas''' self.model.setDataFrame(self.df_original) @pyqtSlot() def on_pushButton_2_clicked(self): '''儲存資料''' self.df.to_excel(r'./data/fund_data_new.xlsx')
def test_setDataFrame():
dataFrame = pandas.DataFrame([0], columns=['A'])
model = DataFrameModel()
model.setDataFrame(dataFrame)
assert not model.dataFrame().empty
assert model.dataFrame() is dataFrame
with pytest.raises(TypeError) as excinfo:
model.setDataFrame(None)
assert "pandas.core.frame.DataFrame" in str(excinfo.value)
class ResultWindow(QWidget):
def __init__(self):
super(self.__class__, self).__init__()
self.setup_ui()
def setup_ui(self):
self.setWindowTitle("查詢結果")
self.setFont(PyQt5.QtGui.QFont("Times New Roman", 11))
self.buttonCopy = QPushButton("複製查詢結果 (Ctrl+C)")
self.buttonCopy.clicked.connect(self.copy)
self.labelInfo = QLabel("")
self.shortcut = QShortcut(PyQt5.QtGui.QKeySequence("Ctrl+C"), self)
self.shortcut.activated.connect(self.copy)
self.formLayout = QFormLayout()
self.formLayout.addRow(self.buttonCopy, self.labelInfo)
self.widget = DataTableWidget()
self.widget.setButtonsVisible(False)
self.widget.setMinimumSize(600, 600)
self.vbox = QVBoxLayout()
self.vbox.addLayout(self.formLayout)
self.vbox.addWidget(self.widget)
self.setLayout(self.vbox)
def update(self, data):
self.df = data
self.labelInfo.setText("")
self.model = DataFrameModel()
self.widget.resize(self.widget.sizeHint())
self.widget.show()
self.widget.setViewModel(self.model)
self.model.setDataFrame(data)
def copy(self):
count = 0
indexes = self.widget.tableView.selectionModel().selectedRows()
if len(indexes) > 0:
rows = [index.row() for index in indexes]
count = len(indexes)
else:
rows = [i for i in range(self.widget.tableView.model().rowCount())]
count = self.widget.tableView.model().rowCount()
s = ''
for i in rows:
r = self.df.iloc[[i]]
for v in r.values[0].astype(str):
s += (v + '\t')
s = s[:-1] + '\n'
pyperclip.copy(s[:-1])
self.labelInfo.setText(str(count) + " 筆資料已複製至剪貼簿")
def show_data(self):
"""
展示读取进来的数据
"""
self.widget = DataTableWidget(self.ui)
model = DataFrameModel()
model.setDataFrame(self.DataHelper.data)
self.widget.setViewModel(model)
self.widget.move(30, 200)
self.widget.resize(350, 350)
self.widget.show()
class Ui_Mine5(object):
def click1(self):
self.form.close()
Form1 = QtWidgets.QMainWindow()
self.ui = Mine.Ui_Mine()
self.ui.setupUi(Form1)
Form1.show()
def setupUi(self, Mine5):
Mine5.setObjectName("Mine5")
Mine5.resize(1259, 879)
Mine5.setStyleSheet("border-image: url(:/M11.jpg);")
Mine5.setWindowFlags(Qt.FramelessWindowHint)
self.form = Mine5
self.centralwidget = QtWidgets.QWidget(Mine5)
self.centralwidget.setObjectName("centralwidget")
self.pushButton = QtWidgets.QPushButton(self.centralwidget)
self.pushButton.setGeometry(QtCore.QRect(20, -10, 861, 101))
self.pushButton.setStyleSheet("border-image: url(:/M16.jpg);\n"
"font: 14pt \"华文新魏\";")
self.pushButton.setObjectName("pushButton")
self.label_2 = QtWidgets.QLabel(self.centralwidget)
self.label_2.setGeometry(QtCore.QRect(890, 0, 361, 81))
self.label_2.setStyleSheet("\n"
"border-image: url(:/M10.jpg);")
self.label_2.setObjectName("label_2")
self.pandastablewidget = DataTableWidget(self.centralwidget)
self.pandastablewidget.setGeometry(QtCore.QRect(-10, 90, 1251, 741))
self.pandastablewidget.setStyleSheet("border-image: url(:/M8.png);")
self.pandastablewidget.setObjectName("pandastablewidget")
self.model = DataFrameModel()
self.pandastablewidget.setViewModel(self.model)
self.model.setDataFrame(db)
Mine5.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(Mine5)
self.menubar.setGeometry(QtCore.QRect(0, 0, 1259, 26))
self.menubar.setObjectName("menubar")
Mine5.setMenuBar(self.menubar)
self.statusbar = QtWidgets.QStatusBar(Mine5)
self.statusbar.setObjectName("statusbar")
Mine5.setStatusBar(self.statusbar)
self.retranslateUi(Mine5)
self.pushButton.clicked.connect(self.click1)
QtCore.QMetaObject.connectSlotsByName(Mine5)
def retranslateUi(self, Mine5):
_translate = QtCore.QCoreApplication.translate
Mine5.setWindowTitle(_translate("Mine5", "欢迎使用喵眼！"))
self.pushButton.setText(_translate("Mine5", "返回"))
self.label_2.setText(_translate("Mine5", "TextLabel"))
def test_flags():
model = DataFrameModel(pandas.DataFrame([0], columns=['A']))
index = model.index(0, 0)
assert index.isValid()
assert model.flags(index) == Qt.ItemIsSelectable | Qt.ItemIsEnabled
model.enableEditing(True)
assert model.flags(index) == Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsEditable
model.setDataFrame(pandas.DataFrame([True], columns=['A']))
index = model.index(0, 0)
model.enableEditing(True)
assert model.flags(index) != Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsEditable
assert model.flags(index) == Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable
def save_file(self, filepath, save_as=None, keep_orig=False, **kwargs):
"""
Saves a DataFrameModel to a file.
:param filepath: (str)
The filepath of the DataFrameModel to save.
:param save_as: (str, default None)
The new filepath to save as.
:param keep_orig: (bool, default False)
True keeps the original filepath/DataFrameModel if save_as is specified.
:param kwargs:
pandas.DataFrame.to_excel(**kwargs) if .xlsx
pandas.DataFrame.to_csv(**kwargs) otherwise.
:return: None
"""
df = self._models[filepath].dataFrame()
kwargs['index'] = kwargs.get('index', False)
if save_as is not None:
to_path = save_as
else:
to_path = filepath
ext = os.path.splitext(to_path)[1].lower()
if ext == ".xlsx":
kwargs.pop('sep', None)
df.to_excel(to_path, **kwargs)
elif ext in ['.csv', '.txt']:
df.to_csv(to_path, **kwargs)
else:
raise NotImplementedError(
"Cannot save file of type {}".format(ext))
if save_as is not None:
if keep_orig is False:
# Re-purpose the original model
# Todo - capture the DataFrameModelManager._updates too
model = self._models.pop(filepath)
model._filePath = to_path
else:
# Create a new model.
model = DataFrameModel()
model.setDataFrame(df, copyDataFrame=True, filePath=to_path)
self._models[to_path] = model
class Order(QWidget, Ui_offer_Form): # 订单
def __init__(self, parent=None):
super(Order, self).__init__(parent)
self.setupUi(self)
self.offer()
def offer(self): # 订单
# self.offerwidget = DataTableWidget()
widget = self.offerwidget
widget.resize(600, 500) # 如果对部件尺寸大小不满意可以在这里设置
self.model = DataFrameModel() # 设置新的模型
widget.setViewModel(self.model)
# conn = pymysql.connect(host='localhost', port=3308,user='******',password='******',db='mrp',charset='utf8')
# # 通过sqlalchemy.create_engine建立连接引擎
# engine = create_engine('mysql+pymysql://root:root@localhost:3308/mrp')
# sql = 'select * from quote'
# self.df = pd.read_sql(sql, con=conn)#MySQL法连接数据库,读取数据需要转换
# self.df = pd.read_sql(sql, engine)#SQLAlchemy法可以直接创建dataframe
# self.df.to_sql(name='user',con=engine,if_exists='append',index=False) 写入数据库
# df.to_sql(目标表名,con=engine, schema=数据库名, index=False, index_label=False, if_exists='append', chunksize=1000)
# pd.io.sql.to_sql(df,table_name,con=conn,schema='w_analysis',if_exists='append') 两个语句???
self.df = pd.read_excel(r'C:/Users/Administrator/Desktop/报价模板.xlsx',
encoding='utf-8')
# self.df_original = self.df.copy() # 备份原始数据
self.model.setDataFrame(self.df)
# d = self.df.loc[:,'num'].sum()
d = sum(self.df['单重'])
print('d' + str(d))
# self.df.apply(sum)
# column_sum = self.df.iloc[:,j].sum()
# dtypedict = {}
# for i, j in zip(self.df.columns, self.df.dtypes):
# # if "object" in str(j):
# # dtypedict.update({i: VARCHAR(256)})
# # if "float" in str(j):
# # dtypedict.update({i: NUMBER(19,8)})
# if "int" in str(j):
# dtypedict.update({i: VARCHAR(19)})
# return dtypedict
# print(dtypedict)
"""
class MainWindow(QMainWindow, Ui_MainWindow):
def __init__(self, parent=None):
super(MainWindow, self).__init__(parent)
self.setupUi(self)
widget = self.pandastablewidget
widget.resize(600, 500)
self.model = DataFrameModel()#设置新的模型
widget.setViewModel(self.model)
self.df = pd.read_excel(r'./data/fund_data.xlsx', encoding='gdk')
self.df_original = self.df.copy()#备份原始数据
self.model.setDataFrame(self.df)
@pyqtSlot()
def on_pushButton_clicked(self):
self.model.setDataFrame(self.df_original)
@pyqtSlot()
def on_pushButton2_clicked(self):
self.df.to_excel(r'./data/fund_data_new.xlsx')
def __init__(self, parent=None):
super(MyMainWidget, self).__init__(parent)
self.setupUi(self)
self.setLayout(self.gridLayout)
# 退出窗口
self.quit_btn.clicked.connect(self.quit_act)
# qtpandas
model = DataFrameModel() # 空模型那个用于存储和处理数据
# print(type(self.widget_2))
self.widget_2.setViewModel(model)
data = {'A': [10, 11, 12], 'B': [12, 11, 10], 'C': ['a', 'b', 'c']}
self.df = pandas.DataFrame(data)
self.df['A'] = self.df['A'].astype(np.int8) # 委托，规定某一列的类型
model.setDataFrame(self.df)
# 保存数据
self.quit_btn_7.clicked.connect(self.save_data)
class MainWindow(QMainWindow, Ui_MainWindow):
"""
Class documentation goes here.
"""
def __init__(self, parent=None):
"""
Constructor
@param parent reference to the parent widget
@type QWidget
"""
super(MainWindow, self).__init__(parent)
self.setupUi(self)
'''初始化pandasqt'''
widget = self.pandastablewidget
widget.resize(600, 500) # 如果对部件尺寸大小不满意可以在这里设置
self.model = DataFrameModel() # 设置新的模型
widget.setViewModel(self.model)
self.df = pd.read_excel(r'./data/fund_data.xlsx',encoding='gbk')
self.df_original = self.df.copy() # 备份原始数据
self.model.setDataFrame(self.df)
@pyqtSlot()
def on_pushButton_clicked(self):
"""
初始化pandas
"""
self.model.setDataFrame(self.df_original)
@pyqtSlot()
def on_pushButton_2_clicked(self):
"""
保存数据
"""
self.df.to_excel(r'./data/fund_data_new.xlsx')
class MainWindow(QMainWindow, Ui_MainWindow):
def __init__(self, parent=None):
super(MainWindow, self).__init__(parent)
self.setWindowTitle("QtPandas")
self.setWindowIcon(QIcon("./images/Python2.ico"))
self.setupUi(self)
widget = self.pandastablewidget
widget.resize(600, 500)
self.model = DataFrameModel()
widget.setViewModel(self.model)
self.df = pd.read_excel(r'./data/fund_data.xlsx', encoding='gbk')
self.df_original = self.df.copy()
self.model.setDataFrame(self.df)
@pyqtSlot()
def on_pushButton_clicked(self):
self.model.setDataFrame(self.df_original)
@pyqtSlot()
def on_pushButton_2_clicked(self):
self.df.to_excel(r'./data/fund_data_new1.xlsx')
def setDataFrame(self, dataFrame):
self.df = dataFrame
dataModel = DataFrameModel()
dataModel.setDataFrame(self.df)
self.dataModel = dataModel
self.dataListView.setModel(dataModel)
self.dataTableView.setViewModel(dataModel)
self.dataComboBox.setModel(dataModel)
# self.dataTableView.resizeColumnsToContents()
# create a simple item model for our choosing combobox
columnModel = QtGui.QStandardItemModel()
for column in self.df.columns:
columnModel.appendRow(QtGui.QStandardItem(column))
self.chooseColumnComboBox.setModel(columnModel)
self.tableViewColumnDtypes.setModel(dataModel.columnDtypeModel())
self.tableViewColumnDtypes.horizontalHeader().setDefaultSectionSize(200)
self.tableViewColumnDtypes.setItemDelegateForColumn(1, DtypeComboDelegate(self.tableViewColumnDtypes))
dataModel.changingDtypeFailed.connect(self.changeColumnValue)
class MainWindow(QMainWindow, Ui_MainWindow):
"""
Class documentation goes here.
"""
def __init__(self, parent=None):
"""
Constructor
@param parent reference to the parent widget
@type QWidget
"""
super(MainWindow, self).__init__(parent)
self.setupUi(self)
'''初始化pandasqt'''
widget = self.pandastablewidget
widget.resize(600, 500) # 如果对部件尺寸大小不满意可以在这里设置
self.model = DataFrameModel() # 设置新的模型
widget.setViewModel(self.model)
self.df = pd.read_excel(r'./data/fund_data.xlsx', encoding='gbk')
self.df_original = self.df.copy() # 备份原始数据
self.model.setDataFrame(self.df)
@pyqtSlot()
def on_pushButton_clicked(self):
"""
初始化pandas
"""
self.model.setDataFrame(self.df_original)
@pyqtSlot()
def on_pushButton_2_clicked(self):
"""
保存数据
"""
self.df.to_excel(r'./data/fund_data_new.xlsx')
class MainWindow(QMainWindow, Ui_MainWindow):
"""
Class documentation goes here.
"""
def __init__(self, parent=None):
"""
Constructor
@param parent reference to the parent widget
@type QWidget
"""
super(MainWindow, self).__init__(parent)
self.setupUi(self)
'''初始化pandasqt'''
widget = self.pandastablewidget
widget.resize(600, 500) # 如果对部件尺寸大小不满意可以在这里设置
self.model = DataFrameModel() # 设置新的模型
widget.setViewModel(self.model)
self.LoadFileName = None
@pyqtSlot()
def on_pushButton_clicked(self):
"""
初始化pandas
"""
self.model.setDataFrame(self.dataframe_original)
@pyqtSlot()
def on_pushButton_2_clicked(self):
"""
保存数据
"""
data = ui.dataframe.values
print(data)
sys.excepthook = excepthook # 設定PyQt的異常鉤子，基本上在本例沒什麼作用
# 建立一個空的模型，該模型用來儲存與處理資料
model = DataFrameModel()
# 建立一個應用程式顯示表格
app = QtGui.QApplication([])
widget = DataTableWidget() # 建立一個空的表格，用來呈現資料
widget.resize(500, 300) # 調整Widget的大小
widget.show()
# 讓表格繫結模型，也就是呈現模型的內容
widget.setViewModel(model)
# 建立測試資料
data = {
'A': [10, 11, 12],
'B': [20, 21, 22],
'C': ['Peter Pan', 'Cpt. Hook', 'Tinkerbell']
}
df = pandas.DataFrame(data)
# 下面兩列用來測試委託是否成立
df['A'] = df['A'].astype(numpy.int8) # A欄資料格式變成整數
df['B'] = df['B'].astype(numpy.float16) # B欄資料格式變成浮點數
# 在模型中填入資料df
model.setDataFrame(df)
# 啟動程式
app.exec_()
class MainWindow(QMainWindow, Ui_MainWindow):
# 自定义信号
close_signal = pyqtSignal()
def __init__(self, parent=None):
super(MainWindow, self).__init__(parent)
self.setupUi(self)
# qtpandas 设置 tableWidget
tableWidget = self.tableWidget
self.model = DataFrameModel()
tableWidget.setViewModel(self.model)
self.df = None # 用于操作的数据
self.df_temp = None # 用于撤销的数据
self.df_original = None # 用于初始化的数据
# 数据导入 tab
self.pushButton_openfile.clicked.connect(self.open_file)
self.saveDataButton.clicked.connect(self.save_data)
self.initDataButton.clicked.connect(self.init_data)
self.pushButton_refresh.clicked.connect(self.data_shape)
self.pushButton_refresh.clicked.connect(self.data_describe)
self.plotButton.clicked.connect(self.image_plot)
# 特征预处理 tab
self.checkNaNButton.clicked.connect(self.check_NaN)
self.okButton_nan.clicked.connect(self.deal_with_NaN)
self.repealButton_nan.clicked.connect(self.repeal_action)
self.okButton_nondim.clicked.connect(self.nondim_action)
self.repealButton_nondim.clicked.connect(self.repeal_action)
self.initButton_nondim.clicked.connect(self.init_data)
# 绘图设置 tab
self.radioButton_2D.toggled.connect(self.set_page_show)
self.radioButton_2Dline.toggled.connect(self.set_page_show)
self.radioButton_2Dscatter.toggled.connect(self.set_page_show)
self.radioButton_2Dhist.toggled.connect(self.set_page_show)
# self.radioButton_2Dpie.toggled.connect(self.set_page_show)
self.comboBox_fitmethod.activated.connect(self.set_page_show)
self.radioButton_3D.toggled.connect(self.set_page_show)
self.radioButton_3Dline.toggled.connect(self.set_page_show)
self.radioButton_3Dscatter.toggled.connect(self.set_page_show)
self.radioButton_3Dsurface.toggled.connect(self.set_page_show)
# 跟随主窗口关闭所有绘图
self.close_signal.connect(lambda: plt.close('all'))
def open_file(self):
""" 打开文件
"""
fname, filetype = QFileDialog.getOpenFileName(
self, 'Open File', '',
"CSV files (*.csv);; Excel files (*.xlsx *.xls);; MAT files (*.mat);; All files (*)"
)
if not fname:
return
try:
if fname.endswith('.csv'):
self.df = pd.read_csv(fname, encoding='utf-8')
elif fname.endswith('.xlsx' or '.xls'):
self.df = pd.read_excel(fname, encoding='utf-8')
elif fname.endswith('.mat'):
tempdata = sio.loadmat(fname)
data_dic = dict()
for key in tempdata.keys():
if not key.startswith('__'):
if not len(tempdata[key]):
data_dic[key] = None
elif len(tempdata[key]) == 1:
data_dic[key] = tempdata[key][0]
else:
for i in range(len(tempdata[key])):
data_dic['{}_{}'.format(key,
i)] = tempdata[key][i]
self.df = pd.DataFrame(data_dic)
else:
self.textBrowser.append('文件格式不符')
except Exception as e:
self.textBrowser.append('无法打开该文件: {}\n 错误原因: {}'.format(fname, e))
return
self.df_original = self.df.copy()
self.load_data_to_table()
self.filenameLabel.setText(fname.split('/')[-1].split('.')[0])
self.fileformatLabel.setText(fname.split('.')[-1])
self.data_shape()
self.data_describe()
self.textBrowser.append('文件已加载: {}'.format(fname))
def load_data_to_table(self):
""" 载入数据至 qtpandasTable
"""
try:
self.model.setDataFrame(self.df)
except Exception as e:
self.textBrowser.append('数据载入失败 \n 错误原因: {}'.format(e))
return
def data_shape(self):
""" 显示数据的维度
"""
(m, n) = self.df.shape
self.dataDimLabel.setText('{}x{}'.format(m, n))
def data_describe(self):
""" 显示数据特征信息
"""
# pd.set_option('display.max_columns', 1000)
# self.dataDescTable.setPlainText('{}'.format(self.df.describe()))
try:
desc = self.df.describe()
self.dataDescTable.setRowCount(desc.shape[0])
self.dataDescTable.setColumnCount(desc.shape[1])
self.dataDescTable.setHorizontalHeaderLabels(desc.columns)
self.dataDescTable.setVerticalHeaderLabels(desc.index)
for i in range(desc.shape[0]):
for j in range(desc.shape[1]):
newItem = QTableWidgetItem(str(round(desc.iloc[i, j], 2)))
self.dataDescTable.setItem(i, j, newItem)
except:
pass
def init_data(self):
""" 数据初始化
"""
try:
self.model.setDataFrame(self.df_original)
self.df = self.df_original.copy()
self.textBrowser.append('数据已还原')
except Exception as e:
self.textBrowser.append('数据初始化失败 \n 错误原因: {}'.format(e))
return
def save_data(self):
""" 保存文件
"""
fname, filetype = QFileDialog.getSaveFileName(
self, 'Save File', '',
"CSV files (*.csv);; Excel files (*.xlsx *.xls);; MAT files (*.mat);; All files (*)"
)
if not fname:
return
try:
if fname.endswith('.csv'):
self.df.to_csv(fname, encoding='utf-8')
elif fname.endswith('.xlsx' or '.xls'):
self.df.to_excel(fname, encoding='utf-8')
elif fname.endswith('.mat'):
tempdata = {
col_name: self.df[col_name].values
for col_name in self.df.columns.values
}
sio.savemat(fname, tempdata)
else:
self.textBrowser.append('无法保存为该文件类型')
except Exception as e:
self.textBrowser.append('文件保存失败: {}\n 错误原因: {}'.format(fname, e))
return
self.textBrowser.append('文件已保存于: {}'.format(fname))
def image_plot(self):
""" 根据绘图设置进行图像绘制
"""
try:
x_label = self.lineEdit_datax.text()
data_x = self.get_data(x_label)
y_label = self.lineEdit_datay.text()
data_y = self.get_data(y_label)
z_label = self.lineEdit_dataz.text()
data_z = self.get_data(z_label)
method = self.comboBox_fitmethod.currentIndex()
if self.radioButton_2D.isChecked():
fig = plt.figure()
ax = fig.add_subplot(111)
if self.radioButton_2Dline.isChecked():
self.plot_2Dline(ax, data_x, data_y)
elif self.radioButton_2Dscatter.isChecked():
self.plot_2Dscatter(ax, data_x, data_y, method)
elif self.radioButton_2Dhist.isChecked():
self.plot_2Dhist(ax, data_x)
# elif self.radioButton_2Dpie.isChecked():
# self.plot_2Dpie(ax, data_x)
else:
fig = plt.figure()
ax = fig.add_subplot(111, projection='3d')
if self.radioButton_3Dline.isChecked():
self.plot_3Dline(ax, data_x, data_y, data_z)
elif self.radioButton_3Dscatter.isChecked():
self.plot_3Dscatter(ax, data_x, data_y, data_z)
elif self.radioButton_3Dsurface.isChecked():
self.plot_3Dsurface(ax, data_x, data_y, data_z)
ax.legend()
plt.show()
except Exception as e:
self.textBrowser.append('无法绘制该图形！\n 错误原因: {}'.format(e))
# 关闭当前错误 figure
plt.close(fig)
def get_data(self, label):
""" 根据序号或者特征名返回数据
"""
data = []
try:
if not label:
return
label_list = label.split(',')
for index in label_list:
try:
i = int(index)
data.append(self.df.iloc[:, i])
except:
data.append(self.df[index])
return data
except:
return
def plot_2Dline(self, ax, x, y):
plt.xlabel(x[0].name)
for y_ in y:
ax.plot(x[0], y_, label=y_.name)
def plot_2Dscatter(self, ax, x, y, method):
plt.xlabel(x[0].name)
for y_ in y:
ax.scatter(x[0], y_, label=y_.name)
if self.checkBox_fit.isChecked():
yvals, func_exp, r_square = self.scatter_fit(x[0], y_, method)
ax.plot(x[0], yvals, '--', label='{}-fit'.format(y_.name))
self.textBrowser.append('{} 拟合表达式为：\n'.format(y_.name) +
func_exp)
self.textBrowser.append('R-square: {}'.format(r_square))
def scatter_fit(self, x, y, method):
"""二维散点图曲线拟合
:param x: input 1-dArray x
:param y: input 1-dArray y
:param method: int, 0: 多项式拟合; 1: e 指数拟合; 2: 对数拟合
:param n: 多项式拟合阶数
:return yvals: 拟合后 1-dArray yvals
:return func_exp: string, 拟合函数表达式
:return r_square: float, 确定系数(R-square), range(0,1)
"""
def func_log(x, a, b):
return a * np.log(x) + b
def func_e(x, a, b, c):
return a * np.exp(b * x) + c
def compute_r(y, yvals):
ybar = np.sum(y) / len(y)
ssreg = np.sum((yvals - ybar)**2)
sstot = np.sum((y - ybar)**2)
return ssreg / sstot
if method == 0:
n = int(self.comboBox_2d_n.currentText())
popt = np.polyfit(x, y, n)
func_exp = '{}'.format(np.poly1d(popt))
yvals = np.polyval(popt, x)
elif method == 1:
popt, _ = curve_fit(func_e, x, y)
func_exp = 'y = {} * exp({} * x) + {}'.format(
popt[0], popt[1], popt[2])
yvals = func_e(x, popt[0], popt[1], popt[2])
elif method == 2:
popt, _ = curve_fit(func_log, x, y)
func_exp = 'y = {} * log(x) + {}'.format(popt[0], popt[1])
yvals = func_log(x, popt[0], popt[1])
r_square = compute_r(y, yvals)
return (yvals, func_exp, r_square)
def plot_2Dhist(self, ax, x):
for x_ in x:
ax.hist(x_, normed=True, histtype='bar', label=x_.name)
if self.checkBox_histfit.isChecked():
x_.plot(kind='kde',
style='r--',
label='{}-kde'.format(x_.name))
# def plot_2Dpie(self, ax, x):
# ax.pie(x[0], startangle=90, autopct='%1.1f%%')
# # Equal aspect ratio ensures that pie is drawn as a circle
# ax.axis('equal')
def plot_3Dline(self, ax, x, y, z):
plt.xlabel(x[0].name)
plt.ylabel(y[0].name)
for z_ in z:
ax.plot(x[0], y[0], z_, label=z_.name)
def plot_3Dscatter(self, ax, x, y, z):
plt.xlabel(x[0].name)
plt.ylabel(y[0].name)
for z_ in z:
ax.scatter(x[0], y[0], z_, label=z_.name)
# if self.checkBox_3dscatterfit.isChecked():
# # ax.plot_trisurf(x[0], y[0], z_, label='{}-fit'.format(z_.name))
# data = np.c_[x[0], y[0], z_]
# mn = np.min(data, axis=0)
# mx = np.max(data, axis=0)
# X, Y = np.meshgrid(
# np.linspace(mn[0], mx[0], 20),
# np.linspace(mn[1], mx[1], 20))
# XX = X.flatten()
# YY = Y.flatten()
# if int(n) == 1:
# A = np.c_[data[:, 0], data[:, 1], np.ones(data.shape[0])]
# C, _, _, _ = lstsq(A, data[:, 2])
# Z = np.dot(np.c_[XX, YY, np.ones(XX.shape)],
# C).reshape(X.shape)
# ax.plot_surface(X, Y, Z, label='fit-surface')
# elif int(n) == 2:
# A = np.c_[np.ones(data.shape[0]), data[:, :2],
# np.prod(data[:, :2], axis=1), data[:, :2]**2]
# C, _, _, _ = lstsq(A, data[:, 2])
# Z = np.dot(
# np.c_[np.ones(XX.shape), XX, YY, XX *
# YY, XX**2, YY**2], C).reshape(X.shape)
# ax.plot_surface(X, Y, Z, label='fit-surface')
def plot_3Dsurface(self, ax, x, y, z):
x_, y_ = np.meshgrid(x[0], y[0])
ax.plot_surface(x_, y_, z[0], cmap='hot')
def check_NaN(self):
""" 检查 NaN 值分布情况，并给出建议
"""
temp = sum(
[any(self.df.iloc[i].isnull()) for i in range(self.df.shape[0])])
self.textBrowser.append('含缺失值总样本数如下所示: {}'.format(temp))
if temp / self.df.shape[0] * 100 < 1:
self.textBrowser.append('带有缺失值样本占总样本数小于 1%，建议删除这些样本')
self.radioButton_delnan.setChecked(True)
else:
self.textBrowser.append('带有缺失值样本占总样本数大于 1%，建议使用均值替代缺失值')
self.radioButton_fillnan.setChecked(True)
def deal_with_NaN(self):
""" 对 NaN 进行处理
"""
self.df_temp = self.df.copy()
if self.radioButton_delnan.isChecked():
self.df.dropna(how='any', inplace=True)
elif self.radioButton_fillnan.isChecked():
try:
self.df.fillna(self.df.mean(), inplace=True)
except:
self.textBrowser.append('无法对该数据集使用均值代替 NaN')
return
else:
return
self.load_data_to_table()
self.textBrowser.append('缺失值处理已完成')
def nondim_action(self):
""" 无量纲化处理
"""
self.df_temp = self.df.copy()
columns = self.df.columns.values
if self.radioButton_01standard.isChecked():
try:
min_max_scaler = preprocessing.MinMaxScaler()
df_01 = min_max_scaler.fit_transform(self.df)
self.df = pd.DataFrame(df_01, columns=columns)
except:
self.textBrowser.append('无法对该数据集进行归一化处理')
return
elif self.radioButton_zstandard.isChecked():
try:
df_scale = preprocessing.scale(self.df)
self.df = pd.DataFrame(df_scale, columns=columns)
except:
self.textBrowser.append('无法对该数据集进行标准化处理')
return
elif self.radioButton_normalizer.isChecked():
try:
df_normalized = preprocessing.normalize(self.df, norm='l2')
self.df = pd.DataFrame(df_normalized, columns=columns)
except Exception as e:
self.textBrowser.append('无法对该数据集进行正则化处理 \n 错误原因: {}'.format(e))
return
else:
return
self.load_data_to_table()
self.textBrowser.append('数据无量纲化处理已完成')
def repeal_action(self):
""" 预处理撤回操作
"""
try:
self.model.setDataFrame(self.df_temp)
self.df = self.df_temp.copy()
self.textBrowser.append('已撤销该操作')
except Exception as e:
self.textBrowser.append('撤销失败 \n 错误原因: {}'.format(e))
return
def set_page_show(self):
""" 根据图像类型单选按钮状态，调整页面显示
"""
if self.radioButton_2D.isChecked():
self.groupBox_2D.setEnabled(True)
self.groupBox_3D.setEnabled(False)
self.groupBox_fit.setEnabled(False)
self.checkBox_histfit.setEnabled(False)
if self.radioButton_2Dline.isChecked(
) or self.radioButton_2Dscatter.isChecked():
self.lineEdit_datax.setEnabled(True)
self.lineEdit_datay.setEnabled(True)
self.lineEdit_dataz.setEnabled(False)
if self.radioButton_2Dscatter.isChecked():
self.groupBox_fit.setEnabled(True)
self.checkBox_fit.setEnabled(True)
self.comboBox_fitmethod.setEnabled(True)
self.comboBox_2d_n.setEnabled(False)
if self.comboBox_fitmethod.currentIndex() == 0:
self.comboBox_2d_n.setEnabled(True)
elif self.radioButton_2Dhist.isChecked():
self.lineEdit_datax.setEnabled(True)
self.lineEdit_datay.setEnabled(False)
self.lineEdit_dataz.setEnabled(False)
if self.radioButton_2Dhist.isChecked():
self.checkBox_histfit.setEnabled(True)
else:
self.groupBox_2D.setEnabled(False)
self.groupBox_3D.setEnabled(True)
self.lineEdit_datax.setEnabled(True)
self.lineEdit_datay.setEnabled(True)
self.lineEdit_dataz.setEnabled(True)
def closeEvent(self, event):
""" 关闭程序
"""
result = QMessageBox.question(self, 'Confirm Exit...',
'Are you sure to exit?',
QMessageBox.Yes | QMessageBox.No)
if result == QMessageBox.Yes:
self.close_signal.emit()
event.accept()
else:
event.ignore()
sys.excepthook = excepthook # 设置PyQt的异常钩子，在本例中基本没什么用
# 创建一个空的模型，该模型用于存储与处理数据
model = DataFrameModel()
# 创建一个应用用于显示表格
app = QtGui.QApplication([])
widget = DataTableWidget() # 创建一个空的表格，主要用来呈现数据
widget.resize(500, 300) # 调整Widget的大小
widget.show()
# 让表格绑定模型，也就是让表格呈现模型的内容
widget.setViewModel(model)
# 创建测试数据
data = {
'A': [10, 11, 12],
'B': [20, 21, 22],
'C': ['Peter Pan', 'Cpt. Hook', 'Tinkerbell']
}
df = pandas.DataFrame(data)
# 下面两列用来测试委托是否成立
df['A'] = df['A'].astype(numpy.int8) # A列数据格式变成整型
df['B'] = df['B'].astype(numpy.float16) # B列数据格式变成浮点型
# 在模型中填入数据df
model.setDataFrame(df)
# 启动程序
app.exec_()
class MainWindows(QMainWindow, Ui_MainWindow):
global fileName, selectedFliter, data, data_orignal, sql, table_name, standard_data, standard, pyechartsdata
'''信号定义'''
readdatabase_signal = pyqtSignal()
showsql_signal = pyqtSignal(int) #sql界面显示信号
confirmsql_signal = pyqtSignal(int) #sql界面确认信号
cleardatashow_signal = pyqtSignal() #清空所显示的数据信号
attributes_changed_datashow_signal = pyqtSignal()
comboBox_content_signal = pyqtSignal(list)
attributesmodel_changed_datashow_signal = pyqtSignal(
pd.DataFrame) #当选择属性发生改变时，显示属性表格需要接受的信号
showmatplotlib_signal = pyqtSignal() #用来描绘视图的信号
'''初始化'''
def __init__(self):
super(MainWindows, self).__init__()
self.setupUi(self)
self.setWindowTitle('数据挖掘')
self.browser = QWebEngineView()
# 加载外部的web界面
self.gridLayout_pciture.addWidget(self.browser)
'''信号与槽的连接'''
self.pushButton_openfile.clicked.connect(
self.preprocess_openfile) #打开文件按钮点击槽函数的连接
#self.showdata_signal.connect(self.showdata) #显示数据槽函数的连接
self.pushButton_save.clicked.connect(self.savedata) #保存文件按钮点击槽函数的连接
self.pushButton_datareturn.clicked.connect(self.datareturn_orginal)
self.pushButton_opendatabase.clicked.connect(
self.onclicked_sqlbutton_way_0)
self.readdatabase_signal.connect(self.preprocess_openfile)
self.pushButton_sqllanguage.clicked.connect(
self.onclicked_sqlbutton_way_1)
self.showsql_signal.connect(self.onclicked_sqlbutton)
self.confirmsql_signal.connect(self.ConfirmSQLInformation)
self.pushButton_logdatabase.clicked.connect(self.opendatabase)
self.pushButton_cleardata.clicked.connect(self.ClearData)
self.cleardatashow_signal.connect(self.ClearDatashow)
self.pushButton_view.clicked.connect(
self.showpyecharts) #!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!需要更改
self.comboBox_attributes.currentTextChanged.connect(
self.comboBox_content_changed)
self.attributesmodel_changed_datashow_signal.connect(
self.showAttributesData)
self.pushButton_queding1.clicked.connect(self.comBoBox_content)
self.comboBox_content_signal.connect(self.comboBox_content)
self.attributes_changed_datashow_signal.connect(
self.attributes_data_show)
#单选按钮的信号连接
self.radioButton_Zscore.clicked.connect(
self.radiobutton_clicked_standard) #单选按钮的信号连接
self.radioButton_xiaoshudingbiao.clicked.connect(
self.radiobutton_clicked_standard)
self.radioButton_Minmax.clicked.connect(
self.radiobutton_clicked_standard)
#读取线程中的信号连接
self.readthread = ReadThread() #读取线程中对象的声明
self.readthread.sendException_signal.connect(
self.Warning_QMessageBox) #读取线程中的信号连接
self.readthread.showdata_signal.connect(self.showdata)
self.readthread.radiobutton_signal.connect(
self.radiobutton_clicked_standard)
self.readthread.comboBox_signal.connect(self.comboBox_content)
#保存线程中的信号连接
self.savethread = SaveThread()
self.savethread.sendSaveException_signal.connect(
self.Warning_QMessageBox)
#标准化数据线程中的信号连接
self.standarddatathread = StandardDataThread()
self.standarddatathread.standardException_signal.connect(
self.Warning_QMessageBox)
#描绘视图的信号链接
#self.showmatplotlib_signal.connect(self.showMatplotlib)
#pyecahrts线程信号的连接
self.pyechartsthread = pyechartsThread()
self.pyechartsthread.show_pyecharts_signal.connect(
self.showAttributesData)
'''图标设置'''
self.setWindowIcon(QtGui.QIcon('Picture/主窗口图标.png'))
'''----------任务栏预先设置-----------'''
self.statusBar().setStyleSheet("QStatusBar::item{border:0px}") #去掉任务栏
self.label_status = QtWidgets.QLabel()
self.label_status.setMinimumSize(QtCore.QSize(20, 10))
self.statusBar().addPermanentWidget(self.label_status,
1) #将lable_status标签加入任务栏
self.label_status.setText("欢迎使用本程序")
#预处理界面按钮的设置
self.pushButton_openfile.setToolTip('支持excel,txt,csv文件')
self.pushButton_opendatabase.setToolTip('打开数据库文件')
self.pushButton_logdatabase.setToolTip('登录数据库账户')
self.pushButton_opendatabase.setToolTip('不需要数据库账户的可直接点击此按钮')
self.pushButton_queding1.setToolTip('当你使用此栏编辑时需要点击确认按钮')
#指定属性结果框DataTableWidget的按钮隐藏
self.qtpandas_attributewidget.editButton.hide()
self.qtpandas_attributewidget.addColumnButton.hide()
self.qtpandas_attributewidget.addRowButton.hide()
self.qtpandas_attributewidget.removeColumnButton.hide()
self.qtpandas_attributewidget.removeRowButton.hide()
'''#视图初始化
self.matplotlib = Matplotlibwidget() # 初始化控件对象
self.gridLayout_picture.addWidget(self.matplotlib) # 将控件对象加入到栅格布局中去'''
def showpyecharts(self):
try:
#print('11')
self.pyechartsthread.start()
#c.render()
self.browser.load(QUrl('D:/办公软件/Python源文件/数据挖掘应用/render.html')
) # 本地Html需要使用绝对地址 ，且用‘/’
except Exception as e:
self.Warning_QMessageBox(str(e))
def showdata(self):
'''初始化pandasqt'''
global data, data_orignal
try:
widget_qtpandas = self.pandastablewidget
widget_qtpandas.resize(370, 250)
self.model = DataFrameModel() # 设置新的模型
widget_qtpandas.setViewModel(self.model)
data = pd.DataFrame(data)
# self.data_orignal = self.data.copy(deep=False) #浅拷贝,旧地址的内容改变不会改变新地址的内容
self.model.setDataFrame(data)
self.label_status.setText('欢迎使用本程序') # 任务栏显示信息完成
except Exception as e:
self.Warning_QMessageBox(str(e))
def showAttributesData(self, attributes_data):
'''初始化指定属性显示表格信息的qtpandastablewidget(qtpandas_attributewidget)'''
global pyechartsdata
pyechartsdata = attributes_data
self.attributes_data = attributes_data
try:
attributewidget_qtpandas = self.qtpandas_attributewidget
self.attributesmodel = DataFrameModel()
attributewidget_qtpandas.setViewModel(self.attributesmodel)
self.attributesmodel.setDataFrame(self.attributes_data)
#self.pyechartsthread.start()
#self.browser.load(QUrl('D:/办公软件/Python源文件/数据挖掘应用/render.html')) # 本地Html需要使用绝对地址 ，且用‘/’
#self.showmatplotlib_signal.emit() #属性图标显示出来后发送视图显示的信号
except Exception as e:
self.Warning_QMessageBox(str(e))
def savedata(self):
global fileNameSave, selectedfliterSave, selectedFliter
try:
if '.db' not in selectedFliter:
fileNameSave, selectedfliterSave = QFileDialog.getSaveFileName(
self, '保存文件', 'C:/windows',
'Excel(*.xlsx);;文本文档(*.txt);;csv文件(*.csv);;数据库文件(*.sqlite);;(*.db);;(*.db3)',
None)
if '.db' in selectedFliter:
self.Input_QInputDialog()
if fileNameSave != '':
self.savethread.start()
else:
pass
except Exception as e:
self.Warning_QMessageBox(str(e))
'''-----------------------------------按钮只能触发一次数据还原，需改进---------------------------------------'''
def datareturn_orginal(self): #还原数据
global data_orignal, data
try:
if data.empty:
self.Information_QMessageBox('空数据')
else:
self.model.setDataFrame(data_orignal)
data = data_orignal.copy()
except Exception as e:
self.Warning_QMessageBox(str(e))
'''
警告提示框
content：警告内容
'''
def Warning_QMessageBox(self, content):
warning = QMessageBox.warning(
self, ("警告"), content,
QMessageBox.StandardButtons(QMessageBox.Retry))
'''
信息提示框
content:提示内容
'''
def Information_QMessageBox(self, content):
information = QMessageBox.information(
self, '提示', content, QMessageBox.StandardButtons(QMessageBox.Ok))
def Input_QInputDialog(self):
global table_name
name, state = QInputDialog.getText(self, '数据库保存', '新建表格名称',
QLineEdit.Normal, ('new_table'))
if state == True:
table_name = name
else:
pass
'''----------------主界面中打开按钮响应的槽函数-------------------------------------'''
def preprocess_openfile(self):
global fileName, selectedFliter
fileName, selectedFliter = QFileDialog.getOpenFileName(
self, '选择文件', 'c:\\windows',
'数据库文件(*.db);;Excel文件(*.xlsx);;csv文件 (*.csv);;文本文档(*.txt);;数据库文件(*.mdf);;数据库文件(*.sqlite);;数据库文件(*.db3)',
None)
if fileName != '':
self.label_status.setText('正在读取数据')
self.readthread.start()
else:
pass
'''-------------------------主界面中打开数据库按钮相应的槽函数-----------------------'''
def opendatabase(self):
global databaseUserInformation
self.databasewindow = DataBaseWindow()
self.databasewindow.show()
self.databasewindow.pushButton_confirm.clicked.connect(
self.ConfirmDatabaseInformation)
self.databasewindow.pushButton_cancel.clicked.connect(
self.databasewindow.close)
'''-----------------数据库登录界面确认按钮响应的槽函数----------------------------'''
'''!!!!!!!!!!!!!!!!这里还需要加入数据库用户密码数据库名称是否输入正确的判断!!!!!!!!!!!!!!!!!!!'''
def ConfirmDatabaseInformation(self):
global databaseUserInformation
if self.databasewindow.lineEdit_Host.text() != '':
databaseUserInformation[
0] = self.databasewindow.lineEdit_Host.text(
) # 获取lineEdit文本框中的内容
else:
self.databasewindow.Warning_QMessageBox('Host栏不能为空')
if self.databasewindow.lineEdit_User.text() != '':
databaseUserInformation[
1] = self.databasewindow.lineEdit_User.text()
else:
self.databasewindow.Warning_QMessageBox('User栏不能为空')
if self.databasewindow.lineEdit_Password.text() != '':
databaseUserInformation[
2] = self.databasewindow.lineEdit_Password.text()
else:
self.databasewindow.Warning_QMessageBox('密码不能为空')
if self.databasewindow.lineEdit_Database.text() != '':
databaseUserInformation[
3] = self.databasewindow.lineEdit_Database.text()
else:
self.databasewindow.Warning_QMessageBox('数据库名称不能为空')
if (self.databasewindow.lineEdit_Host.text() != ''
and self.databasewindow.lineEdit_User.text() != ''
and self.databasewindow.lineEdit_Password.text() != ''
and self.databasewindow.lineEdit_Database.text() != ''):
self.databasewindow.close()
'''-------!!!!!账户判断语句！！！！！！！！！！----------'''
self.Information_QMessageBox('登录成功')
'''-------------------点击SQL语言按钮相应的槽函数-----------------------------'''
def onclicked_sqlbutton(self, way):
'''
:param way: 0:输入初始sql语句后，打开文件夹选择数据库文件打开
1：更新sql语句，对数据重新处理，不打开文件夹选择文件打开
:return: None
'''
self.sqlwindow = SQLWindow()
self.sqlwindow.show()
if way == 0:
self.sqlwindow.pushButton_sqlconfirm.clicked.connect(
self.ConfirmSQLInformation_0)
if way == 1:
self.sqlwindow.pushButton_sqlconfirm.clicked.connect(
self.ConfirmSqlInformation_1)
self.sqlwindow.pushButton_sqlcancel.clicked.connect(
self.sqlwindow.close)
def onclicked_sqlbutton_way_1(self):
global selectedFliter
if '.db' in selectedFliter: #！！！！！！！！！！！！！！！！！！！！！！！可以增加
self.showsql_signal.emit(1)
else:
self.Warning_QMessageBox('不是数据库文件')
def onclicked_sqlbutton_way_0(self):
self.showsql_signal.emit(0)
def ConfirmSQLInformation_0(self):
self.confirmsql_signal.emit(0)
def ConfirmSqlInformation_1(self):
self.confirmsql_signal.emit(1)
'''---------------------SQL界面确认按钮相应的槽函数------------------------------'''
def ConfirmSQLInformation(self, way):
'''
:param way: 0:表示输入sql语句后打开文件
1：表示打开文件后修改sql语句
:return: 全局变量 sql
'''
global sql, data, selectedFliter
if self.sqlwindow.plainTextEdit_sql.toPlainText() != '':
sql = self.sqlwindow.plainTextEdit_sql.toPlainText(
) # plainTextEdit.toPlainText()获取文本框中的文本内容
if way == 0:
self.readdatabase_signal.emit()
self.sqlwindow.close()
if way == 1:
if data.empty: # 注意DataFrame数据结构判断是否为空的方法，若用data!=None也是错误的
self.sqlwindow.Warning_QMessageBox('当前未读入任何数据，请先点击打开数据库按钮')
else:
self.readthread.start()
self.label_status.setText('正在读取数据')
self.sqlwindow.close()
else:
self.sqlwindow.Warning_QMessageBox('SQL语句不能为空')
# print(sql)
def ClearData(self):
'''
清空数据
'''
global data, data_orignal, standard_data
if data.empty:
self.Information_QMessageBox('已经是空数据')
else:
data = pd.DataFrame(None)
data_orignal = pd.DataFrame(None)
standard_data = pd.DataFrame(None)
self.attributes_data = pd.DataFrame(None)
self.comboBox_attributes.clear()
self.cleardatashow_signal.emit()
#self.matplotlib.clear_plot()
def ClearDatashow(self):
global data
self.model.setDataFrame(data)
self.attributesmodel.setDataFrame(self.attributes_data)
def radiobutton_clicked_standard(self):
'''
数据标准化规则
'''
global standard
if self.radioButton_Minmax.isChecked():
standard = 'Minmax'
if self.radioButton_xiaoshudingbiao.isChecked():
standard = 'xiaoshudingbiao'
if self.radioButton_Zscore.isChecked(): #标准差标准化
standard = 'Zscore'
self.standarddatathread.start()
def comBoBox_content(self):
global data
if data.empty:
self.Warning_QMessageBox('数据为空')
else:
try:
# print('1111')
self.comboBox_content_signal.emit(list(data.columns))
# print(list(data.columns))
except Exception as e:
print(str(e))
def comboBox_content(self, attributes):
self.comboBox_attributes.clear() #清空之前的下拉列表内容
self.attributes = attributes
for i in range(len(self.attributes)):
self.comboBox_attributes.addItem(self.attributes[i])
def comboBox_content_changed(self):
self.label_comboboxattributes.setText(
self.comboBox_attributes.currentText())
if self.comboBox_attributes.currentText(
) != '': #判断一下下拉列表框是否为空，因为有清空列表框的操作，会触发值改变的信号的产生
self.attributes_changed_datashow_signal.emit()
else:
pass
def attributes_data_show(self):
global data
try:
self.attributes_data = data.copy()
self.attribute_value = self.comboBox_attributes.currentText()
self.valuecounts = self.attributes_data[
self.attribute_value].value_counts()
self.attributes_data = pd.DataFrame({
# 'No.': [x for x in range(len(self.valuecounts))], #当前属性不同标签值的个数
'Label': [y for y in self.valuecounts.index],
'Count': [self.valuecounts[z] for z in self.valuecounts.index]
})
self.attributesmodel_changed_datashow_signal.emit(
self.attributes_data)
except Exception as e:
self.Warning_QMessageBox(str(e))
def showMatplotlib(self):
try:
self.X = list(self.attributes_data['Label'])
self.Y = list(self.attributes_data['Count'])
self.matplotlib.plot(range(len(self.X)), self.Y)
except Exception as e:
self.Warning_QMessageBox(str(e))
class myMainWindow(QMainWindow, Ui_MainWindow, QScrollArea):
def __init__(self):
super().__init__()
self.setAcceptDrops(True)
self.setupUi(self)
'''
以下是各按键与各函数的绑定
'''
#数据初始化
self.orignal_df = None
self.tempDF = None
#异常模块窗口初始化信息
self.modelseted = False
#数据摘要窗口初始化信息
self.tableViewSeted = False
self.setFixedSize(1068, 737)
self.setWindowTitle('Data Visualization')
self.setWindowIcon(
QIcon(r'Z:\Data_Visualization\venv\qrc\icons\icon.png'))
#self.progressBar.hide()
#openfile键与获取文件信息并展示函数的绑定
self.actionOpen_file.triggered.connect(self.getFile_toShow)
#save as csv file键与函数save as csv 文件的绑定
self.actionas_a_Csv_File.triggered.connect(self.File_save_as_csv)
#save as excel file 与 函数save as execl文件的绑定
self.actionas_a_Excel_file.triggered.connect(self.File_save_as_excel)
#退出键
self.actionExit.triggered.connect(self.close)
#删除所有问题行操作键与相关函数的绑定
self.actionDelete_problematic_rows.triggered.connect(
self.Quick_Operation_delete_rows)
#删除所有问题列操作键与相关函数的绑定
self.actionDelete_problematic_columns.triggered.connect(
self.Quick_Operation_delete_cols)
#删除重复值操作键与相关函数的绑定
self.actionDelete_duplicate_values.triggered.connect(
self.Quick_Operation_delete_duplicate_values)
#刷新所有窗口数据操作键与其相关函数的绑定
self.actionUpdating_all_the_Windows.triggered.connect(
self.Updating_all_windows)
#填充空缺值操作
self.actionFill_problematic_columns_with.triggered.connect(
self.FillnaWith)
#强制类型转换与相关ui界面的绑定
self.actionForced_type_Conversion.triggered.connect(
self.Forced_type_conversion)
#筛选操作的绑定
self.actionread_only.triggered.connect(self.Selection_read_only)
#筛选操作并替换原来的数据
self.actioninplace.triggered.connect(self.Selection_inplace)
#科学计算add与相关ui界面的绑定
self.actionadd.triggered.connect(self.Scientific_add)
#科学计算radd与相关ui界面的绑定
self.actionradd.triggered.connect(self.Scientific_radd)
#科学计算sub与相关ui界面的绑定
self.actionsub.triggered.connect(self.Scientific_sub)
#科学计算rsub与相关ui界面的绑定
self.actionrsub.triggered.connect(self.Scientific_rsub)
#科学计算div与相关ui界面的绑定
self.actiondiv.triggered.connect(self.Scientific_div)
#科学计算rdiv与相关ui界面的绑定
self.actionrdiv.triggered.connect(self.Scientific_rdiv)
#科学计算floordiv与相关ui界面的绑定
self.actionfloordiv.triggered.connect(self.Scientific_floordiv)
#科学计算rfloordiv与相关ui界面的绑定
self.actionrfloordiv.triggered.connect(self.Scientific_rfloordiv)
#科学计算pow与相关ui界面的绑定
self.actionpow.triggered.connect(self.Scientific_pow)
#科学计算rpow与相关ui界面的绑定
self.actionrpow.triggered.connect(self.Scientific_rpow)
#相关系数计算
self.actioncorr.triggered.connect(self.Scientific_corr)
#cov
self.actioncov.triggered.connect(self.Scientific_cov)
#绘画函数plot与相关函数绑定
self.actionplot.triggered.connect(self.Matplotlib_plot)
#散点图
self.actionscatter.triggered.connect(self.Matplotlib_scatter)
#饼图
self.actionpie.triggered.connect(self.Matplotlib_pie)
#柱状图
self.actionbar.triggered.connect(self.Matplotlib_bar)
#水平柱状图
self.actionbarh.triggered.connect(self.Matplotlib_barh)
#直方图
self.actionhist.triggered.connect(self.Matplotlib_hist)
#self.orignal_df[i[0]] = to_datetime(self.orignal_df[i[0]], errors='ignore', format=self.formatStr)
def Forced_type_conversion(self):
if self.orignal_df is not None:
self.ui = myTypeConversion(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(self.Forced_type_conversion_recive)
def Forced_type_conversion_recive(self, data):
#print(data)
try:
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].astype(
info[i[1]], errors='ignore')
self.Updating_all_windows()
except:
QMessageBox.information(self, '提示', '类型转换不支持', QMessageBox.Yes)
#print('end')
#print(self.orignal_df.dtypes)
def dragEnterEvent(self, event):
if event.mimeData().hasUrls():
event.accept()
else:
event.ignore()
def closeEvent(self, *args, **kwargs):
try:
self.ui.close()
except:
pass
finally:
self.close()
def dropEvent(self, event):
filename = event.mimeData().urls()[0].path()[1:]
#print(filename)
if filename[-4:] == '.csv' or filename[-4:] == 'xlsx' or filename[
-4:] == '.xls':
self.thread0 = File_read_thread(filename)
# 线程0文件读取完毕发送信号与receive以及viewFile函数绑定
self.thread0.fileReadDone[type(DataFrame())].connect(self.receive)
self.thread0.fileReadDone[type(DataFrame())].connect(self.viewFile)
self.thread0.start()
self.timer = QBasicTimer()
self.step = 0
self.progressBar = QProgressDialog(self)
self.progressBar.setCancelButton(None)
#self.progressBar.setWindowFlags(Qt.WindowCloseButtonHint)
self.progressBar.setWindowModality(Qt.WindowModal)
self.progressBar.setWindowTitle('文件读取中...')
self.progressBar.setLabelText('解析进行中，请稍候...')
self.progressBar.setRange(0, 100)
self.progressBar.show()
if self.timer.isActive():
self.timer.stop()
self.step = 0
else:
self.step = 0
self.timer.start(100, self)
else:
QMessageBox.warning(self, 'warning!', '文件格式不支持', QMessageBox.Yes,
QMessageBox.Yes)
def receive(self, df):
'''
接收线程发送的文件信息
:return:
'''
#self.orignal_df = self.thread0.df
self.orignal_df = df
self.progressBar.setValue(300)
self.timer.stop()
self.progressBar.close()
def Temp_data_init(self):
self.tempDF = None
def data_recovery(self):
if self.tempDF is not None:
self.orignal_df = self.tempDF
self.tempDF = None
self.Updating_all_windows()
def Fillna_data_recive(self, data):
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
if i[1] == 'Null':
self.orignal_df[i[0]].fillna(Numbertic(i[2]), inplace=True)
self.Updating_all_windows()
else:
if i[1] == 'mean':
self.orignal_df[i[0]].fillna(self.orignal_df[i[0]].mean(),
inplace=True)
self.Updating_all_windows()
elif i[1] == 'median':
self.orignal_df[i[0]].fillna(
self.orignal_df[i[0]].median(), inplace=True)
self.Updating_all_windows()
elif i[1] == 'max':
self.orignal_df[i[0]].fillna(self.orignal_df[i[0]].max(),
inplace=True)
self.Updating_all_windows()
elif i[1] == 'min':
self.orignal_df[i[0]].fillna(self.orignal_df[i[0]].min(),
inplace=True)
self.Updating_all_windows()
def Scientific_add(self):
if self.orignal_df is not None:
self.ui = myAdd(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(self.Scientific_data_add_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_add_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].add(Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_radd(self):
if self.orignal_df is not None:
self.ui = myrAdd(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(self.Scientific_data_radd_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_radd_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].radd(Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_sub(self):
if self.orignal_df is not None:
self.ui = mySub(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(self.Scientific_data_sub_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_sub_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].sub(Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_rsub(self):
if self.orignal_df is not None:
self.ui = myrSub(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(self.Scientific_data_rsub_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_rsub_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].rsub(Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_div(self):
if self.orignal_df is not None:
self.ui = myDiv(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(self.Scientific_data_div_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_div_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].div(Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_rdiv(self):
if self.orignal_df is not None:
self.ui = myrDiv(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(self.Scientific_data_rdiv_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_rdiv_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].rdiv(Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_floordiv(self):
if self.orignal_df is not None:
self.ui = myFloordiv(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(
self.Scientific_data_floordiv_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_floordiv_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].floordiv(
Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_rfloordiv(self):
if self.orignal_df is not None:
self.ui = myrFloordiv(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(
self.Scientific_data_rfloordiv_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_rfloordiv_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].rfloordiv(
Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_pow(self):
if self.orignal_df is not None:
self.ui = myPow(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(self.Scientific_data_pow_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_pow_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].pow(Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_rpow(self):
if self.orignal_df is not None:
self.ui = myrPow(self.orignal_df)
self.ui.show()
self.ui.okButtonPressed.connect(self.Scientific_data_rpow_recive)
self.ui.applyButtonPressed.connect(self.Temp_data_init)
self.ui.cancalButtonPressed.connect(self.data_recovery)
def Scientific_data_rpow_recive(self, data):
#print(data)
if self.tempDF is None:
self.tempDF = self.orignal_df.copy()
for i in data:
self.orignal_df[i[0]] = self.orignal_df[i[0]].rpow(Numbertic(i[1]))
self.Updating_all_windows()
def Scientific_data_corr_recive(self, data):
#print(data)
result = self.orignal_df.corr(data[0])
self.ui = Corr_show(result)
self.ui.show()
def Scientific_corr(self):
if self.orignal_df is not None:
self.ui = myCorr()
self.ui.show()
self.ui.window.connect(self.Scientific_data_corr_recive)
def Scientific_cov(self):
if self.orignal_df is not None:
result = self.orignal_df.cov()
self.ui = Cov_show(result)
self.ui.show()
def Selection_data_recive(self, data):
'''
:param data: 返回操作信息
:return: None
'''
#判空操作，若非空则执行
#print(data)
tempData = [] #各逻辑操作返回的数据集
for i in data:
tempData.append(sih(self.orignal_df, i))
#对数据集进行取交集操作
#print(tempData)
try:
if type(str()) in list(map(type, tempData)):
ok = QMessageBox.warning(self.ui, 'waring', '参数错误,\n请检查输入的参数。',
QMessageBox.Yes | QMessageBox.Yes,
QMessageBox.No)
if ok:
self.ui.settableWidget()
else:
if len(tempData) == 1 and tempData[0].empty:
QMessageBox.information(self, '提示', '数据集为空',
QMessageBox.Yes, QMessageBox.Yes)
elif len(tempData) == 1 and not tempData[0].empty:
if type(self.tempDF) is type(None):
self.tempDF = self.orignal_df
self.orignal_df = tempData[0]
self.Updating_all_windows()
else:
self.orignal_df = tempData[0]
self.Updating_all_windows()
else:
if type(self.tempDF) is type(None):
self.tempDF = self.orignal_df
if True in list(map(lambda x: x.empty, tempData)):
#print(list(map(lambda x:x.empty,tempData)))
QMessageBox.information(self, '提示', '数据集为空',
QMessageBox.Yes,
QMessageBox.Yes)
else:
for i in range(len(tempData) - 1):
self.orignal_df = merge(tempData[i],
tempData[i + 1],
how='inner')
if self.orignal_df.empty:
QMessageBox.information(self, '提示', '数据集为空',
QMessageBox.Yes,
QMessageBox.Yes)
else:
self.Updating_all_windows()
except Exception as e:
QMessageBox.warning(self.ui, 'waring', str(e.args),
QMessageBox.Yes, QMessageBox.Yes)
def Selection_inplace(self):
if self.orignal_df is not None:
self.ui = mySelection(self.orignal_df)
self.ui.show()
self.ui.SelectionSignal[type(list())].connect(
self.Selection_data_recive)
def FillnaWith(self):
if self.orignal_df is None:
QMessageBox.information(self, '提示', '未读取数据', QMessageBox.Yes,
QMessageBox.Yes)
else:
self.ui = myFillnaWith(self.orignal_df)
if len(self.ui.columns) == 0:
QMessageBox.information(self, '提示', '数据不存在异常值',
QMessageBox.Yes, QMessageBox.Yes)
self.ui.close()
else:
self.ui.show()
self.ui.FillNaSignal.connect(self.Fillna_data_recive)
self.ui.CancalButtonPressed.connect(self.data_recovery)
self.ui.ExitButtonPressed.connect(self.Temp_data_init)
def Selection_read_only(self):
if self.orignal_df is not None:
self.ui = mySelection(self.orignal_df)
self.ui.show()
self.ui.SelectionSignal[type(list())].connect(
self.Selection_data_recive)
self.ui.closeButtonPressed.connect(self.data_recovery)
def timerEvent(self, *args, **kwargs):
self.progressBar.setValue(self.step)
if self.step >= 100:
self.timer.stop()
self.step = 0
return
elif self.step < 99:
self.step += 1
def getFile_toShow(self):
'''
获取文件信息并显示
:return:
'''
FileName = QFileDialog.getOpenFileName(
filter="Data file(*.csv *.xlsx *.xls)")
#print(FileName)
#print(FileName[0])
if FileName[0] != '':
self.thread0 = File_read_thread(FileName[0])
#线程0文件读取完毕发送信号与receive以及viewFile函数绑定
self.thread0.fileReadDone[type(DataFrame())].connect(self.receive)
self.thread0.fileReadDone[type(DataFrame())].connect(self.viewFile)
self.thread0.start()
self.timer = QBasicTimer()
self.step = 0
self.progressBar = QProgressDialog(self)
self.progressBar.setCancelButton(None)
#self.progressBar.setWindowFlags(Qt.WindowCloseButtonHint)
self.progressBar.setWindowModality(Qt.WindowModal)
self.progressBar.setWindowTitle('文件读取中...')
self.progressBar.setLabelText('解析进行中，请稍候...')
self.progressBar.setRange(0, 100)
self.progressBar.show()
if self.timer.isActive():
self.timer.stop()
self.step = 0
else:
self.step = 0
self.timer.start(100, self)
def viewFile(self):
'''
展示文件的函数
:return:
'''
if self.orignal_df.empty:
QMessageBox.warning(self, '警告', '文件为空文件！', QMessageBox.Yes,
QMessageBox.Yes)
self.ErrorDataview_show()
self.tableView_show()
self.label_2.close()
self.widget = self.pandastablewidget
self.model = DataFrameModel()
self.widget.setViewModel(self.model)
self.model.setDataFrame(self.orignal_df)
def File_save_as_csv(self):
'''
文件保存为csv文件
:return:
'''
if self.orignal_df is None:
QMessageBox.information(self, '提示', '未读取文件', QMessageBox.Yes,
QMessageBox.Yes)
else:
FileName = QFileDialog.getSaveFileUrl(filter="Data file(*.csv )")
print(str(FileName[0].toString())[8:])
if str(FileName[0].toString()) != '':
try:
self.thread2 = File_Save_as_csv(
self.orignal_df,
str(FileName[0].toString())[8:])
self.thread2.start()
except Exception as e:
QMessageBox.information(self, '提示', '文件保存失败！',
QMessageBox.Yes, QMessageBox.Yes)
#text, ok = QInputDialog.getText(self, 'save as csv', '请输入文件名称')
#if ok:
#FileName = text + '.csv'
#self.thread2 = File_Save_as_csv(self.orignal_df, FileName)
#self.thread2.start()
def File_save_as_excel(self):
'''
文件保存为excel文件
:return:
'''
if self.orignal_df is None:
QMessageBox.information(self, '提示', '未读取文件', QMessageBox.Yes,
QMessageBox.Yes)
else:
FileName = QFileDialog.getSaveFileUrl(
filter="Data file(*.xls *xlsx)")
#print(str(FileName[0].toString())[8:])
if str(FileName[0].toString()) != '':
try:
self.thread2 = File_Save_as_excel(
self.orignal_df,
str(FileName[0].toString())[8:])
self.thread2.start()
except Exception as e:
QMessageBox.information(self, '提示', '文件保存失败！',
QMessageBox.Yes, QMessageBox.Yes)
def tableView_show(self):
'''
数据摘要显示
:return:
'''
if not self.orignal_df.empty:
self.tableViewSeted = True
col, row = self.orignal_df.describe().shape
self.tableView.model = QStandardItemModel(col, row)
self.tableView.model.setHorizontalHeaderLabels(
list(self.orignal_df.describe().columns))
self.tableView.model.setVerticalHeaderLabels(
list(self.orignal_df.describe().index))
self.tableView.setModel(self.tableView.model)
self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
#self.tableView.horizontalHeader().setStretchLastSection(True)
#self.tableView.verticalHeader().setStretchLastSection(True)
index_x = 0
for row in self.orignal_df.describe().iteritems():
index_x += 1
index_y = 0
for s in list(row[1]):
index_y += 1
newItem = QStandardItem(str(s))
self.tableView.model.setItem(index_y - 1, index_x - 1,
newItem)
elif self.orignal_df.empty and self.tableViewSeted == True:
self.tableView.model.clear()
def ErrorDataview_show(self):
if not self.orignal_df[self.orignal_df.isnull().values ==
True].empty and not self.orignal_df.empty:
self.modelseted = True
data = self.orignal_df[self.orignal_df.isnull().values == True]
col, row = data.shape
self.tableView_2.model = QStandardItemModel(col, row)
self.tableView_2.model.setHorizontalHeaderLabels(list(
data.columns))
self.tableView_2.model.setVerticalHeaderLabels(
list((map(str, data.index))))
self.tableView_2.setModel(self.tableView_2.model)
self.tableView_2.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.tableView_2.setSelectionBehavior(QAbstractItemView.SelectRows)
#self.tableView_2.horizontalHeader().setStretchLastSection(True)
#self.tableView_2.verticalHeader().setStretchLastSection(True)
index_x = 0
for row in data.iteritems():
index_x += 1
index_y = 0
for s in list(row[1]):
index_y += 1
newItem = QStandardItem(str(s))
self.tableView_2.model.setItem(index_y - 1, index_x - 1,
newItem)
elif self.orignal_df.empty and self.modelseted == True:
self.tableView_2.model.clear()
elif self.orignal_df[self.orignal_df.isnull().values ==
True].empty and not self.orignal_df.empty:
if self.modelseted == True:
self.tableView_2.model.clear()
else:
pass
def Quick_Operation_delete_rows(self):
if self.orignal_df is None:
QMessageBox.information(self, '提示', '未读取数据', QMessageBox.Yes,
QMessageBox.Yes)
else:
self.orignal_df = self.orignal_df.dropna()
self.Updating_all_windows()
def Quick_Operation_delete_cols(self):
if self.orignal_df is None:
QMessageBox.information(self, '提示', '未读取数据', QMessageBox.Yes,
QMessageBox.Yes)
else:
self.orignal_df = self.orignal_df.dropna(axis=1)
self.Updating_all_windows()
def Quick_Operation_delete_duplicate_values(self):
if self.orignal_df is None:
QMessageBox.information(self, '提示', '未读取数据', QMessageBox.Yes,
QMessageBox.Yes)
else:
self.orignal_df = self.orignal_df.drop_duplicates()
self.Updating_all_windows()
def Updating_all_windows(self):
if self.orignal_df is None:
QMessageBox.information(self, '提示', '未读取数据', QMessageBox.Yes,
QMessageBox.Yes)
else:
self.ErrorDataview_show()
self.tableView_show()
self.widget = self.pandastablewidget
self.model = DataFrameModel()
self.widget.setViewModel(self.model)
self.model.setDataFrame(self.orignal_df)
def Matplotlib_plot_show(self, data):
self.ui = QmyMainWindow(data[0], data[1], data[2])
self.ui.show()
def Matplotlib_plot(self):
if self.orignal_df is not None:
self.ui = Dia(self.orignal_df[self.orignal_df.describe().columns])
self.ui.show()
self.ui.window.connect(self.Matplotlib_plot_show)
def Matplotlib_scatter_show(self, data):
self.ui = QmyMainWindow1(data[0], data[1], data[2])
self.ui.show()
def Matplotlib_scatter(self):
if self.orignal_df is not None:
self.ui = Dia1(self.orignal_df[self.orignal_df.describe().columns])
self.ui.show()
self.ui.window.connect(self.Matplotlib_scatter_show)
def Matplotlib_pie_show(self, data):
#print(data)
self.ui = QmyMainWindow2(data[0], data[1], data[2], data[3], data[4],
self.orignal_df)
self.ui.show()
def Matplotlib_pie(self):
if self.orignal_df is not None:
self.ui = Dia2(self.orignal_df)
self.ui.show()
self.ui.window.connect(self.Matplotlib_pie_show)
def Matplotlib_bar_show(self, data):
#print(data)
self.ui = QmyMainWindow3(data, self.orignal_df)
self.ui.show()
def Matplotlib_bar(self):
if self.orignal_df is not None:
self.ui = Dia3(self.orignal_df)
self.ui.show()
self.ui.window.connect(self.Matplotlib_bar_show)
def Matplotlib_barh_show(self, data):
#print(data)
if self.orignal_df is not None:
self.ui = QmyMainWindow4(data, self.orignal_df)
self.ui.show()
def Matplotlib_barh(self):
if self.orignal_df is not None:
self.ui = Dia4(self.orignal_df)
self.ui.show()
self.ui.window.connect(self.Matplotlib_barh_show)
def Matplotlib_hist_show(self, data):
#print(data)
if self.orignal_df is not None:
self.ui = QmyMainWindow5(data, self.orignal_df)
self.ui.show()
def Matplotlib_hist(self):
if self.orignal_df is not None:
self.ui = Dia5(self.orignal_df)
self.ui.show()
self.ui.window.connect(self.Matplotlib_hist_show)
class BaiduMapCrawler_main(QtWidgets.QMainWindow, Ui_MainWindow):
def __init__(self):
super(BaiduMapCrawler_main, self).__init__()
self.setupUi(self)
self.setWindowTitle("百度地图数据采集工具_by_夜雨微寒")
self.setWindowIcon(QtGui.QIcon(r'./resource/image/spider3.png'))
self.result = []
'''初始化pandasqt'''
widget = self.pandastablewidget
# 设置qtpandas.DataFrameMode()
self.model = DataFrameModel()
widget.setViewModel(self.model)
# 初始化省份
self.comboBox_prov.clear()
self.comboBox_prov.addItem('请选择')
for k, v in province_dict.items():
self.comboBox_prov.addItem(v, k)
# 定义按下 开始抓取 按钮后的操作
@pyqtSlot()
def on_pushButton_crawl_clicked(self):
self.crawl()
@pyqtSlot()
def on_pushButton_clear_clicked(self):
self.result = []
self.listWidget.clear()
df = self.result_to_dataframe()
self.model.setDataFrame(df)
# 初始化城市
@pyqtSlot(int)
def on_comboBox_prov_activated(self, index):
self.comboBox_city.addItem('请选择')
key = int(self.comboBox_prov.itemData(index))
self.comboBox_city.clear()
city_info_dict = cities_dict[key]
for k, v in city_info_dict.items():
self.comboBox_city.addItem(v, k)
def insert2list(self, crawl_result):
for item in crawl_result:
self.result.append(item)
self.listWidget_show()
# 将抓取的poi名称在listWidget中显示
def listWidget_show(self):
df = self.result_to_dataframe()
self.model.setDataFrame(df)
self.listWidget.clear()
for item in self.result:
self.listWidget.addItem(item[2])
def show_crawl_progress(self, string):
self.label_progress.setText(string)
def display_poi_is_crawling(self, poi_str):
# print(poi_str)
df = DataFrame([poi_str], columns=['poi_name', '区域'])
print(df)
self.model.setDataFrame(df)
def crawl(self):
keyword = self.lineEdit_keyword.text().strip()
province = self.comboBox_prov.currentText()
print(province)
city = self.comboBox_city.currentText()
print(city)
print(city, keyword)
if keyword.replace(' ', '') == '':
return
# 获取百度地图城市代码
try:
citycode = city_code_dict[city]
except BaseException:
citycode = ''
print(citycode)
try:
self.crawler = BdMapCrawler(keyword, province, city, citycode)
self.crawler.finished_signal.connect(self.insert2list)
self.crawler.page_done_signal.connect(self.show_crawl_progress)
self.crawler.poi_is_crawling.connect(self.display_poi_is_crawling)
self.crawler.start()
except Exception as e:
print(e)
def result_to_dataframe(self):
columns_name = [
'省', '市', '百度商户名', '地址', '行业', '电话', '所属区域', '详细地址', '曾用名',
'行业及代码', 'aoi', '经纬度范围', '经度', '纬度'
]
# 将列表数据转化为dataframe
df = DataFrame(self.result, columns=columns_name)
return df
def write_to_excel(self, df):
filename = time.strftime("%Y%m%d_%Hh_%Mm_%Ss") + '.xlsx'
df.to_excel(filename, index=False)
print("抓取数据已经写入到本地！")
def openFile(self):
DirName = QtWidgets.QFileDialog.getExistingDirectory(
self.Filedialog, "浏览文件", "C:", QtWidgets.QFileDialog.ShowDirsOnly)
self.DirlineEdit.setText(DirName)
def saveFileToExcel(self):
try:
fileName = self.FilelineEdit.text()
if fileName == '':
fileName = time.strftime("%Y%m%d_%Hh_%Mm_%Ss") + '.xlsx'
fileName_path = self.DirlineEdit.text()
fileName_path = os.path.join(fileName_path, '%s.xlsx' % fileName)
print(fileName_path)
if os.path.isfile(fileName_path):
existMessage = QtWidgets.QMessageBox.warning(
self, '文件已存在', fileName + ' 已存在,是否覆盖该文件',
QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No)
if existMessage == QtWidgets.QMessageBox.No:
return
df = self.result_to_dataframe()
df.to_excel(r'%s' % fileName_path, index=False)
SucessMessage = QtWidgets.QMessageBox.information(
self, '导出EXCEL', '导出成功', QtWidgets.QMessageBox.Yes)
if SucessMessage == QtWidgets.QMessageBox.Yes:
self.Filedialog.close()
except Exception as e:
QtWidgets.QMessageBox.information(self, '导出失败', '%s' % e,
QtWidgets.QMessageBox.Yes)
class PandasWindow(QMainWindow, Ui_MainWindow):
"""
pandas_pyqt.
"""
def __init__(self, parent=None):
"""
Constructor构造函数
@父控件的参数父引用
@type QWidget
"""
super(PandasWindow, self).__init__(parent)
self.setupUi(self)
# 初始化pandasqt
# widget = self.pandastablewidget
# self.widget.resize(600, 500) # 如果对部件尺寸大小不满意可以在这里设置
self.model = DataFrameModel() # 设置新的模型
self.widget.setViewModel(self.model)
# MySQL法连接数据库,读取数据需要转换
# conn = pymysql.connect(
# host='localhost', port=3308, user='******', password='******', db='mrp', charset='utf8')
sql = 'select * from ht'
# self.df = pd.read_sql(sql, conn)
# 通过sqlalchemy.create_engine建立连接引擎.echo=True，会显示在加载数据库所执行的SQL语句，可不选此参数，默认为False
# engine = create_engine("mysql+pymysql://root:[email protected]:3308/mrp?charset=utf8")
# engine = create_engine('mysql+pymysql://root:[email protected]:3308/mrp', encoding='utf-8', echo=True)
# engine = create_engine(
# "mysql+pymysql://{}:{}@{}/{}".format('root', 'root', 'localhost:3308', 'mrp'), encoding='utf-8')
# engine = create_engine(
# "mysql+mysqlconnector://{}:{}@{}/{}".format
# ('root', 'root', 'localhost:3308', 'mrp'), encoding='utf-8')
engine = create_engine("mysql+mysqlconnector://root:root@localhost:3308/mrp?charset=utf8")
# con = engine.connect() # 创建连接
# SQLAlchemy法,查询数据并转为pandas.DataFrame，指定DataFrame的index为数据库中的生产编号字段
self.df = pd.read_sql(sql, engine)
# self.df.head()
# print(self.df.head())
# self.new_df = self.df[["生产编号", "序号", "名称", "制造标准", "规格型号", "材质", "数量", "已机加数", "机加部门", "机加日期", "生产状态"]]
# print(self.df)
# 读取excel表格数据
# self.df = pd.read_excel(r'C:/Users/Administrator/Desktop/报价模板1.xlsx', encoding='utf-8')
# self.df_original = self.df.copy() # 备份原始数据
#创建与数据库的会话session class ,注意,这里返回给session的是个class类,不是实例
self.session = sessionmaker(bind=engine)() # 另一种方法
# DBSession = sessionmaker(bind=engine) #创建用于数据库session对象
# self.session = DBSession() #这里才是生成session实例可以理解为cursor
self.model.setDataFrame(self.df)
self.widget.tableView.horizontalHeader().setStretchLastSection(True)
# self.widget.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.widget.tableView.resizeColumnsToContents()
self.widget.tableView.resizeRowsToContents()
# ==============将mysql数据库中文件查询生成dataframe格式文件，写入tableWidget文件=====================================
# if self.new_df.empty==False:
# row_num3=self.new_df.shape[0] # 取行数
# col_num3=self.new_df.shape[1] # 取列数
# self.tableWidget.setRowCount(row_num3)
# self.tableWidget.setColumnCount(col_num3)
# for i in range(row_num3):
# for j in range(col_num3):
# temp_db_a2=self.new_df.iloc[i, j]
# db_a2=QTableWidgetItem(str(temp_db_a2))
# self.tableWidget.setItem(i, j, db_a2)
# # 取列名称,设为列表
# col_name3=list(self.new_df.columns)
# # print(col_name3)
# self.tableWidget.setHorizontalHeaderLabels(col_name3) # 设置标题列名
# =========================常用pandas代码==============================
# 测试插入时间
# start = time.clock()
# end = time.clock()
# print('[插入多个执行] 使用时间:', end-start)
# print(self.df.describe()) # 基础数据集统计结果
# self.df.info() # 基础数据集特征信息
#替换丢失的数据 用“value”的值替换“to_replace”中给出的值。
# self.df.replace(to_replace=None, value=None)
# 将对象类型转换为 float 将对象类型转换为数字型以便计算（如果它们是字符串的话）
# self.pd.to_numeric(self.df["feature_name"], errors='coerce')
# 将数据转换为 Numpy 数组
# self.df.as_matrix()
# 获取数据的头“n”行
# self.df.head(n)
# 按特征名称获取数据
# self.df.loc[feature_name]
# 这个函数将数据里“height”一列中的所有值乘以2
# self.df["height"].apply(*lambda* height: 2 * height)
# 重命名数据列 将数据的第3列重命名为“size”
# self.df.rename(columns = {self.df.columns[2]:'size'}, inplace=True)
# 单独提取某一列
# self.df["name"].unique()
# 访问子数据 从数据中选择“name”和“size”两列
# new_df = self.df[["name", "size"]]
# self.df.columns # 列出列名称
# 数据之和df.sum()
# 数据中的最小值df.min()
# 数据中的最大值df.max()
# 最小值的索引df.idxmin()
# 最大值的索引df.idxmax()
# 数据统计信息，有四分位数，中位数等df.describe()
# 平均值df.mean()
# 中位数值df.median()
# '数量'列之和df.sum()
# d = self.df.loc[:, '数量'].sum()
# print('d' +str(d))
# self.df.apply(sum)
# column_sum = self.df.iloc[:,j].sum()
# 对数据进行排序
# self.df.sort_values(ascending = False)
# 布尔索引 过滤“size”的数据列，以显示等于5的值：
# self.df[self.df["size"] == 5]
# 选择某值 选择“size”列的第一行：
# self.df.loc([0], ['size'])
"""
# 查询数据并转为pandas.DataFrame，指定DataFrame的index为数据库中的id字段
df = pd.read_sql('SELECT * FROM students', engine, index_col='id')
print(df)
# 修改DataFrame中的数据（移除age列）
dft = df.drop(['age'], axis=1)
# 将修改后的数据追加至原表,index=False代表不插入索引，因为数据库中id字段为自增字段
dft.to_sql('students', engine, index=False, if_exists='append')
"""
# 定义函数，自动输出DataFrme数据写入mysql的数类型字典表,配合to_sql方法使用(注意，其类型只能是SQLAlchemy type )
def mapping_df_types(df):
"""自动获取DataFrme各列的数据类型，生成字典。"""
dtypedict = {}
for i, j in zip(df.columns, df.dtypes):
if "object" in str(j):
dtypedict.update({i: VARCHAR(256)})
if "float" in str(j):
dtypedict.update({i: NUMBER(19,8)})
if "int" in str(j):
dtypedict.update({i: VARCHAR(19)})
return dtypedict
# data_base.to_sql('stock_class',engine,index=False,if_exists='append',dtype=dtypedict,chunksize=100)参考
@pyqtSlot()
def on_pushButton_clicked(self):
"""
初始化pandas
"""
self.model.setDataFrame(self.df_original)
@pyqtSlot()
def on_pushButton_2_clicked(self):
"""
保存数据
"""
# self.df.to_excel(r'./data/fund_data_new.xlsx')
# print(self.df)
engine = create_engine("mysql+pymysql://root:[email protected]:3308/mrp?charset=utf8")
# index=False自增型关键字用false默认为True，指定DataFrame的index是否一同写入数据库
self.new_df = self.df[["生产编号", "序号", "处理数量", "处理工艺"]]
print(self.new_df)
# 去掉ID主键,因ID是自增型
# self.new_df.to_sql(name='ht', con=engine, if_exists='fail', index=False)
# sql_1 = "INSERT INTO ht (生产编号,序号,处理日期,处理数量,处理工艺) VALUES(%s, %s, %s, %s, %s) \
# ON DUPLICATE KEY UPDATE 处理日期=VALUES(处理日期),处理数量=VALUES(处理数量),处理工艺=VALUES(处理工艺)"
for row in range(0, len(self.new_df)):
row_data = table_class(column_1=self.new_df.ix[i]['生产编号'],
column_2=self.new_df.ix[i]['序号'],
)
self.session.merge(row_data)
self.session.commit()
