"

 COPYRIGHT (c) 2018 by eXept Software AG

              All Rights Reserved

 This software is furnished under a license and may be used

 only in accordance with the terms of that license and with the

 inclusion of the above copyright notice.   This software may not

 be provided or otherwise made available to, or used by, any

 other person.  No title to or ownership of the software is

 hereby transferred.

"

"{ Package: 'stx:libbasic2' }"

"{ NameSpace: Smalltalk }"

Object subclass:#TableData

	instanceVariableNames:'rowData columnNames columnTypes tableName'

	classVariableNames:''

	poolDictionaries:''

	category:'Collections-Sequenceable-Table'

!

!TableData class methodsFor:'documentation'!

copyright

"

 COPYRIGHT (c) 2018 by eXept Software AG

              All Rights Reserved

 This software is furnished under a license and may be used

 only in accordance with the terms of that license and with the

 inclusion of the above copyright notice.   This software may not

 be provided or otherwise made available to, or used by, any

 other person.  No title to or ownership of the software is

 hereby transferred.

"

!

documentation

"   Unfinished

    Ongoing work to support some algorithms on table data (such as CSV files).

    Snippets to read and process tabular (CSV) data

    Snippets to generate a plot.

    The category DWD (Deutscher Wetter Dienst / German weather service)

    contains specific code to deal with historic weather data.

    plot requires the r language to be installed (uses 'r' command).

    [author:]

        Claus Gittinger

    [instance variables:]

        rowData                 the actual data

        columnNames             name per column 

        columnTypes             type per column (default is String)

        tableName               name - only used for labeling graphs

    [class variables:]

    [see also:]

"

!

examples

