Class: Sycsvpro::Calculator

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

Overview

Processes operations on columns of a csv file.

A column value has to be a number in case of arithmetical operations.

Possible operations are +, -, *, /, % and **.

It is possible to use values of columns as an operator like multiply column 1 of the csv file with 2 and assign it to column 4 of the result file: c1*2

Other values might be dates or strings.

d1

date value in column 1

s2

string value in column 2

c3

number value in column 3

To assign a string from column 1 of the csv file to column 3 of the resulting file you can do like so: 3:s1

You can also use Ruby expressions to assign values: 0:.min - This will assign the least date value from columns 1, 2 and 3 to column 0.

Note: If you assign a value to column 1 and subsequently are using column 1 in other assignments then column 1 will have the result of a previous operation.

Example: Having a row “CA/123456” and you want to have 123456 in column 0 of the resulting csv file and CA in column 2. If you conduct following operations it will fail

1:s0.scan(/\/(.+)/).flatten[0]   -> 123456 
2:s0.scan(/([A-Z]+)/).flatten[0] -> nil

To achieve the required result you have to change the operational sequence like so

2:s0.scan(/([A-Z]+)/).flatten[0] -> CA
1.so.scan(/\/(.+)/).flatten[0]   -> 123456

Constant Summary

Constants included from Dsl

Dsl::COMMA_SPLITTER_REGEX

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Dsl

#clean_up, #params, #rows, #split_by_comma_regex, #str2utf8, #unstring, #write_to

Constructor Details

#initialize(options = {}) ⇒ Calculator

Creates a new Calculator. Optionally a header can be provided. The header can be supplemented with additional column names that are generated due to an arithmetic operation that creates new columns :call-seq:

Sycsvpro::Calculator.new(infile:       "in.csv",
                         outfile:      "out.csv",
                         df:           "%d.%m.%Y",
                         rows:         "1,2,BEGINn3>20END",
                         header:       "*,Count",
                         final_header: false,
                         cols:         "4:c1+c2*2",
                         write:        "1,3-5",
                         sum:          true).execute
infile

File that contains the rows to be operated on

outfile

Result of the operations

df

Date format

rows

Row filter that indicates which rows to consider

header

Header of the columns

final_header

Indicates that if write filters columns the header should

not be filtered when written

cols

Operations on the column values

write

Columns that are written to the outfile

sum

Indicate whether to add a sum row



94
95
96
97
98
99
100
101
102
103
104
105
106
 
# File 'lib/sycsvpro/calculator.rb', line 94

def initialize(options={})
  @infile       = options[:infile]
  @outfile      = options[:outfile]
  @date_format  = options[:df] || "%Y-%m-%d"
  @row_filter   = RowFilter.new(options[:rows], df: options[:df])
  @write_filter = ColumnFilter.new(options[:write], df: options[:df])
  @header       = Header.new(options[:header])
  @final_header = options[:final_header]
  @sum_row      = []
  @add_sum_row  = options[:sum]
  @formulae     = {}
  create_calculator(options[:cols])
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(id, *args, &block) ⇒ Object

Retrieves the values from a row as the result of a arithmetic operation with #eval



110
111
112
113
114
115
 
# File 'lib/sycsvpro/calculator.rb', line 110

def method_missing(id, *args, &block)
  return to_number(columns[$1.to_i]) if id =~ /c(\d+)/
  return to_date(columns[$1.to_i])   if id =~ /d(\d+)/
  return columns[$1.to_i]            if id =~ /s(\d+)/
  super
end

Instance Attribute Details

#add_sum_row ⇒ Object (readonly)

if true add a sum row at the bottom of the out file



69
70
71
 
# File 'lib/sycsvpro/calculator.rb', line 69

def add_sum_row
  @add_sum_row
end

#columns ⇒ Object (readonly)

filter that is used for columns



65
66
67
 
# File 'lib/sycsvpro/calculator.rb', line 65

def columns
  @columns
end

#date_format ⇒ Object (readonly)

date format for date operations



56
57
58
 
# File 'lib/sycsvpro/calculator.rb', line 56

def date_format
  @date_format
end

#final_header ⇒ Object (readonly)

indicates whether this header is final and should not be filtered in respect to the columns defined by write



63
64
65
 
# File 'lib/sycsvpro/calculator.rb', line 63

def final_header
  @final_header
end

#formulae ⇒ Object (readonly)

the operations on columns



58
59
60
 
# File 'lib/sycsvpro/calculator.rb', line 58

def formulae
  @formulae
end

#header ⇒ Object (readonly)

header of the outfile



60
61
62
 
# File 'lib/sycsvpro/calculator.rb', line 60

def header
  @header
end

#infile ⇒ Object (readonly)

infile contains the data that is operated on



50
51
52
 
# File 'lib/sycsvpro/calculator.rb', line 50

def infile
  @infile
end

#outfile ⇒ Object (readonly)

outfile is the file where the result is written to



52
53
54
 
# File 'lib/sycsvpro/calculator.rb', line 52

def outfile
  @outfile
end

#row_filter ⇒ Object (readonly)

filter that is used for rows



54
55
56
 
# File 'lib/sycsvpro/calculator.rb', line 54

def row_filter
  @row_filter
end

#write ⇒ Object (readonly)

selected columns to be written to outfile



67
68
69
 
# File 'lib/sycsvpro/calculator.rb', line 67

def write
  @write
end

Instance Method Details

#execute ⇒ Object

Executes the calculator and writes the result to the outfile



118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
 
# File 'lib/sycsvpro/calculator.rb', line 118

def execute
  processed_header = false

  File.open(outfile, 'w') do |out|
    File.open(infile).each_with_index do |line, index|
      next if line.chomp.empty? || unstring(line).chomp.split(';').empty?

      unless processed_header
        header_row = header.process(line.chomp)
        header_row = @write_filter.process(header_row) unless @final_header
        out.puts header_row unless header_row.nil? or header_row.empty?
        processed_header = true
        next
      end

      next if row_filter.process(line, row: index).nil?

      @columns = unstring(line).chomp.split(';')
      formulae.each do |col, formula|
        @columns[col.to_i] = eval(formula)
      end
      out.puts @write_filter.process(@columns.join(';'))

      @columns.each_with_index do |column, index|
        column = 0 unless column.to_s =~ /^[\d\.,]*$/

        if @sum_row[index]
          @sum_row[index] += to_number column
        else
          @sum_row[index] =  to_number column
        end
      end if add_sum_row

    end

    out.puts @write_filter.process(@sum_row.join(';')) if add_sum_row

  end
end