Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

To group the text (rows) by similar columns-names in a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting To group the text (rows) by similar columns-names in a file
# 1  
Old 02-13-2018
To group the text (rows) by similar columns-names in a file
As part of some report generation, I've written a script to fetch the values from DB. But, unluckily, for certain Time ranges(1-9.99,10-19.99 etc), I don't have data in DB.
In such cases, I would like to write zero (0) instead of empty. The desired output will be exported to csv file.
Possibility of fixed columns are: DATE:HH:1-9.99,10-19.99,20-29.99,30-39.99,40-49.99,50-59.99,60+


I've a file with below content:
Code:
DATE:HH:1-9.99,10-19.99,20-29.99,30-39.99,40-49.99,50-59.99,60+
---------------------------------------------------------------------
  2017-07-18  10  1-9.99    1:100% 
 2017-07-18  11  60+       3:100%  
2017-07-18  12  40-49.99  1:50%     60+       1:50% 
 2017-07-18  13  1-9.99    5:12.2%   10-19.99  4:9.76%   20-29.99  2:4.88%    30-39.99  2:4.88%  60+  28:68.29%  
2017-07-18  14  40-49.99  1:14.29%  60+       6:85.71% 
 2017-07-18  16  60+       1:100%  
2017-07-18  17  1-9.99    1:7.69%   30-39.99  1:7.69%   60+       11:84.62%

Desired output is:
Code:
DATE:HH:1-9.99,10-19.99,20-29.99,30-39.99,40-49.99,50-59.99,60+
---------------------------------------------------------------------
  **2017-07-18  10  1-9.99    1:100%  0                   0                       0                       0                   0  
2017-07-18  11  0                   0                   0                       0                       0                   60+  3:100%  
2017-07-18  12  0                   0                   0                       0                       40-49.99  1:50%     60+  1:50%  
2017-07-18  13  1-9.99    5:12.2%   10-19.99  4:9.76%   20-29.99  2:4.88%       30-39.99  2:4.88%       0                   60+  28:68.29%  
2017-07-18  14  0                   0                   0                       0                       40-49.99  1:14.29%  60+  6:85.71%  
2017-07-18  16  0                   0                   0                       0                       0                   60+  1:100% 
 2017-07-18  17  1-9.99    1:7.69%   0                   0                       30-39.99  1:7.69%       0                   60+  11:84.62%**

Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, output, and code segments (as required by forum rules).
Last edited by Don Cragun; 02-13-2018 at 03:45 AM.. Reason: Add CODE tags.
# 2  
Old 02-13-2018
Is this a homework assignment? Homework and coursework questions can only be posted in the Homework & Coursework Questions forum under special homework rules.

Please review the rules, which you agreed to when you registered, if you have not already done so.

If you did not post homework, please explain the company you work for and the nature of the problem you are working on.

If you did post homework in the main forums, please review the guidelines for posting homework and repost.
Quote:
Originally Posted by kumar_karpuram
As part of some report generation, I've written a script to fetch the values from DB. But, unluckily, for certain Time ranges(1-9.99,10-19.99 etc), I don't have data in DB.
In such cases, I would like to write zero (0) instead of empty. The desired output will be exported to csv file.
Possibility of fixed columns are: DATE:HH:1-9.99,10-19.99,20-29.99,30-39.99,40-49.99,50-59.99,60+


I've a file with below content:
Code:
DATE:HH:1-9.99,10-19.99,20-29.99,30-39.99,40-49.99,50-59.99,60+
---------------------------------------------------------------------
  2017-07-18  10  1-9.99    1:100% 
 2017-07-18  11  60+       3:100%  
2017-07-18  12  40-49.99  1:50%     60+       1:50% 
 2017-07-18  13  1-9.99    5:12.2%   10-19.99  4:9.76%   20-29.99  2:4.88%    30-39.99  2:4.88%  60+  28:68.29%  
2017-07-18  14  40-49.99  1:14.29%  60+       6:85.71% 
 2017-07-18  16  60+       1:100%  
2017-07-18  17  1-9.99    1:7.69%   30-39.99  1:7.69%   60+       11:84.62%

Desired output is:
Code:
DATE:HH:1-9.99,10-19.99,20-29.99,30-39.99,40-49.99,50-59.99,60+
---------------------------------------------------------------------
  **2017-07-18  10  1-9.99    1:100%  0                   0                       0                       0                   0  