"  

     self new 

        readCSV:'/Users/exept/Downloads/masie_4km_allyears_extent_sqkm.csv';

        inspect.

     ((self new 

        readCSV:'/Users/exept/Downloads/masie_4km_allyears_extent_sqkm.csv' separator:$, skip:1)

            masie_meanPerMonthOfColumnNamed:'(0) Northern_Hemisphere')

                plot

     '/tmp/xxx.csv' asFilename writingFileDo:[:s |

        ((self new 

            readCSV:'/Users/exept/Downloads/masie_4km_allyears_extent_sqkm.csv' separator:$, skip:1)

                masie_meanPerMonthOfColumnNamed:'(0) Northern_Hemisphere')

                    writeCSVToStream:s

      ]  

     |data tmax|

     data := self fromFile:'/Users/exept/Desktop/klima/monatswerte_KL_04927_17920101_19840731_hist/produkt_klima_monat_17920101_19840731_04927.txt'.

     tmax := data withColumnsNamed:#('MESS_DATUM_BEGINN' 'MO_TT').

     tmax tableName:'Stuttgart 04927'.

     tmax plot:{ #x -> 'MESS_DATUM_BEGINN' . #y -> { 'MO_TT'  }}.

     |data|

     data := self fromFile:'/Users/exept/Desktop/klima/monatswerte_KL_04927_17920101_19840731_hist/produkt_klima_monat_17920101_19840731_04927.txt'.

     data := data withColumnsNamed:#('MESS_DATUM_BEGINN' 'MO_TT').

     data := data dwd_meanPerYearOfColumnNamed:'MO_TT'.

     data tableName:'Stuttgart 04927'.

     data plot:{ #x -> 'MESS_DATUM' . #y -> { 'Mean_Per_Year_MO_TT'  }}.

     |data|

     data := self fromFile:'/Users/exept/Desktop/klima/monatswerte_KL_05792_19000801_20181231_hist/produkt_klima_monat_19000801_20181231_05792.csv'.

     data := data withColumnsNamed:#('MESS_DATUM_BEGINN' 'MO_TT').

     data := data dwd_meanPerYearOfColumnNamed:'MO_TT'.

     data tableName:'Zugspitze 04927'.

     data plot:{ #x -> 'MESS_DATUM' . #y -> { 'Mean_Per_Year_MO_TT'  }}.

"

! !

!TableData class methodsFor:'instance creation'!

fromFile:filename

    ^ self new readCSV:filename

    "

     self fromFile:'/Users/cg/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt'

    "

    "Created: / 06-01-2019 / 13:28:53 / Claus Gittinger"

    "Modified: / 09-01-2019 / 12:08:02 / Claus Gittinger"

!

rows:rowData columnNames:names

    ^ self new rows:rowData columnNames:names

    "Created: / 06-01-2019 / 13:25:37 / Claus Gittinger"

!

rows:rowData columnNames:names tableName:tableName

    ^ self new rows:rowData columnNames:names tableName:tableName

    "Created: / 06-01-2019 / 19:24:47 / Claus Gittinger"

! !

!TableData methodsFor:'accessing'!

getColumn:index

    "return a column (by index) as a vector"

    ^ rowData collect:[:row | row at:index].

    "

     self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt';

        getColumn:1.

    "

    "Created: / 06-01-2019 / 19:04:08 / Claus Gittinger"

    "Modified (comment): / 30-05-2019 / 11:29:54 / Claus Gittinger"

!

getColumnNamed:name

    "return a column (by name) as a vector"

    ^ self getColumn:(self indexOfColumnNamed:name)

    "

     self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt';

        getColumnNamed:'TXK'.

    "

    "/ StringCollection('MESS_DATUM' 'QN_3' 'FX' 'FM' 'QN_4' 'RSK' 'RSKF' 'SDK' 'SHK_TAG' 'NM' 'VPM' 'PM' 'TMK' 'UPM' 'TXK' 'TNK' 'TGK' 'eor')

    "Created: / 06-01-2019 / 19:04:24 / Claus Gittinger"

    "Modified (comment): / 30-05-2019 / 11:30:00 / Claus Gittinger"

!

getColumns:indexCollection

    "return multiple columns (by index vector) as a vector of columns"

    ^ rowData collect:[:row | 

        indexCollection collect:[:eachColIdx | row at:eachColIdx] as:Array.

    ].        

    "

     self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt';

        getColumns:#(1 2 3).

    "

    "Created: / 06-01-2019 / 19:04:47 / Claus Gittinger"

    "Modified (comment): / 30-05-2019 / 11:30:30 / Claus Gittinger"

!

getColumnsNamed:names

    "return multiple columns (by name vector) as a vector of columns"

    |colsIndices|

    colsIndices := names collect:[:eachName | self indexOfColumnNamed:eachName].

    ^ self getColumns:colsIndices

    "

     self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt';

        getColumnsNamed:#('MESS_DATUM' 'TXK' 'TNK').

    "

    "/ StringCollection('MESS_DATUM' 'QN_3' 'FX' 'FM' 'QN_4' 'RSK' 'RSKF' 'SDK' 'SHK_TAG' 'NM' 'VPM' 'PM' 'TMK' 'UPM' 'TXK' 'TNK' 'TGK' 'eor')

    "Created: / 06-01-2019 / 19:04:56 / Claus Gittinger"

    "Modified (comment): / 30-05-2019 / 11:30:41 / Claus Gittinger"

!

tableName:aString

    "set the table's name"

    tableName := aString.

    "Modified (comment): / 30-05-2019 / 11:30:52 / Claus Gittinger"

!

tableNamePrefix:aString

    "prepend a prefix to the table's name"

    tableName := aString,tableName.

    "Created: / 06-01-2019 / 21:13:12 / Claus Gittinger"

    "Modified (comment): / 30-05-2019 / 11:31:08 / Claus Gittinger"

!

tableNameSuffix:aString

    "append a suffix to the table's name"

    tableName := tableName,aString.

    "Created: / 06-01-2019 / 21:13:01 / Claus Gittinger"

    "Modified (comment): / 30-05-2019 / 11:31:17 / Claus Gittinger"

! !

!TableData methodsFor:'analysis'!

addBincoSlidingMean3ForColumnNamed:colName

    "add a column with the sliding binco mean (1/4 + 1/2 + 1/4).

     As binco:3 is quite common,

     that is a tuned version of addBincoSlidingMean:3 forColumnNamed:colName"

    | outRows valColIdx prevRow firstIndex lastIndex meanColIdx|

    valColIdx := self indexOfColumnNamed:colName.

    outRows := OrderedCollection new.

    prevRow := nil.

    firstIndex := 1. lastIndex := rowData size.

    rowData := rowData collectWithIndex:[:row :idx|

        |slidingSum|

        ((idx > firstIndex) and:[idx < lastIndex]) ifTrue:[

            slidingSum := (

                               ((rowData at:idx-1) at:valColIdx)

                             + (((rowData at:idx) at:valColIdx)*2)

                             + ((rowData at:idx+1) at:valColIdx)

                          ) / 4.

        ].

        row copyWith:slidingSum.

    ].

    meanColIdx := columnNames size+1.

    (rowData at:firstIndex) at:meanColIdx put:((rowData at:firstIndex+1) at:meanColIdx).

    (rowData at:lastIndex) at:meanColIdx put:((rowData at:lastIndex-1) at:meanColIdx).

    columnNames := columnNames copyWith:('Sliding_Binco_Mean_of_',colName).

    ^ self 

    "

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            meanPerYearOfColumnNamed:'TXK')

                addBincoSlidingMean3ForColumnNamed:'Mean_Per_Year_TXK')

                    tableName:'Feldberg/Schwarzwald';

                    plot:{ #x -> 'MESS_DATUM' . #y -> { 'Mean_Per_Year_TXK' . 'Sliding_Binco_Mean_of_Mean_Per_Year_TXK' .  }}.

    "

    "/ StringCollection('MESS_DATUM' 'QN_3' 'FX' 'FM' 'QN_4' 'RSK' 'RSKF' 'SDK' 'SHK_TAG' 'NM' 'VPM' 'PM' 'TMK' 'UPM' 'TXK' 'TNK' 'TGK' 'eor')

    "Created: / 08-01-2019 / 00:10:16 / Claus Gittinger"

    "Modified (comment): / 09-01-2019 / 12:32:34 / Claus Gittinger"

!

addBincoSlidingMean:n forColumnNamed:colName

    "add a column with the sliding binco mean (1/2^n + ... + 1/4 + 1/2 + 1/4 + ... + 1/2^n)"

    |nH outRows valColIdx prevRow firstIndex lastIndex meanColIdx wAll|

    self assert:(n odd).

    nH := n // 2.

    valColIdx := self indexOfColumnNamed:colName.

    outRows := OrderedCollection new.

    prevRow := nil.

    firstIndex := 1+nH. lastIndex := rowData size-nH.

    wAll := 0.

    1 to:nH do:[:delta |

        wAll := wAll + (1 / (2 raisedTo:delta)).    

        wAll := wAll + (1 / (2 raisedTo:delta)).    

    ].

    wAll := wAll + 1.

    rowData := rowData collectWithIndex:[:row :idx|

        |slidingSum|

        ((idx >= firstIndex) and:[idx <= lastIndex]) ifTrue:[

            slidingSum := 0.

            "/ ... /8 /4 /2 /1 /2 /4 /8 ...

            1 to:nH do:[:delta |

                slidingSum := slidingSum + (((rowData at:idx-delta) at:valColIdx) / (2 raisedTo:delta)).    

                slidingSum := slidingSum + (((rowData at:idx+delta) at:valColIdx) / (2 raisedTo:delta)).    

            ].

            slidingSum := slidingSum + ((rowData at:idx) at:valColIdx).

            slidingSum := slidingSum / wAll.

        ].

        row copyWith:slidingSum.

    ].

    meanColIdx := columnNames size+1.

    1 to:nH do:[:delta |

        (rowData at:0+delta) at:meanColIdx put:((rowData at:nH+1) at:meanColIdx)

    ].

    1 to:nH do:[:delta |

        (rowData at:rowData size+1-delta) at:meanColIdx put:((rowData at:rowData size-nH-1) at:meanColIdx)

    ].

    columnNames := columnNames copyWith:('Sliding_Binco_Mean_of_',colName).

    ^ self 

    "

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            meanPerYearOfColumnNamed:'TXK')

                addBincoSlidingMean:5 forColumnNamed:'Mean_Per_Year_TXK')

                    tableName:'Feldberg/Schwarzwald';

                    plot:{ #x -> 'MESS_DATUM' . 

                           #y -> { 'Mean_Per_Year_TXK' . 'Sliding_Binco_Mean_of_Mean_Per_Year_TXK' .  }}.

    "

    "/ StringCollection('MESS_DATUM' 'QN_3' 'FX' 'FM' 'QN_4' 'RSK' 'RSKF' 'SDK' 'SHK_TAG' 'NM' 'VPM' 'PM' 'TMK' 'UPM' 'TXK' 'TNK' 'TGK' 'eor')

    "Created: / 07-01-2019 / 15:37:16 / Claus Gittinger"

    "Modified (comment): / 09-01-2019 / 12:22:31 / Claus Gittinger"

!

addSlidingMean:n forColumnNamed:colName

    "add a column with the sliding mean.

     The sliding mean looks meaner, but may introduce lag (phase shift),

     which binco avoids."

    |slidingValues slidingSum outRows valColIdx prevYear sum countDaysPerYear|

    valColIdx := self indexOfColumnNamed:colName.

    "/ take the the first n values as initial sliding mean

    slidingValues := (1 to:n) collect:[:rowIdx | (rowData at:rowIdx) at:valColIdx] as:OrderedCollection.

    slidingSum := slidingValues sum.

    outRows := OrderedCollection new.

    prevYear := nil.

    sum := countDaysPerYear := 0.

    rowData := rowData collect:[:row |

        | rowsDate rowsYear val|

        slidingSum := slidingSum - slidingValues first.

        slidingValues removeFirst.

        slidingValues add:(row at:valColIdx).

        slidingSum := slidingSum + slidingValues last.

        row copyWith:(slidingSum / n).

    ].

    columnNames := columnNames copyWith:('Sliding_Mean_of_',colName).

    ^ self 

    "

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            meanPerYearOfColumnNamed:'TXK')

                addSlidingMean:11 forColumnNamed:'Mean_Per_Year_TXK')

                    tableName:'Feldberg/Schwarzwald';

                    plot:{ #x -> 'MESS_DATUM' . #y -> { 'Mean_Per_Year_TXK' . 'Sliding_Mean_of_Mean_Per_Year_TXK' .  }}.

    "

    "/ StringCollection('MESS_DATUM' 'QN_3' 'FX' 'FM' 'QN_4' 'RSK' 'RSKF' 'SDK' 'SHK_TAG' 'NM' 'VPM' 'PM' 'TMK' 'UPM' 'TXK' 'TNK' 'TGK' 'eor')

    "Created: / 06-01-2019 / 20:05:42 / Claus Gittinger"

    "Modified (comment): / 09-01-2019 / 12:33:16 / Claus Gittinger"

!

extractRowsWhere:filterBlock

    "return a new table containing only rows for which filterBlock evaluates to true"

    |outRows|

    outRows := rowData select:filterBlock.

    ^ self class 

        rows:outRows columnNames:columnNames

    "

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            meanPerMonthOfColumnNamed:'TXK')

                extractRowsWhere:[:row | (row at:1) startsWith:'1945'])

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            meanPerMonthOfColumnNamed:'TXK')

                extractRowsWhere:[:row | ((row at:1) from:5 to:6) = '01'])

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            meanPerMonthOfColumnNamed:'TXK')

                extractMonth:8)

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            meanPerMonthOfColumnNamed:'TXK')

                extractMonth:9)

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            meanPerMonthOfColumnNamed:'TXK')

                extractMonth:10)

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19530101_20171231_04931.txt')

            meanPerMonthOfColumnNamed:'TXK')

                extractMonth:10)

                    tableNameSuffix:'-Echterdingen';

                    plot

    "

    "/ StringCollection('MESS_DATUM' 'QN_3' 'FX' 'FM' 'QN_4' 'RSK' 'RSKF' 'SDK' 'SHK_TAG' 'NM' 'VPM' 'PM' 'TMK' 'UPM' 'TXK' 'TNK' 'TGK' 'eor')

    "Created: / 09-01-2019 / 12:35:44 / Claus Gittinger"

