Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Create a pivot table from CSV file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Create a pivot table from CSV file
# 1  
Old 03-15-2015
Create a pivot table from CSV file
Gents,

Can you please help me to create a pivot table from a csv file. ( I have zip the csv file)

Using the file attached, columns 1,28 and 21 i would like to get something like this output

Code:
JD  Val  1    2    3    4    5    6    7    8    9    10   11    12   Total
-----------------------------------------------------------------------------------
60       0    0    0    0    0    0    0    0    0    12    0     1     13
61       1    6    11   0    1    0    0    0    0    27    5     8     59
62       0    1    4    0    0    1    0    0    0    21    5     0     32
-----------------------------------------------------------------------------------
Total    1    7    15   0    1    1    0    0    0    60    10    9     104

Please try to help me..

Thanks for your help Smilie
file.zip (402.0 KB) 
Last edited by jiam912; 03-15-2015 at 03:41 PM..
# 2  
Old 03-15-2015
Mayhap sth. in this direction:
Code:
awk     'NR>1   {LN[$28]; HD[$1]; MX[$28,$1]+=$21; SUM[$28]+=$21; TOT[$1]+=$21; TOTAL+=$21}
         END    {               printf "%10s", "";      for (i in HD) printf "%10s", i;         printf "     Total\n";
                 for (j in LN) {printf "%10s",j;        for (i in HD) printf "%10s", MX[j,i]+0; printf "%10s\n", SUM[j]}
                                printf "%10s", "Total"; for (i in HD) printf "%10s", TOT[i] ;   printf "%10s\n", TOTAL;
                }
        ' FS="," file
                  10        11        12         1         2         3         4         5         6         7         8         9     Total
        60        12         0         1         0         0         0         0         0         0         0         0         0        13
        61        27         5         8         1         6        11         0         1         0         0         0         0        59
        62        21         5         0         0         1         4         0         0         1         0         0         0        32
     Total        60        10         9         1         7        15         0         1         1         0         0         0       104

? May need some beautifying...


AND - PLEASE make very sure <CR> characters are removed before running *nix scripts on or before posting data!
Last edited by RudiC; 03-15-2015 at 04:14 PM..
This User Gave Thanks to RudiC For This Post:
jiam912
# 3  
Old 03-16-2015
Hi RudiC,

Thanks a lot for your help.. it works

I have modify little your script to get my desired output.

Code:
awk     'NR>1   {LN[$28]; HD[$1]; MX[$28,$1]+=$21; SUM[$28]+=$21; TOT[$1]+=$21; TOTAL+=$21}
         END    {               printf "%9s", "";      for (i in HD) printf "%9s", i;         printf "     Total\n";
                 for (j in LN) {printf "%9s",j;        for (i in HD) printf "%9s", MX[j,i]+0; printf "%9s\n", SUM[j]}
                                printf "%9s", "Total"; for (i in HD) printf "%9s", TOT[i] ;   printf "%9s\n", TOTAL;
                }
        ' FS="," $file | sort -k1n | awk 'NR==1{s=$0;next}1;END{print s}' > tmp1

sed -i '1iREPORT\' tmp1
sed -i '2i-----------------------------------------------------------------------------------------------------------------------------------' tmp1
sed -i '4i-----------------------------------------------------------------------------------------------------------------------------------' tmp1
sed -i '$i-----------------------------------------------------------------------------------------------------------------------------------' tmp1
sed -i '$a-----------------------------------------------------------------------------------------------------------------------------------' tmp1
cat tmp1

Then, I get this:

Code:
REPORT
-----------------------------------------------------------------------------------------------------------------------------------
                 4        5        6        7        8        9       10       11       12        1        2        3     Total
-----------------------------------------------------------------------------------------------------------------------------------
       60        0        0        0        0        0        0       12        0        1        0        0        0       13
       61        0        1        0        0        0        0       27        5        8        1        6       11       59
       62        0        0        1        0        0        0       21        5        0        0        1        4       32
-----------------------------------------------------------------------------------------------------------------------------------
    Total        0        1        1        0        0        0       60       10        9        1        7       15      104
-----------------------------------------------------------------------------------------------------------------------------------

Is there the chance to get this:

Code:
REPORT
-----------------------------------------------------------------------------------------------------------------------------------
                 1        2        3        4        5        6        7        8        9       10       11       12     Total  
-----------------------------------------------------------------------------------------------------------------------------------
       60        0        0        0        0        0        0        0        0        0       12        0        1       13   
       61        1        6       11        0        1        0        0        0        0       27        5        8       59   
       62        0        1        4        0        0        1        0        0        0       21        5        0       32   
-----------------------------------------------------------------------------------------------------------------------------------
    Total        1       7        15        0        1        1        0        0        0       60       10        9      104
-----------------------------------------------------------------------------------------------------------------------------------

