Class: Sycsvpro::SpreadSheet

Inherits:
Object
  • Object
show all
Includes:
Dsl
Defined in:
lib/sycsvpro/spread_sheet.rb

Overview

A spread sheet is used to do column and row wise calculations between spread sheets. The calculations can be *, /, + and - where the operations are conducted between corresponding columns and rows. It is not as with matrix operations.

Example:

        [0] [1]          [0] [1]
A = [0]   1   2  B = [0]   5   6 
    [1]   3   4      [1]   7   8

              [0*0]  [1*1]
A * B = [0*0]     5     12  
        [1*1]    21     32

If spread sheets are not the same size then the operation is looping through the smaller spread sheets values

Example:

        [0] [1]          [0]          [0]
A = [0]   1   2  B = [0]   5  C = [0]   8
    [1]   3   4      [1]   7

              [0*0]  [1*1]
A * B = [0*0]     5     35
        [1*1]    21     28

              [0*0]  [1*0]
A * C = [0*0]     8     16
        [1*0]    24     32

Constant Summary

Constants included from Dsl

Dsl::COMMA, Dsl::COMMA_POINT_SPACE_REGEX, Dsl::COMMA_SPACE_REGEX, Dsl::COMMA_SPLITTER_REGEX, Dsl::DECIMAL_COMMA_REGEX, Dsl::DECIMAL_POINT_REGEX, Dsl::EMPTY, Dsl::INTEGER_REGEX, Dsl::POINT, Dsl::POINT_SPACE_REGEX, Dsl::SEMICOLON

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Dsl

#clean_up, #is_float?, #is_integer?, #params, #split_by_comma_regex, #str2num, #str2utf8, #unstring, #write_to

Constructor Details

#initialize(*rows) ⇒ SpreadSheet

Creates a new spread sheet with rows and optional options.

SpreadSheet.new([A,1,2], [B,3,4], r: true, c: false)

rlabel: first column of the row contains labels if true clabel: first row are labels if true

Creates a spread sheet with row labels ‘A’, ‘B’ and no column labels

    [0] [1]
[A]   1   2
[B]   3   4

SpreadSheet.new(['One','Two'],['A',1,2],['B',3,4], 
                r = true, 
                c = true)

Creates a spread sheet with row and column labels

    [One] [Two]
[A]     1     2
[B]     3     4

It is also possible to specify row and column labels explicit

SpreadSheet.new([1,2],[3,4], row_labels: ['A','B'], 
                             col_labels: ['One','Two'])

Params

r

has row labels if true

c

has column labels if true

row_labels

explicitly provides row labels

col_labels

explicitly provides column labels

values

flat array with values

rows

indicates the row count in combination with values param

cols

indicates the col count in combination with values param

equalize

If columns are of different size equalizes the column size

file

file that contains values to create spread sheet with

ds

decimal spearator ‘.’ or ‘,’ where ‘.’ is default. The decimal separator is used when spread sheet is created from file



90
91
92
93
94
95
96
97
98
 
# File 'lib/sycsvpro/spread_sheet.rb', line 90

def initialize(*rows)
  opts = rows.pop if rows.last.is_a?(::Hash)
  @opts = opts || {}
  rows = rows_from_params(@opts) if rows.empty?
  rows = equalize_rows(rows) if @opts[:equalize]
  check_validity_of(rows)
  @row_labels, @col_labels = create_labels(rows)
  @rows = rows
end

Instance Attribute Details

#col_labels ⇒ Object

column labels



47
48
49
 
# File 'lib/sycsvpro/spread_sheet.rb', line 47

def col_labels
  @col_labels
end

#opts ⇒ Object

options of the spread sheet



43
44
45
 
# File 'lib/sycsvpro/spread_sheet.rb', line 43

def opts
  @opts
end

#row_labels ⇒ Object

row labels



45
46
47
 
# File 'lib/sycsvpro/spread_sheet.rb', line 45

