This command computes the share of attribute in each record against the total value of that attribute in all records. The share is dervived by: value_record / value_allrecords.
Summary of options and usage: xtshare
Goal: Find out the share of quantity and amount for each day with respect to the entire total
Step 1: Editing your script
Startup FD. Repulciate the script "xtcut.sh" used in the previous lesson by selecting xtcut.sh and press "c+CTRL", you will then be prompted for a new name, name the new script as "xtshare.sh"Methodology: Select "date", "quantity" and "amount" by xtcut, the result is piped to xtagg which computes the total quantity and amount based on each unique "date". The share of total quantity and total amount for each day relative to the total is then calculated by xtshare command. The output will pipe to xtheader, define a new title and comment as need, update the -o parameter and write the result to the file "xtagg.xt".
Defining Attributes and Options
Key - -k
Note: Since the share is calculated against total, the key does not need to be defined.Attributes - -f Quantity,Amount
Note: -f defines which attributes the shares will be computed on. Spaces should be omitted between multiple field argumentsYour script will look like:
#/bin/bash
#===============================================================
# MUSASHI bash script
#===============================================================
#---- title
title="Tutorial"
#---- comment
comment="xtshare"
#---- variables
inPath="/home/public/tutorial"
#---------------------------------------------------------------
# command
#---------------------------------------------------------------
xtcut -f Date,Quantity,Amount -i $inPath/dat.xt |
xtagg -k Date -f Quantity,Amount -c sum |
xtshare -f Quantity:Quantity_Share,Amount:Amount_Share |
xtheader -l "$title" -c "$comment" -o xtshare.xt #===============================================================When you are done, save and execute the script. The result should look as follows:
<?xml version="1.0" encoding="euc-jp"?>
<xmltbl version="1.00">
<header>
<title>
Tutorial
</title>
<comment>
xtagg
</comment>
<field no="1">
<name>Date</name>
</field>
<field no="2">
<name>Quantity</name>
</field>
<field no="3">
<name>Amount</name>
</field>
<field no="4">
<name>Quantity_Share</name>
</field>
<field no="3">
<name>Amount_Share</name>
</field>
</header>
<body><![CDATA[
20020101 161 60034 0.003013 0.002888
20020102 40 13959 0.000748 0.000672
20020103 155 62402 0.0029 0.003002
20020104 107 41467 0.002002 0.001995
20020105 52 21283 0.000973 0.001024
20020106 106 43070 0.001983 0.002072
20020107 87 31458 0.001628 0.001513
Step 2: After we have verified the share of quantity and amount are correct, modify the script to find out the share of quantity and amount for each 4-digit classification code in each 2-digit classificaiton code.
Methodology: Select "CategoryCode2", "CategoryCode4","quantity" and "amount" by xtcut, computes the total quantity and amount based on each unique CategoryCode2 and CategoryCode4. Calculate the share of total quantity and total amount for each 4-digit category in their parent cateogry Direct the output to xtheader, define a new title and comment as need, update the -o parameter and write the result to the file "xtagg.xt".
Specify the parameters as follows:
Key - -k CategoryCode2 Field - -f Quantity:TotalQuantity,Amount:TotalAmountYour script should look as follows:
xtcut -f CategoryCode2,CategoryCode4,Quantity,Amount -i $inPath/dat.xt |Save and execute your script. The result should be as follows:
xtagg -k CategoryCode2,CategoryCode4 -f Quantity:TotalQuantity,Amount:TotalAmount -c sum |
xtshare -k CategoryCode2 -f TotalQuantity:Quantity_Share,TotalAmount:Amount_Share |
xtheader -l "$title" -c "$comment" -o xtshare.xt
11 1101 6247 2388930 0.182431 0.179297
11 1102 1515 546193 0.044243 0.040994
11 1103 365 145373 0.010659 0.010911
11 1104 2173 849791 0.063458 0.06378
11 1105 2249 860320 0.065678 0.06457
11 1106 1277 487220 0.037292 0.036568
11 1107 2087 913009 0.060947 0.068524
11 1108 1855 621047 0.054172 0.046612
11 1110 2091 844705 0.061064 0.063398
| กก
In some instances, you may see null values (*) in your reports. This following are possible reasons:
Share is represented by a null value due to the fact that some products does not have the middle classification code. The share value will be null when there is a null value in the records used in the calculation of share. MUSASHI adopts the rules where computation involving a null value, values with zero division, or overflow in the intermediate stage of computation return a null value. For record selection commands such as xtsel and xtselstr, null values will not be included in the selection and will be skipped. Yet, the null values can be taken care of by the two commands: xtdelnul and xtnulto, which processes the null values to a replaced value. |
| You may want to check the intermediate
result when there are increasing number of commands used in the script as in the example above or when the final result
is not what you expected.
Alternatively, you can use the "tee" utility in UNIX where the standard input will be copied to standard output,
making a copy in zero or more files. E.g:
xtcut -f Date,Quantity -i $inPath/dat.xt | tee checkdat.dat | xtagg -k Date.... |
Let's create report with xtshare. Check your results with the scripts and output files given below.
| Report Description |
Script name | Output file (xt) | Output file (html) |
| Shares of total quantity and total amount for each manufacturer | xtshare1.sh | xtshare1.xt | xtshare1.html |
| Shares of total quantity and total amount for each manufacturer in 2-digit classification code. | xtshare2.sh | xtshare2.xt | xtshare2.html |