How to consolidate values in one column from different rows into one?

Login or Register to Ask a Question and Join Our Community

How to consolidate values in one column from different rows into one?

Tags

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to consolidate values in one column from different rows into one?

11-04-2009

Registered User

12, 0

Join Date: Jul 2009

Last Activity: 4 November 2009, 12:05 PM EST

Posts: 12

Thanks Given: 0

Thanked 0 Times in 0 Posts

How to consolidate values in one column from different rows into one?

Hi Guys,

Thank you all for helping me with my different queries and I continue to get better at scripting because of help from all of you!

I have a file that would look something like -

Code:

ID        SUB ID       VALUE
1           10             5
2           18             7
1           8              4
2           18             2
3           15             9
3           16             1
3           15             1
1           10             4

The output should be generated consolidated and sorted as following -

Code:

ID        SUB ID         VALUE (I dont care if headers are not generated too)
1         10                |4|5|
1           8                |4|
2         18                |2|7|
3         15                |1|9|
3         16                |1|

Grouping should be based on ID and SUB ID. SUB ID doesn't have to be in sorted order but would be nice to have. Can I achieve this through some AWK script?

As always, really appreciate your help!

sncoupons

View Public Profile for sncoupons

Find all posts by sncoupons

11-04-2009

Registered User

2,163, 123

Join Date: Nov 2007

Last Activity: 31 July 2016, 9:42 AM EDT

Location: H3X

Posts: 2,163

Thanks Given: 11

Thanked 123 Times in 116 Posts

Something like that?

Code:

sort -n -k1 -k2 file | awk  '/^[0-9]/{a[$1 t $2]=(a[$1 t $2])?a[$1 t $2] $NF OFS:a[$1 t $2] OFS $NF OFS}END{for(i in a)print i t a[i]}' t="\t" OFS="|"

danmero

View Public Profile for danmero

Find all posts by danmero

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare values in multiple rows in one column using awk

I would like to compare values in column 8, and grep the ones where the different is > 1, columns 1 and 2 are the key for array. Every 4 rows the records values in columns 1 and 2 changed. Then, the comparison in the column 8 need to be done for the 4 rows everytime columns 1 and 2 changed ...

2. Programming

DB2 Query -Convert multi values from column to rows

Hi Team I am using DB2 artisan tool and struck to handle multi values present in columns that are comma(,) separated. I want to convert those column values in separate rows . For example : Column 1 Column2 Jan,Feb Hold,Sell,Buy Expected Result Column1 ...

3. UNIX for Beginners Questions & Answers

Pivoting values from column to rows

I/P: I/P: 2017/01/01 a 10 2017/01/01 b 20 2017/01/01 c 40 2017/02/01 a 10 2017/02/01 b 20 2017/02/01 c 30 O/P: a b c 2017/01/01 10 20 40 2017/02/01 10 20 30

4. Shell Programming and Scripting

Convert Column data values to rows

Hi all , I have a file with the below content Header Section employee|employee name||Job description|Job code|Unitcode|Account|geography|C1|C2|C3|C4|C5|C6|C7|C8|C9|Csource|Oct|Nov|Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep Data section ...

5. Shell Programming and Scripting

Choosing rows based on column values

I have a .csv file: A,B,0.6 C,D,-0.7 D,E,0.1 A,E,0.45 D,G, -0.4 I want to select rows based on the values of the 3rd columns such that it is >=0.5 or <= -0.5 Thanks. A,B,0.6 D,G, -0.7

6. Shell Programming and Scripting

Remove rows with e column values

Hi All, I have a big file with 232 columns and 9 million rows, I want to delete all rows with same column values in col3 through col232. Also the output should be sorted based on first 2 columns. Here is a reduced example with 6 columns. I want to remove rows with duplicate values in col3...

7. Shell Programming and Scripting

join rows based on the column values

Hi, Please help me to convert the input file to a new one. input file: -------- 1231231231 3 A 4561223343 0 D 1231231231 1 A 1231231231 2 A 1231231231 4 D 7654343444 2 A 4561223343 1 D 4561223343 2 D the output should be: -------------------- 1231231231 3#1#2 A 4561223343 0 D...

8. UNIX for Dummies Questions & Answers

count number of rows based on other column values

Could anybody help with this? I have input below ..... david,39 david,39 emelie,40 clarissa,22 bob,42 bob,42 tim,32 bob,39 david,38 emelie,47 what i want to do is count how many names there are with different ages, so output would be like this .... david,2 emelie,2 clarissa,1...

9. UNIX for Dummies Questions & Answers

How to assign scores to rows based on column values

Hi, I'm trying to assign a score to each row which will allow me to identify which rows differ. In the example file below, I've used "," to indicate column separators (my actual file has tab separators). In this example, I'd like to identify that row 1 and row 5 are the same, and row 2 and row...

10. Shell Programming and Scripting

Print rows in reverse order if values decrease along the column

Hi, Guys. Please help me to find solution to this problem using shell scripting. I have an INPUT file with 4 columns separated by tab. Each block of records is separated by ----- ----- Sample1 5402 6680 Pattern01 Sample2 2216 2368 Pattern02...

Login or Register to Ask a Question