Thanks
# 4  
Old 03-16-2015
Why not in one awk? Try
Code:
awk     'BEGIN  {DASH=sprintf("%150s",_); gsub(/ /,"-",DASH); print "REPORT"; print DASH}
         NR>1   {LN[$28]; HD[$1]; MX[$28,$1]+=$21; SUM[$28]+=$21; TOT[$1]+=$21; TOTAL+=$21}
         END    {                printf "%10s", "";      for (i in HD) printf "%10s", i;         printf "     Total\n";
                                 print DASH
                 for (j in LN)  {printf "%10s",j;        for (i in HD) printf "%10s", MX[j,i]+0; printf "%10s\n", SUM[j]}
                                 print DASH
                                 printf "%10s", "Total"; for (i in HD) printf "%10s", TOT[i] ;   printf "%10s\n", TOTAL;
                                 print DASH
                }
        ' FS="," file
REPORT
------------------------------------------------------------------------------------------------------------------------------------------------------
                  10        11        12         1         2         3         4         5         6         7         8         9     Total
------------------------------------------------------------------------------------------------------------------------------------------------------
        60        12         0         1         0         0         0         0         0         0         0         0         0        13
        61        27         5         8         1         6        11         0         1         0         0         0         0        59
        62        21         5         0         0         1         4         0         0         1         0         0         0        32
------------------------------------------------------------------------------------------------------------------------------------------------------
     Total        60        10         9         1         7        15         0         1         1         0         0         0       104
------------------------------------------------------------------------------------------------------------------------------------------------------

The order of the columns is difficult to modify, see man awk:
Quote:
There is a form of the for statement that loops over each index of an array.

for ( var in array ) statement

sets var to each index of array and executes statement. The order that var transverses the indices of array is not defined.
This User Gave Thanks to RudiC For This Post:
jiam912
# 5  
Old 03-16-2015
Hello RudiC,

Thanks for the update in the script.. Great
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

1. This will insert the records into db table by reading from ta csv file

I have this code with me but the condition is If any of the mandatory columns are null then entire file will be rejected. LOAD DATA infile ' ' #specifies the name of a datafile containing data that you want to load BADFILE ' ' #specifies the name of... (1 Reply)
Discussion started by: raka123
1 Replies

2. Shell Programming and Scripting

Awk- Pivot Table Averages

Hi everyone, Has anyone figured out yet how to do pivot table averages using AWK. I didn't see anything with regards to doing averages. For example, suppose you have the following table with various individuals and their scores in round1 and round2: SAMPLE SCORE1 SCORE2 British ... (6 Replies)
Discussion started by: Geneanalyst
6 Replies

3. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

4. Shell Programming and Scripting

Update the table using values from a csv file

i want to run update query for oracle which is in up.sql taking values from a.csv. I have implemented shell script to do it. extn="perl" ls -1 | while read file do echo "$file,$extn" > a.csv done up.sql contains update file_list set filename=$1 where extn=$2; The code to update is... (2 Replies)
Discussion started by: millan
2 Replies

5. Shell Programming and Scripting

Convert file in csv or table

Hi there, i have a file like that in attachment (PLEVA3_280711_SAP.txt), i would extract some basic information from it and report in a new file or table like this: i try to use bash and i extract the single object in this way (see attach scriptino.sh), but i receive a strange... (5 Replies)
Discussion started by: alen192
5 Replies

6. Shell Programming and Scripting

Create Pivot table

I would like to use awk to parse a file with three columns in, like: Chennai,01,1 Chennai,07,1 Chennai,08,3 Chennai,09,6 Chennai,10,12 Chennai,11,19 Chennai,12,10 Chennai,13,12 Kerala,09,2 AP,10,1 AP,11,1 Delhi,13,1 Kerala,13,3 Chennai,00,3 Chennai,01,1 Chennai,02,1 Chennai,07,5 (3 Replies)
Discussion started by: boston_nilesh
3 Replies

7. UNIX for Dummies Questions & Answers

Storing data from a table into a csv file

Hi I need to write a bash script to take the data stored in 3 oracle tables .. and filter them and store the results in a csv file. It is an Oracle database Thank you (1 Reply)
Discussion started by: ladyAnne
1 Replies

8. Shell Programming and Scripting

Store table contents in csv file

I need to write a script to store the contents of a table in a csv file I'm using Toad, it's a Oracle database. (5 Replies)
Discussion started by: ladyAnne
5 Replies

9. Shell Programming and Scripting

to create an output file as a table

Hi, I have four input files and would like to create an output file as a table. Please check the example below. File 1. 111111 222222 333333 444444 File 2. 555555 666666 777777 888888 File 3. aaaaa bbbbb ccccc ddddd (2 Replies)
Discussion started by: marcelus
2 Replies

10. 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... (2 Replies)
Discussion started by: gio001
2 Replies
Login or Register to Ask a Question