1package ui23import (4 "cmp"5 "errors"6 "log/slog"7 "strconv"89 tea "charm.land/bubbletea/v2"1011 "src.rest/cat/012/internal/sheet"12)1314// Pivot tables follow Sheets: Data > Pivot table summarizes the15// selection, or the table around the active cell (its first row naming16// the fields), on a new sheet named Pivot Table 1, and opens the pivot17// editor (pivoteditor.go) to pick rows, columns, values and filters. The18// engine keeps the results live; editing them is refused with a note on19// the context line, as Sheets refuses. Data > Frequency table counts the20// values of the active column the same way, as VisiData's Shift+F: a21// frequency table is a pivot like any other.2223func init() {24 register(25 &command{id: "data.pivot", title: "Pivot table", desc: "Summarize the selection, or the table around the active cell, on a new sheet",26 run: (*Model).createPivot,27 answer: func(m *Model, text string) (tea.Cmd, error) {28 m.createPivot()29 e, ok := m.overlay.(*pivotEditor)30 if !ok {31 return nil, errors.New(cmp.Or(m.errMsg, m.note))32 }33 return nil, e.answer(m, text)34 }},35 &command{id: "data.pivot_edit", title: "Edit pivot table", desc: "Change the pivot table's rows, columns, values and filters",36 enabled: func(m *Model) bool { _, ok := m.sheet.Pivot(); return ok },37 run: func(m *Model) tea.Cmd {38 m.openPivotEditor(m.book().Checkpoint(), nil)39 return nil40 },41 answer: func(m *Model, text string) (tea.Cmd, error) {42 if _, ok := m.sheet.Pivot(); !ok {43 return nil, errors.New(m.sheet.Name() + " has no pivot table")44 }45 return nil, m.openPivotEditor(m.book().Checkpoint(), nil).answer(m, text)46 }},47 &command{id: "data.frequency", title: "Frequency table (column stats)", desc: "Count each value of the active column, most frequent first, on a new sheet, as Sheets' Column stats do",48 run: (*Model).frequency},49 )50 // VisiData's Shift+F; plain Shift+F types an F, as in Sheets.51 keymap["alt+shift+f"] = "data.frequency"52 keyAliases["alt+F"] = "alt+shift+f"53}5455// refuseEdit reports whether r overlaps a pivot table's results or56// cells an array formula spills (unless r holds the formula too, or57// keepsSpills says the edit moves or formats cells rather than writing58// them), which can't be edited, saying so on the context line.59func (m *Model) refuseEdit(r sheet.Rect, keepsSpills bool) bool {60 if !m.mayEdit() {61 return true62 }63 if m.out != nil {64 m.note = "An output can't be changed: G sends it to a sheet, where its copy can"65 return true66 }67 if m.sheet.InPivot(r) {68 m.note = sheet.ErrPivotEdit.Error()69 return true70 }71 if keepsSpills {72 return false73 }74 if a, reg, ok := m.sheet.InRegion(r); ok {75 if reg.Linked() {76 m.note = sheet.ErrLinkedEdit.Error()77 return true78 }79 m.note = a.String() + " shows " + reg.Name + ", a notebook cell's output: change the cell, or freeze the region"80 return true81 }82 a, ok := m.sheet.InSpill(r)83 if !ok {84 return false85 }86 anchor, _ := m.sheet.SpillAnchor(a)87 m.note = a.String() + " shows part of the array " + anchor.String() + " spills: edit the formula in " + anchor.String()88 return true89}9091// Targets of editing commands, for command.edits.9293func cellTarget(m *Model) sheet.Rect { return sheet.Rect{From: m.cur, To: m.cur} }9495// rowsTarget is the whole rows of the selection and everything below,96// which inserting or deleting rows moves.97func rowsTarget(m *Model) sheet.Rect {98 r := m.selection()99 return sheet.Rect{From: sheet.Addr{Row: r.From.Row}, To: sheet.Addr{Col: sheet.MaxCols - 1, Row: sheet.MaxRows - 1}}100}101102func rowsBelowTarget(m *Model) sheet.Rect {103 r := rowsTarget(m)104 r.From.Row = min(m.selection().To.Row+1, sheet.MaxRows-1)105 return r106}107108// colsTarget is the whole columns of the selection and everything right109// of them.110func colsTarget(m *Model) sheet.Rect {111 r := m.selection()112 return sheet.Rect{From: sheet.Addr{Col: r.From.Col}, To: sheet.Addr{Col: sheet.MaxCols - 1, Row: sheet.MaxRows - 1}}113}114115func colsRightTarget(m *Model) sheet.Rect {116 r := colsTarget(m)117 r.From.Col = min(m.selection().To.Col+1, sheet.MaxCols-1)118 return r119}120121// noCells is command.changes for commands that move cells without122// changing any, such as inserting rows, or that only put rules on them123// (a checkbox, a dropdown).124func noCells(*Model) (sheet.Rect, bool) { return sheet.Rect{}, false }125126// selectedRowsTarget and selectedColsTarget are the whole rows or127// columns of the selection, which deleting them changes.128func selectedRowsTarget(m *Model) (sheet.Rect, bool) {129 r := m.selection()130 return sheet.Rect{From: sheet.Addr{Row: r.From.Row}, To: sheet.Addr{Col: sheet.MaxCols - 1, Row: r.To.Row}}, true131}132133func selectedColsTarget(m *Model) (sheet.Rect, bool) {134 r := m.selection()135 return sheet.Rect{From: sheet.Addr{Col: r.From.Col}, To: sheet.Addr{Col: r.To.Col, Row: sheet.MaxRows - 1}}, true136}137138// pasteTarget is where Ctrl+V writes: the selection, and at least the139// clip's size from the active cell.140func (m *Model) pasteTarget() sheet.Rect {141 r := m.selection()142 if c := m.copied.clip; c != nil {143 cols, rows := c.Size()144 r.To.Col = min(max(r.To.Col, r.From.Col+cols-1), sheet.MaxCols-1)145 r.To.Row = min(max(r.To.Row, r.From.Row+rows-1), sheet.MaxRows-1)146 }147 return r148}149150// pivotData is the range a new pivot or frequency table reads: the data151// commands' range, cut to the used cells, and whether it has a header row152// and at least one row below it.153func (m *Model) pivotData() (sheet.Rect, bool) {154 r := m.dataRange()155 used, ok := m.sheet.UsedRange()156 if !ok {157 return r, false158 }159 r.To.Row, r.To.Col = min(r.To.Row, used.To.Row), min(r.To.Col, used.To.Col)160 return r, r.To.Row > r.From.Row && r.To.Col >= r.From.Col161}162163// createPivot starts a pivot table on a new sheet and opens its editor.164func (m *Model) createPivot() tea.Cmd {165 r, ok := m.pivotData()166 if !ok {167 m.note = "Select the data to summarize first: a row of headers and the rows below it"168 return nil169 }170 src, start := m.sheet, m.book().Checkpoint()171 s, err := m.book().CreatePivot(src, r, "", sheet.NewPivot(src, r))172 if err != nil {173 m.fail(err.Error())174 return nil175 }176 m.showSheet(s)177 m.openPivotEditor(start, src)178 return nil179}180181// frequency makes a frequency table of the active column on a new sheet.182func (m *Model) frequency() tea.Cmd {183 r, ok := m.pivotData()184 col := m.cur.Col185 if !ok || col < r.From.Col || col > r.To.Col {186 m.note = "Move to a column of data first: a header and the values below it"187 return nil188 }189 span := m.spans.Start("frequency", slog.Int("rows", r.To.Row-r.From.Row))190 defer span.End()191 p := sheet.FrequencyPivot(m.sheet, r, col)192 field := m.book().FieldName(p, col)193 s, err := m.book().CreatePivot(m.sheet, r, "Frequency of "+field, p)194 if err != nil {195 m.fail(err.Error())196 return nil197 }198 m.showSheet(s)199 out, _ := s.PivotRange()200 distinct := max(out.To.Row-1, 0) // less the header and Grand Total rows201 m.note = "Counted " + field + ": " + strconv.Itoa(distinct) + plural(distinct, " distinct value", " distinct values") +202 " " + m.th.KeyHints("Ctrl+Z", "undo")203 return nil204}