COMMAND（ratio）

ratio(USP)

Name

ratio : Returns a ratio

Synopsis

Usage : ratio [key=<key>] val=<val> <file>

Options : -<p>

+<n>h

--first

--last

--max

--min

--auto

-e

-s<c>

Version : Tue Jan 9 09:02:34 JST 2024

Edition : 1

Description

Within a group of records (rows) in <file> (or stdin) that have the

same value in the <key> field , calculates the ratio that each row

makes up out of the total of all rows in the group, and inserts the

ratio as a new field immediately following the key field.

<key> designates the field position as fllows:

single field 2 the 2nd field

NF the last field

NF-1 the field just before the last field

contiguous fields 2/4 from the 2nd field to the 4th field

4/2 from the 4th field to the 2nd field

NF-3/NF from NF-3 field to the NF field

combination 2@NF the 2nd field and the NF field

There is no limit on the length of the key field or on the number

of key fields. The key field can also contain multi-byte characters

such as Japanese.

If you specify "r" as comparison method after the field position,

the fields are compared in reverse order. If you specify "n" as

comparison method after the field position, that field's values will

be compared as numbers. If you specify "nr" as comparison method

after the field, the values will be compared in reverse order as

numbers. If you specify comparison method before or after the "/",

you must use the same comparison method for both fields.

2n/5n OK

2n/5nr Error

2n/5r Error

When you specify "e" as comparison method or specify -e ootion and no

method, characters in the field are replaced as follows and compared

as string:

_ ==> 0x20 (space)

\0 ==> 0x00 (null)

\t ==> 0x09 (tab stop)

\n ==> 0x0a (new line)

\r ==> 0x0d (carrige return)

\_ ==> 0x5f (underscore)

\\ ==> 0x5c (back slash)

<val> is same as <key> except no comparison method is allowed.

Example 1

(Original Data: data) Shop Date Sold_Qty Cust_Qty

$ cat data

aShop Day1 103 62

bShop Day1 211 113

cShop Day1 81 52

dShop Day1 75 48

eShop Day1 210 140

Calculates ratio of Sold_Qty (3rd Field) for each shop and inserts it

after Sold_Qty.

$ ratio val=3 data | fcols

aShop Day1 103 15.1 62

bShop Day1 211 31.0 113

cShop Day1 81 11.9 52

dShop Day1 75 11.0 48

eShop Day1 210 30.9 140

^^^^ Ratio Field

Example 2 "key" Option

Calculates the ratio of each record within a group of records who share

the same value in the <key> field.

The file must be sorted on the key field.

(Original Data: data2) Shop Date Sold_Qty Cust_Qty

$ cat data2

aShop Day1 103 62

aShop Day2 157 94

aShop Day3 62 30

bShop Day1 211 113

bShop Day2 237 121

bShop Day3 150 82

cShop Day1 81 52

cShop Day2 76 49

cShop Day3 38 21

dShop Day1 75 48

dShop Day2 72 42

dShop Day3 34 19

eShop Day1 210 140

eShop Day2 149 91

eShop Day3 120 73

Calculates ratio of Sold_Qty for each shop for each day (Field 2).

$ msort key=2/1 data2 | ratio key=2 val=3 | fcols

aShop Day1 103 15.1 62 ----

bShop Day1 211 31.0 113 |

cShop Day1 81 11.9 52 | Ratio on Day1

dShop Day1 75 11.0 48 |

eShop Day1 210 30.9 140 ----

aShop Day2 157 22.7 94 ----

bShop Day2 237 34.3 121 |

cShop Day2 76 11.0 49 | Ratio on Day2

dShop Day2 72 10.4 42 |

eShop Day2 149 21.6 91 ----

aShop Day3 62 15.3 30 ----

bShop Day3 150 37.1 82 |

cShop Day3 38 9.4 21 | Ratio on Day3

dShop Day3 34 8.4 19 |

eShop Day3 120 29.7 73 ----

^^^^ Ratio Field

Example 3 "+h" Option

The "+<n>h" option will skip the first <n> records in the file. This

is useful when the first line is a header line. The header for the

ratio field that is inserted is "@".

(Original Data: data3)

$ cat data3

Shop Date Sold_Qty Cust_Qty <- Header Line

aShop Day1 103 62

bShop Day1 211 113

cShop Day1 81 52

dShop Day1 75 48

eShop Day1 210 140

Skipping the first line, Calculates ratio of Sold_Qty (3rd Field) for

each shop and inserts it after Sold_Qty.

$ ratio +h val=3 data3 | fcols

Shop Date Sold_Qty @ Cust_Qty <- "@" is inserted at added field

aShop Day1 103 15.1 62

bShop Day1 211 31.0 113

cShop Day1 81 11.9 52

dShop Day1 75 11.0 48

eShop Day1 210 30.9 140

^^^^ Ratio Field

Example 4 "-<p>" Option

The -<p> option lets you set the decimal precision of the ratio.

Calculates ratio of Sold_Qty (3rd Field) for each shop out to three

decimal places.

$ ratio -3 val=3 data | fcols

aShop Day1 103 15.147 62

bShop Day1 211 31.029 113

cShop Day1 81 11.912 52

dShop Day1 75 11.029 48

eShop Day1 210 30.882 140

Example 5 Compensation option

Sums of ratios may not be 100.0 because of the rounding. Compensation

option forces sums of ratios to 100.0 by adding the difference to a

record. The record to which adding the difference is:

--first the first record

--last the last record

--max the maximum value record

--min the minimum value record

--auto the maximum value record or the minimum value record

depending on the sign of the difference

$ ratio --first val=3 data | fcols

aShop Day1 103 15.2 62 <- compensated record

bShop Day1 211 31.0 113

cShop Day1 81 11.9 52

dShop Day1 75 11.0 48

eShop Day1 210 30.9 140

$ ratio --last val=3 data | fcols

aShop Day1 103 15.1 62

bShop Day1 211 31.0 113

cShop Day1 81 11.9 52

dShop Day1 75 11.0 48

eShop Day1 210 31.0 140 <- compensated record

$ ratio --max val=3 data | fcols

aShop Day1 103 15.1 62

bShop Day1 211 31.1 113 <- compensated record

cShop Day1 81 11.9 52

dShop Day1 75 11.0 48

eShop Day1 210 30.9 140

$ ratio --min val=3 data | fcols

aShop Day1 103 15.1 62

bShop Day1 211 31.0 113

cShop Day1 81 11.9 52

dShop Day1 75 11.1 48 <- compensated record

eShop Day1 210 30.9 140

$ ratio --auto val=3 data | fcols

aShop Day1 103 15.1 62

bShop Day1 211 31.1 113 <- compensated record

cShop Day1 81 11.9 52

dShop Day1 75 11.0 48

eShop Day1 210 30.9 140

(Note)

The old syntax is "ratio [ref=<ref>] key=<key> <file>"

This old syntax won't be supported in the near future.

(See Also)

proportion(USP)