def row_labels
  @row_labels
end

#rows ⇒ Object

rows of the spread sheet



41
42
43
 
# File 'lib/sycsvpro/spread_sheet.rb', line 41

def rows
  @rows
end

Class Method Details

.bind_columns(*sheets) ⇒ Object

Binds spread sheets column wise

    1 2 3      10 20 30
A = 4 5 6  B = 40 50 60
    7 8 9      70 80 90

C = SpeadSheet.bind_columns(A,B)

    1 2 3 10 20 30
C = 4 5 6 40 50 60
    7 8 9 70 80 90

If the spread sheets have different row sizes the columns of the spread sheet with fewer rows are filled with NotAvailable

    1 2 3      10 20 30
A = 4 5 6  B = 40 50 60
    7 8 9      

C = SpeadSheet.bind_columns(A,B)

    1 2 3 10 20 30
C = 4 5 6 40 50 60
    7 8 9 NA NA NA

The column lables are also combined from the spread sheets and the row labels of the spread sheet with the higher row count are used

Returns the result in a new spread sheet



180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/sycsvpro/spread_sheet.rb', line 180

def self.bind_columns(*sheets)
  row_count = sheets.collect { |s| s.nrows }.max
  binds = Array.new(row_count, [])
  0.upto(row_count - 1) do |r|
    sheets.each do |sheet|
      sheet_row = sheet.rows[r]
      binds[r] += sheet_row.nil? ? [NotAvailable] * sheet.ncols : sheet_row
    end
  end
  c_labels = sheets.collect { |s| s.col_labels }.inject(:+)
  r_labels = sheets.collect { |s| 
               s.row_labels if s.row_labels.size == row_count 
             }.first
  SpreadSheet.new(*binds, col_labels: c_labels, row_labels: r_labels)
end

.bind_rows(*sheets) ⇒ Object

Binds spread sheets row wise

    1 2 3      10 20 30
A = 4 5 6  B = 40 50 60
    7 8 9      

C = SpeadSheet.bind_rows(A,B)

     1  2  3 
     4  5  6
C =  7  8  9
    10 20 30
    40 50 60

If the spread sheets have different column sizes the columns of the spread sheet with fewer columns are filled with NotAvailable

    1 2 3      10 20
A = 4 5 6  B = 40 50
    7 8 9      

C = SpeadSheet.bind_rows(A,B)

     1  2  3
     4  5  6
C =  7  8  9
    10 20 NA
    40 50 NA

The row lables are also combined from the spread sheets and the column labels of the spread sheet with the higher column count are used



227
228
229
230
231
232
233
234
235
236
237
238
239
240
 
# File 'lib/sycsvpro/spread_sheet.rb', line 227

def self.bind_rows(*sheets)
  col_count = sheets.collect { |s| s.ncols }.max
  binds = []
  sheets.each do |sheet|
    binds << sheet.rows.collect { |r| 
               r + [NotAvailable] * ((col_count - r.size) % col_count) 
             }
  end
  r_labels = sheets.collect { |s| s.col_labels }.inject(:+)
  c_labels = sheets.collect { |s| s.col_labels if s.ncols == col_count }.first
  SpreadSheet.new(*binds.flatten(1), 
                  row_labels: r_labels, 
                  col_labels: c_labels)
end

Instance Method Details

#*(s) ⇒ Object

Returns the result in a new spread sheet Multiplies two spreadsheets column by column and returns a new spread sheet with the result

1 2 3   3 2 1    3  4  3
4 5 6 * 6 5 4 = 24 25 24
7 8 9   9 8 7   63 64 63



248
249
250
 
# File 'lib/sycsvpro/spread_sheet.rb', line 248

def *(s)
  process("*", s)
end

#+(s) ⇒ Object

Adds two spreadsheets column by column and returns a new spread sheet with the result

