6AxtXEh6

1#!/usr/bin/env python
2"""
3    Implements the core pandastable classes.
4    Created Jan 2014
5    Copyright (C) Damien Farrell
6
7    This program is free software; you can redistribute it and/or
8    modify it under the terms of the GNU General Public License
9    as published by the Free Software Foundation; either version 3
10    of the License, or (at your option) any later version.
11
12    This program is distributed in the hope that it will be useful,
13    but WITHOUT ANY WARRANTY; without even the implied warranty of
14    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15    GNU General Public License for more details.
16
17    You should have received a copy of the GNU General Public License
18    along with this program; if not, write to the Free Software
19    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
20"""
21
22from __future__ import absolute_import, division, print_function
23try:
24    from tkinter import *
25    from tkinter.ttk import *
26    from tkinter import filedialog, messagebox, simpledialog
27except:
28    from Tkinter import *
29    from ttk import *
30    import tkFileDialog as filedialog
31    import tkSimpleDialog as simpledialog
32    import tkMessageBox as messagebox
33from tkinter import font
34import math, time
35import os, types
36import string, copy
37import platform
38import numpy as np
39import pandas as pd
40from .data import TableModel
41from .headers import ColumnHeader, RowHeader, IndexHeader
42from .plotting import MPLBaseOptions, PlotViewer
43from .prefs import Preferences
44from .dialogs import ImportDialog
45from . import images, util
46from .dialogs import *
47
48themes = {'dark':{'cellbackgr':'gray25','grid_color':'gray50', 'textcolor':'#f2eeeb',
49                 'rowselectedcolor':'#ed9252', 'colselectedcolor':'#3d65d4'},
50          'bold':{'cellbackgr':'white','grid_color':'gray50', 'textcolor':'black',
51                 'rowselectedcolor':'yellow', 'colselectedcolor':'#e4e3e4'},
52          'default':{'cellbackgr':'#F4F4F3','grid_color':'#ABB1AD', 'textcolor':'black',
53                 'rowselectedcolor':'#E4DED4', 'colselectedcolor':'#e4e3e4'}
54         }
55
56prevData = ""
57
58class Table(Canvas):
59    """A tkinter class for providing table functionality.
60
61    Args:
62        parent: parent Frame
63        model: a TableModel with some data
64        dataframe: a pandas DataFrame
65        width: width of frame
66        height: height of frame
67        rows: number of rows if creating empty table
68        cols: number of columns if creating empty table
69        showtoolbar: whether to show the toolbar, default False
70        showstatusbar: whether to show the statusbar
71    """
72    __callback_fn = ''
73
74    def __init__(self, parent=None, model=None, dataframe=None,
75                   width=None, height=None,
76                   rows=20, cols=5, showtoolbar=False, showstatusbar=False, callbackfn="",
77                   **kwargs):
78
79        Canvas.__init__(self, parent, bg='white',
80                         width=width, height=height,
81                         relief=GROOVE,
82                         scrollregion=(0,0,300,200))
83        self.__callback_fn = callbackfn
84        self.parentframe = parent
85        #get platform into a variable
86        self.ostyp = util.checkOS()
87        self.platform = platform.system()
88        self.width = width
89        self.height = height
90        self.filename = None
91        self.showtoolbar = showtoolbar
92        self.showstatusbar = showstatusbar
93        self.set_defaults()
94
95        self.currentpage = None
96        self.navFrame = None
97        self.currentrow = 0
98        self.currentcol = 0
99        self.reverseorder = 0
100        self.startrow = self.endrow = None
101        self.startcol = self.endcol = None
102        self.allrows = False
103        self.multiplerowlist=[]
104        self.multiplecollist=[]
105        self.col_positions=[]
106        self.mode = 'normal'
107        self.editable = True
108        self.filtered = False
109        self.child = None
110        self.queryrow = 4
111        self.childrow = 5
112        self.loadPrefs()
113        self.currentdir = os.path.expanduser('~')
114        #set any options passed in kwargs to overwrite defaults and prefs
115        for key in kwargs:
116            self.__dict__[key] = kwargs[key]
117
118        if dataframe is not None:
119            self.model = TableModel(dataframe=dataframe)
120        elif model != None:
121            self.model = model
122        else:
123            self.model = TableModel(rows=rows,columns=cols)
124
125        self.rows = self.model.getRowCount()
126        self.cols = self.model.getColumnCount()
127        self.tablewidth = (self.cellwidth)*self.cols
128        self.doBindings()
129        self.parentframe.bind("<Destroy>", self.close)
130
131        #column specific actions, define for every column type in the model
132        #when you add a column type you should edit this dict
133        self.columnactions = {'text' : {"Edit":  'drawCellEntry' },
134                              'number' : {"Edit": 'drawCellEntry' }}
135        self.setFontSize()
136        self.plotted = False
137        self.importpath = None
138        self.prevdf = None
139        return
140
141    def close(self, evt=None):
142        if hasattr(self, 'parenttable'):
143            return
144        if hasattr(self, 'pf') and self.pf is not None:
145            self.pf.quit()
146        return
147
148    def set_defaults(self):
149        """Set default settings"""
150
151        self.cellwidth = 60
152        self.maxcellwidth = 300
153        self.mincellwidth = 30
154        self.rowheight = 20
155        self.horizlines = 1
156        self.vertlines = 1
157        self.autoresizecols = 1
158        self.inset = 2
159        self.x_start = 0
160        self.y_start = 1
161        self.linewidth = 1.0
162        self.thefont = ('Arial',12)
163        self.textcolor = 'black'
164        self.cellbackgr = '#F4F4F3'
165        self.entrybackgr = 'white'
166        self.grid_color = '#ABB1AD'
167        self.rowselectedcolor = '#E4DED4'
168        self.multipleselectioncolor = '#E0F2F7'
169        self.boxoutlinecolor = '#084B8A'
170        self.colselectedcolor = '#e4e3e4'
171        self.floatprecision = 0
172        self.showindex = False
173        self.columncolors = {}
174        #store general per column formatting as sub dicts
175        self.columnformats = {}
176        self.columnformats['alignment'] = {}
177        self.rowcolors = pd.DataFrame()
178        self.bg = Style().lookup('TLabel.label', 'background')
179        return
180
181    def setFontSize(self):
182        """Set font size to match font, we need to get rid of `size as
183            a separate variable?"""
184
185        if hasattr(self, 'thefont') and type(self.thefont) is tuple:
186            self.fontsize = self.thefont[1]
187        return
188
189    def setTheme(self, name='light'):
190        style = themes[name]
191        for s in style:
192            if s in self.__dict__:
193                self.__dict__[s] = style[s]
194        self.redraw()
195        return
196
197    def mouse_wheel(self, event):
198        """Handle mouse wheel scroll for windows"""
199
200        if event.num == 5 or event.delta == -120:
201            event.widget.yview_scroll(1, UNITS)
202            self.rowheader.yview_scroll(1, UNITS)
203        if event.num == 4 or event.delta == 120:
204            if self.canvasy(0) < 0:
205                return
206            event.widget.yview_scroll(-1, UNITS)
207            self.rowheader.yview_scroll(-1, UNITS)
208        self.redrawVisible()
209        return
210
211    def doBindings(self):
212        """Bind keys and mouse clicks, this can be overriden"""
213
214        self.bind("<Button-1>",self.handle_left_click)
215        self.bind("<Double-Button-1>",self.handle_double_click)
216        self.bind("<Control-Button-1>", self.handle_left_ctrl_click)
217        self.bind("<Shift-Button-1>", self.handle_left_shift_click)
218
219        self.bind("<ButtonRelease-1>", self.handle_left_release)
220        if self.ostyp=='mac':
221            #For mac we bind Shift, left-click to right click
222            self.bind("<Button-2>", self.handle_right_click)
223            self.bind('<Shift-Button-1>',self.handle_right_click)
224        else:
225            self.bind("<Button-3>", self.handle_right_click)
226
227        self.bind('<B1-Motion>', self.handle_mouse_drag)
228        #self.bind('<Motion>', self.handle_motion)
229
230        self.bind("<Control-c>", self.copy)
231        #self.bind("<Control-x>", self.deleteRow)
232        #self.bind_all("<Control-n>", self.addRow)
233        self.bind("<Delete>", self.clearData)
234        self.bind("<Control-v>", self.paste)
235        self.bind("<Control-a>", self.selectAll)
236        self.bind("<Control-f>",self.findText)
237
238        self.bind("<Right>", self.handle_arrow_keys)
239        self.bind("<Left>", self.handle_arrow_keys)
240        self.bind("<Up>", self.handle_arrow_keys)
241        self.bind("<Down>", self.handle_arrow_keys)
242        self.parentframe.master.bind_all("<KP_8>", self.handle_arrow_keys)
243        self.parentframe.master.bind_all("<Return>", self.handle_arrow_keys)
244        self.parentframe.master.bind_all("<Tab>", self.handle_arrow_keys)
245        #if 'windows' in self.platform:
246        self.bind("<MouseWheel>", self.mouse_wheel)
247        self.bind('<Button-4>', self.mouse_wheel)
248        self.bind('<Button-5>', self.mouse_wheel)
249        self.focus_set()
250        
251        return
252
253    def show(self, callback=None):
254        """Adds column header and scrollbars and combines them with
255           the current table adding all to the master frame provided in constructor.
256           Table is then redrawn."""
257
258        #Add the table and header to the frame
259        self.rowheader = RowHeader(self.parentframe, self)
260        self.tablecolheader = ColumnHeader(self.parentframe, self)
261        self.rowindexheader = IndexHeader(self.parentframe, self)
262        self.Yscrollbar = AutoScrollbar(self.parentframe,orient=VERTICAL,command=self.set_yviews)
263        self.Yscrollbar.grid(row=1,column=2,rowspan=1,sticky='news',pady=0,ipady=0)
264        self.Xscrollbar = AutoScrollbar(self.parentframe,orient=HORIZONTAL,command=self.set_xviews)
265        self.Xscrollbar.grid(row=2,column=1,columnspan=1,sticky='news')
266        self['xscrollcommand'] = self.Xscrollbar.set
267        self['yscrollcommand'] = self.Yscrollbar.set
268        self.tablecolheader['xscrollcommand'] = self.Xscrollbar.set
269        self.rowheader['yscrollcommand'] = self.Yscrollbar.set
270        self.parentframe.rowconfigure(1,weight=1)
271        self.parentframe.columnconfigure(1,weight=1)
272
273        self.rowindexheader.grid(row=0,column=0,rowspan=1,sticky='news')
274        self.tablecolheader.grid(row=0,column=1,rowspan=1,sticky='news')
275        self.rowheader.grid(row=1,column=0,rowspan=1,sticky='news')
276        self.grid(row=1,column=1,rowspan=1,sticky='news',pady=0,ipady=0)
277
278        self.adjustColumnWidths()
279        #bind redraw to resize, may trigger redraws when widgets added
280        self.parentframe.bind("<Configure>", self.resized) #self.redrawVisible)
281        self.tablecolheader.xview("moveto", 0)
282        self.xview("moveto", 0)
283        if self.showtoolbar == True:
284            self.toolbar = ToolBar(self.parentframe, self)
285            self.toolbar.grid(row=0,column=3,rowspan=2,sticky='news')
286        if self.showstatusbar == True:
287            self.statusbar = statusBar(self.parentframe, self)
288            self.statusbar.grid(row=3,column=0,columnspan=2,sticky='ew')
289        #self.redraw(callback=callback)
290        self.currwidth = self.parentframe.winfo_width()
291        self.currheight = self.parentframe.winfo_height()
292        if hasattr(self, 'pf'):
293            self.pf.updateData()
294        return
295
296    def resized(self, event):
297        """Check if size changed when event triggered to avoid unnecessary redraws"""
298
299        if self.currwidth !=self.parentframe.winfo_width() or \
300           self.currheight != self.parentframe.winfo_height():
301            self.redrawVisible()
302        self.currwidth = self.parentframe.winfo_width()
303        self.currheight = self.parentframe.winfo_height()
304
305    def remove(self):
306        """Close table frame"""
307
308        if hasattr(self, 'parenttable'):
309            self.parenttable.child.destroy()
310            self.parenttable.child = None
311            self.parenttable.plotted = 'main'
312        self.parentframe.destroy()
313        return
314
315    def getVisibleRegion(self):
316        """Get visible region of canvas"""
317
318        x1, y1 = self.canvasx(0), self.canvasy(0)
319        #w, h = self.winfo_width(), self.winfo_height()
320        #if w <= 1.0 or h <= 1.0:
321        w, h = self.master.winfo_width(), self.master.winfo_height()
322        x2, y2 = self.canvasx(w), self.canvasy(h)
323        return x1, y1, x2, y2
324
325    def getRowPosition(self, y):
326        """Set row position"""
327
328        h = self.rowheight
329        y_start = self.y_start
330        row = (int(y)-y_start)/h
331        if row < 0:
332            return 0
333        if row > self.rows:
334            row = self.rows
335        return int(row)
336
337    def getColPosition(self, x):
338        """Get column position at coord"""
339
340        x_start = self.x_start
341        w = self.cellwidth
342        i=0
343        col=0
344        for c in self.col_positions:
345            col = i
346            if c+w>=x:
347                break
348            i+=1
349        return int(col)
350
351    def getVisibleRows(self, y1, y2):
352        """Get the visible row range"""
353
354        start = self.getRowPosition(y1)
355        end = self.getRowPosition(y2)+1
356        if end > self.rows:
357            end = self.rows
358        return start, end
359
360    def getVisibleCols(self, x1, x2):
361        """Get the visible column range"""
362
363        start = self.getColPosition(x1)
364        end = self.getColPosition(x2)+1
365        if end > self.cols:
366            end = self.cols
367        return start, end
368
369    def redrawVisible(self, event=None, callback=None):
370        """Redraw the visible portion of the canvas. This is the core redraw
371        method. Refreshes all table elements. Called by redraw() method as shorthand.
372
373        Args:
374            event: tkinter event to trigger method, default None
375            callback: function to be called after redraw, default None
376        """
377
378        model = self.model
379        self.rows = len(self.model.df.index)
380        self.cols = len(self.model.df.columns)
381        if self.cols == 0 or self.rows == 0:
382            self.delete('entry')
383            self.delete('rowrect','colrect')
384            self.delete('currentrect','fillrect')
385            self.delete('gridline','text')
386            self.delete('multicellrect','multiplesel')
387            self.delete('colorrect')
388            self.setColPositions()
389            if self.cols == 0:
390                self.tablecolheader.redraw()
391            if self.rows == 0:
392                self.visiblerows = []
393                self.rowheader.redraw()
394            return
395        self.tablewidth = (self.cellwidth) * self.cols
396        self.configure(bg=self.cellbackgr)
397        self.setColPositions()
398
399        #are we drawing a filtered subset of the recs?
400        if self.filtered == True:
401            self.delete('colrect')
402
403        self.rowrange = list(range(0,self.rows))
404        self.configure(scrollregion=(0,0, self.tablewidth+self.x_start,
405                        self.rowheight*self.rows+10))
406
407        x1, y1, x2, y2 = self.getVisibleRegion()
408        startvisiblerow, endvisiblerow = self.getVisibleRows(y1, y2)
409        self.visiblerows = list(range(startvisiblerow, endvisiblerow))
410        startvisiblecol, endvisiblecol = self.getVisibleCols(x1, x2)
411        self.visiblecols = list(range(startvisiblecol, endvisiblecol))
412
413        self.drawGrid(startvisiblerow, endvisiblerow)
414        align = self.align
415        self.delete('fillrect')
416        bgcolor = self.cellbackgr
417        df = self.model.df
418
419        #st=time.time()
420        def set_precision(x, p):
421            if not pd.isnull(x):
422                if x<1:
423                    x = '{:.{}g}'.format(x, p)
424                else:
425                    x = '{:.{}f}'.format(x, p)
426            return x
427
428        prec = self.floatprecision
429        rows = self.visiblerows
430        for col in self.visiblecols:
431            coldata = df.iloc[rows,col]
432            colname = df.columns[col]
433            cfa = self.columnformats['alignment']
434            if colname in cfa:
435                align = cfa[colname]
436            else:
437                align = self.align
438            if prec != 0:
439                if coldata.dtype == 'float64':
440                    coldata = coldata.apply(lambda x: set_precision(x, prec), 1)
441                    #print (coldata)
442            coldata = coldata.astype(object).fillna('')
443            offset = rows[0]
444            for row in self.visiblerows:
445                text = coldata.iloc[row-offset]
446                self.drawText(row, col, text, align)
447
448        self.colorColumns()
449        self.colorRows()
450        self.tablecolheader.redraw()
451        self.rowheader.redraw(align=self.align)
452        self.rowindexheader.redraw()
453        self.drawSelectedRow()
454        self.drawSelectedRect(self.currentrow, self.currentcol)
455        #self.drawSelectedCol()
456        #for c in self.multiplecollist:
457        #    self.drawSelectedCol(c, delete=0)
458
459        if len(self.multiplerowlist)>1:
460            self.rowheader.drawSelectedRows(self.multiplerowlist)
461            self.drawMultipleRows(self.multiplerowlist)
462            self.drawMultipleCells()
463        #if callback is not None:
464        #    callback()
465        return
466
467    def redraw(self, event=None, callback=None):
468        """Redraw table"""
469
470        self.redrawVisible(event, callback)
471        if hasattr(self, 'statusbar'):
472            self.statusbar.update()
473        return
474
475    def redrawCell(self, row=None, col=None, recname=None, colname=None):
476        """Redraw a specific cell only"""
477
478        text = self.model.getValueAt(row,col)
479        self.delete('celltext'+str(col)+'_'+str(row))
480        self.drawText(row, col, text)
481        return
482
483    def setColumnColors(self, cols=None, clr=None):
484        """Set a column color and store it"""
485
486        if clr is None:
487            clr = self.getaColor('#dcf1fc')
488        if clr == None:
489            return
490        if cols == None:
491            cols = self.multiplecollist
492        colnames = self.model.df.columns[cols]
493        for c in colnames:
494            self.columncolors[c] = clr
495        self.redraw()
496        return
497
498    def colorColumns(self, cols=None, color='gray'):
499        """Color visible columns"""
500
501        if cols is None:
502            cols = self.visiblecols
503        self.delete('colorrect')
504        for c in cols:
505            colname = self.model.df.columns[c]
506            if colname in self.columncolors:
507                clr = self.columncolors[colname]
508                self.drawSelectedCol(c, delete=0, color=clr, tag='colorrect')
509        return
510
511    def setColorByMask(self, col, mask, clr):
512        """Color individual cells in a column using a mask."""
513
514        df = self.model.df
515        if len(self.rowcolors) == 0:
516            self.rowcolors = pd.DataFrame(index=range(len(df)))
517        rc = self.rowcolors
518        if col not in rc.columns:
519            rc[col] = pd.Series()
520        rc[col] = rc[col].where(-mask, clr)
521        #print (rc)
522        return
523
524    def colorRows(self):
525        """Color individual cells in column(s). Requires that the rowcolors
526         dataframe has been set. This needs to be updated if the index is reset"""
527
528        #if len(self.rowcolors==0):
529        #    return
530        df = self.model.df
531        rc = self.rowcolors
532        rows = self.visiblerows
533        offset = rows[0]
534        idx = df.index[rows]
535        for col in self.visiblecols:
536            colname = df.columns[col]
537            if colname in list(rc.columns):
538                colors = rc[colname].ix[idx]
539                for row in rows:
540                    clr = colors.iloc[row-offset]
541                    if not pd.isnull(clr):
542                        self.drawRect(row, col, color=clr, tag='colorrect', delete=1)
543        return
544
545    def setRowColors(self, rows=None, clr=None, cols=None):
546        """Set rows color from menu.
547        Args:
548            rows: row numbers to be colored
549            clr: color in hex
550            cols: column numbers, can also use 'all'
551        """
552
553        if clr is None:
554            clr = self.getaColor('#dcf1fc')
555        if clr == None:
556            return
557        if rows == None:
558            rows = self.multiplerowlist
559        df = self.model.df
560        idx = df.index[rows]
561        rc = self.rowcolors
562        if cols is None:
563            cols = self.multiplecollist
564        elif cols is 'all':
565            cols = range(len(df.columns))
566        colnames = df.columns[cols]
567        for c in colnames:
568            if c not in rc.columns:
569                rc[c] = pd.Series(np.nan,index=df.index)
570            rc[c][idx] = clr
571        self.redraw()
572        return
573
574    def setColorbyValue(self):
575        """Set row colors in a column by values"""
576
577        import pylab as plt
578        cmaps = sorted(m for m in plt.cm.datad if not m.endswith("_r"))
579        cols = self.multiplecollist
580        d = MultipleValDialog(title='color by value',
581                                initialvalues=[cmaps,1.0],
582                                labels=['colormap:','alpha:'],
583                                types=['combobox','string'],
584                                parent = self.parentframe)
585        if d.result == None:
586            return
587        cmap = d.results[0]
588        alpha =float(d.results[1])
589        df = self.model.df
590        for col in cols:
591            colname = df.columns[col]
592            x = df[colname]
593            clrs = self.values_to_colors(x, cmap, alpha)
594            clrs = pd.Series(clrs,index=df.index)
595            rc = self.rowcolors
596            rc[colname] = clrs
597        self.redraw()
598        return
599
600    def values_to_colors(self, x, cmap='jet', alpha=1):
601        """Convert columnn values to colors"""
602
603        import pylab as plt
604        import matplotlib as mpl
605        cmap = plt.cm.get_cmap(cmap)
606        #if x.dtype in ['int','float64']:
607        if x.dtype in ['object']:#,'category']:
608            x = pd.Categorical(x).codes
609        x = (x-x.min())/(x.max()-x.min())
610        clrs = cmap(x)
611        clrs = mpl.colors.to_rgba_array(clrs, alpha)
612        clrs = [mpl.colors.rgb2hex(i) for i in clrs]
613        return clrs
614
615    def setAlignment(self, colnames=None):
616        """Set column alignments, overrides global value"""
617
618        cols = self.multiplecollist
619        df = self.model.df
620        cf = self.columnformats
621        cfa = cf['alignment']
622        vals = ['w','e','center']
623        d = MultipleValDialog(title='set alignment',
624                                initialvalues=[vals],
625                                labels=['Align:'],
626                                types=['combobox'],
627                                parent = self.parentframe)
628        if d.result == None:
629            return
630        aln = d.results[0]
631        for col in cols:
632            colname = df.columns[col]
633            cfa[colname] = aln
634        self.redraw()
635        return
636
637    def getScale(self):
638        try:
639            fontsize = self.thefont[1]
640        except:
641            fontsize = self.fontsize
642        scale = 8.5 * float(fontsize)/9
643        return scale
644
645    def setWrap(self):
646        ch=self.tablecolheader
647        if ch.wrap is False:
648            ch.wrap = True
649        else:
650            ch.wrap = False
651        self.redraw()
652        return
653
654    def zoomIn(self):
655        """Zoom in, increases font and row heights."""
656
657        self.fontsize = self.fontsize+1
658        self.rowheight += 2
659        self.tablecolheader.height +=1
660        self.thefont = (self.thefont[0],self.fontsize)
661        self.adjustColumnWidths()
662        self.redraw()
663        return
664
665    def zoomOut(self):
666        """Zoom out, decreases font and row heights."""
667
668        self.fontsize = self.fontsize-1
669        self.rowheight -= 2
670        self.tablecolheader.height -=1
671        self.thefont = (self.thefont[0],self.fontsize)
672        self.adjustColumnWidths()
673        self.redraw()
674        return
675
676    def expandColumns(self):
677        """Reduce column widths"""
678
679        self.cellwidth +=10
680        widths = self.model.columnwidths
681        for c in widths:
682            widths[c] += 10
683        self.redraw()
684        return
685
686    def contractColumns(self):
687        """Reduce column widths"""
688
689        self.cellwidth -=10
690        widths = self.model.columnwidths
691        for c in widths:
692            widths[c] -= 10
693        self.redraw()
694        return
695
696    def adjustColumnWidths(self, limit=30):
697        """Optimally adjust col widths to accomodate the longest entry
698            in each column - usually only called on first redraw.
699        Args:
700            limit: don't resize for large number of columns
701            """
702
703        try:
704            fontsize = self.thefont[1]
705        except:
706            fontsize = self.fontsize
707        scale = self.getScale()
708        if self.cols > 30:
709            return
710        for col in range(self.cols):
711            colname = self.model.getColumnName(col)
712            if colname in self.model.columnwidths:
713                w = self.model.columnwidths[colname]
714            else:
715                w = self.cellwidth
716            l = self.model.getlongestEntry(col)
717            txt = ''.join(['X' for i in range(l+1)])
718            tw,tl = util.getTextLength(txt, self.maxcellwidth,
719                                       font=self.thefont)
720            #print (col,txt,l,tw)
721            if tw >= self.maxcellwidth:
722                tw = self.maxcellwidth
723            elif tw < self.cellwidth:
724                tw = self.cellwidth
725            self.model.columnwidths[colname] = tw
726        return
727
728    def autoResizeColumns(self):
729        """Automatically set nice column widths and draw"""
730
731        self.adjustColumnWidths()
732        self.redraw()
733        return
734
735    def setColPositions(self):
736        """Determine current column grid positions"""
737
738        df = self.model.df
739        self.col_positions=[]
740        w = self.cellwidth
741        x_pos = self.x_start
742        self.col_positions.append(x_pos)
743        for col in range(self.cols):
744            try:
745                colname = df.columns[col].encode('utf-8','ignore').decode('utf-8')
746            except:
747                colname = str(df.columns[col])
748            if colname in self.model.columnwidths:
749                x_pos = x_pos+self.model.columnwidths[colname]
750            else:
751                x_pos = x_pos+w
752            self.col_positions.append(x_pos)
753        self.tablewidth = self.col_positions[len(self.col_positions)-1]
754        return
755
756    def sortTable(self, columnIndex=None, ascending=1, index=False):
757        """Sort rows based on currently selected columns"""
758
759        df = self.model.df
760        if columnIndex == None:
761            columnIndex = self.multiplecollist
762        if isinstance(columnIndex, int):
763            columnIndex = [columnIndex]
764        #assert len(columnIndex) < len(df.columns)
765        if index == True:
766            df.sort_index(inplace=True)
767        else:
768            colnames = list(df.columns[columnIndex])
769            for col in colnames:
770                if df[col].dtype is 'category':
771                    print (df[col].cats)
772            try:
773                df.sort_values(by=colnames, inplace=True, ascending=ascending)
774            except Exception as e:
775                print('could not sort')
776                print(e)
777        self.redraw()
778        return
779
780    def sortColumnIndex(self):
781        """Sort the column header by the current rows values"""
782
783        cols = self.model.df.columns
784        #get only sortable cols
785        temp = self.model.df.convert_objects(convert_numeric=True)
786        temp = temp.select_dtypes(include=['int','float'])
787        rowindex = temp.index[self.currentrow]
788        row = temp.ix[rowindex]
789        #add unsortable cols to end of new ordered ones
790        newcols = list(temp.columns[row.argsort()])
791        a = list(set(cols) - set(newcols))
792        newcols.extend(a)
793        self.model.df = self.model.df.reindex(columns=newcols)
794        self.redraw()
795        return
796
797    def groupby(self, colindex):
798        """Group by"""
799
800        grps = self.model.groupby(colindex)
801        return
802
803    def setindex(self):
804        """Set indexes"""
805
806        cols = self.multiplecollist
807        self.model.setindex(cols)
808        if self.model.df.index.name is not None:
809            self.showIndex()
810        self.setSelectedCol(0)
811        self.update_rowcolors()
812        self.redraw()
813        #self.drawSelectedCol()
814        if hasattr(self, 'pf'):
815            self.pf.updateData()
816        return
817
818    def resetIndex(self):
819        """Reset index and redraw row header"""
820
821        self.storeCurrent()
822        df=self.model.df
823        drop = False
824        if df.index.name is None or df.index.names[0] is None:
825            drop = messagebox.askyesno("Reset Index", "Drop the index?",
826                                      parent=self.parentframe)
827        self.model.df.reset_index(drop=drop, inplace=True)
828        self.update_rowcolors()
829        self.redraw()
830        #self.drawSelectedCol()
831        if hasattr(self, 'pf'):
832            self.pf.updateData()
833        self.tableChanged()
834        return
835
836    def flattenIndex(self):
837        """FLatten multiindex"""
838
839        df = self.model.df
840        df.columns = df.columns.get_level_values(0)
841        #self.model.df
842        self.redraw()
843        if hasattr(self, 'pf'):
844            self.pf.updateData()
845        return
846
847    def copyIndex(self):
848        """Copy index to a column"""
849
850        self.model.copyIndex()
851        self.redraw()
852        return
853
854    def renameIndex(self, ):
855        """Rename the row index"""
856
857        n = self.model.df.index.name
858        name = simpledialog.askstring("New index name",
859                                      "New name:",initialvalue=n,
860                                       parent=self.parentframe)
861        if name:
862            self.model.df.index.name = name
863            self.rowindexheader.redraw()
864        return
865
866    def showIndex(self):
867        """Show the row index"""
868
869        self.showindex = True
870        return
871
872    def update_rowcolors(self):
873        """Update row colors if present"""
874
875        df = self.model.df
876        if len(self.rowcolors) == len(df):
877            self.rowcolors.set_index(df.index, inplace=True)
878        return
879
880    def set_xviews(self,*args):
881        """Set the xview of table and col header"""
882
883        self.xview(*args)
884        self.tablecolheader.xview(*args)
885        self.redrawVisible()
886        return
887
888    def set_yviews(self,*args):
889        """Set the xview of table and row header"""
890
891        self.yview(*args)
892        self.rowheader.yview(*args)
893        self.redrawVisible()
894        return
895
896    def addRow(self):
897        """Insert a new row"""
898
899        row = self.getSelectedRow()
900        key = self.model.addRow(row)
901        self.redraw()
902        return
903
904    def addRows(self, num=None):
905        """Add new rows"""
906
907        if num == None:
908            num = simpledialog.askinteger("Now many rows?",
909                                            "Number of rows:",initialvalue=1,
910                                             parent=self.parentframe)
911        if not num:
912            return
913        self.storeCurrent()
914        keys = self.model.autoAddRows(num)
915        self.redraw()
916        return
917
918    def addColumn(self, newname=None):
919        """Add a new column"""
920
921        if newname == None:
922            coltypes = ['object','float64']
923            d = MultipleValDialog(title='New Column',
924                                    initialvalues=(coltypes, ''),
925                                    labels=('Column Type','Name'),
926                                    types=('combobox','string'),
927                                    parent = self.parentframe)
928            if d.result == None:
929                return
930            else:
931                dtype = d.results[0]
932                newname = d.results[1]
933
934        df = self.model.df
935        if newname != None:
936            if newname in self.model.df.columns:
937                messagebox.showwarning("Name exists",
938                                        "Name already exists!",
939                                        parent=self.parentframe)
940            else:
941                self.storeCurrent()
942                self.model.addColumn(newname, dtype)
943                self.parentframe.configure(width=self.width)
944                self.redraw()
945                self.tableChanged()
946        return
947
948    def deleteRow(self):
949        """Delete a row"""
950
951        if len(self.multiplerowlist)>1:
952            n = messagebox.askyesno("Delete",
953                                      "Delete selected rows?",
954                                      parent=self.parentframe)
955            if n == True:
956                self.storeCurrent()
957                rows = self.multiplerowlist
958                self.model.deleteRows(rows)
959                self.setSelectedRow(0)
960                self.clearSelected()
961                self.redraw()
962        else:
963            n = messagebox.askyesno("Delete",
964                                      "Delete this row?",
965                                      parent=self.parentframe)
966            if n:
967                self.storeCurrent()
968                row = self.getSelectedRow()
969                self.model.deleteRows([row])
970                self.setSelectedRow(row-1)
971                self.clearSelected()
972                self.redraw()
973        return
974
975    def duplicateRows(self):
976        """Make copy of rows"""
977
978        rows = self.multiplerowlist
979        df = self.model.df
980        d = df.iloc[rows]
981        self.model.df = pd.concat([df, d])
982        self.redraw()
983        return
984
985    def deleteColumn(self):
986        """Delete currently selected column(s)"""
987
988        n =  messagebox.askyesno("Delete",
989                                   "Delete Column(s)?",
990                                   parent=self.parentframe)
991        if not n:
992            return
993        self.storeCurrent()
994        cols = self.multiplecollist
995        self.model.deleteColumns(cols)
996        self.setSelectedCol(0)
997        self.redraw()
998        #self.drawSelectedCol()
999        self.tableChanged()
1000        return
1001
1002    def copyColumn(self):
1003        """Copy a column"""
1004
1005        col = self.currentcol
1006        df = self.model.df
1007        name = df.columns[col]
1008
1009        new = simpledialog.askstring("New name",
1010                                     "New name:",initialvalue=name+'1',
1011                                     parent=self.parentframe)
1012        if new is None:
1013            return
1014        df[new] = df[name]
1015        self.placeColumn(new, name)
1016        #self.redraw()
1017        self.tableChanged()
1018        return
1019
1020    def moveColumns(self, names=None, pos='start'):
1021        """Move column(s) to start/end, used for large tables"""
1022
1023        df = self.model.df
1024        if names is None:
1025            cols = self.multiplecollist
1026            names = df.columns[cols]
1027        m = df[names]
1028        df.drop(labels=names, axis=1,inplace=True)
1029        if pos == 'start':
1030            self.model.df = m.join(df)
1031        else:
1032            self.model.df = df.join(m)
1033        self.redraw()
1034        self.tableChanged()
1035        return
1036
1037    def tableChanged(self):
1038        """Callback to be used when dataframe changes so that other
1039            widgets and data can be updated"""
1040
1041        self.updateFunctions()
1042        self.updateWidgets()
1043        if hasattr(self, 'pf'):
1044            self.pf.updateData()
1045        return
1046
1047    def storeCurrent(self):
1048        """Store current version of the table before a major change is made"""
1049
1050        self.prevdf = self.model.df.copy()
1051        return
1052
1053    def saveRow(self):
1054        """Save the edited row """ 
1055        columnCount = self.model.getColumnCount()
1056        #import pdb; pdb.set_trace()
1057        val = 0
1058        for i in range(columnCount):
1059            cn = self.model.getColumnName(i)
1060            if cn=='id':
1061                val = i
1062                break
1063        rowId = self.getSelectedRow()
1064        lst = []
1065        for i in range(val, columnCount):
1066            lst.append( self.model.getValueAt(rowId, i) )
1067        #print( lst )
1068        return self.__callback_fn(lst)
1069
1070    def undo(self):
1071        """Undo last major table change"""
1072
1073        if self.prevdf is None:
1074            return
1075        self.model.df = self.prevdf
1076        self.redraw()
1077        self.prevdf = None
1078        self.updateModel(self.model)
1079        return
1080
1081    def deleteCells(self, rows, cols, answer=None):
1082        """Clear the cell contents"""
1083
1084        if answer == None:
1085            answer =  messagebox.askyesno("Clear Confirm",
1086                                    "Clear this data?",
1087                                    parent=self.parentframe)
1088        if not answer:
1089            return
1090        self.storeCurrent()
1091        self.model.deleteCells(rows, cols)
1092        self.redraw()
1093        return
1094
1095    def clearData(self, evt=None):
1096        """Delete cells from gui event"""
1097
1098        if self.allrows == True:
1099            self.deleteColumn()
1100            return
1101        rows = self.multiplerowlist
1102        cols = self.multiplecollist
1103        self.deleteCells(rows, cols)
1104        return
1105
1106    def clearTable(self):
1107        """Make an empty table"""
1108        n =  messagebox.askyesno("Clear Confirm",
1109                                   "This will clear the entire table.\nAre you sure?",
1110                                   parent=self.parentframe)
1111        if not n:
1112            return
1113        self.storeCurrent()
1114        model = TableModel(pd.DataFrame())
1115        self.updateModel(model)
1116        self.redraw()
1117        return
1118
1119    def fillColumn(self):
1120        """Fill a column with a data range"""
1121
1122        dists = ['normal','gamma','uniform','random integer','logistic']
1123        d = MultipleValDialog(title='New Column',
1124                                initialvalues=(0,1,False,dists,1.0,1.0),
1125                                labels=('Low','High','Random Noise','Distribution','Mean','Std'),
1126                                types=('string','string','checkbutton','combobox','float','float'),
1127                                tooltips=('start value if filling with data',
1128                                          'end value if filling with data',
1129                                          'create random noise data in the ranges',
1130                                          'sampling distribution for noise',
1131                                          'mean/scale of distribution',
1132                                          'std dev./shape of distribution'),
1133                                parent = self.parentframe)
1134        if d.result == None:
1135            return
1136        else:
1137            low = d.results[0]
1138            high = d.results[1]
1139            random = d.results[2]
1140            dist = d.results[3]
1141            param1 = float(d.results[4])
1142            param2 = float(d.results[5])
1143
1144        df = self.model.df
1145        self.storeCurrent()
1146        if low != '' and high != '':
1147            try:
1148                low=float(low); high=float(high)
1149            except:
1150                return
1151        if random == True:
1152            if dist == 'normal':
1153                data = np.random.normal(param1, param2, len(df))
1154            elif dist == 'gamma':
1155                data = np.random.gamma(param1, param2, len(df))
1156            elif dist == 'uniform':
1157                data = np.random.uniform(low, high, len(df))
1158            elif dist == 'random integer':
1159                data = np.random.randint(low, high, len(df))
1160            elif dist == 'logistic':
1161                data = np.random.logistic(low, high, len(df))
1162        else:
1163            step = (high-low)/len(df)
1164            data = pd.Series(np.arange(low,high,step))
1165        col = df.columns[self.currentcol]
1166        df[col] = data
1167        self.redraw()
1168        self.tableChanged()
1169        return
1170
1171    def autoAddColumns(self, numcols=None):
1172        """Automatically add x number of cols"""
1173
1174        if numcols == None:
1175            numcols = simpledialog.askinteger("Auto add rows.",
1176                                                "How many empty columns?",
1177                                                parent=self.parentframe)
1178        self.model.auto_AddColumns(numcols)
1179        self.parentframe.configure(width=self.width)
1180        self.redraw()
1181        return
1182
1183    def setColumnType(self):
1184        """Change the column dtype"""
1185
1186        df = self.model.df
1187        col = df.columns[self.currentcol]
1188        coltypes = ['object','str','int','float64','category']
1189        curr = df[col].dtype
1190        d = MultipleValDialog(title='current type is %s' %curr,
1191                                initialvalues=[coltypes],
1192                                labels=['Type:'],
1193                                types=['combobox'],
1194                                parent = self.parentframe)
1195        if d.result == None:
1196            return
1197        t = d.results[0]
1198        try:
1199            self.model.df[col] = df[col].astype(t)
1200            self.redraw()
1201        except:
1202            print('failed')
1203        return
1204
1205    def findDuplicates(self):
1206        """Find duplicate rows"""
1207
1208        df = self.model.df
1209        keep = ['first','last']
1210        d = MultipleValDialog(title='Find duplicates',
1211                                initialvalues=[False,False,keep],
1212                                labels=['Remove duplicates:','Use selected columns:','Keep:'],
1213                                types=['checkbutton','checkbutton','combobox'],
1214                                parent = self.parentframe)
1215        if d.result == None:
1216            return
1217        remove = d.results[0]
1218        if d.results[1] is True:
1219            cols = self.multiplecollist
1220        else:
1221            cols = df.columns
1222        keep = d.results[2]
1223        new = df[df.duplicated(subset=cols,keep=keep)]
1224        if remove == True:
1225            self.model.df = df.drop_duplicates(subset=cols,keep=keep)
1226            self.redraw()
1227        if len(new)>0:
1228            self.createChildTable(new)
1229        return
1230
1231    def cleanData(self):
1232        """Deal with missing data"""
1233
1234        df = self.model.df
1235        cols = df.columns
1236        fillopts = ['fill scalar','','ffill','bfill','interpolate']
1237        d = MultipleValDialog(title='Clean Data',
1238                                initialvalues=(fillopts,'-','10',0,0,['any','all'],0,0,0),
1239                                labels=('Fill missing method:',
1240                                        'Fill empty data with:',
1241                                        'Limit gaps:',
1242                                        'Drop columns with null data:',
1243                                        'Drop rows with null data:',
1244                                        'Drop method:',
1245                                        'Drop duplicate rows:',
1246                                        'Drop duplicate columns:',
1247                                        'Round numbers:'),
1248                                types=('combobox','string','string','checkbutton',
1249                                       'checkbutton','combobox','checkbutton','checkbutton','string'),
1250                                parent = self.parentframe)
1251        if d.result == None:
1252            return
1253        self.storeCurrent()
1254        method = d.results[0]
1255        symbol = d.results[1]
1256        limit = int(d.results[2])
1257        dropcols = d.results[3]
1258        droprows = d.results[4]
1259        how = d.results[5]
1260        dropdups = d.results[6]
1261        dropdupcols = d.results[7]
1262        rounddecimals = int(d.results[8])
1263        if method == '':
1264            pass
1265        elif method == 'fill scalar':
1266            df = df.fillna(symbol)
1267        elif method == 'interpolate':
1268            df = df.interpolate()
1269        else:
1270            df = df.fillna(method=method, limit=limit)
1271        if dropcols == 1:
1272            df = df.dropna(axis=1,how=how)
1273        if droprows == 1:
1274            df = df.dropna(axis=0,how=how)
1275        if dropdups == 1:
1276            df = df.drop_duplicates()
1277        if dropdupcols == 1:
1278            df = df.loc[:,~df.columns.duplicated()]
1279        if rounddecimals != 0:
1280            df = df.round(rounddecimals)
1281        self.model.df = df
1282        self.redraw()
1283        return
1284
1285    def createCategorical(self):
1286        """Get a categorical column from selected"""
1287
1288        df = self.model.df
1289        col = df.columns[self.currentcol]
1290
1291        d = MultipleValDialog(title='Categorical data',
1292                                initialvalues=(0,'',0,'','',''),
1293                                labels=('Convert to integer codes:','Name:',
1294                                        'Get dummies:','Dummies prefix:',
1295                                        'Numerical bins:','Labels:'),
1296                                types=('checkbutton','string','checkbutton',
1297                                       'string','string','string'),
1298                                tooltips=(None, 'name if new column',
1299                                         'get dummy columns for fitting',None,
1300                                         'define bins edges for numerical data',
1301                                         'labels for bins'),
1302                                parent = self.parentframe)
1303        if d.result == None:
1304            return
1305        self.storeCurrent()
1306        convert = d.results[0]
1307        name = d.results[1]
1308        dummies = d.results[2]
1309        prefix = d.results[3]
1310        bins = d.results[4]
1311        binlabels = d.results[5]
1312
1313        if name == '':
1314            name = col
1315        if prefix == '':
1316            prefix=None
1317        if dummies == 1:
1318            new = pd.get_dummies(df[col], prefix=prefix)
1319            new.columns = new.columns.astype(str)
1320            self.model.df = pd.concat([df,new],1)
1321        elif convert == 1:
1322            df[name] = pd.Categorical(df[col]).codes
1323        elif bins != '':
1324            bins = [int(i) for i in bins.split(',')]
1325            if len(bins)==1:
1326                bins = int(bins[0])
1327                binlabels = list(string.ascii_uppercase[:bins])
1328            else:
1329                binlabels = binlabels.split(',')
1330            if name == col:
1331                name = col+'_binned'
1332            df[name] = pd.cut(df[col], bins, labels=binlabels)
1333        else:
1334            df[name] = df[col].astype('category')
1335        if name != col:
1336            self.placeColumn(name, col)
1337        else:
1338            self.redraw()
1339        return
1340
1341    def _getFunction(self, funcname, obj=None):
1342        if obj != None:
1343            func = getattr(obj, funcname)
1344            return func
1345        if hasattr(pd, funcname):
1346            func = getattr(pd, funcname)
1347        elif hasattr(np, funcname):
1348            func = getattr(np, funcname)
1349        else:
1350            return
1351        return func
1352
1353    def applyColumnFunction(self, evt=None):
1354        """Apply column wise functions, applies a calculation per row and
1355        ceates a new column."""
1356
1357        df = self.model.df
1358        cols = list(df.columns[self.multiplecollist])
1359
1360        funcs = ['mean','std','max','min','log','exp','log10','log2',
1361                 'round','floor','ceil','trunc',
1362                 'sum','subtract','divide','mod','remainder','convolve',
1363                 'negative','sign','power',
1364                 'sin','cos','tan','degrees','radians']
1365
1366        d = MultipleValDialog(title='Apply Function',
1367                                initialvalues=(funcs,'',False,'_x'),
1368                                labels=('Function:',
1369                                        'New column name:',
1370                                        'In place:',
1371                                        'New column suffix:'),
1372                                types=('combobox','string','checkbutton','string'),
1373                                tooltips=(None,
1374                                          'New column name',
1375                                          'Update in place',
1376                                          'suffix for new columns'),
1377                                parent = self.parentframe)
1378        if d.result == None:
1379            return
1380        self.storeCurrent()
1381        funcname = d.results[0]
1382        newcol = d.results[1]
1383        inplace = d.results[2]
1384        suffix = d.results[3]
1385
1386        func = getattr(np, funcname)
1387        if newcol == '':
1388            if len(cols)>3:
1389                s = ' %s cols' %len(cols)
1390            else:
1391                s =  '(%s)' %(','.join(cols))[:20]
1392            newcol = funcname + s
1393
1394        if funcname in ['subtract','divide','mod','remainder','convolve']:
1395            newcol = cols[0]+' '+ funcname +' '+cols[1]
1396            df[newcol] = df[cols[0]].combine(df[cols[1]], func=func)
1397        else:
1398            if inplace == True:
1399                newcol = cols[0]
1400            df[newcol] = df[cols].apply(func, 1)
1401        if inplace == False:
1402            self.placeColumn(newcol,cols[-1])
1403        else:
1404            self.redraw()
1405        return
1406
1407    def applyTransformFunction(self, evt=None):
1408        """Apply resampling and transform functions on a single column."""
1409
1410        df = self.model.df
1411        cols = list(df.columns[self.multiplecollist])
1412        col = cols[0]
1413
1414        funcs = ['rolling window','expanding','shift']
1415        winfuncs = ['mean','sum','median','min','max','std']
1416        wintypes = ['','boxcar','triang','blackman','hamming','bartlett',
1417                    'parzen','bohman','blackmanharris','nuttall','barthann']
1418        d = MultipleValDialog(title='Apply Function',
1419                                initialvalues=(funcs,winfuncs,wintypes,2,'_1',False),
1420                                labels=('Operation:','Window function:','Window type:',
1421                                'Window size:', 'New column suffix:','In place:'),
1422                                types=('combobox','combobox','combobox','integer','string','checkbutton'),
1423                                tooltips=(None,'Summary function for windowing','Window type',
1424                                        'Window size', 'Suffix for new column','Replace column'),
1425                                parent = self.parentframe)
1426        if d.result == None:
1427            return
1428
1429        self.storeCurrent()
1430        op = d.results[0]
1431        winfunc = d.results[1]
1432        wintype = d.results[2]
1433        window = int(d.results[3])
1434        suffix = d.results[4]
1435        inplace = d.results[5]
1436
1437        if wintype == '':
1438            wintype=None
1439        if op == 'rolling window':
1440            w = df[col].rolling(window=window, win_type=wintype, center=True)
1441            func = self._getFunction(winfunc, obj=w)
1442            new = func()
1443        elif op == 'expanding':
1444            func = self._getFunction(winfunc)
1445            new = df[col].expanding(2, center=True).apply(func)
1446        elif op == 'shift':
1447            new = df[col].shift(periods=1)
1448
1449        if new is None:
1450            return
1451        name = col+suffix
1452        if inplace == True:
1453            df[col] = new
1454        else:
1455            df[name] = new
1456            self.placeColumn(name, cols[-1])
1457        self.redraw()
1458        return
1459
1460    def resample(self):
1461        """Table time series resampling dialog. Should set a datetime index first."""
1462
1463        df = self.model.df
1464        if not isinstance(df.index, pd.DatetimeIndex):
1465            messagebox.showwarning("No datetime index", 'Your date/time column should be the index.',
1466                                   parent=self.parentframe)
1467            return
1468
1469        conv = ['start','end']
1470        freqs = ['M','W','D','H','min','S','Q','A','AS','L','U']
1471        funcs = ['mean','sum','count','max','min','std','first','last']
1472        d = MultipleValDialog(title='Resample',
1473                                initialvalues=(freqs,1,funcs,conv),
1474                                labels=('Frequency:','Periods','Function'),
1475                                types=('combobox','string','combobox'),
1476                                tooltips=('Unit of time e.g. M for months',
1477                                          'How often to group e.g. every 2 months',
1478                                          'Function to apply'),
1479                                parent = self.parentframe)
1480        if d.result == None:
1481            return
1482        freq = d.results[0]
1483        period = d.results[1]
1484        func = d.results[2]
1485
1486        rule = str(period)+freq
1487        new = df.resample(rule).apply(func)
1488        self.createChildTable(new, index=True)
1489        #df.groupby(pd.TimeGrouper(freq='M'))
1490        return
1491
1492    def valueCounts(self):
1493        """Value counts for column(s)"""
1494
1495        df = self.model.df
1496        cols = list(df.columns[self.multiplecollist])
1497        if len(cols) <2:
1498            col = cols[0]
1499            new = df[col].value_counts()
1500            df = pd.DataFrame(new)
1501        else:
1502            #if more than one col we use the first as an index and pivot
1503            df = df.pivot_table(index=cols[0], columns=cols[1:], aggfunc='size', fill_value=0).T
1504        self.createChildTable(df, index=True)
1505        return
1506
1507    def applyStringMethod(self):
1508        """Apply string operation to column(s)"""
1509
1510        df = self.model.df
1511        cols = list(df.columns[self.multiplecollist])
1512        col = cols[0]
1513        funcs = ['','split','strip','lstrip','lower','upper','title','swapcase','len',
1514                 'slice','replace','concat']
1515        d = MultipleValDialog(title='Apply Function',
1516                                initialvalues=(funcs,',',0,1,'','',1),
1517                                labels=('Function:',
1518                                        'Split sep:',
1519                                        'Slice start:',
1520                                        'Slice end:',
1521                                        'Pattern:',
1522                                        'Replace with:',
1523                                        'In place:'),
1524                                types=('combobox','string','int',
1525                                       'int','string','string','checkbutton'),
1526                                tooltips=(None,'separator for split or concat',
1527                                          'start index for slice',
1528                                          'end index for slice',
1529                                          'characters or regular expression for replace',
1530                                          'characters to replace with',
1531                                          'replace column'),
1532                                parent = self.parentframe)
1533        if d.result == None:
1534            return
1535        self.storeCurrent()
1536        func = d.results[0]
1537        sep = d.results[1]
1538        start = d.results[2]
1539        end = d.results[3]
1540        pat = d.results[4]
1541        repl = d.results[5]
1542        inplace = d.results[6]
1543        if func == 'split':
1544            new = df[col].str.split(sep).apply(pd.Series)
1545            new.columns = [col+'_'+str(i) for i in new.columns]
1546            self.model.df = pd.concat([df,new],1)
1547            self.redraw()
1548            return
1549        elif func == 'strip':
1550            x = df[col].str.strip()
1551        elif func == 'lstrip':
1552            x = df[col].str.lstrip(pat)
1553        elif func == 'upper':
1554            x = df[col].str.upper()
1555        elif func == 'lower':
1556            x = df[col].str.lower()
1557        elif func == 'title':
1558            x = df[col].str.title()
1559        elif func == 'swapcase':
1560            x = df[col].str.swapcase()
1561        elif func == 'len':
1562            x = df[col].str.len()
1563        elif func == 'slice':
1564            x = df[col].str.slice(start,end)
1565        elif func == 'replace':
1566            x = df[col].replace(pat, repl, regex=True)
1567        elif func == 'concat':
1568            x = df[col].str.cat(df[cols[1]].astype(str), sep=sep)
1569        if inplace == 0:
1570            newcol = col+'_'+func
1571        else:
1572            newcol = col
1573        df[newcol] = x
1574        if inplace == 0:
1575            self.placeColumn(newcol,col)
1576        self.redraw()
1577        return
1578
1579    def convertDates(self):
1580        """Convert single or multiple columns into datetime"""
1581
1582        df = self.model.df
1583        cols = list(df.columns[self.multiplecollist])
1584        if len(cols) == 1:
1585            colname = cols[0]
1586            temp = df[colname]
1587        else:
1588            colname = '-'.join(cols)
1589            temp = df[cols]
1590
1591        if len(cols) == 1 and temp.dtype == 'datetime64[ns]':
1592            title = 'Date->string extract'
1593        else:
1594            title = 'String->datetime convert'
1595        timeformats = ['infer','%d%m%Y','%Y%m%d']
1596        props = ['day','month','hour','minute','second','year',
1597                 'dayofyear','weekofyear','quarter']
1598        d = MultipleValDialog(title=title,
1599                                initialvalues=['',timeformats,props,False],
1600                                labels=['Column name:','Convert to date:',
1601                                        'Extract from datetime:','In place:'],
1602                                types=['string','combobox','combobox','checkbutton'],
1603                                parent = self.parentframe)
1604
1605        if d.result == None:
1606            return
1607        self.storeCurrent()
1608        newname = d.results[0]
1609        if newname != '':
1610            colname = newname
1611        fmt = d.results[1]
1612        prop = d.results[2]
1613        inplace = d.results[3]
1614        if fmt == 'infer':
1615            fmt = None
1616
1617        if len(cols) == 1 and temp.dtype == 'datetime64[ns]':
1618            if newname == '':
1619                colname = prop
1620            df[colname] = getattr(temp.dt, prop)
1621        else:
1622            try:
1623                df[colname] = pd.to_datetime(temp, format=fmt, errors='coerce')
1624            except Exception as e:
1625                messagebox.showwarning("Convert error", e,
1626                                        parent=self.parentframe)
1627        if inplace == False or len(cols)>1:
1628            self.placeColumn(colname, cols[-1])
1629
1630        self.redraw()
1631        self.tableChanged()
1632        return
1633
1634    def showAll(self):
1635        """Re-show unfiltered"""
1636
1637        if hasattr(self, 'dataframe'):
1638            self.model.df = self.dataframe
1639        self.filtered = False
1640        self.redraw()
1641        return
1642
1643    def statsViewer(self):
1644        """Show model fitting dialog"""
1645
1646        from .stats import StatsViewer
1647        if StatsViewer._doimport() == 0:
1648            messagebox.showwarning("no such module",
1649                                    "statsmodels is not installed.",
1650                                    parent=self.parentframe)
1651            return
1652
1653        if not hasattr(self, 'sv') or self.sv == None:
1654            sf = self.statsframe = Frame(self.parentframe)
1655            sf.grid(row=self.queryrow+1,column=0,columnspan=3,sticky='news')
1656            self.sv = StatsViewer(table=self,parent=sf)
1657        return self.sv
1658
1659    def getRowsFromIndex(self, idx=None):
1660        """Get row positions from index values"""
1661
1662        df = self.model.df
1663        if idx is not None:
1664            return [df.index.get_loc(i) for i in idx]
1665        return []
1666
1667    def getRowsFromMask(self, mask):
1668        df = self.model.df
1669        if mask is not None:
1670            idx = df.ix[mask].index
1671        return self.getRowsFromIndex(idx)
1672
1673    def findText(self):
1674        """Simple text search in whole table"""
1675
1676        if hasattr(self, 'qframe') and self.qframe != None:
1677            return
1678        self.searchframe = FindReplaceDialog(self)
1679        self.searchframe.grid(row=self.queryrow,column=0,columnspan=3,sticky='news')
1680
1681        
1682        mask = df.apply(lambda row: row.astype(str).str.contains(search).any(), axis=1)
1683        found = df[mask]
1684        print (len(found))
1685        idx = found.index
1686        rows = self.multiplerowlist = self.getRowsFromIndex(idx)
1687        return
1688
1689    def query(self, evt=None):
1690        """Do query"""
1691
1692        self.qframe.query()
1693        return
1694
1695    def queryBar(self, evt=None):
1696        """Query/filtering dialog"""
1697
1698        if hasattr(self, 'qframe') and self.qframe != None:
1699            return
1700        self.qframe = QueryDialog(self)
1701        self.qframe.grid(row=self.queryrow,column=0,columnspan=3,sticky='news')
1702        return
1703
1704    def updateWidgets(self):
1705        """Update some dialogs when table changed"""
1706
1707        if hasattr(self, 'qframe') and self.qframe != None:
1708            self.qframe.update()
1709        return
1710
1711    def _eval(self, df, ex):
1712        """Evaluate an expression using numexpr"""
1713
1714        #uses assignments to globals() - check this is ok
1715        import numexpr as ne
1716        for c in df:
1717            globals()[c] = df[c].as_matrix()
1718        a = ne.evaluate(ex)
1719        return a
1720
1721    def evalFunction(self, evt=None):
1722        """Apply a function to create new columns"""
1723
1724        #self.convertNumeric(ask=False)
1725        s = self.evalvar.get()
1726
1727        if s=='':
1728            return
1729        df = self.model.df
1730        vals = s.split('=')
1731        if len(vals)==1:
1732            ex = vals[0]
1733            n = ex
1734        else:
1735            n, ex = vals
1736        if n == '':
1737            return
1738        #evaluate
1739        try:
1740            df[n] = self._eval(df, ex)
1741            self.functionentry.configure(style="White.TCombobox")
1742        except Exception as e:
1743            print ('function parse error')
1744            print (e)
1745            self.functionentry.configure(style="Red.TCombobox")
1746            return
1747        #keep track of which cols are functions?
1748        self.formulae[n] = ex
1749
1750        if self.placecolvar.get() == 1:
1751            cols = df.columns
1752            self.placeColumn(n,cols[0])
1753        if self.recalculatevar.get() == 1:
1754            self.recalculateFunctions(omit=n)
1755        else:
1756            self.redraw()
1757        if hasattr(self, 'pf') and self.updateplotvar.get()==1:
1758            self.plotSelected()
1759        #update functions list in dropdown
1760        funclist = ['='.join(i) for i in self.formulae.items()]
1761        self.functionentry['values'] = funclist
1762        return
1763
1764    def recalculateFunctions(self, omit=None):
1765        """Re evaluate any columns that were derived from functions
1766        and dependent on other columns (except self derived?)"""
1767
1768        df = self.model.df
1769        for n in self.formulae:
1770            if n==omit: continue
1771            ex = self.formulae[n]
1772            #need to check if self calculation here...
1773            try:
1774                df[n] = self._eval(df, ex)
1775            except:
1776                print('could not calculate %s' %ex)
1777        self.redraw()
1778        return
1779
1780    def updateFunctions(self):
1781        """Remove functions if a column has been deleted"""
1782
1783        if not hasattr(self, 'formulae'):
1784            return
1785        df = self.model.df
1786        cols = list(df.columns)
1787        for n in list(self.formulae.keys()):
1788            if n not in cols:
1789                del(self.formulae[n])
1790        return
1791
1792    def functionsBar(self, evt=None):
1793        """Apply python functions from a pre-defined set, this is
1794        for stuff that can't be done with eval strings"""
1795
1796        def reset():
1797            self.evalframe.destroy()
1798            self.evalframe = None
1799            self.showAll()
1800
1801        def apply():
1802            #self.convertNumeric()
1803            f = self.funcvar.get()
1804            print (f)
1805            df = self.model.df
1806            z = df['filename'].apply(lambda x: x.replace('fa',''))
1807            print (z)
1808            return
1809
1810        if hasattr(self, 'funcsframe') and self.funcsframe != None:
1811            return
1812        ef = self.funcsframe = Frame(self.parentframe)
1813        ef.grid(row=self.queryrow,column=1,sticky='news')
1814        #self.evalvar = StringVar()
1815        #e = Entry(ef, textvariable=self.evalvar, font="Courier 13 bold")
1816        #e.bind('<Return>', self.evalFunction)
1817        funcs = ['replace']
1818        self.funcvar = StringVar()
1819        f = Combobox(ef, values=funcs,
1820                       textvariable=self.funcvar)
1821        f.pack(fill=BOTH,side=LEFT,expand=1,padx=2,pady=2)
1822        b = Button(ef,text='apply',width=5,command=apply)
1823        b.pack(fill=BOTH,side=LEFT,padx=2,pady=2)
1824        b = Button(ef,text='close',width=5,command=reset)
1825        b.pack(fill=BOTH,side=LEFT,padx=2,pady=2)
1826
1827        return
1828
1829    def evalBar(self, evt=None):
1830        """Use pd.eval to apply a function colwise or preset funcs."""
1831
1832        def reset():
1833            self.evalframe.destroy()
1834            self.evalframe = None
1835            self.showAll()
1836        def clear():
1837            n = messagebox.askyesno("Clear formulae",
1838                                    "This will clear stored functions.\nProceed?",
1839                                    parent=self.parentframe)
1840            if n == None:
1841                return
1842            self.formulae = {}
1843            self.functionentry['values'] = []
1844            return
1845        def addcolname(evt):
1846            self.functionentry.insert(END,colvar.get())
1847            return
1848
1849        self.estyle = Style()
1850        self.estyle.configure("White.TCombobox",
1851                         fieldbackground="white")
1852        self.estyle.configure("Red.TCombobox",
1853                         fieldbackground="#ffcccc")
1854
1855        if hasattr(self, 'evalframe') and self.evalframe != None:
1856            return
1857        if not hasattr(self, 'formulae'):
1858            self.formulae = {}
1859        ef = self.evalframe = Frame(self.parentframe)
1860        ef.grid(row=self.queryrow,column=0,columnspan=3,sticky='news')
1861        bf = Frame(ef)
1862        bf.pack(side=TOP, fill=BOTH)
1863        self.evalvar = StringVar()
1864        funclist = ['='.join(i) for i in self.formulae.items()]
1865        self.functionentry = e = Combobox(bf, values=funclist,
1866                                    textvariable=self.evalvar,width=34,
1867                                    font="Courier 13 bold",
1868                                    style="White.TCombobox")
1869        e.bind('<Return>', self.evalFunction)
1870        e.pack(fill=BOTH,side=LEFT,expand=1,padx=2,pady=2)
1871        addButton(bf, 'apply', self.evalFunction, images.accept(), 'apply', side=LEFT)
1872        addButton(bf, 'preset', self.applyColumnFunction, images.function(), 'preset function', side=LEFT)
1873        addButton(bf, 'clear', clear, images.delete(), 'clear stored functions', side=LEFT)
1874        addButton(bf, 'close', reset, images.cross(), 'close', side=LEFT)
1875
1876        bf = Frame(ef)
1877        bf.pack(side=TOP, fill=BOTH)
1878        columns = list(self.model.df.columns)
1879        colvar = StringVar()
1880        Label(bf, text='insert column:').pack(side=LEFT,fill=BOTH)
1881        c = Combobox(bf, values=columns,textvariable=colvar,width=14)
1882        c.bind("<<ComboboxSelected>>", addcolname)
1883        c.pack(side=LEFT,fill=BOTH)
1884
1885        self.updateplotvar = IntVar()
1886        self.placecolvar = IntVar()
1887        self.recalculatevar = IntVar()
1888        Checkbutton(bf, text="Update plot", variable=self.updateplotvar).pack(side=LEFT)
1889        Checkbutton(bf, text="Place new columns", variable=self.placecolvar).pack(side=LEFT)
1890        Checkbutton(bf, text="Recalculate all", variable=self.recalculatevar).pack(side=LEFT)
1891        return
1892
1893    def resizeColumn(self, col, width):
1894        """Resize a column by dragging"""
1895
1896        colname = self.model.getColumnName(col)
1897        if self.tablecolheader.wrap == True:
1898            if width<40:
1899                width=40
1900        self.model.columnwidths[colname] = width
1901        self.setColPositions()
1902        self.delete('colrect')
1903        #self.drawSelectedCol(self.currentcol)
1904        self.redraw()
1905        return
1906
1907    def get_row_clicked(self, event):
1908        """Get row where event on canvas occurs"""
1909
1910        h=self.rowheight
1911        #get coord on canvas, not window, need this if scrolling
1912        y = int(self.canvasy(event.y))
1913        y_start=self.y_start
1914        rowc = int((int(y)-y_start)/h)
1915        return rowc
1916
1917    def get_col_clicked(self,event):
1918        """Get column where event on the canvas occurs"""
1919
1920        w = self.cellwidth
1921        x = int(self.canvasx(event.x))
1922        x_start = self.x_start
1923        for colpos in self.col_positions:
1924            try:
1925                nextpos = self.col_positions[self.col_positions.index(colpos)+1]
1926            except:
1927                nextpos = self.tablewidth
1928            if x > colpos and x <= nextpos:
1929                #print 'x=', x, 'colpos', colpos, self.col_positions.index(colpos)
1930                return self.col_positions.index(colpos)
1931        return
1932
1933    def setSelectedRow(self, row):
1934        """Set currently selected row and reset multiple row list"""
1935
1936        self.currentrow = row
1937        self.multiplerowlist = []
1938        self.multiplerowlist.append(row)
1939        return
1940
1941    def setSelectedCol(self, col):
1942        """Set currently selected column"""
1943
1944        self.currentcol = col
1945        self.multiplecollist = []
1946        self.multiplecollist.append(col)
1947        return
1948
1949    def setSelectedCells(self, startrow, endrow, startcol, endcol):
1950        """Set a block of cells selected"""
1951
1952        self.currentrow = startrow
1953        self.currentcol = startcol
1954        if startrow < 0 or startcol < 0:
1955            return
1956        if endrow > self.rows or endcol > self.cols:
1957            return
1958        for r in range(startrow, endrow):
1959            self.multiplerowlist.append(r)
1960        for c in range(startcol, endcol):
1961            self.multiplecollist.append(c)
1962        return
1963
1964    def getSelectedRow(self):
1965        """Get currently selected row"""
1966        return self.currentrow
1967
1968    def getSelectedColumn(self):
1969        """Get currently selected column"""
1970        return self.currentcol
1971
1972    def selectAll(self, evt=None):
1973        """Select all rows and cells"""
1974
1975        self.startrow = 0
1976        self.endrow = self.rows
1977        self.multiplerowlist = list(range(self.startrow,self.endrow))
1978        self.drawMultipleRows(self.multiplerowlist)
1979        self.startcol = 0
1980        self.endcol = self.cols
1981        self.multiplecollist = list(range(self.startcol, self.endcol))
1982        self.drawMultipleCells()
1983        return
1984
1985    def selectNone(self):
1986        """Deselect current, called when table is redrawn with
1987        completely new cols and rows e.g. after model is updated."""
1988
1989        self.multiplecollist = []
1990        self.multiplerowlist = []
1991        self.startrow = self.endrow = 0
1992        self.delete('multicellrect','multiplesel','colrect')
1993        return
1994
1995    def getCellCoords(self, row, col):
1996        """Get x-y coordinates to drawing a cell in a given row/col"""
1997
1998        colname=self.model.getColumnName(col)
1999        if colname in self.model.columnwidths:
2000            w=self.model.columnwidths[colname]
2001        else:
2002            w=self.cellwidth
2003        h=self.rowheight
2004        x_start=self.x_start
2005        y_start=self.y_start
2006
2007        #get nearest rect co-ords for that row/col
2008        x1=self.col_positions[col]
2009        y1=y_start+h*row
2010        x2=x1+w
2011        y2=y1+h
2012        return x1,y1,x2,y2
2013
2014    def getCanvasPos(self, row, col):
2015        """Get the cell x-y coords as a fraction of canvas size"""
2016
2017        if self.rows==0:
2018            return None, None
2019        x1,y1,x2,y2 = self.getCellCoords(row,col)
2020        cx=float(x1)/self.tablewidth
2021        cy=float(y1)/(self.rows*self.rowheight)
2022        return cx, cy
2023
2024    def isInsideTable(self,x,y):
2025        """Returns true if x-y coord is inside table bounds"""
2026
2027        if self.x_start < x < self.tablewidth and self.y_start < y < self.rows*self.rowheight:
2028            return 1
2029        else:
2030            return 0
2031        return answer
2032
2033    def setRowHeight(self, h):
2034        """Set the row height"""
2035        self.rowheight = h
2036        return
2037
2038    def clearSelected(self):
2039        """Clear selections"""
2040
2041        self.delete('rect')
2042        self.delete('entry')
2043        self.delete('tooltip')
2044        self.delete('searchrect')
2045        self.delete('colrect')
2046        self.delete('multicellrect')
2047        return
2048
2049    def gotoprevRow(self):
2050        """Programmatically set previous row - eg. for button events"""
2051
2052        self.clearSelected()
2053        current = self.getSelectedRow()
2054        self.setSelectedRow(current-1)
2055        self.startrow = current-1
2056        self.endrow = current-1
2057        #reset multiple selection list
2058        self.multiplerowlist=[]
2059        self.multiplerowlist.append(self.currentrow)
2060        self.drawSelectedRect(self.currentrow, self.currentcol)
2061        self.drawSelectedRow()
2062        coltype = self.model.getColumnType(self.currentcol)
2063        if coltype == 'text' or coltype == 'number':
2064            self.drawCellEntry(self.currentrow, self.currentcol)
2065        return
2066
2067    def gotonextRow(self):
2068        """Programmatically set next row - eg. for button events"""
2069
2070        self.clearSelected()
2071        current = self.getSelectedRow()
2072        self.setSelectedRow(current+1)
2073        self.startrow = current+1
2074        self.endrow = current+1
2075        #reset multiple selection list
2076        self.multiplerowlist=[]
2077        self.multiplerowlist.append(self.currentrow)
2078        self.drawSelectedRect(self.currentrow, self.currentcol)
2079        self.drawSelectedRow()
2080        coltype = self.model.getColumnType(self.currentcol)
2081        if coltype == 'text' or coltype == 'number':
2082            self.drawCellEntry(self.currentrow, self.currentcol)
2083        return
2084
2085    def handle_left_click(self, event):
2086        """Respond to a single press"""
2087
2088        self.clearSelected()
2089        self.allrows = False
2090        #which row and column is the click inside?
2091        rowclicked = self.get_row_clicked(event)
2092        colclicked = self.get_col_clicked(event)
2093        if colclicked == None:
2094            return
2095        self.focus_set()
2096
2097        if hasattr(self, 'cellentry'):
2098            self.cellentry.destroy()
2099        #ensure popup menus are removed if present
2100        if hasattr(self, 'rightmenu'):
2101            self.rightmenu.destroy()
2102        if hasattr(self.tablecolheader, 'rightmenu'):
2103            self.tablecolheader.rightmenu.destroy()
2104
2105        self.startrow = rowclicked
2106        self.endrow = rowclicked
2107        self.startcol = colclicked
2108        self.endcol = colclicked
2109        #reset multiple selection list
2110        self.multiplerowlist=[]
2111        self.multiplerowlist.append(rowclicked)
2112        if 0 <= rowclicked < self.rows and 0 <= colclicked < self.cols:
2113            self.setSelectedRow(rowclicked)
2114            self.setSelectedCol(colclicked)
2115            self.drawSelectedRect(self.currentrow, self.currentcol)
2116            self.drawSelectedRow()
2117            self.rowheader.drawSelectedRows(rowclicked)
2118            self.tablecolheader.delete('rect')
2119        if hasattr(self, 'cellentry'):
2120            self.cellentry.destroy()
2121        return
2122
2123    def handle_left_release(self,event):
2124        self.endrow = self.get_row_clicked(event)
2125        return
2126
2127    def handle_left_ctrl_click(self, event):
2128        """Handle ctrl clicks for multiple row selections"""
2129
2130        rowclicked = self.get_row_clicked(event)
2131        colclicked = self.get_col_clicked(event)
2132        if 0 <= rowclicked < self.rows and 0 <= colclicked < self.cols:
2133            if rowclicked not in self.multiplerowlist:
2134                self.multiplerowlist.append(rowclicked)
2135            else:
2136                self.multiplerowlist.remove(rowclicked)
2137            self.drawMultipleRows(self.multiplerowlist)
2138            if colclicked not in self.multiplecollist:
2139                self.multiplecollist.append(colclicked)
2140            self.drawMultipleCells()
2141        return
2142
2143    def handle_left_shift_click(self, event):
2144        """Handle shift click, for selecting multiple rows"""
2145
2146        self.handle_mouse_drag(event)
2147        return
2148
2149    def handle_mouse_drag(self, event):
2150        """Handle mouse moved with button held down, multiple selections"""
2151
2152        if hasattr(self, 'cellentry'):
2153            self.cellentry.destroy()
2154        rowover = self.get_row_clicked(event)
2155        colover = self.get_col_clicked(event)
2156        if colover == None or rowover == None:
2157            return
2158
2159        if rowover >= self.rows or self.startrow > self.rows:
2160            return
2161        else:
2162            self.endrow = rowover
2163        #do columns
2164        if colover > self.cols or self.startcol > self.cols:
2165            return
2166        else:
2167            self.endcol = colover
2168            if self.endcol < self.startcol:
2169                self.multiplecollist=list(range(self.endcol, self.startcol+1))
2170            else:
2171                self.multiplecollist=list(range(self.startcol, self.endcol+1))
2172            #print self.multiplecollist
2173        #draw the selected rows
2174        if self.endrow != self.startrow:
2175            if self.endrow < self.startrow:
2176                self.multiplerowlist=list(range(self.endrow, self.startrow+1))
2177            else:
2178                self.multiplerowlist=list(range(self.startrow, self.endrow+1))
2179            self.drawMultipleRows(self.multiplerowlist)
2180            self.rowheader.drawSelectedRows(self.multiplerowlist)
2181            #draw selected cells outline using row and col lists
2182            self.drawMultipleCells()
2183        else:
2184            self.multiplerowlist = []
2185            self.multiplerowlist.append(self.currentrow)
2186            if len(self.multiplecollist) >= 1:
2187                self.drawMultipleCells()
2188            self.delete('multiplesel')
2189        return
2190
2191    def handle_arrow_keys(self, event):
2192        """Handle arrow keys press"""
2193        #print event.keysym
2194
2195        row = self.get_row_clicked(event)
2196        col = self.get_col_clicked(event)
2197        x,y = self.getCanvasPos(self.currentrow, self.currentcol-1)
2198        rmin = self.visiblerows[0]
2199        rmax = self.visiblerows[-1]-2
2200        cmax = self.visiblecols[-1]-1
2201        cmin = self.visiblecols[0]
2202        if x == None:
2203            return
2204
2205        if event.keysym == 'Up':
2206            if self.currentrow == 0:
2207                return
2208            else:
2209                #self.yview('moveto', y)
2210                #self.rowheader.yview('moveto', y)
2211                self.currentrow  = self.currentrow - 1
2212        elif event.keysym == 'Down':
2213            if self.currentrow >= self.rows-1:
2214                return
2215            else:
2216                self.currentrow  = self.currentrow + 1
2217        elif event.keysym == 'Right' or event.keysym == 'Tab':
2218            if self.currentcol >= self.cols-1:
2219                if self.currentrow < self.rows-1:
2220                    self.currentcol = 0
2221                    self.currentrow  = self.currentrow + 1
2222                else:
2223                    return
2224            else:
2225                self.currentcol  = self.currentcol + 1
2226        elif event.keysym == 'Left':
2227            if self.currentcol>0:
2228                self.currentcol = self.currentcol - 1
2229
2230        if self.currentcol > cmax or self.currentcol <= cmin:
2231            print (self.currentcol, self.visiblecols)
2232            self.xview('moveto', x)
2233            self.tablecolheader.xview('moveto', x)
2234            self.redraw()
2235
2236        if self.currentrow <= rmin:
2237            #we need to shift y to page up enough
2238            vh=len(self.visiblerows)/2
2239            x,y = self.getCanvasPos(self.currentrow-vh, 0)
2240
2241        if self.currentrow >= rmax or self.currentrow <= rmin:
2242            self.yview('moveto', y)
2243            self.rowheader.yview('moveto', y)
2244            self.redraw()
2245
2246        self.drawSelectedRect(self.currentrow, self.currentcol)
2247        coltype = self.model.getColumnType(self.currentcol)
2248        return
2249
2250    def handle_double_click(self, event):
2251        """Do double click stuff. Selected row/cols will already have
2252           been set with single click binding"""
2253
2254        row = self.get_row_clicked(event)
2255        col = self.get_col_clicked(event)
2256        self.drawCellEntry(self.currentrow, self.currentcol)
2257        return
2258
2259    def handle_right_click(self, event):
2260        """respond to a right click"""
2261
2262        self.delete('tooltip')
2263        self.rowheader.clearSelected()
2264        if hasattr(self, 'rightmenu'):
2265            self.rightmenu.destroy()
2266        rowclicked = self.get_row_clicked(event)
2267        colclicked = self.get_col_clicked(event)
2268        if colclicked == None:
2269            self.rightmenu = self.popupMenu(event, outside=1)
2270            return
2271
2272        if (rowclicked in self.multiplerowlist or self.allrows == True) and colclicked in self.multiplecollist:
2273            self.rightmenu = self.popupMenu(event, rows=self.multiplerowlist, cols=self.multiplecollist)
2274        else:
2275            if 0 <= rowclicked < self.rows and 0 <= colclicked < self.cols:
2276                self.clearSelected()
2277                self.allrows = False
2278                self.setSelectedRow(rowclicked)
2279                self.setSelectedCol(colclicked)
2280                self.drawSelectedRect(self.currentrow, self.currentcol)
2281                self.drawSelectedRow()
2282            if self.isInsideTable(event.x,event.y) == 1:
2283                self.rightmenu = self.popupMenu(event,rows=self.multiplerowlist, cols=self.multiplecollist)
2284            else:
2285                self.rightmenu = self.popupMenu(event, outside=1)
2286        return
2287
2288    def placeColumn(self, col1, col2):
2289        """Move col1 next to col2, useful for placing a new column
2290        made from the first one next to it so user can see it easily"""
2291
2292        ind1 = self.model.df.columns.get_loc(col1)
2293        ind2 = self.model.df.columns.get_loc(col2)
2294        self.model.moveColumn(ind1, ind2+1)
2295        self.redraw()
2296        return
2297
2298    def gotonextCell(self):
2299        """Move highlighted cell to next cell in row or a new col"""
2300
2301        if hasattr(self, 'cellentry'):
2302            self.cellentry.destroy()
2303        self.currentrow = self.currentrow+1
2304        #if self.currentcol >= self.cols-1:
2305        #    self.currentcol = self.currentcol+1
2306        self.drawSelectedRect(self.currentrow, self.currentcol)
2307        return
2308
2309    def movetoSelectedRow(self, row=None, recname=None):
2310        """Move to selected row, updating table"""
2311
2312        row=self.model.getRecordIndex(recname)
2313        self.setSelectedRow(row)
2314        self.drawSelectedRow()
2315        x,y = self.getCanvasPos(row, 0)
2316        self.yview('moveto', y-0.01)
2317        self.tablecolheader.yview('moveto', y)
2318        return
2319
2320    def copyTable(self, event=None):
2321        """Copy from the clipboard"""
2322
2323        df = self.model.df.copy()
2324        #flatten multi-index
2325        df.columns = df.columns.get_level_values(0)
2326        df.to_clipboard(sep=',')
2327        return
2328
2329    def pasteTable(self, event=None):
2330        """Paste a new table from the clipboard"""
2331
2332        self.storeCurrent()
2333        try:
2334            df = pd.read_clipboard(sep=',',error_bad_lines=False)
2335        except Exception as e:
2336            messagebox.showwarning("Could not read data", e,
2337                                    parent=self.parentframe)
2338            return
2339        if len(df) == 0:
2340            return
2341
2342        df = pd.read_clipboard(sep=',', index_col=0, error_bad_lines=False)
2343        model = TableModel(df)
2344        self.updateModel(model)
2345        self.redraw()
2346        return
2347
2348    def paste(self, event=None):
2349        """Paste selections - not implemented"""
2350        #df = pd.read_clipboard()
2351        return
2352
2353    def copy(self, rows, cols=None):
2354        """Copy cell contents to clipboard"""
2355
2356        data = self.getSelectedDataFrame()
2357        try:
2358            if len(data) == 1 and len(data.columns)==1:
2359                data.to_clipboard(index=False,header=False)
2360            else:
2361                data.to_clipboard()
2362        except:
2363            messagebox.showwarning("Warning",
2364                                   "No clipboard software.\nInstall xclip",
2365                                   parent=self.parentframe)
2366        return
2367
2368    def transpose(self):
2369        """Transpose table"""
2370
2371        self.storeCurrent()
2372        self.model.transpose()
2373        self.updateModel(self.model)
2374        self.setSelectedRow(0)
2375        self.redraw()
2376        self.drawSelectedCol()
2377        return
2378
2379    def transform(self):
2380        """Apply element-wise transform"""
2381
2382        df = self.model.df
2383        cols = list(df.columns[self.multiplecollist])
2384        rows = self.multiplerowlist
2385        funcs = ['log','exp','log10','log2',
2386                 'round','floor','ceil','trunc',
2387                 'subtract','divide','mod',
2388                 'negative','power',
2389                 'sin','cos','tan','degrees','radians']
2390
2391        d = MultipleValDialog(title='Apply Function',
2392                                initialvalues=(funcs,1,False),
2393                                labels=('Function:','Constant:','Use Selected'),
2394                                types=('combobox','string','checkbutton'),
2395                                tooltips=(None,'value to apply with arithmetic operations',
2396                                          'apply to selected data only'),
2397                                parent = self.parentframe)
2398        if d.result == None:
2399            return
2400        self.storeCurrent()
2401        funcname = d.results[0]
2402        func = getattr(np, funcname)
2403        const = float(d.results[1])
2404        use_sel = float(d.results[2])
2405
2406        if funcname in ['round']:
2407            const = int(const)
2408
2409        if funcname in ['subtract','divide','mod','power','round']:
2410            if use_sel == True:
2411                df.ix[rows, cols] = df.ix[rows, cols].applymap(lambda x: func(x, const))
2412            else:
2413                df = df.applymap( lambda x: func(x, const))
2414        else:
2415            if use_sel == True:
2416                df.ix[rows, cols] = df.ix[rows, cols].applymap(func)
2417            else:
2418                df = df.applymap(func)
2419
2420        self.model.df = df
2421        self.redraw()
2422        return
2423
2424    def aggregate(self):
2425        """Show aggregate dialog"""
2426
2427        df = self.model.df
2428        from .dialogs import AggregateDialog
2429        dlg = AggregateDialog(self, df=self.model.df)
2430
2431        '''if replace == True:
2432            self.model.df = g
2433            self.showIndex()
2434            self.redraw()
2435        else:
2436            self.createChildTable(g, 'aggregated', index=True)'''
2437        return
2438
2439    def melt(self):
2440        """Melt table"""
2441
2442        df = self.model.df
2443        cols = list(df.columns)
2444        valcols = list(df.select_dtypes(include=[np.float64,np.int32]))
2445        d = MultipleValDialog(title='Melt',
2446                                initialvalues=(cols,valcols,'var'),
2447                                labels=('ID vars:', 'Value vars:', 'var name:'),
2448                                types=('combobox','listbox','entry'),
2449                                tooltips=('Column(s) to use as identifier variables',
2450                                          'Column(s) to unpivot',
2451                                          'name of variable column'),
2452                                parent = self.parentframe)
2453        idvars = d.results[0]
2454        valuevars = d.results[1]
2455        varname = d.results[2]
2456        if valuevars == '':
2457            valuevars = None
2458        elif len(valuevars) == 1:
2459            valuevars = valuevars[0]
2460        t = pd.melt(df, id_vars=idvars, value_vars=valuevars,
2461                 var_name=varname,value_name='value')
2462        #print(t)
2463        self.createChildTable(t, '', index=True)
2464        return
2465
2466    def pivot(self):
2467        """Pivot table"""
2468
2469        #self.convertNumeric()
2470        df = self.model.df
2471        cols = list(df.columns)
2472        valcols = list(df.select_dtypes(include=[np.float64,np.int32]))
2473        funcs = ['mean','sum','count','max','min','std','first','last']
2474        d = MultipleValDialog(title='Pivot',
2475                                initialvalues=(cols,cols,valcols,funcs),
2476                                labels=('Index:', 'Column:', 'Values:','Agg Function:'),
2477                                types=('combobox','combobox','listbox','combobox'),
2478                                tooltips=('a unique index to reshape on','column with variables',
2479                                    'selecting no values uses all remaining cols',
2480                                    'function to aggregate on'),
2481                                parent = self.parentframe)
2482        if d.result == None:
2483            return
2484        index = d.results[0]
2485        column = d.results[1]
2486        values = d.results[2]
2487        func = d.results[3]
2488        if values == '': values = None
2489        elif len(values) == 1: values = values[0]
2490
2491        p = pd.pivot_table(df, index=index, columns=column, values=values, aggfunc=func)
2492        #print (p)
2493        self.tableChanged()
2494        if type(p) is pd.Series:
2495            p = pd.DataFrame(p)
2496        self.createChildTable(p, 'pivot-%s-%s' %(index,column), index=True)
2497        return
2498
2499    def doCombine(self):
2500        """Do combine/merge operation"""
2501
2502        if self.child == None:
2503            messagebox.showwarning("No data", 'You need a sub-table to merge with.',
2504                                    parent=self.parentframe)
2505            return
2506        self.storeCurrent()
2507        from .dialogs import CombineDialog
2508        cdlg = CombineDialog(self, df1=self.model.df, df2=self.child.model.df)
2509        #df = cdlg.merged
2510        #if df is None:
2511        #    return
2512        #model = TableModel(dataframe=df)
2513        #self.updateModel(model)
2514        #self.redraw()
2515        return
2516
2517    def merge(self, table):
2518        """Merge with another table."""
2519
2520        df1 = self.model.df
2521        df2 = table.model.df
2522        new = pd.merge(df1,df2,left_on=c1,right_on=c2,how=how)
2523        model = TableModel(new)
2524        self.updateModel(model)
2525        self.redraw()
2526        return
2527
2528    def describe(self):
2529        """Create table summary"""
2530
2531        g = self.model.df.describe()
2532        self.createChildTable(g)
2533        return
2534
2535    def convertColumnNames(self, s='_'):
2536        """Convert col names so we can use numexpr"""
2537
2538        d = MultipleValDialog(title='Convert col names',
2539                                initialvalues=['','','',0,0],
2540                                labels=['replace','with:',
2541                                        'add symbol to start:',
2542                                        'make lowercase','make uppercase'],
2543                                types=('string','string','string','checkbutton','checkbutton'),
2544                                parent = self.parentframe)
2545        if d.result == None:
2546            return
2547        self.storeCurrent()
2548        pattern = d.results[0]
2549        repl = d.results[1]
2550        start = d.results[2]
2551        lower = d.results[3]
2552        upper = d.results[4]
2553        df = self.model.df
2554        if start != '':
2555            df.columns = start + df.columns
2556        if pattern != '':
2557            df.columns = [i.replace(pattern,repl) for i in df.columns]
2558        if lower == 1:
2559            df.columns = df.columns.str.lower()
2560        elif upper == 1:
2561            df.columns = df.columns.str.upper()
2562        self.redraw()
2563        self.tableChanged()
2564        return
2565
2566    def convertNumeric(self):
2567        """Convert cols to numeric if possible"""
2568
2569        types = ['float','int']
2570        d = MultipleValDialog(title='Convert col names',
2571                                initialvalues=[types,1,0],
2572                                labels=['convert to',
2573                                        'selected columns only:',
2574                                        'fill empty:'],
2575                                types=('combobox','checkbutton','checkbutton'),
2576                                parent = self.parentframe)
2577        if d.result == None:
2578            return
2579
2580        self.storeCurrent()
2581        convtype = d.results[0]
2582        useselected = d.results[1]
2583        fillempty = d.results[2]
2584
2585        cols = self.multiplecollist
2586        df = self.model.df
2587        if useselected == 1:
2588            colnames = df.columns[cols]
2589        else:
2590            colnames = df.columns
2591
2592        for c in colnames:
2593            x=df[c]
2594            if fillempty == 1:
2595                x = x.fillna(0)
2596            self.model.df[c] = pd.to_numeric(x, errors='coerce').astype(convtype)
2597
2598        self.redraw()
2599        self.tableChanged()
2600        return
2601
2602    def corrMatrix(self):
2603        """Correlation matrix"""
2604
2605        df = self.model.df
2606        corr = df.corr()
2607        self.createChildTable(corr)
2608        return
2609
2610    def createChildTable(self, df, title=None, index=False, out=False):
2611        """Add the child table"""
2612
2613        self.closeChildTable()
2614        if out == True:
2615            win = Toplevel()
2616            x,y,w,h = self.getGeometry(self.master)
2617            win.geometry('+%s+%s' %(int(x+w/2),int(y+h/2)))
2618            if title != None:
2619                win.title(title)
2620        else:
2621            win = Frame(self.parentframe)
2622            win.grid(row=self.childrow,column=0,columnspan=2,sticky='news')
2623        self.childframe = win
2624        newtable = self.__class__(win, dataframe=df, showtoolbar=0, showstatusbar=1)
2625        newtable.parenttable = self
2626        newtable.adjustColumnWidths()
2627        newtable.show()
2628        toolbar = ChildToolBar(win, newtable)
2629        toolbar.grid(row=0,column=3,rowspan=2,sticky='news')
2630        self.child = newtable
2631        if hasattr(self, 'pf'):
2632            newtable.pf = self.pf
2633        if index==True:
2634            newtable.showIndex()
2635        return
2636
2637    def closeChildTable(self):
2638        """Close the child table"""
2639
2640        if self.child != None:
2641            self.child.destroy()
2642        if hasattr(self, 'childframe'):
2643            self.childframe.destroy()
2644        return
2645
2646    def tableFromSelection(self):
2647        """Create a new table from the selected cells"""
2648
2649        df = self.getSelectedDataFrame()
2650        if len(df) <=1:
2651            df = pd.DataFrame()
2652        self.createChildTable(df, 'selection')
2653        return
2654
2655    '''def pasteChildTable(self):
2656        """Paste child table back into main one"""
2657
2658        answer =  messagebox.askyesno("Confirm",
2659                                "This will overwrite the main table.\n"+\
2660                                "Are you sure?",
2661                                parent=self.parentframe)
2662        if not answer:
2663            return
2664        table = self.parenttable
2665        model = TableModel(self.model.df)
2666        table.updateModel(model)
2667        return'''
2668
2669    def showInfo(self):
2670        """Show dataframe info"""
2671
2672        df = self.model.df
2673        import io
2674        buf = io.StringIO()
2675        df.info(verbose=True,buf=buf,memory_usage=True)
2676        from .dialogs import SimpleEditor
2677        w = Toplevel(self.parentframe)
2678        w.grab_set()
2679        w.transient(self)
2680        ed = SimpleEditor(w, height=25)
2681        ed.pack(in_=w, fill=BOTH, expand=Y)
2682        ed.text.insert(END, buf.getvalue())
2683        return
2684
2685    def get_memory(self, ):
2686        """memory usage of current table"""
2687
2688        df = self.model.df
2689        return df.memory_usage()
2690
2691    def showasText(self):
2692        """Get table as formatted text - for printing"""
2693
2694        d = MultipleValDialog(title='Table to Text',
2695                                initialvalues=(['left','right'],1,1,0,'',0,0),
2696                                labels=['justify:','header ','include index:',
2697                                        'sparsify:','na_rep:','max_cols','use selected'],
2698                                types=('combobox','checkbutton','checkbutton',
2699                                       'checkbutton','string','int','checkbutton'),
2700                                parent = self.parentframe)
2701        if d.result == None:
2702            return
2703        justify = d.results[0]
2704        header = d.results[1]
2705        index = d.results[2]
2706        sparsify = d.results[3]
2707        na_rep = d.results[4]
2708        max_cols = d.results[5]
2709        selected = d.results[6]
2710
2711        if max_cols == 0:
2712            max_cols=None
2713        if selected == True:
2714            df = self.getSelectedDataFrame()
2715        else:
2716            df = self.model.df
2717        s = df.to_string(justify=justify,header=header,index=index,
2718                         sparsify=sparsify,na_rep=na_rep,max_cols=max_cols)
2719        #from tkinter.scrolledtext import ScrolledText
2720        from .dialogs import SimpleEditor
2721        w = Toplevel(self.parentframe)
2722        w.grab_set()
2723        w.transient(self)
2724        ed = SimpleEditor(w)
2725        ed.pack(in_=w, fill=BOTH, expand=Y)
2726        ed.text.insert(END, s)
2727        return
2728
2729    # --- Some cell specific actions here ---
2730
2731    def popupMenu(self, event, rows=None, cols=None, outside=None):
2732        """Add left and right click behaviour for canvas, should not have to override
2733            this function, it will take its values from defined dicts in constructor"""
2734
2735        defaultactions = {
2736                        "Copy" : lambda: self.copy(rows, cols),
2737                        "Undo" : lambda: self.undo(),
2738                        #"Paste" : lambda: self.paste(rows, cols),
2739                        "Fill Down" : lambda: self.fillDown(rows, cols),
2740                        #"Fill Right" : lambda: self.fillAcross(cols, rows),
2741                        "Add Row(s)" : lambda: self.addRows(),
2742                        #"Delete Row(s)" : lambda: self.deleteRow(),
2743                        "Add Column(s)" : lambda: self.addColumn(),
2744                        "Delete Column(s)" : lambda: self.deleteColumn(),
2745                        "Clear Data" : lambda: self.deleteCells(rows, cols),
2746                        "Select All" : self.selectAll,
2747                        #"Auto Fit Columns" : self.autoResizeColumns,
2748                        "Table Info" : self.showInfo,
2749                        "Set Color" : self.setRowColors,
2750                        "Show as Text" : self.showasText,
2751                        "Filter Rows" : self.queryBar,
2752                        "New": self.new,
2753                        "Open": self.load,
2754                        "Save": self.save,
2755                        "Save As": self.saveAs,
2756                        "Import Text/CSV": lambda: self.importCSV(dialog=True),
2757                        "Export": self.doExport,
2758                        "Plot Selected" : self.plotSelected,
2759                        "Hide plot" : self.hidePlot,
2760                        "Show plot" : self.showPlot,
2761                        "Preferences" : self.showPrefs,
2762                        "Table to Text" : self.showasText,
2763                        "Clean Data" : self.cleanData,
2764                        "Clear Formatting" : self.clearFormatting,
2765                        "Undo Last Change": self.undo,
2766                        "Save Row": self.saveRow,
2767                        "Copy Table": self.copyTable}
2768
2769        main = ["Copy", "Undo", "Fill Down", #"Fill Right",
2770                "Clear Data", "Set Color"]
2771        general = ["Select All", "Filter Rows",
2772                   "Show as Text", "Table Info", "Preferences"]
2773
2774        filecommands = ['Open','Import Text/CSV','Save','Save As','Export']
2775        editcommands = ['Undo Last Change','Copy Table', 'Save Row']
2776        plotcommands = ['Plot Selected','Hide plot','Show plot']
2777        tablecommands = ['Table to Text','Clean Data','Clear Formatting']
2778
2779        def createSubMenu(parent, label, commands):
2780            menu = Menu(parent, tearoff = 0)
2781            popupmenu.add_cascade(label=label,menu=menu)
2782            for action in commands:
2783                menu.add_command(label=action, command=defaultactions[action])
2784            applyStyle(menu)
2785            return menu
2786
2787        def add_commands(fieldtype):
2788            """Add commands to popup menu for column type and specific cell"""
2789            functions = self.columnactions[fieldtype]
2790            for f in list(functions.keys()):
2791                func = getattr(self, functions[f])
2792                popupmenu.add_command(label=f, command= lambda : func(row,col))
2793            return
2794
2795        popupmenu = Menu(self, tearoff = 0)
2796        def popupFocusOut(event):
2797            popupmenu.unpost()
2798
2799        if outside == None:
2800            #if outside table, just show general items
2801            row = self.get_row_clicked(event)
2802            col = self.get_col_clicked(event)
2803            coltype = self.model.getColumnType(col)
2804            def add_defaultcommands():
2805                """now add general actions for all cells"""
2806                for action in main:
2807                    if action == 'Fill Down' and (rows == None or len(rows) <= 1):
2808                        continue
2809                    if action == 'Fill Right' and (cols == None or len(cols) <= 1):
2810                        continue
2811                    if action == 'Undo' and self.prevdf is None:
2812                        continue
2813                    else:
2814                        popupmenu.add_command(label=action, command=defaultactions[action])
2815                return
2816
2817            if coltype in self.columnactions:
2818                add_commands(coltype)
2819            add_defaultcommands()
2820
2821        for action in general:
2822            popupmenu.add_command(label=action, command=defaultactions[action])
2823
2824        popupmenu.add_separator()
2825        createSubMenu(popupmenu, 'File', filecommands)
2826        createSubMenu(popupmenu, 'Edit', editcommands)
2827        createSubMenu(popupmenu, 'Plot', plotcommands)
2828        createSubMenu(popupmenu, 'Table', tablecommands)
2829        popupmenu.bind("<FocusOut>", popupFocusOut)
2830        popupmenu.focus_set()
2831        popupmenu.post(event.x_root, event.y_root)
2832        applyStyle(popupmenu)
2833        return popupmenu
2834
2835    # --- spreadsheet type functions ---
2836
2837    def fillDown(self, rowlist, collist):
2838        """Fill down a column, or multiple columns"""
2839
2840        self.storeCurrent()
2841        df = self.model.df
2842        val = df.iloc[rowlist[0],collist[0]]
2843        #remove first element as we don't want to overwrite it
2844        rowlist.remove(rowlist[0])
2845        df.iloc[rowlist,collist] = val
2846        self.redraw()
2847        return
2848
2849    def fillAcross(self, collist, rowlist):
2850        """Fill across a row, or multiple rows"""
2851
2852        self.storeCurrent()
2853        model = self.model
2854        frstcol = collist[0]
2855        collist.remove(frstcol)
2856        self.redraw()
2857        return
2858
2859    def getSelectionValues(self):
2860        """Get values for current multiple cell selection"""
2861
2862        if len(self.multiplerowlist) == 0 or len(self.multiplecollist) == 0:
2863            return None
2864        rows = self.multiplerowlist
2865        cols = self.multiplecollist
2866        model = self.model
2867        if len(rows)<1 or len(cols)<1:
2868            return None
2869        #if only one row selected we plot whole col
2870        if len(rows) == 1:
2871            rows = self.rowrange
2872        lists = []
2873
2874        for c in cols:
2875            x=[]
2876            for r in rows:
2877                #absr = self.get_AbsoluteRow(r)
2878                val = model.getValueAt(r,c)
2879                if val == None or val == '':
2880                    continue
2881                x.append(val)
2882            lists.append(x)
2883        return lists
2884
2885    def showPlotViewer(self, parent=None, layout='horizontal'):
2886        """Create plot frame"""
2887
2888        if not hasattr(self, 'pf'):
2889            self.pf = PlotViewer(table=self, parent=parent, layout=layout)
2890        if hasattr(self, 'child') and self.child is not None:
2891            self.child.pf = self.pf
2892        return self.pf
2893
2894    def hidePlot(self):
2895        """Hide plot frame"""
2896
2897        if hasattr(self, 'pf'):
2898            self.pf.hide()
2899            #self.pf = None
2900        return
2901
2902    def showPlot(self):
2903        if hasattr(self, 'pf'):
2904            self.pf.show()
2905        return
2906
2907    def getSelectedDataFrame(self):
2908        """Return a sub-dataframe of the selected cells"""
2909
2910        df = self.model.df
2911        rows = self.multiplerowlist
2912        if not type(rows) is list:
2913            rows = list(rows)
2914        if len(rows)<1 or self.allrows == True:
2915            rows = list(range(self.rows))
2916        cols = self.multiplecollist
2917        try:
2918            data = df.iloc[list(rows),cols]
2919        except Exception as e:
2920            print ('error indexing data')
2921            return pd.DataFrame()
2922        return data
2923
2924    def getPlotData(self):
2925        """Plot data from selection"""
2926
2927        data = self.getSelectedDataFrame()
2928        #data = data.convert_objects(convert_numeric='force')
2929        #print (data)
2930        return data
2931
2932    def plotSelected(self):
2933        """Plot the selected data in the associated plotviewer"""
2934
2935        if not hasattr(self, 'pf') or self.pf == None:
2936            self.pf = PlotViewer(table=self)
2937        else:
2938            if type(self.pf.main) is Toplevel:
2939                self.pf.main.deiconify()
2940        #plot could be hidden
2941        self.showPlot()
2942        #update reference to table
2943        self.pf.table = self
2944        #call plot, updates plot data with current selection
2945        self.pf.replot()
2946        if hasattr(self, 'parenttable'):
2947            self.parenttable.plotted = 'child'
2948        else:
2949            self.plotted = 'main'
2950        return
2951
2952    def plot3D(self):
2953
2954        if not hasattr(self, 'pf'):
2955            self.pf = PlotViewer(table=self)
2956
2957        data = self.getPlotData()
2958        self.pf.data = data
2959        self.pf.plot3D()
2960        return
2961
2962    #--- Drawing stuff ---
2963
2964    def drawGrid(self, startrow, endrow):
2965        """Draw the table grid lines"""
2966        self.delete('gridline','text')
2967        rows=len(self.rowrange)
2968        cols=self.cols
2969        w = self.cellwidth
2970        h = self.rowheight
2971        x_start=self.x_start
2972        y_start=self.y_start
2973        x_pos=x_start
2974
2975        if self.vertlines==1:
2976            for col in range(cols+1):
2977                x=self.col_positions[col]
2978                self.create_line(x,y_start,x,y_start+rows*h, tag='gridline',
2979                                     fill=self.grid_color, width=self.linewidth)
2980        if self.horizlines==1:
2981            for row in range(startrow, endrow+1):
2982                y_pos=y_start+row*h
2983                self.create_line(x_start,y_pos,self.tablewidth,y_pos, tag='gridline',
2984                                    fill=self.grid_color, width=self.linewidth)
2985        return
2986
2987    def drawRowHeader(self):
2988        """User has clicked to select a cell"""
2989
2990        self.delete('rowheader')
2991        x_start=self.x_start
2992        y_start=self.y_start
2993        h=self.rowheight
2994        rowpos=0
2995        for row in self.rowrange:
2996            x1,y1,x2,y2 = self.getCellCoords(rowpos,0)
2997            self.create_rectangle(0,y1,x_start-2,y2,
2998                                      fill='gray75',
2999                                      outline='white',
3000                                      width=1,
3001                                      tag='rowheader')
3002            self.create_text(x_start/2,y1+h/2,
3003                                      text=row+1,
3004                                      fill='black',
3005                                      font=self.thefont,
3006                                      tag='rowheader')
3007            rowpos+=1
3008        return
3009
3010    def drawSelectedRect(self, row, col, color=None):
3011        """User has clicked to select a cell"""
3012
3013        if col >= self.cols:
3014            return
3015        self.delete('currentrect')
3016        #bg = self.selectedcolor
3017        if color == None:
3018            color = 'gray25'
3019        w=2
3020        x1,y1,x2,y2 = self.getCellCoords(row,col)
3021        rect = self.create_rectangle(x1+w/2+1,y1+w/2+1,x2-w/2,y2-w/2,
3022                                  outline=color,
3023                                  width=w,
3024                                  tag='currentrect')
3025        #raise text above all
3026        self.lift('celltext'+str(col)+'_'+str(row))
3027        return
3028
3029    def drawRect(self, row, col, color=None, tag=None, delete=1):
3030        """Cell is colored"""
3031
3032        if delete==1:
3033            self.delete('cellbg'+str(row)+str(col))
3034        if color==None or color==self.cellbackgr:
3035            return
3036        else:
3037            bg=color
3038        if tag==None:
3039            recttag='fillrect'
3040        else:
3041            recttag=tag
3042        w=1
3043        x1,y1,x2,y2 = self.getCellCoords(row,col)
3044        rect = self.create_rectangle(x1+w/2,y1+w/2,x2-w/2,y2-w/2,
3045                                  fill=bg,
3046                                  outline=bg,
3047                                  width=w,
3048                                  tag=(recttag,'cellbg'+str(row)+str(col)))
3049        self.lower(recttag)
3050        return
3051
3052    def handleCellEntry(self, row, col):
3053        """Callback for cell entry"""
3054
3055        global prevData
3056
3057        value = self.cellentryvar.get()
3058        self.model.setValueAt(value,row,col)
3059        self.drawText(row, col, value, align=self.align)
3060        status = self.saveRow()
3061        self.delete('entry')
3062        self.gotonextCell()
3063        if status==False:
3064            #self.gotonextCell()
3065            if prevData==None or prevData=='':
3066                prevData = ''
3067            #print( prevData )
3068            self.model.setValueAt(prevData, row, col)
3069            self.drawText(row, col, prevData, align=self.align)
3070            prevData = ""
3071        return
3072
3073    def drawCellEntry(self, row, col, text=None):
3074        """When the user single/double clicks on a text/number cell,
3075          bring up entry window and allow edits."""
3076        global prevData
3077        if self.editable == False:
3078            return
3079        h = self.rowheight
3080        model = self.model
3081        text = self.model.getValueAt(row, col)
3082        prevData = text
3083        if pd.isnull(text):
3084            text = ''
3085        x1,y1,x2,y2 = self.getCellCoords(row,col)
3086        w=x2-x1
3087        self.cellentryvar = txtvar = StringVar()
3088        txtvar.set(text)
3089
3090        self.cellentry = Entry(self.parentframe,width=20,
3091                        textvariable=txtvar,
3092                        takefocus=1,
3093                        font=self.thefont)
3094        self.cellentry.icursor(END)
3095        self.cellentry.bind('<Return>', lambda x: self.handleCellEntry(row,col))
3096        self.cellentry.focus_set()
3097        self.entrywin = self.create_window(x1,y1,
3098                                width=w,height=h,
3099                                window=self.cellentry,anchor='nw',
3100                                tag='entry')
3101        return
3102
3103    def checkDataEntry(self,event=None):
3104        """do validation checks on data entry in a widget"""
3105
3106        value=event.widget.get()
3107        if value!='':
3108            try:
3109                value=re.sub(',','.', value)
3110                value=float(value)
3111            except ValueError:
3112                event.widget.configure(bg='red')
3113                return 0
3114        elif value == '':
3115            return 1
3116        return 1
3117
3118    def drawText(self, row, col, celltxt, align=None):
3119        """Draw the text inside a cell area"""
3120
3121        self.delete('celltext'+str(col)+'_'+str(row))
3122        h = self.rowheight
3123        x1,y1,x2,y2 = self.getCellCoords(row,col)
3124        w=x2-x1
3125        wrap = False
3126        pad=5
3127        #if type(celltxt) is np.float64:
3128        #    celltxt = np.round(celltxt,3)
3129        celltxt = str(celltxt)
3130        length = len(celltxt)
3131        if length == 0:
3132            return
3133
3134        if w<=10:
3135            return
3136        if w < 18:
3137            celltxt = '.'
3138            return
3139
3140        fgcolor = self.textcolor
3141        if align == None:
3142            align = 'center'
3143        elif align == 'w':
3144            x1 = x1-w/2+pad
3145        elif align == 'e':
3146            x1 = x1+w/2-pad
3147
3148        tw,newlength = util.getTextLength(celltxt, w-pad, font=self.thefont)
3149        width=0
3150        celltxt = celltxt[0:int(newlength)]
3151        y=y1+h/2
3152        rect = self.create_text(x1+w/2,y,
3153                                  text=celltxt,
3154                                  fill=fgcolor,
3155                                  font=self.thefont,
3156                                  anchor=align,
3157                                  tag=('text','celltext'+str(col)+'_'+str(row)),
3158                                  width=width)
3159        return
3160
3161    def drawSelectedRow(self):
3162        """Draw a highlight rect for the currently selected rows"""
3163
3164        self.delete('rowrect')
3165        row = self.currentrow
3166        x1,y1,x2,y2 = self.getCellCoords(row,0)
3167        x2 = self.tablewidth
3168        rect = self.create_rectangle(x1,y1,x2,y2,
3169                                  fill=self.rowselectedcolor,
3170                                  outline=self.rowselectedcolor,
3171                                  tag='rowrect')
3172        self.lower('rowrect')
3173        #self.lower('fillrect')
3174        self.lower('colorrect')
3175        self.rowheader.drawSelectedRows(self.currentrow)
3176        return
3177
3178    def drawSelectedCol(self, col=None, delete=1, color=None, tag='colrect'):
3179        """Draw a highlight rect for the current column selection"""
3180
3181        if color == None:
3182            color = self.colselectedcolor
3183        if delete == 1:
3184            self.delete(tag)
3185        if len(self.model.df.columns) == 0:
3186            return
3187        if col == None:
3188            col = self.currentcol
3189        w=2
3190        x1,y1,x2,y2 = self.getCellCoords(0,col)
3191        y2 = self.rows * self.rowheight
3192        rect = self.create_rectangle(x1+w/2,y1+w/2,x2,y2+w/2,
3193                                     width=w,fill=color,outline='',
3194                                     tag=tag)
3195        self.lower('rowrect')
3196        self.lower('colrect')
3197        return
3198
3199    def drawMultipleRows(self, rowlist):
3200        """Draw more than one row selection"""
3201
3202        self.delete('multiplesel')
3203        #self.delete('rowrect')
3204        cols = self.visiblecols
3205        rows = list(set(rowlist) & set(self.visiblerows))
3206        if len(rows)==0:
3207            return
3208        for col in cols:
3209            colname = self.model.df.columns[col]
3210            #if col is colored we darken it
3211            if colname in self.columncolors:
3212                clr = self.columncolors[colname]
3213                clr = util.colorScale(clr, -30)
3214            else:
3215                clr = self.rowselectedcolor
3216            for r in rows:
3217                x1,y1,x2,y2 = self.getCellCoords(r,col)
3218                rect = self.create_rectangle(x1,y1,x2,y2,
3219                                          fill=clr,
3220                                          outline=self.rowselectedcolor,
3221                                          tag=('multiplesel','rowrect'))
3222        self.lower('multiplesel')
3223        self.lower('fillrect')
3224        self.lower('colorrect')
3225        return
3226
3227    def drawMultipleCols(self):
3228        """Draw multiple column selections"""
3229
3230        for c in self.multiplecollist:
3231            self.drawSelectedCol(c, delete=False)
3232        return
3233
3234    def drawMultipleCells(self):
3235        """Draw an outline box for multiple cell selection"""
3236
3237        self.delete('currentrect')
3238        self.delete('multicellrect')
3239        rows = self.multiplerowlist
3240        cols = self.multiplecollist
3241        if len(rows) == 0 or len(cols) == 0:
3242            return
3243        w=2
3244        x1,y1,a,b = self.getCellCoords(rows[0],cols[0])
3245        c,d,x2,y2 = self.getCellCoords(rows[len(rows)-1],cols[len(cols)-1])
3246        rect = self.create_rectangle(x1+w/2,y1+w/2,x2,y2,
3247                             outline=self.boxoutlinecolor, width=w,
3248                             tag='multicellrect')
3249        return
3250
3251    def setcellbackgr(self):
3252        clr = self.getaColor(self.cellbackgr)
3253        if clr != None:
3254            self.cellbackgr = clr
3255        return
3256
3257    def setgrid_color(self):
3258        clr = self.getaColor(self.grid_color)
3259        if clr != None:
3260            self.grid_color = clr
3261        return
3262
3263    def setrowselectedcolor(self):
3264        """Set selected row color"""
3265
3266        clr = self.getaColor(self.rowselectedcolor)
3267        if clr != None:
3268            self.rowselectedcolor = clr
3269        return
3270
3271    def getaColor(self, oldcolor):
3272
3273        import tkinter.colorchooser
3274        ctuple, newcolor = tkinter.colorchooser.askcolor(title='pick a color',
3275                                                         initialcolor=oldcolor,
3276                                                         parent=self.parentframe)
3277        if ctuple == None:
3278            return None
3279        return str(newcolor)
3280
3281    #--- Preferences stuff ---
3282
3283    def showPrefs(self, prefs=None):
3284        """Show table options dialog using an instance of prefs"""
3285
3286        if self.prefs == None:
3287            self.loadPrefs()
3288        self.prefswindow=Toplevel()
3289        x,y,w,h = self.getGeometry(self.master)
3290        #self.prefswindow.geometry('+%s+%s' %(x+w/2,y+h/2))
3291        self.prefswindow.title('Preferences')
3292        self.prefswindow.resizable(width=FALSE, height=FALSE)
3293        self.prefswindow.grab_set()
3294        self.prefswindow.transient(self)
3295
3296        frame1=Frame(self.prefswindow)
3297        frame1.pack(side=LEFT)
3298        frame2=Frame(self.prefswindow)
3299        frame2.pack()
3300        def close_prefsdialog():
3301            self.prefswindow.destroy()
3302        row=0
3303        Checkbutton(frame1, text="Show horizontal lines", variable=self.horizlinesvar,
3304                    onvalue=1, offvalue=0).grid(row=row,column=0, columnspan=2, sticky='news')
3305        row=row+1
3306        Checkbutton(frame1, text="Show vertical lines", variable=self.vertlinesvar,
3307                    onvalue=1, offvalue=0).grid(row=row,column=0, columnspan=2, sticky='news')
3308        row=row+1
3309        Checkbutton(frame1, text="Auto resize columns", variable=self.autoresizecolsvar,
3310                    onvalue=1, offvalue=0).grid(row=row,column=0, columnspan=2, sticky='news')
3311        row=row+1
3312        lblrowheight = Label(frame1,text='Row Height:')
3313        lblrowheight.grid(row=row,column=0,padx=3,pady=2)
3314        rowheightentry = Scale(frame1,from_=12,to=50,resolution=1,orient='horizontal',
3315                            variable=self.rowheightvar)
3316        rowheightentry.configure(fg='black', bg=self.bg)
3317        rowheightentry.grid(row=row,column=1)
3318        row=row+1
3319        lblcellwidth = Label(frame1,text='Cell Width:')
3320        lblcellwidth.grid(row=row,column=0,padx=3,pady=2)
3321        cellwidthentry = Scale(frame1,from_=20,to=500,resolution=10,orient='horizontal',
3322                            variable=self.cellwidthvar)
3323        cellwidthentry.configure(fg='black', bg=self.bg)
3324        cellwidthentry.grid(row=row,column=1)
3325        row=row+1
3326
3327        lbllinewidth = Label(frame1,text='Line Width:')
3328        lbllinewidth.grid(row=row,column=0,padx=3,pady=2)
3329        linewidthentry = Scale(frame1,from_=0,to=10,resolution=1,orient='horizontal',
3330                            variable=self.linewidthvar)
3331        linewidthentry.configure(fg='black', bg=self.bg)
3332        linewidthentry.grid(row=row,column=1)
3333        row=row+1
3334
3335        #fonts
3336        fts = self.getFonts()
3337        Label(frame2,text='font').grid(row=row,column=0)
3338        fb = Combobox(frame2, values=fts,
3339                       textvariable=self.fontvar)
3340        #currfont = self.prefs.get('celltextfont')
3341        fb.grid(row=row,column=1, columnspan=2, sticky='nes', padx=3,pady=2)
3342        row=row+1
3343
3344        lblfontsize = Label(frame2,text='Text Size:')
3345        lblfontsize.grid(row=row,column=0,padx=3,pady=2)
3346        fontsizeentry = Scale(frame2,from_=6,to=50,resolution=1,orient='horizontal',
3347                                variable=self.celltextsizevar)
3348        fontsizeentry.configure(fg='black', bg=self.bg)
3349        fontsizeentry.grid(row=row,column=1, sticky='wens',padx=3,pady=2)
3350        row=row+1
3351
3352        #cell alignment
3353        lbl=Label(frame2,text='Alignment:')
3354        lbl.grid(row=row,column=0,padx=3,pady=2)
3355
3356        alignments=['center','w','e']
3357        alignentry_button = Combobox(frame2, values=alignments,
3358                              textvariable=self.cellalignvar)
3359        alignentry_button.grid(row=row,column=1, sticky='nes', padx=3,pady=2)
3360        row=row+1
3361
3362        #float precision
3363        lbl=Label(frame2,text='Float precision:')
3364        lbl.grid(row=row,column=0,padx=3,pady=2)
3365        fpentry = Entry(frame2, textvariable=self.floatprecvar, width=10)
3366        fpentry.grid(row=row,column=1, sticky='nes', padx=3,pady=2)
3367        row=row+1
3368
3369        #colors
3370        style = Style()
3371        style.configure("cb.TButton", background=self.cellbackgr)
3372        cellbackgrbutton = Button(frame2, text='table background',style="cb.TButton",
3373                                  command=self.setcellbackgr)
3374
3375        cellbackgrbutton.grid(row=row,column=0,columnspan=2, sticky='news')
3376        row=row+1
3377        style = Style()
3378        style.configure("gc.TButton", background=self.grid_color)
3379        grid_colorbutton = Button(frame2, text='grid color', style="gc.TButton",
3380                                command=self.setgrid_color)
3381        grid_colorbutton.grid(row=row,column=0,columnspan=2, sticky='news')
3382        row=row+1
3383        style = Style()
3384        style.configure("rhc.TButton", background=self.rowselectedcolor)
3385        rowselectedcolorbutton = Button(frame2, text='row highlight color', style="rhc.TButton",
3386                                command=self.setrowselectedcolor)
3387        rowselectedcolorbutton.grid(row=row,column=0,columnspan=2, sticky='news')
3388        row=row+1
3389
3390        frame=Frame(self.prefswindow)
3391        frame.pack(fill=BOTH,expand=1)
3392        # Apply Button
3393        b = Button(frame, text="Apply Settings", command=self.applyPrefs)
3394        b.pack(side=LEFT,expand=1)
3395
3396        # Close button
3397        c=Button(frame,text='Close', command=close_prefsdialog)
3398        c.pack(side=LEFT,expand=1)
3399        self.prefswindow.focus_set()
3400        self.prefswindow.grab_set()
3401        self.prefswindow.wait_window()
3402        return self.prefswindow
3403
3404    def getFonts(self):
3405
3406        fonts = set(list(font.families()))
3407        fonts = sorted(list(fonts))
3408        return fonts
3409
3410    def loadPrefs(self, prefs=None):
3411        """Load table specific prefs from the prefs instance used
3412           if they are not present, create them."""
3413
3414        if prefs==None:
3415            prefs=Preferences('Table',{'check_for_update':1})
3416        self.prefs = prefs
3417        defaultprefs = {'horizlines':self.horizlines, 'vertlines':self.vertlines,
3418                        'rowheight':self.rowheight,
3419                        'cellwidth':80,
3420                        'autoresizecols': self.autoresizecols,
3421                        'align': 'w',
3422                        'floatprecision': self.floatprecision,
3423                        'celltextsize':10, 'celltextfont':'Arial',
3424                        'cellbackgr': self.cellbackgr, 'grid_color': self.grid_color,
3425                        'linewidth' : self.linewidth,
3426                        'rowselectedcolor': self.rowselectedcolor}
3427
3428        for prop in list(defaultprefs.keys()):
3429            try:
3430                self.prefs.get(prop);
3431            except:
3432                self.prefs.set(prop, defaultprefs[prop])
3433        self.defaultprefs = defaultprefs
3434
3435        #Create tkvars for dialog
3436        self.fontvar = StringVar()
3437        self.fontvar.set(self.prefs.get('celltextfont'))
3438        self.rowheightvar = IntVar()
3439        self.rowheightvar.set(self.prefs.get('rowheight'))
3440        self.rowheight = self.rowheightvar.get()
3441        self.cellwidthvar = IntVar()
3442        self.cellwidthvar.set(self.prefs.get('cellwidth'))
3443        self.cellwidth = self.cellwidthvar.get()
3444        self.cellalignvar = StringVar()
3445        self.cellalignvar.set(self.prefs.get('align'))
3446        self.align = self.cellalignvar.get()
3447        self.floatprecvar = IntVar()
3448        self.floatprecvar.set(self.prefs.get('floatprecision'))
3449        self.linewidthvar = StringVar()
3450        self.linewidthvar.set(self.prefs.get('linewidth'))
3451        self.horizlinesvar = IntVar()
3452        self.horizlinesvar.set(self.prefs.get('horizlines'))
3453        self.vertlinesvar = IntVar()
3454        self.vertlinesvar.set(self.prefs.get('vertlines'))
3455        self.autoresizecolsvar = IntVar()
3456        self.autoresizecolsvar.set(self.prefs.get('autoresizecols'))
3457        self.celltextsizevar = IntVar()
3458        self.celltextsizevar.set(self.prefs.get('celltextsize'))
3459        self.cellbackgr = self.prefs.get('cellbackgr')
3460        self.grid_color = self.prefs.get('grid_color')
3461        self.rowselectedcolor = self.prefs.get('rowselectedcolor')
3462        self.fontsize = self.celltextsizevar.get()
3463        self.thefont = (self.prefs.get('celltextfont'), self.prefs.get('celltextsize'))
3464        #self.rowheaderwidthvar = IntVar()
3465        #self.rowheaderwidthvar.set(self.prefs.get('rowheaderwidth'))
3466        #self.rowheaderwidth = self.rowheaderwidthvar.get()
3467        return
3468
3469    def savePrefs(self):
3470        """Save and set the prefs"""
3471        try:
3472            self.prefs.set('horizlines', self.horizlinesvar.get())
3473            self.horizlines = self.horizlinesvar.get()
3474            self.prefs.set('vertlines', self.vertlinesvar.get())
3475            self.vertlines = self.vertlinesvar.get()
3476            self.prefs.set('autoresizecols', self.autoresizecolsvar.get())
3477            self.autoresizecols = self.autoresizecolsvar.get()
3478            self.prefs.set('rowheight', self.rowheightvar.get())
3479            self.rowheight = self.rowheightvar.get()
3480            self.prefs.set('cellwidth', self.cellwidthvar.get())
3481            self.cellwidth = self.cellwidthvar.get()
3482            self.prefs.set('align', self.cellalignvar.get())
3483            self.align = self.cellalignvar.get()
3484            self.floatprecision = self.floatprecvar.get()
3485            self.prefs.set('floatprecision', self.floatprecvar.get())
3486            self.prefs.set('linewidth', self.linewidthvar.get())
3487            self.linewidth = self.linewidthvar.get()
3488            self.prefs.set('celltextsize', self.celltextsizevar.get())
3489            self.prefs.set('celltextfont', self.fontvar.get())
3490            self.prefs.set('cellbackgr', self.cellbackgr)
3491            self.prefs.set('grid_color', self.grid_color)
3492            self.prefs.set('rowselectedcolor', self.rowselectedcolor)
3493            #self.prefs.set('rowheaderwidth', self.rowheaderwidth)
3494            #self.rowheaderwidth = self.rowheaderwidthvar.get()
3495            self.thefont = (self.prefs.get('celltextfont'), self.prefs.get('celltextsize'))
3496            self.fontsize = self.prefs.get('celltextsize')
3497
3498        except ValueError:
3499            pass
3500        self.prefs.save_prefs()
3501        return
3502
3503    def applyPrefs(self):
3504        """Apply prefs to the table by redrawing"""
3505
3506        self.savePrefs()
3507        self.autoResizeColumns()
3508        #self.show()
3509        self.redraw()
3510        return
3511
3512    def show_progress_window(self, message=None):
3513        """Show progress bar window for loading of data"""
3514
3515        progress_win=Toplevel() # Open a new window
3516        progress_win.title("Please Wait")
3517        #progress_win.geometry('+%d+%d' %(self.parentframe.rootx+200,self.parentframe.rooty+200))
3518        #force on top
3519        progress_win.grab_set()
3520        progress_win.transient(self.parentframe)
3521        if message==None:
3522            message='Working'
3523        lbl = Label(progress_win,text=message,font='Arial 16')
3524
3525        lbl.grid(row=0,column=0,columnspan=2,sticky='news',padx=6,pady=4)
3526        progrlbl = Label(progress_win,text='Progress:')
3527        progrlbl.grid(row=1,column=0,sticky='news',padx=2,pady=4)
3528
3529        prog_bar = Progress(self.master)
3530
3531        return progress_win
3532
3533    def updateModel(self, model=None):
3534        """Should call this method when a new table model is loaded.
3535           Recreates widgets and redraws the table."""
3536
3537        if model is not None:
3538            self.model = model
3539        self.rows = self.model.getRowCount()
3540        self.cols = self.model.getColumnCount()
3541        self.tablewidth = (self.cellwidth)*self.cols
3542        self.tablecolheader.model = model
3543        self.rowheader.model = model
3544        self.tableChanged()
3545        self.adjustColumnWidths()
3546        #if hasattr(self, 'tablecolheader'):
3547            #self.tablecolheader.destroy()
3548            #self.rowheader.destroy()
3549            #self.selectNone()
3550        #self.show()
3551        return
3552
3553    def new(self):
3554        """Clears all the data and makes a new table"""
3555
3556        mpDlg = MultipleValDialog(title='Create new table',
3557                                    initialvalues=(50, 10),
3558                                    labels=('rows','columns'),
3559                                    types=('int','int'),
3560                                    parent=self.parentframe)
3561        if mpDlg.result == True:
3562            rows = mpDlg.results[0]
3563            cols = mpDlg.results[1]
3564            model = TableModel(rows=rows,columns=cols)
3565            self.updateModel(model)
3566            self.redraw()
3567        return
3568
3569    def load(self, filename=None):
3570        """load from a file"""
3571        if filename == None:
3572            filename = filedialog.askopenfilename(parent=self.master,
3573                                                      defaultextension='.mpk',
3574                                                      initialdir=os.getcwd(),
3575                                                      filetypes=[("msgpack","*.mpk"),
3576                                                                 ("pickle","*.pickle"),
3577                                                        ("All files","*.*")])
3578        if not os.path.exists(filename):
3579            print('file does not exist')
3580            return
3581        if filename:
3582            #prog_bar = Progress(self.master, row=0, column=0, columnspan=2)
3583            filetype = os.path.splitext(filename)[1]
3584            model = TableModel()
3585            model.load(filename, filetype)
3586            self.updateModel(model)
3587            self.filename = filename
3588            self.adjustColumnWidths()
3589            self.redraw()
3590            #prog_bar.pb_stop()
3591        return
3592
3593    def saveAs(self, filename=None):
3594        """Save dataframe to file"""
3595
3596        if filename == None:
3597            filename = filedialog.asksaveasfilename(parent=self.master,
3598                                                     #defaultextension='.mpk',
3599                                                     initialdir = self.currentdir,
3600                                                     filetypes=[("msgpack","*.mpk"),
3601                                                                ("pickle","*.pickle"),
3602                                                                ("All files","*.*")])
3603        if filename:
3604            self.model.save(filename)
3605            self.filename = filename
3606            self.currentdir = os.path.basename(filename)
3607        return
3608
3609    def save(self):
3610        """Save current file"""
3611
3612        self.saveAs(self.filename)
3613        return
3614
3615    def importCSV(self, filename=None, dialog=False, **kwargs):
3616        """Import from csv file"""
3617
3618        if self.importpath == None:
3619            self.importpath = os.getcwd()
3620        if filename == None:
3621            filename = filedialog.askopenfilename(parent=self.master,
3622                                                          defaultextension='.csv',
3623                                                          initialdir=self.importpath,
3624                                                          filetypes=[("csv","*.csv"),
3625                                                                     ("tsv","*.tsv"),
3626                                                                     ("txt","*.txt"),
3627                                                            ("All files","*.*")])
3628        if not filename:
3629            return
3630        if dialog == True:
3631            impdialog = ImportDialog(self, filename=filename)
3632            df = impdialog.df
3633            if df is None:
3634                return
3635        else:
3636            df = pd.read_csv(filename, **kwargs)
3637        model = TableModel(dataframe=df)
3638        self.updateModel(model)
3639        self.redraw()
3640        self.importpath = os.path.dirname(filename)
3641        return
3642
3643    def loadExcel(self, filename=None):
3644        """Load excel file"""
3645
3646        if filename == None:
3647            filename = filedialog.askopenfilename(parent=self.master,
3648                                                          defaultextension='.xls',
3649                                                          initialdir=os.getcwd(),
3650                                                          filetypes=[("xls","*.xls"),
3651                                                                     ("xlsx","*.xlsx"),
3652                                                            ("All files","*.*")])
3653        if not filename:
3654            return
3655        df = pd.read_excel(filename,sheetname=0)
3656        model = TableModel(dataframe=df)
3657        self.updateModel(model)
3658        return
3659
3660    def doExport(self, filename=None):
3661        """Do a simple export of the cell contents to csv"""
3662
3663        if filename == None:
3664            filename = filedialog.asksaveasfilename(parent=self.master,
3665                                                      defaultextension='.csv',
3666                                                      initialdir = os.getcwd(),
3667                                                      filetypes=[("csv","*.csv"),
3668                                                           ("excel","*.xls"),
3669                                                           ("html","*.html"),
3670                                                        ("All files","*.*")])
3671        if filename:
3672            self.model.save(filename)
3673        return
3674
3675    def getGeometry(self, frame):
3676        """Get frame geometry"""
3677        return frame.winfo_rootx(), frame.winfo_rooty(), frame.winfo_width(), frame.winfo_height()
3678
3679    def clearFormatting(self):
3680        self.set_defaults()
3681        self.columncolors = {}
3682        self.rowcolors = pd.DataFrame()
3683        self.columnformats['alignment'] = {}
3684        self.redraw()
3685        return
3686
3687class ToolBar(Frame):
3688    """Uses the parent instance to provide the functions"""
3689    def __init__(self, parent=None, parentapp=None):
3690
3691        Frame.__init__(self, parent, width=600, height=40)
3692        self.parentframe = parent
3693        self.parentapp = parentapp
3694        img = images.open_proj()
3695        addButton(self, 'Load table', self.parentapp.load, img, 'load table')
3696        img = images.save_proj()
3697        addButton(self, 'Save', self.parentapp.save, img, 'save')
3698        img = images.importcsv()
3699        func = lambda: self.parentapp.importCSV(dialog=1)
3700        addButton(self, 'Import', func, img, 'import csv')
3701        img = images.excel()
3702        addButton(self, 'Load excel', self.parentapp.loadExcel, img, 'load excel file')
3703        img = images.copy()
3704        addButton(self, 'Copy', self.parentapp.copyTable, img, 'copy table to clipboard')
3705        img = images.paste()
3706        addButton(self, 'Paste', self.parentapp.pasteTable, img, 'paste table')
3707        img = images.plot()
3708        addButton(self, 'Plot', self.parentapp.plotSelected, img, 'plot selected')
3709        img = images.transpose()
3710        addButton(self, 'Transpose', self.parentapp.transpose, img, 'transpose')
3711        img = images.aggregate()
3712        addButton(self, 'Aggregate', self.parentapp.aggregate, img, 'aggregate')
3713        img = images.pivot()
3714        addButton(self, 'Pivot', self.parentapp.pivot, img, 'pivot')
3715        img = images.melt()
3716        addButton(self, 'Melt', self.parentapp.melt, img, 'melt')
3717        img = images.merge()
3718        addButton(self, 'Merge', self.parentapp.doCombine, img, 'merge, concat or join')
3719        img = images.table_multiple()
3720        addButton(self, 'Table from selection', self.parentapp.tableFromSelection,
3721                    img, 'sub-table from selection')
3722        img = images.filtering()
3723        addButton(self, 'Query', self.parentapp.queryBar, img, 'filter table')
3724        img = images.calculate()
3725        addButton(self, 'Evaluate function', self.parentapp.evalBar, img, 'calculate')
3726        img = images.fit()
3727        addButton(self, 'Stats models', self.parentapp.statsViewer, img, 'model fitting')
3728
3729        img = images.table_delete()
3730        addButton(self, 'Clear', self.parentapp.clearTable, img, 'clear table')
3731        #img = images.prefs()
3732        #addButton(self, 'Prefs', self.parentapp.showPrefs, img, 'table preferences')
3733        return
3734
3735class ChildToolBar(ToolBar):
3736    """Smaller toolbar for child table"""
3737    def __init__(self, parent=None, parentapp=None):
3738        Frame.__init__(self, parent, width=600, height=40)
3739        self.parentframe = parent
3740        self.parentapp = parentapp
3741        img = images.open_proj()
3742        addButton(self, 'Load table', self.parentapp.load, img, 'load table')
3743        img = images.importcsv()
3744        func = lambda: self.parentapp.importCSV(dialog=1)
3745        addButton(self, 'Import', func, img, 'import csv')
3746        img = images.plot()
3747        addButton(self, 'Plot', self.parentapp.plotSelected, img, 'plot selected')
3748        img = images.transpose()
3749        addButton(self, 'Transpose', self.parentapp.transpose, img, 'transpose')
3750        img = images.copy()
3751        addButton(self, 'Copy', self.parentapp.copyTable, img, 'copy to clipboard')
3752        img = images.paste()
3753        addButton(self, 'Paste', self.parentapp.pasteTable, img, 'paste table')
3754        img = images.table_delete()
3755        addButton(self, 'Clear', self.parentapp.clearTable, img, 'clear table')
3756        img = images.cross()
3757        addButton(self, 'Close', self.parentapp.remove, img, 'close')
3758        return
3759
3760class statusBar(Frame):
3761    """Status bar class"""
3762    def __init__(self, parent=None, parentapp=None):
3763
3764        Frame.__init__(self, parent)
3765        self.parentframe = parent
3766        self.parentapp = parentapp
3767        df = self.parentapp.model.df
3768        sfont = ("Helvetica bold", 10)
3769        clr = '#A10000'
3770        self.rowsvar = StringVar()
3771        self.rowsvar.set(len(df))
3772        l=Label(self,textvariable=self.rowsvar,font=sfont,foreground=clr)
3773        l.pack(fill=X, side=LEFT)
3774        Label(self,text='rows x',font=sfont,foreground=clr).pack(side=LEFT)
3775        self.colsvar = StringVar()
3776        self.colsvar.set(len(df.columns))
3777        l=Label(self,textvariable=self.colsvar,font=sfont,foreground=clr)
3778        l.pack(fill=X, side=LEFT)
3779        Label(self,text='columns',font=sfont,foreground=clr).pack(side=LEFT)
3780        self.filenamevar = StringVar()
3781        l=Label(self,textvariable=self.filenamevar,font=sfont)
3782        l.pack(fill=X, side=RIGHT)
3783        fr = Frame(self)
3784        fr.pack(fill=Y,side=RIGHT)
3785
3786        img = images.contract_col()
3787        addButton(fr, 'Contract Cols', self.parentapp.contractColumns, img, 'contract columns', side=LEFT, padding=1)
3788        img = images.expand_col()
3789        addButton(fr, 'Expand Cols', self.parentapp.expandColumns, img, 'expand columns', side=LEFT, padding=1)
3790        img = images.zoom_out()
3791        addButton(fr, 'Zoom Out', self.parentapp.zoomOut, img, 'zoom out', side=LEFT, padding=1)
3792        img = images.zoom_in()
3793        addButton(fr, 'Zoom In', self.parentapp.zoomIn, img, 'zoom in', side=LEFT, padding=1)
3794        return
3795
3796    def update(self):
3797        """Update status bar"""
3798
3799        model = self.parentapp.model
3800        self.rowsvar.set(len(model.df))
3801        self.colsvar.set(len(model.df.columns))
3802        if self.parentapp.filename != None:
3803            self.filenamevar.set(self.parentapp.filename)
3804        return