1
# -*- coding: utf-8 -*-
3
# Copyright © 2014 Spyder development team
4
# Licensed under the terms of the New BSD License
6
# DataFrameModel is based on the class ArrayModel from array editor
7
# and the class DataFrameModel from the pandas project.
8
# Present in pandas.sandbox.qtpandas in v0.13.1
9
# Copyright (c) 2011-2012, Lambda Foundry, Inc.
10
# and PyData Development Team All rights reserved
13
Pandas DataFrame Editor Dialog
16
from spyderlib.qt.QtCore import (QAbstractTableModel, Qt, QModelIndex,
18
from spyderlib.qt.QtGui import (QDialog, QTableView, QColor, QGridLayout,
19
QDialogButtonBox, QHBoxLayout, QPushButton,
20
QCheckBox, QMessageBox, QInputDialog,
21
QLineEdit, QApplication, QMenu)
22
from spyderlib.qt.compat import to_qvariant, from_qvariant
23
from spyderlib.utils.qthelpers import (qapplication, get_icon, create_action,
24
add_actions, keybinding)
26
from spyderlib.baseconfig import _
27
from spyderlib.guiconfig import get_font
28
from spyderlib.py3compat import io, is_text_string, to_text_string
29
from spyderlib.utils import encoding
30
from spyderlib.widgets.arrayeditor import get_idx_rect
32
from pandas import DataFrame, TimeSeries
35
# Supported Numbers and complex numbers
36
_sup_nr = (float, int, np.int64, np.int32)
37
_sup_com = (complex, np.complex64, np.complex128)
38
# Used to convert bool intrance to false since bool('False') will return True
39
_bool_false = ['false', '0']
42
def bool_false_check(value):
44
Used to convert bool intrance to false since any string in bool('')
47
if value.lower() in _bool_false:
52
def global_max(col_vals, index):
53
"""Returns the global maximum and minimum"""
54
max_col, min_col = zip(*col_vals)
55
return max(max_col), min(min_col)
58
class DataFrameModel(QAbstractTableModel):
59
""" DataFrame Table Model"""
60
def __init__(self, dataFrame, format="%.3g", parent=None):
61
QAbstractTableModel.__init__(self)
65
self.bgcolor_enabled = True
66
self.complex_intran = None
68
huerange = [.66, .99] # Hue
69
self.sat = .7 # Saturation
71
self.alp = .6 # Alpha-channel
72
self.hue0 = huerange[0]
73
self.dhue = huerange[1]-huerange[0]
74
self.max_min_col = None
75
self.max_min_col_update()
76
self.colum_avg_enabled = True
79
def max_min_col_update(self):
80
"""Determines the maximum and minimum number in each column"""
81
max_r = self.df.max(numeric_only=True)
82
min_r = self.df.min(numeric_only=True)
83
self.max_min_col = list(zip(max_r, min_r))
84
if len(self.max_min_col) != self.df.shape[1]:
85
# Then it contain complex numbers or other types
86
float_intran = self.df.applymap(lambda e: isinstance(e, _sup_nr))
87
self.complex_intran = self.df.applymap(lambda e:
88
isinstance(e, _sup_com))
89
mask = float_intran & (~ self.complex_intran)
90
df_abs = self.df[self.complex_intran].abs()
91
max_c = df_abs.max(skipna=True)
92
min_c = df_abs.min(skipna=True)
93
df_real = self.df[mask]
94
max_r = df_real.max(skipna=True)
95
min_r = df_real.min(skipna=True)
96
self.max_min_col = list(zip(DataFrame([max_c,
97
max_r]).max(skipna=True),
99
min_r]).min(skipna=True)))
100
self.max_min_col = [[vmax, vmin-1] if vmax == vmin else [vmax, vmin]
101
for vmax, vmin in self.max_min_col]
103
def get_format(self):
104
"""Return current format"""
105
# Avoid accessing the private attribute _format from outside
108
def set_format(self, format):
109
"""Change display format"""
110
self._format = format
113
def bgcolor(self, state):
114
"""Toggle backgroundcolor"""
115
self.bgcolor_enabled = state > 0
118
def colum_avg(self, state):
119
"""Toggle backgroundcolor"""
120
self.colum_avg_enabled = state > 0
121
if self.colum_avg_enabled:
122
self.return_max = lambda col_vals, index: col_vals[index]
124
self.return_max = global_max
127
def headerData(self, section, orientation, role=Qt.DisplayRole):
128
"""Set header data"""
129
if role != Qt.DisplayRole:
132
if orientation == Qt.Horizontal:
136
return to_qvariant(to_text_string(self.df.columns.tolist()
141
def get_bgcolor(self, index):
142
"""Background color depending on value"""
143
column = index.column()
145
color = QColor(Qt.lightGray)
148
if not self.bgcolor_enabled:
150
value = self.get_value(index.row(), column-1)
151
if isinstance(value, _sup_com):
155
if isinstance(value, _sup_nr+_sup_com) and self.bgcolor_enabled:
156
vmax, vmin = self.return_max(self.max_min_col, column-1)
157
hue = self.hue0 + self.dhue*(vmax-color_func(value)) / (vmax-vmin)
158
hue = float(abs(hue))
159
color = QColor.fromHsvF(hue, self.sat, self.val, self.alp)
160
elif is_text_string(value):
161
color = QColor(Qt.lightGray)
164
color = QColor(Qt.lightGray)
168
def get_value(self, row, column):
169
"""Returns the value of the DataFrame"""
170
# To increase the performance iat is used but that requires error
171
# handeling when index contains nan, so fallback uses iloc
173
value = self.df.iat[row, column]
175
value = self.df.iloc[row, column]
178
def data(self, index, role=Qt.DisplayRole):
180
if not index.isValid():
182
if role == Qt.DisplayRole or role == Qt.EditRole:
183
column = index.column()
186
return to_qvariant(to_text_string(self.df.index.tolist()[row]))
188
value = self.get_value(row, column-1)
189
if isinstance(value, float):
190
return to_qvariant(self._format % value)
193
return to_qvariant(to_text_string(value))
194
except UnicodeDecodeError:
195
return to_qvariant(encoding.to_unicode(value))
196
elif role == Qt.BackgroundColorRole:
197
return to_qvariant(self.get_bgcolor(index))
198
elif role == Qt.FontRole:
199
return to_qvariant(get_font('arrayeditor'))
202
def sort(self, column, order=Qt.AscendingOrder):
203
"""Overriding sort method"""
204
if self.complex_intran is not None:
205
if self.complex_intran.any(axis=0).iloc[column-1]:
206
QMessageBox.critical(self.dialog, "Error",
207
"TypeError error: no ordering "
208
"relation is defined for complex numbers")
212
self.df.sort(columns=self.df.columns[column-1],
213
ascending=order, inplace=True)
215
self.df.sort_index(inplace=True, ascending=order)
216
except TypeError as e:
217
QMessageBox.critical(self.dialog, "Error",
218
"TypeError error: %s" % str(e))
224
def flags(self, index):
226
if index.column() == 0:
227
return Qt.ItemIsEnabled | Qt.ItemIsSelectable
228
return Qt.ItemFlags(QAbstractTableModel.flags(self, index) |
231
def setData(self, index, value, role=Qt.EditRole, change_type=None):
232
"""Cell content change"""
233
column = index.column()
236
if change_type is not None:
238
value = self.data(index, role=Qt.DisplayRole)
239
val = from_qvariant(value, str)
240
if change_type is bool:
241
val = bool_false_check(val)
242
self.df.iloc[row, column - 1] = change_type(val)
244
self.df.iloc[row, column - 1] = change_type('0')
246
val = from_qvariant(value, str)
247
current_value = self.get_value(row, column-1)
248
if isinstance(current_value, bool):
249
val = bool_false_check(val)
250
if isinstance(current_value, ((bool,) + _sup_nr + _sup_com)) or \
251
is_text_string(current_value):
253
self.df.iloc[row, column-1] = current_value.__class__(val)
254
except ValueError as e:
255
QMessageBox.critical(self.dialog, "Error",
256
"Value error: %s" % str(e))
259
QMessageBox.critical(self.dialog, "Error",
260
"The type of the cell is not a supported "
263
self.max_min_col_update()
270
def rowCount(self, index=QModelIndex()):
271
"""DataFrame row number"""
272
return self.df.shape[0]
274
def columnCount(self, index=QModelIndex()):
275
"""DataFrame column number"""
276
shape = self.df.shape
277
# This is done to implement timeseries
284
class DataFrameView(QTableView):
285
"""Data Frame view class"""
286
def __init__(self, parent, model):
287
QTableView.__init__(self, parent)
290
self.sort_old = [None]
291
self.header_class = self.horizontalHeader()
292
self.connect(self.header_class,
293
SIGNAL("sectionClicked(int)"), self.sortByColumn)
294
self.menu = self.setup_menu()
296
def sortByColumn(self, index):
297
""" Implement a Column sort """
298
if self.sort_old == [None]:
299
self.header_class.setSortIndicatorShown(True)
300
sort_order = self.header_class.sortIndicatorOrder()
301
if not self.model().sort(index, sort_order):
302
if len(self.sort_old) != 2:
303
self.header_class.setSortIndicatorShown(False)
305
self.header_class.setSortIndicator(self.sort_old[0],
308
self.sort_old = [index, self.header_class.sortIndicatorOrder()]
310
def contextMenuEvent(self, event):
311
"""Reimplement Qt method"""
312
self.menu.popup(event.globalPos())
315
def setup_menu(self):
316
"""Setup context menu"""
317
copy_action = create_action(self, _( "Copy"),
318
shortcut=keybinding("Copy"),
319
icon=get_icon('editcopy.png'),
321
context=Qt.WidgetShortcut)
322
functions = ((_("To bool"), bool), (_("To complex"), complex),
323
(_("To int"), int), (_("To float"), float),
324
(_("To str"), to_text_string))
325
types_in_menu = [copy_action]
326
for name, func in functions:
327
types_in_menu += [create_action(self, name,
328
triggered=lambda func=func:
329
self.change_type(func),
330
context=Qt.WidgetShortcut)]
332
add_actions(menu, types_in_menu)
335
def change_type(self, func):
336
"""A function that changes types of cells"""
338
index_list = self.selectedIndexes()
339
[model.setData(i, '', change_type=func) for i in index_list]
341
def copy(self, index=False, header=False):
342
"""Copy text to clipboard"""
344
col_min, col_max) = get_idx_rect(self.selectedIndexes())
349
if col_max == 0: # To copy indices
350
contents = '\n'.join(map(str, df.index.tolist()[slice(row_min,
352
else: # To copy DataFrame
353
if df.shape[0] == row_max+1 and row_min == 0:
355
obj = df.iloc[slice(row_min, row_max+1), slice(col_min-1, col_max)]
356
output = io.StringIO()
357
obj.to_csv(output, sep='\t', index=index, header=header)
358
contents = output.getvalue()
360
clipboard = QApplication.clipboard()
361
clipboard.setText(contents)
364
class DataFrameEditor(QDialog):
365
""" Data Frame Editor Dialog """
366
def __init__(self, parent=None):
367
QDialog.__init__(self, parent)
368
# Destroying the C++ object right after closing the dialog box,
369
# otherwise it may be garbage-collected in another QThread
370
# (e.g. the editor's analysis thread in Spyder), thus leading to
371
# a segmentation fault on UNIX or an application crash on Windows
372
self.setAttribute(Qt.WA_DeleteOnClose)
373
self.is_time_series = False
376
def setup_and_check(self, data, title=''):
378
Setup DataFrameEditor:
379
return False if data is not supported, True otherwise
382
for dim in data.shape:
385
answer = QMessageBox.warning(self, _("%s editor")
386
% data.__class__.__name__,
387
_("Opening and browsing this "
389
"Do you want to continue anyway?")
390
% data.__class__.__name__,
391
QMessageBox.Yes | QMessageBox.No)
392
if answer == QMessageBox.No:
395
self.layout = QGridLayout()
396
self.setLayout(self.layout)
397
self.setWindowIcon(get_icon('arredit.png'))
399
title = to_text_string(title) # in case title is not a string
401
title = _("%s editor") % data.__class__.__name__
402
if isinstance(data, TimeSeries):
403
self.is_time_series = True
404
data = data.to_frame()
406
self.setWindowTitle(title)
407
self.resize(600, 500)
409
self.dataModel = DataFrameModel(data, parent=self)
410
self.dataTable = DataFrameView(self, self.dataModel)
412
self.layout.addWidget(self.dataTable)
413
self.setLayout(self.layout)
414
self.setMinimumSize(400, 300)
415
# Make the dialog act as a window
416
self.setWindowFlags(Qt.Window)
417
btn_layout = QHBoxLayout()
419
btn = QPushButton(_("Format"))
420
# disable format button for int type
421
btn_layout.addWidget(btn)
422
self.connect(btn, SIGNAL("clicked()"), self.change_format)
423
btn = QPushButton(_('Resize'))
424
btn_layout.addWidget(btn)
425
self.connect(btn, SIGNAL("clicked()"),
426
self.dataTable.resizeColumnsToContents)
428
bgcolor = QCheckBox(_('Background color'))
429
bgcolor.setChecked(self.dataModel.bgcolor_enabled)
430
bgcolor.setEnabled(self.dataModel.bgcolor_enabled)
431
self.connect(bgcolor, SIGNAL("stateChanged(int)"),
432
self.change_bgcolor_enable)
433
btn_layout.addWidget(bgcolor)
435
self.bgcolor_global = QCheckBox(_('Column min/max'))
436
self.bgcolor_global.setChecked(self.dataModel.colum_avg_enabled)
437
self.bgcolor_global.setEnabled(not self.is_time_series and
438
self.dataModel.bgcolor_enabled)
439
self.connect(self.bgcolor_global, SIGNAL("stateChanged(int)"),
440
self.dataModel.colum_avg)
441
btn_layout.addWidget(self.bgcolor_global)
443
btn_layout.addStretch()
444
bbox = QDialogButtonBox(QDialogButtonBox.Ok | QDialogButtonBox.Cancel)
445
self.connect(bbox, SIGNAL("accepted()"), SLOT("accept()"))
446
self.connect(bbox, SIGNAL("rejected()"), SLOT("reject()"))
447
btn_layout.addWidget(bbox)
449
self.layout.addLayout(btn_layout, 2, 0)
453
def change_bgcolor_enable(self, state):
455
This is implementet so column min/max is only active when bgcolor is
457
self.dataModel.bgcolor(state)
458
self.bgcolor_global.setEnabled(not self.is_time_series and state > 0)
460
def change_format(self):
461
"""Change display format"""
462
format, valid = QInputDialog.getText(self, _('Format'),
463
_("Float formatting"),
465
self.dataModel.get_format())
471
QMessageBox.critical(self, _("Error"),
472
_("Format (%s) is incorrect") % format)
474
self.dataModel.set_format(format)
477
"""Return modified Dataframe -- this is *not* a copy"""
478
# It is import to avoid accessing Qt C++ object as it has probably
479
# already been destroyed, due to the Qt.WA_DeleteOnClose attribute
480
df = self.dataModel.get_data()
481
if self.is_time_series:
487
def test_edit(data, title="", parent=None):
488
"""Test subroutine"""
489
dlg = DataFrameEditor(parent=parent)
490
if dlg.setup_and_check(data, title=title) and dlg.exec_():
491
return dlg.get_value()
498
"""DataFrame editor test"""
499
from numpy import nan
507
[np.random.rand(3, 3), "Unkown type"],
508
["Large value", 100],
511
index=['a', 'b', nan, nan, nan, 'c',
512
"Test global max", 'd'],
513
columns=[nan, 'Type'])
516
out = test_edit(df1.iloc[0])
518
df1 = DataFrame(np.random.rand(100001, 10))
519
# Sorting large DataFrame takes time
520
df1.sort(columns=[0, 1], inplace=True)
523
out = test_edit(TimeSeries(np.arange(10)))
528
if __name__ == '__main__':
529
_app = qapplication()