1 2 3   3 2 1    4  4  4
4 5 6 + 6 5 4 = 10 10 10 
7 8 9   9 8 7   16 16 16



266
267
268
 
# File 'lib/sycsvpro/spread_sheet.rb', line 266

def +(s)
  process("+", s)
end

#-(s) ⇒ Object

Subtracts two spreadsheets column by column and returns a new spread sheet with the result

1 2 3   3 2 1   -2 0 2
4 5 6 - 6 5 4 = -2 0 2
7 8 9   9 8 7   -2 0 2



275
276
277
 
# File 'lib/sycsvpro/spread_sheet.rb', line 275

def -(s)
  process("-", s)
end

#/(s) ⇒ Object

Divides two spreadsheets column by column and returns a new spread sheet with the result

1 2 3   3 2 1   1/3 1  3
4 5 6 / 6 5 4 = 2/3 1 6/4
7 8 9   9 8 7   7/9 1 9/7



257
258
259
 
# File 'lib/sycsvpro/spread_sheet.rb', line 257

def /(s)
  process("/", s)
end

#==(other) ⇒ Object

Compares if two spread sheets are equal. Two spread sheets are equal if the spread sheets A and B are equal if Aij = Bij, that is elements at the same position are equal



282
283
284
285
286
287
288
289
290
291
292
 
# File 'lib/sycsvpro/spread_sheet.rb', line 282

def ==(other)
  return false unless other.instance_of?(SpreadSheet)
  return false unless dim == other.dim
  row_count, col_count = dim
  0.upto(row_count - 1) do |r|
    0.upto(col_count - 1) do |c|
      return false unless rows[r][c] == other.rows[r][c]
    end
  end  
  true
end

#[](*range) ⇒ Object

Returns a subset of the spread sheet and returns a new spread sheet with the result and the corresponding row and column labels



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
 
# File 'lib/sycsvpro/spread_sheet.rb', line 129

def [](*range)
  r, c = range
  r ||= 0..(nrows-1)
  c ||= 0..(ncols-1)
 
  row_selection = rows.values_at(*r)
  col_selection = []

  if rows_are_arrays?(row_selection)
    row_selection.each do |row|
      values = row.values_at(*c)
      col_selection << (values.respond_to?(:to_ary) ? values : [values])
    end 
  else
    col_selection << row_selection[*c]
  end

  SpreadSheet.new(*col_selection, 
                  row_labels: row_labels.values_at(*r),
                  col_labels: col_labels.values_at(*c))
end

#column_collect(&block) ⇒ Object

Collects the operation on each column and returns the result in an array



300
301
302
303
304
 
# File 'lib/sycsvpro/spread_sheet.rb', line 300

def column_collect(&block)
  result = []
  0.upto(ncols-1) { |i| result << block.call(self[nil,i]) }
  result
end

#dim ⇒ Object

Returns the dimension [rows, columns] of the spread sheet

SpreadSheet.new([1,2,3], [4,5,6]).dim -> [2,3]



102
103
104
 
# File 'lib/sycsvpro/spread_sheet.rb', line 102

def dim
  [nrows, ncols]
end

#each_column ⇒ Object

Yields each column



295
296
297
 
# File 'lib/sycsvpro/spread_sheet.rb', line 295

def each_column
  0.upto(ncols-1) { |i| yield self[nil,i] }
end

#ncols ⇒ Object

Returns the number of columns



117
118
119
 
# File 'lib/sycsvpro/spread_sheet.rb', line 117

def ncols
  rows[0].size
end

#nrows ⇒ Object

Returns the number of rows



112
113
114
 
# File 'lib/sycsvpro/spread_sheet.rb', line 112

def nrows
  rows.size
end

#rename(opts = {}) ⇒ Object

Renames the row and column labels

sheet.rename(rows: ['Row 1', 'Row 2'], cols: ['Col 1', 'Col 2'])