2017-07-18  11  0                   0                   0                       0                       0                   60+  3:100%  
2017-07-18  12  0                   0                   0                       0                       40-49.99  1:50%     60+  1:50%  
2017-07-18  13  1-9.99    5:12.2%   10-19.99  4:9.76%   20-29.99  2:4.88%       30-39.99  2:4.88%       0                   60+  28:68.29%  
2017-07-18  14  0                   0                   0                       0                       40-49.99  1:14.29%  60+  6:85.71%  
2017-07-18  16  0                   0                   0                       0                       0                   60+  1:100% 
 2017-07-18  17  1-9.99    1:7.69%   0                   0                       30-39.99  1:7.69%       0                   60+  11:84.62%**

Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, output, and code segments (as required by forum rules).
And, if this is not homework, please tell us what operating system and shell you're using and show us what you have attempted to do to solve this problem on your own.

With what you have shown us, I don't understand what you're trying to do. You say you want CSV output, but you show us desired output that has colons and commas as separators only on the heading line. The data lines following the heading sometimes have leading a leading space, sometimes two leading spaces and two asterisks, and one line has two trailing asterisks. And the fields in all of the data lines are separated by varying numbers of spaces, not by commas (or any other consistent field separator character.

Please clearly explain what you are trying to do and show us desired sample output that matches that description (in CODE tags).
This User Gave Thanks to Don Cragun For This Post:
kumar_karpuram
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Converting columns of text to rows, with blank lines

I've spent the past hour trying different things and googling for this solution and cannot find the answer. Found variations of this, but not this exact thing. I have the following text, which is the output from our mainframe. Each field is on a separate line, with a blank line between each... (7 Replies)
Discussion started by: lupin..the..3rd
7 Replies

2. Shell Programming and Scripting

Transposing rows to columns with multiple similar lines

Hi, I am trying to transpose rows to columns for thousands of records. The problem is there are records that have the same lines that need to be separated. the input file as below:- ID 1A02_HUMAN AC P01892; O19619; P06338; P10313; P30444; P30445; P30446; P30514; AC Q29680; Q29837;... (2 Replies)
Discussion started by: redse171
2 Replies

3. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

4. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on the values of two columns (given ranges)

Hi, I have a tab delimited text file with multiple columns. The second and third columns include numbers that have not been sorted. I want to extract rows where the second column includes a value between -0.01 and 0.01 (including both numbers) and the first third column includes a value between... (1 Reply)
Discussion started by: evelibertine
1 Replies

5. Shell Programming and Scripting

Merging two columns from two files with similar names into a loop

I have two files like this: fileA.net A B C fileA.dat 1 2 3 and I want the output output_expected A 1 B 2 C 3 I know that the easier way is to do a paste fileA.net fileA.dat, but the problem is that I have 10,000 couple of files (fileB.net with fileB.dat; fileC.net with... (3 Replies)
Discussion started by: valente
3 Replies

6. UNIX for Dummies Questions & Answers

Swapping the columns of a text file for a subset of rows

Hi, I'd like to swap the columns 1 and 2 of a space-delimited text file but only for the first 1000 rows. How do I go about doing that? Thanks! (1 Reply)
Discussion started by: evelibertine
1 Replies

7. Shell Programming and Scripting

Convert rows to columns group

Hi I have the input file following like this "AIX" "AIX 6.0" "AIX 7.0" "Redhat 8" "Redhat 9" "Redhat 5.0 Enterprise Linux" "Sun Solaris 9" "Sun Solaris 10", "Sun Microsystems" "Oracle" .................................Like this 2000 lines I need to convert this input into... (5 Replies)
Discussion started by: selvanraj
5 Replies

8. Shell Programming and Scripting

parsing file names and then grouping similar files

Hello Friends, I have .tar files which exists under different directories after the below code is run: find . -name "*" -type f -print | grep .tar > tmp.txt cat tmp.txt ./dir1/subdir1/subdir2/database-db1_28112009.tar ./dir2/subdir3/database-db2_28112009.tar... (2 Replies)
Discussion started by: EAGL€
2 Replies

9. Shell Programming and Scripting

Merge group numbers and add a column containing group names

I have a file in the following format. Groups of data merge together and the group number is indicated above each group. 1 adrf dfgr dfg 2 dfgr dfgr 3 dfef dfr fd 4 fgrt fgr fgg 5 fgrt fgr (3 Replies)
Discussion started by: Lucky Ali
3 Replies

10. Shell Programming and Scripting

merging similar columns in a single line file

Hi Guys. I have tried the commands sort and join. But I couldn't able to to find the command for joining in a single line based on keys.My example inputs and outputs are like the following. Help would be appreciated.:D Input file a1tabXXXXXXX a2tabXXXXXXX a6tabYYYYYYYYY a71tabXXXXXXX... (7 Replies)
Discussion started by: repinementer
7 Replies
Login or Register to Ask a Question