Pivot multiple Post: 302879299

Sponsored Content

Top Forums UNIX for Dummies Questions & Answers Pivot multiple Post 302879299 by DGPickett on Wednesday 11th of December 2013 04:40:42 PM

12-11-2013

Registered User

A pipelined approach, very old school, is to sort your input by ville and then reg, so you can add up each reg for a ville and spit out the sum when the ville changes. To start the loop, you need a last_ville variable set to a safe value or unset. To end the loop, you need to add in a dummy line at EOF (which does not need to be sorted).

Code:

#!/bin/bash
 
unset last_ville
 
IFS='\t
'
 
echo "ville\tCollege\tEcole\tKindergaten\tStudent & Employee\tUnidentified"
 
(
sort -t'\t' -k3,5 in_file
echo "x\tx\tx\tdummyXX
)|while read date country ville reg popul
do
 if [ "$ville" != "$last_ville" ]
 then
  if [ "$last_ville" != "" ]
  then
   echo "$last_ville\t$coll_sum\t$ecole_sum\t$kinder_sum\t$se_sum\t$unid_sum"
  fi
  if [ "$ville" = "dummyXX" ]
  then
   break
  fi
  last_ville="$ville" coll_sum=0 ecole_sum=0 kinder_sum=0 se_sum=0 unid_sum=0
 fi
 case "$reg" in
 (College)
  (( coll_sum += popul ))
  ;;
 (Ecole)
 .
 .
 .
 (*) #default are unidentified
  (( unid_sum += popul ))
  ;;
 esac
done

I am assuming the fields are tab separated, so the embedded blanks do not set off the sort field identification. When is say \t, I mean a real tab character. If you "typeset -A pop_sum" and use the $ref as $pop_sum's key, it can hold all the sums, but then emptying it is very chatty. You might destroy it and recreate it, but then "" is not a zero, so you need to find a way to add the zero's for "" when printing. Tab separated text is very spreadsheet friendly.

Last edited by DGPickett; 12-11-2013 at 05:52 PM..

DGPickett

View Public Profile for DGPickett

Find all posts by DGPickett

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

pivot

I have a sql table with : Acitvity Date Value ABC 7/11 10 DEF 7/11 98 ABC 7/12 23 DEF 7/12 100 SER 7/12 67 GRH 7/13 123 HJY 7/14 12 I...

2. Shell Programming and Scripting

Pivot table

Hello everyone, I would like to use awk to parse a file with three columns in, like: monday 0 1 monday 1 1 monday 2 1 monday 3 1 monday 4 1 monday 5 1 tuesday 0 5 tuesday 1 1 tuesday 2 1 tuesday 3 1 tuesday 4 1 wednesday 0 1 monday 5 25 they represent the day the hour and the...

3. Shell Programming and Scripting

Pivot file contents

Hi All, I am trying to pivot the contents in a file. Ex: I have a file sample.txt with data "A B C D", i need the contents to pivot & resulting file should look like "A B C ...

4. Shell Programming and Scripting

Pivot script

Hi Please suggest a script that would do a horizontal pivot , on the fields separated by a semicolon Below is my input file 1|c2|aa 1|c3|dd 1|c4|cc 1|c5|aa 1|c6|ss 1|c7|dd 1|c8|bb 1|c9|jjj 1|c10|kkk 1|c11|fffg 1|c12|nnn;indi;pak;linf;wer 1|c13|lllnk;li;sdfsd;oiuo 1|c14|ppp...

5. Shell Programming and Scripting

Pivot using awk

Hi, I am writing a code to basically pivot the data. awk -v var1="" -v var2="" -v var3="" -v var4="" -v var5="" -v Disp=0\ 'BEGIN {FS=":"; OFS="|";}\ /^Pattern1/ {var1=$2;Disp=0;} \ /^Pattern2/ {var2=$2;} \ /^Pattern3/ {var3=$2;} \ /^Pattern4/ {var4=$2;} \ /^Pattern5/...

6. Shell Programming and Scripting

Pivot Column using awk

Hello everyone I have a 20M file which is having the below sample layout 1111,ABC,100 1111,DEF,200 1111,XYZ,300 4444,LMN,100 4444,AKH,500 4444,WCD,400 2222,ABC,100 7777,DEF,300 7777,WCD,300 I need to covert this to below format Output Party_ID|ABC|AKH|DEF|LMN|WCD|XYZ...

7. Shell Programming and Scripting

Pivot data using awk

8. UNIX for Beginners Questions & Answers

Data Pivot

Good Day, I have file input 6285296582710|20170509|INDOTEL 6285296835209|20170509|INDOTEL 6285296940311|20170509|INDOTEL 6285297027737|20170509|MULTIFLAG 6285297027737|20170509|DELTA 6285297304373|20170510|INDOTEL 6285297384129|20170510|INDOTEL 6285296940311|20170510|MULTIFLAG...

9. Programming

DB Query for Pivot

hi team With below results in Db2 v10.5 . Please refer column A and B are same,while Staus column defers with distinct values . A B STATUS Insert Update Old Insert Update New Insert Update Final Can someone guide how to...

10. Shell Programming and Scripting

Pivot example

Hi all, I am new to shell scripting so pardon me for the questions I will be asking. I was given a task where I have to pivot my data Example Source SGPAPCTUMACCHEA Expected output SGP APC TUM SGP APC ACC SGP APC HEA Can anybody assist me on this?Please use CODE tags as required...

LEARN ABOUT DEBIAN

graphics::primitive::insets

Graphics::Primitive::Insets(3pm)			User Contributed Perl Documentation			  Graphics::Primitive::Insets(3pm)

NAME

       Graphics::Primitive::Insets - Space between things

DESCRIPTION

       Graphics::Primitive::Insets represents the amount of space that surrounds something.  This object can be used to represent either padding
       or margins (in the CSS sense, one being inside the bounding box, the other being outside)

SYNOPSIS

	 use Graphics::Primitive::Insets;

	 my $insets = Graphics::Primitive::Insets->new({
	   top	   => 5,
	   bottom  => 5,
	   left    => 5,
	   right   => 5
	 });

METHODS

   Constructor
       new Creates a new Graphics::Primitive::Insets.

   Instance Methods
       as_array
	   Return these insets as an array in the form of top, right, bottom and left.

       bottom
	   Set/Get the inset from the bottom.

       equal_to
	   Determine if these Insets are equal to another.

       left
	   Set/Get the inset from the left.

       right
	   Set/Get the inset from the right.

       top Set/Get the inset from the top.

       zero
	   Sets all the insets (top, left, bottom, right) to 0.

AUTHOR

       Cory Watson, "<gphat@cpan.org>"

SEE ALSO

       perl(1)

COPYRIGHT &; LICENSE
       Copyright 2008-2010 by Cory G Watson.

       This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.12.3							    2010-08-21					  Graphics::Primitive::Insets(3pm)

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

pivot

Discussion started by: mukhanj

2. Shell Programming and Scripting

Pivot table

Discussion started by: gio001

3. Shell Programming and Scripting

Pivot file contents

Discussion started by: new_ds_man

4. Shell Programming and Scripting

Pivot script

Discussion started by: mabarif16