Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Pivot Rows to Columns, with field separator

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Pivot Rows to Columns, with field separator
# 1  
Old 02-16-2016
Pivot Rows to Columns, with field separator
Hi All,

I have a requirement to flatten data out, based on the value in COL_2. Our file is pipe delimited, however COL_2 contains a comma separated string, which we would like to pivot out from one row into multiple rows. Please see my example input data below:

Input Data

Code:
 
COL_1|COL_2|COL_3|COL_4
123|ABC,DEF,GHI|80|AA
456|ABC,DEF,GHI|90|BB

We would like to pivot the data, so that each record, becomes many records, based on the comma separated values in COL_2. Please see my example output data below:

Output Data

Code:
 
COL_1|COL_2|COL_3|COL_4
123|ABC|80|AA
123|DEF|80|AA
123|GHI|80|AA
456|ABC|90|BB
456|DEF|90|BB
456|GHI|90|BB

I am assuming awk would be an efficient way of doing this?

Thanks in advance for any assistance you may be able to provide.
# 2  
Old 02-16-2016
Hi, try:
Code:
awk 'NR==1{print; next} {n=split($2,F,/,/); for(i=1; i<=n; i++) {$2=F[i]; print}}' FS=\| OFS=\|  file

This User Gave Thanks to Scrutinizer For This Post:
RichZR
# 3  
Old 02-17-2016
Quote:
Originally Posted by Scrutinizer
Hi, try:
Code:
awk 'NR==1{print; next} {n=split($2,F,/,/); for(i=1; i<=n; i++) {$2=F[i]; print}}' FS=\| OFS=\|  file

That works a treat! SmilieSmilie
# 4  
Old 03-10-2016
Quote:
Originally Posted by Scrutinizer
Hi, try:
Code:
awk 'NR==1{print; next} {n=split($2,F,/,/); for(i=1; i<=n; i++) {$2=F[i]; print}}' FS=\| OFS=\|  file

Hi we have utilised:
Code:
awk 'NR==1{print; next} {n=split($2,F,/,/); for(i=1; i<=n; i++) {$=F[i]; print}}' FS=\| OFS=\|  <filename>

and face a new challenge. Example of source data:
Quote:
"John"|"[AAA],[BBB],[CCC],[DDD]"|"123"|"UK"
Output wanted:
Quote:
"John"|"[AAA]"|"123"|"UK"
"John"|"[BBB]"|"123"|"UK"
"John"|"[CCC]"|"123"|"UK"
"John"|"[DDD]"|"123"|"UK"
Output obtained:
Quote:
"John"|"[AAA]|"123"|"UK"
"John"|"[BBB]|"123"|"UK"
"John"|"[CCC]|"123"|"UK"
"John"|[DDD]"|"123"|"UK"
It seems additional logic is needed to add the additional
Quote:
"
at the end of first, at the start and end of the in betweens, and at the start of last. Can you help with filling the additional required
Quote:
"
pls?
# 5  
Old 03-10-2016
Try
Code:
awk '{n=split($2,F,","); for(i=1; i<=n; i++) {$2=F[i]; gsub (/^"*|"*$/, "\"", $2); print}}' FS=\| OFS=\|  file
"John"|"[AAA]"|"123"|"UK"
"John"|"[BBB]"|"123"|"UK"
"John"|"[CCC]"|"123"|"UK"
"John"|"[DDD]"|"123"|"UK"

YMMV with different awk versions...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Inserting a field without disturbing field separator on other fields

Hi All, I have the input as below: cat input 032016002 2.891 97.109 16.605 27.172 24.017 32.207 0.233 0.021 39.810 0.077 0.026 19.644 13.882 0.131 11.646 0.102 11.449 76.265 23.735 16.991 83.009 8.840 91.160 0.020 99.980 52.102 47.898 44.004 55.996 39.963 18.625 0.121 1.126 40.189... (15 Replies)
Discussion started by: am24
15 Replies

2. Shell Programming and Scripting

Linux - Pivot Rows to Columns

Morning All, I am wanting to try and Pivot a set of data which is currently in a row format, to a column format. This will potentially need to run over a large dataset, therefore I am thinking awk may be the most efficient solution? I would like to Pivot the data around Col1 & Col2 which are... (9 Replies)
Discussion started by: RichZR
9 Replies

3. Shell Programming and Scripting

Field separator

Hello All, I have a file, but I want to separate the file at a particular record with comma"," in the line Input file APPLE6SSAMSUNGS5PRICEPERPIECEDOLLAR600EACH010020340URX581949695US to Output file APPLE6S,SAMSUNGS5,PRICEPERPIECE,DOLLAR600EACH,010020340URX581949695,US This is for... (11 Replies)
Discussion started by: m6248m
11 Replies

4. Shell Programming and Scripting

Print rows based on separator

For below lines in a file. 68078971 dance routine (jess far back corner) 13902786 368079527 dance routine 13902786 368081191 dance routine (jess far back) 13902786 I am looking for output as below 68078971, "dance routine (jess far back corner)", 13902786... (4 Replies)
Discussion started by: Anjan1
4 Replies

5. Shell Programming and Scripting

Rows to columns with first field constant

Hi Friends, I have tried many options to convert rows to column in below fashion. Can you help me pls? input file: kamal|1,2,3|4,5,6|7,8,9 mich|4,7,4|6,7,9 jose|1,1,2|3,3,2|5,5,0 output required: kamal|1,2,3 kamal|4,5,6 kamal|7,8,9 mich|4,7,4 mich|6,7,9 jose|1,1,2 jose|3,3,2... (2 Replies)
Discussion started by: suresh3566
2 Replies

6. Shell Programming and Scripting

How to reverse all columns of a file having some field separator?

Hello, I have a file: xandyandz x & y & z x*y*z*a I require output as: zandyandx z & y & x a*z*y*x here all lines have different field seperator (and & * )based on that i want to reverse the column of a file. Pl. help. (8 Replies)
Discussion started by: nehashine
8 Replies

7. UNIX for Dummies Questions & Answers

change field separator only from nth field until NF

Hi ! input: 111|222|333|aaa|bbb|ccc 999|888|777|nnn|kkk 444|666|555|eee|ttt|ooo|ppp With awk, I am trying to change the FS "|" to "; " only from the 4th field until the end (the number of fields vary between records). In order to get: 111|222|333|aaa; bbb; ccc 999|888|777|nnn; kkk... (1 Reply)
Discussion started by: beca123456
1 Replies

8. Shell Programming and Scripting

Field separator X'1F'

Hi, I have a flat file with fields separated by a X'1F' i have to fetch 4th field from second line. please help me how to achieve it. I tried with below command and its not working. cut -f4 -d`echo -e '\x1f'` filename.txt I am using SunOS. Thanks in advance. (2 Replies)
Discussion started by: rohan10k
2 Replies

9. Shell Programming and Scripting

awk, string as record separator, transposing rows into columns

I'm working on a different stage of a project that someone helped me address elsewhere in these threads. The .docs I'm cycling through look roughly like this: 1 of 26 DOCUMENTS Copyright 2010 The Age Company Limited All Rights Reserved The Age (Melbourne, Australia) November 27, 2010... (9 Replies)
Discussion started by: spindoctor
9 Replies

10. Shell Programming and Scripting

awk, comma as field separator and text inside double quotes as a field.

Hi, all I need to get fields in a line that are separated by commas, some of the fields are enclosed with double quotes, and they are supposed to be treated as a single field even if there are commas inside the quotes. sample input: for this line, 5 fields are supposed to be extracted, they... (8 Replies)
Discussion started by: kevintse
8 Replies
Login or Register to Ask a Question