If the provided rows and columns are larger than the spread sheet’s rows and columns then only the respective row and column values are used. If the row and column labels are fewer than the respective row and column sizes the old labels are left untouched for the missing new labels



314
315
316
317
318
319
320
321
322
323
324
325
326
327
 
# File 'lib/sycsvpro/spread_sheet.rb', line 314

def rename(opts = {})
  if opts[:rows]
    opts[:rows] = opts[:rows][0,nrows]
    opts[:rows] += row_labels[opts[:rows].size, nrows]
  end

  if opts[:cols]
    opts[:cols] = opts[:cols][0,ncols]
    opts[:cols] += col_labels[opts[:cols].size, ncols]
  end

  @row_labels = opts[:rows] if opts[:rows]
  @col_labels = opts[:cols] if opts[:cols]
end

#size ⇒ Object

Returns the size of the spread sheet, that is the count of elements



107
108
109
 
# File 'lib/sycsvpro/spread_sheet.rb', line 107

def size
  nrows * ncols
end

#summary ⇒ Object

Prints a summary of the spread sheet



359
360
361
362
363
364
365
366
 
# File 'lib/sycsvpro/spread_sheet.rb', line 359

def summary
  puts "\nSummary"
  puts   "-------\n"
  puts "rows: #{nrows}, columns: #{ncols}, dimension: #{dim}, size: #{size}"
  puts
  puts "row labels:\n #{row_labels}"
  puts "column labels:\n #{col_labels}\n"
end

#to_s ⇒ Object

Prints the spread sheet in a matrix with column labels and row labels. If no labels are available the column number and row number is printed



370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
 
# File 'lib/sycsvpro/spread_sheet.rb', line 370

def to_s
  col_label_sizes = col_labels.collect { |c| c.to_s.size + 2 }
  row_label_size = row_labels.collect { |r| r.to_s.size + 2 }.max

  row_col_sizes = rows.transpose.collect { |r| r.collect { |c| c.to_s.size } } 

  i = -1
  col_sizes = col_label_sizes.collect do |s| 
    i += 1
    [row_col_sizes[i],s].flatten.max + 1
  end

  s = (sprintf("%#{row_label_size}s", " "))
  col_labels.each_with_index { |l,i| s << (sprintf("%#{col_sizes[i]}s", 
                                                   "[#{l}]"))           } 
  s << "\n"

  rows.each_with_index do |row, i|
    s << (sprintf("%#{row_label_size}s", "[#{row_labels[i]}]"))
    row.each_with_index { |c,j| s << (sprintf("%#{col_sizes[j]}s", c)) }
    s << "\n"
  end

  s
end

#transpose ⇒ Object

Swaps rows and columns and returns new spread sheet with result



122
123
124
125
 
# File 'lib/sycsvpro/spread_sheet.rb', line 122

def transpose
  SpreadSheet.new(*rows.transpose, row_labels: col_labels, 
                                   col_labels: row_labels)
end

#write(file, opts = {}) ⇒ Object

Writes spread sheet to a file separated with ‘;’. Accepts two boolean arguments to indicate whether the row and column labels should be saved along with the spread sheet’s labels

r

when true row labels will be saved, default is true

c

when true column labels will be saved, default is true



334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
 
# File 'lib/sycsvpro/spread_sheet.rb', line 334

def write(file, opts = {})
  opts = {r: true, c: true}.merge(opts)

  if opts[:r]
    construct_row = -> row,i { row.insert(0,row_labels[i]).join(SEMICOLON) }
  else
    construct_row = -> row,i { row.join(SEMICOLON) }
  end
  
  File.open(file, 'w') do |out|
    if opts[:c]
      if opts[:r]
        out.puts "#{SEMICOLON}#{col_labels.join(SEMICOLON)}"
      else
        out.puts col_labels.join(SEMICOLON) 
      end
    end

    rows.each_with_index do |row, i| 
      out.puts construct_row.call row, i
    end
  end 
end