3.55 mslide - Slide Data Series

Shift data series in the specified column to a new column by specified number of times. For example, the function can be used to calculate difference between data items in the same field such as deriving the rate of return using daily stock price data (today’s stock price/previous day’s stock price)

Table 3.28 - 3.31 below highlights a commonly used example.

Table 3.28: input data

date	val
4/6	1
4/7	2
4/8	3
4/9	4

Table 3.29: f=val:nextVal

date	val	nextVal
4/6	1	2
4/7	2	3
4/8	3	4

Table 3.30: f=val:nextVal -r

date	val	nextVal
4/7	2	1
4/8	3	2
4/9	4	3

Table 3.31: f=val t=2

date	val	val1	val2
4/6	1	2	3
4/7	2	3	4

Table 3.28 shows the input data containing daily total values for four consecutive days. The figures could represent the changes in supermarket sales or stock price trends. Calculate the rate of increase between two days (for simplicity, assume "rate of increase = rate of day2/rate of day1").

The input data contains data series from 4/6 to 4/9. In Table 3.29, the data series is shifted up by one line and stored in a new column (newVal). The rate of increase is calculated from nextVal/val using mcal command. After the records have shifted, the last record in the input data dated 4/9 is not longer available. In this case, specify the -n option to print NULL values.

Table 3.29 shows the data output when sliding up the data series by one row, it is also possible to slide in reverse order by sliding the first row to a new column as shown in (Table 3.30).

Furthermore, t= allows user to specify the number of times to slide up or down. Table3.31 shows the result when t=2. The following function is the same as using mslide consecutively:

"mslide f=val:val1 mslide f=val1:val2|".

When t= parameter is used, field names are created for each new column based on the field name specified at f= followed by an incremental value. When t= parameter is used with -l option, data from the initial column and the last column is displayed.

The functions of mwindow and mslide are similar. The difference between the two is that mslide is used for calculation between data items, and the command is often followed by mcal or msel. mwindow is used for calculation by row, which is usually followed by msum or mavg.

Format

mslide f= [s=] [k=key] [t=] [-r] [-n] [-l] [i=] [o=] [-nfn] [-nfno] [-x] [-q] [--help] [--version]

`f=`	Field name for sliding records. Multiple fields can be specified.
	The If you do not specify `t=`, the field name can be specified by f=fieldname:newfieldname.
`s=`	After the specified field is sorted (multiple fields can be specified), records are shifted.
	`s=` parameter is required when `-q` option is not specified.
`k=`	Specify the field for shifting of records.
`t=`	Number of times (rows) to shift. Default value is `t=1` if this parameter is not defined.
`-r`	Shift records in the opposite direction (shift the first record below).
`-n`	Print a null value if next (or previous) line is not available.
`-l`	Print results from the final shift.

Examples

Example 1: Basic Example

$ more dat1.csv
date,val
20130406,1
20130407,2
20130408,3
20130409,4
$ mslide s=date f=val:newVal i=dat1.csv o=rsl1.csv
#END# kgslide f=val:newVal i=dat1.csv o=rsl1.csv s=date
$ more rsl1.csv
date%0,val,newVal
20130406,1,2
20130407,2,3
20130408,3,4

Example 2: Slide rows in reverse direction

$ mslide s=date f=val:newVal -r i=dat1.csv o=rsl2.csv
#END# kgslide -r f=val:newVal i=dat1.csv o=rsl2.csv s=date
$ more rsl2.csv
date%0,val,newVal
20130407,2,1
20130408,3,2
20130409,4,3

Example 3: Slide records more than once

$ mslide s=date f=val t=2 i=dat1.csv o=rsl3.csv
#END# kgslide f=val i=dat1.csv o=rsl3.csv s=date t=2
$ more rsl3.csv
date%0,val,val1,val2
20130406,1,2,3
20130407,2,3,4

Example 4: Display output from the last column shifted

$ mslide s=date f=val t=2 -l i=dat1.csv o=rsl4.csv
#END# kgslide -l f=val i=dat1.csv o=rsl4.csv s=date t=2
$ more rsl4.csv
date%0,val,val2
20130406,1,3
20130407,2,4

Example 5: Change multiple field names

$ mslide s=date f=date:d_,val:v_ t=2 i=dat1.csv o=rsl5.csv
#END# kgslide f=date:d_,val:v_ i=dat1.csv o=rsl5.csv s=date t=2
$ more rsl5.csv
date%0,val,d_1,d_2,v_1,v_2
20130406,1,20130407,20130408,2,3
20130407,2,20130408,20130409,3,4