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()