! !

!TableData methodsFor:'analysis - DWD'!

dwd_extractMonth:monthIndex

    "return a new table containing only rows for that month.

     This is specific to DWD data"

    ^ self dwd_extractMonth:monthIndex columnName:'MESS_DATUM' format:'%4y%2m%2d'

    "

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            dwd_meanPerMonthOfColumnNamed:'TXK')

                dwd_extractMonth:1 columnName:'MESS_DATUM' format:'%4y%2m')

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            dwd_meanPerMonthOfColumnNamed:'TXK')

                dwd_extractMonth:2 columnName:'MESS_DATUM' format:'%4y%2m')

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            dwd_meanPerMonthOfColumnNamed:'TXK')

                dwd_extractMonth:8 columnName:'MESS_DATUM' format:'%4y%2m')

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            dwd_meanPerMonthOfColumnNamed:'TXK')

                dwd_extractMonth:9 columnName:'MESS_DATUM' format:'%4y%2m')

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            dwd_meanPerMonthOfColumnNamed:'TXK')

                dwd_extractMonth:10 columnName:'MESS_DATUM' format:'%4y%2m')

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19530101_20171231_04931.txt')

            dwd_meanPerMonthOfColumnNamed:'TXK')

                dwd_extractMonth:10 columnName:'MESS_DATUM' format:'%4y%2m')

                    tableNameSuffix:'-Echterdingen';

                    plot

    "

    "/ StringCollection('MESS_DATUM' 'QN_3' 'FX' 'FM' 'QN_4' 'RSK' 'RSKF' 'SDK' 'SHK_TAG' 'NM' 'VPM' 'PM' 'TMK' 'UPM' 'TXK' 'TNK' 'TGK' 'eor')

    "Created: / 06-01-2019 / 19:00:52 / Claus Gittinger"

    "Modified (comment): / 09-01-2019 / 18:30:09 / Claus Gittinger"

!

dwd_extractMonth:monthIndex columnName:monthColumnName

    "return a new table containing only rows for that month.

     This is specific to DWD data"

    ^ self dwd_extractMonth:monthIndex columnName:monthColumnName format:'%4y%2m%2d'

    "

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            dwd_meanPerMonthOfColumnNamed:'TXK')

                dwd_extractMonth:1 columnName:'MESS_DATUM' format:'%4y%2m')

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            dwd_meanPerMonthOfColumnNamed:'TXK')

                dwd_extractMonth:2 columnName:'MESS_DATUM' format:'%4y%2m')

                    plot

     (((self new 

        readCSV:'~/Documents/klima/data/DWD/daily/kl/historical/produkt_klima_tag_19450101_20171231_01346.txt')

            dwd_meanPerMonthOfColumnNamed:'TXK')

                dwd_extractMonth:8 columnName:'MESS_DATUM' format:'%4y%2m')

                    plot

     (((self new