Merging fields in CSV


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merging fields in CSV
# 1  
Old 11-20-2013
Merging fields in CSV

Hi experts,
I have a csv file which has one field (ID) repeated multiple times with corresponding other field values.
I need to convert this file in a format where for a ID all other values has to be present in single field.

For Eg : Here in below file ID 1 is repeated 3 times with different city values(blr,chn.dlh). All these are in separate lines.
All this values has to be brought into one single field with | as a separator. This should also retain the headers.

Code:
cat file1.csv

ID	city	acct_no
1	blr	500
1	chn	600
1	dlh	700
2	hyd	888
2	blr	999
3	dlh	111
3	blr	222
3	hyd	333


Required output

ID	city	acct_no
1	blr|chn|dlh	500|600|700
2	hyd|blr	888|999
3	blr|hyd	111|222|333


Last edited by bharathbangalor; 11-20-2013 at 04:14 AM..
# 2  
Old 11-20-2013
I would have thought you would want:
Code:
3	dlh|blr|hyd	111|222|333

instead of:
Code:
3	blr|hyd	111|222|333

as the last line of output for your sample input file. Assuming this is what you want, try:
Code:
awk '
BEGIN { OFS="\t" }
$1 != last_id {
        if(NR > 1) print last_id, cities, accounts
        last_id = $1
        cities = $2
        accounts = $3
        next
}
{       cities = cities "|" $2
        accounts = accounts "|" $3
}
END {   print last_id, cities, accounts }' file1.csv

If you want to run this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk.
This User Gave Thanks to Don Cragun For This Post:
# 3  
Old 11-20-2013
Try this:
Code:
awk '
 NR==1{
  h=$0
 } 
 NR>1{
  c[$1]=c[$1]?c[$1]"|"$2:$2
  a[$1]=a[$1]?a[$1]"|"$3:$3
 } 
 END{
  print h
  for (i in c) print i,c[i],a[i]
 }' file

# 4  
Old 11-20-2013
Quote:
Originally Posted by Don Cragun
I would have thought you would want:
Code:
3	dlh|blr|hyd	111|222|333

instead of:
Code:
3	blr|hyd	111|222|333

as the last line of output for your sample input file. Assuming this is what you want, try:
Code:
awk '
BEGIN { OFS="\t" }
$1 != last_id {
        if(NR > 1) print last_id, cities, accounts
        last_id = $1
        cities = $2
        accounts = $3
        next
}
{       cities = cities "|" $2
        accounts = accounts "|" $3
}
END {   print last_id, cities, accounts }' file1.csv

If you want to run this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk.
Thanks Don, It works good for 3 columns. But what if we have multiple columns say around 100.

---------- Post updated at 04:12 AM ---------- Previous update was at 04:09 AM ----------

Quote:
Originally Posted by Subbeh
Try this:
Code:
awk '
 NR==1{
  h=$0
 } 
 NR>1{
  c[$1]=c[$1]?c[$1]"|"$2:$2
  a[$1]=a[$1]?a[$1]"|"$3:$3
 } 
 END{
  print h
  for (i in c) print i,c[i],a[i]
 }' file

Thanks Subbeh ,
It works good for 3 columns. But what if we have multiple columns say around 100. More over the fields are coma separated
# 5  
Old 11-20-2013
This should get you into the right direction:
Code:
awk '
 NR==1{
  print
  next
 }
 {
  for (i=2; i<=NF; i++) {
   a[$1,i]=a[$1,i]?a[$1,i]"|"$i:$i
  }
  l[$1]
 }
 END {
  for (i in l) {
   printf("%s ", i)
   for (j=2; j<=NF; j++) {
    printf("%s ",a[i,j])
   }
   printf "\n"
  }
 }' file

# 6  
Old 11-20-2013
Hi bharathbangalor,
The sample input file you showed us and the output you said you wanted have tabs as the field separators. But you told Subbeh "More over the fields are coma (sic) separated".

Are you saying your input file has commas instead of tabs as field separators?

Are you saying you want the output to use commas instead of tabs as field separators?

The sample input file you showed us is sorted by key, city, and account. The awk script I provided assumes that all entries in your input file with the same key are on contiguous lines and prints output that is in the same order as the input. The awk script Subbeh provided will work no matter what order the input is in, but (other than the header) prints output lines in random order.

To be sure we're coming up with code that will work for you:
  1. Do all of the input lines for a given id in your real data appear on adjacent lines?
  2. Do you care about the order of the output lines?
  3. What operating system and version are you using? (I.e., what is the output from uname -a?
  4. What is the output from the command getconf LINE_MAX?
  5. Will the length in bytes of any input line (including field separators and the trailing newline character) in your real data exceed the number printed by getconf?
  6. Will the length in bytes of the longest output line you want to produce from your real data exceed the number printed by getconf? If it will, will the number of bytes in the longest output field you want to produce from your real data exceed the number printed by getconf?
  7. And, does every line in your input file have the same number of fields?
# 7  
Old 11-22-2013
Quote:
Originally Posted by Don Cragun
Hi bharathbangalor,
The sample input file you showed us and the output you said you wanted have tabs as the field separators. But you told Subbeh "More over the fields are coma (sic) separated".

Are you saying your input file has commas instead of tabs as field separators?

Are you saying you want the output to use commas instead of tabs as field separators?

The sample input file you showed us is sorted by key, city, and account. The awk script I provided assumes that all entries in your input file with the same key are on contiguous lines and prints output that is in the same order as the input. The awk script Subbeh provided will work no matter what order the input is in, but (other than the header) prints output lines in random order.

To be sure we're coming up with code that will work for you:
  1. Do all of the input lines for a given id in your real data appear on adjacent lines?
  2. Do you care about the order of the output lines?
  3. What operating system and version are you using? (I.e., what is the output from uname -a?
  4. What is the output from the command getconf LINE_MAX?
  5. Will the length in bytes of any input line (including field separators and the trailing newline character) in your real data exceed the number printed by getconf?
  6. Will the length in bytes of the longest output line you want to produce from your real data exceed the number printed by getconf? If it will, will the number of bytes in the longest output field you want to produce from your real data exceed the number printed by getconf?
  7. And, does every line in your input file have the same number of fields?
Hi Don,

PFA my actual data and required output sample.
PFB the answers for your question
1. Yes
2. No
3.2.6.32-279.5.1.el6.x86_64 #1 SMP Tue Jul 24 13:57:35 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
4.2048
5.NO
6.NO.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help with merging two CSV files

Hi, I have following 2 CSV files file1.txt A1,B1,C1,D1,E1 A2,B2,C2,D2,E2 A3,B3,C3,D3,E3 .... file2.txt A1,B1,P1,Q1,R1,S1,T1,U1 A1,B1,P2,Q2,R2,S2,T2,U2 A1,B1,P3,Q3,R3,S3,T3,U3 A2,B2,X1,Y1,Z1,I1,J1,K1 A2,B2,X2,Y2,Z2,I2,J2,K2 A2,B2,X3,Y3,Z3,I3,J3,K3 A2,B2,X4,Y4,Z4,I4,J4,K4... (2 Replies)
Discussion started by: learnoutmore99
2 Replies

2. Shell Programming and Scripting

Matching and Merging csv data fields based on a common field

Dear List, I have a file of csv data which has a different line per compliance check per host. I do not want any omissions from this csv data file which looks like this: date,hostname,status,color,check 02-03-2012,COMP1,FAIL,Yellow,auth_pass_change... (3 Replies)
Discussion started by: landossa
3 Replies

3. Shell Programming and Scripting

Merging CSV fields based on a common field

Hi List, I have two files. File1 contains all of the data I require to be processed, and I need to add another field to this data by matching a common field in File2 and appending a corresponding field to the data in File1 based on the match... So: File 1:... (1 Reply)
Discussion started by: landossa
1 Replies

4. Shell Programming and Scripting

Merging all (48) CSV files from a directory

I have 48 csv files in my directory that all have this form: Storm Speed (mph),43.0410781151 Storm motion direction (degrees),261.580774982 MLCAPE,2450.54098661 MLCIN,-9.85040520279 MLLCL,230 MLLFC,1070.39871 MLEL,207.194689294 MLCT,Not enough data Sbcape,2203.97617778... (3 Replies)
Discussion started by: RissaR
3 Replies

5. Shell Programming and Scripting

Merging two files by comparing three fields

Hi Experts, I need your timely help. I have a problem with merging two files. Here my situation : Here I have to compare first three fields from FILE1 with FILE2. If they are equal, I have to append the remaining values from FILE2 with FILE1 to create the output. FILE1: Class ... (3 Replies)
Discussion started by: Hunter85
3 Replies

6. Shell Programming and Scripting

Merging fields --- Join is not working

Hi GUYS sorry for putting simple query. I have tried the methods posted previously in this site but I'm unable to join the similar values in different columns of different files. I used sort -u file1 and join but no use.?? I'm attaching my inputfiles.Plz chek them I have two files. 1st file... (10 Replies)
Discussion started by: repinementer
10 Replies

7. Shell Programming and Scripting

Merging fields --- Help me plz

INPUT have a file with 2 columns. evry set in a column ends with a symbol //. the first one with something like chr, chr no, chromosome name, cell no. cell no. etc and the second column has values belong to the first columnlike chr Xy, 22, 345,22222 etc. Some clumns have repeated but not... (4 Replies)
Discussion started by: bogu0001
4 Replies

8. UNIX for Dummies Questions & Answers

Merging Non-Empty Columns within a CSV

I am trying to place all my data in a single row (order doesn't matter). Note I am a Unix novice, so please go easy on me. Here is an example Raw data: row# (1) 45 64 23 (2) 32 1 6 56 (3) 32 45 Needs to be like this: row# (1) 45 (2) 32 (3) 32 ... (2 Replies)
Discussion started by: mmann1123
2 Replies

9. Shell Programming and Scripting

merging fields from 2 different files.

File 1 3337304 2 4 DH.ER@TORONTO.CA 20080504 04622 3337305 2 4 A@C.COM 20080504 04622 3337306 2 4 JO@NET.NET 20080504 04622 3337307 2 4 L@GMAIL.COM 20080504 05344 2479201 2 2 ORY@YAHOO.COM 20080504 05344 File 2 ... (5 Replies)
Discussion started by: rudoraj
5 Replies

10. UNIX for Dummies Questions & Answers

Merging 2 .CSV files in Unix

I need a little help as I am a complete novice at scripting in unix. However, i am posed with an issue...:eek: i have two csv files in the following format@ FILE1.CSV: HEADER HEADER Header , , HEADER 001X ,,200 002X ,,300 003X ... (6 Replies)
Discussion started by: chachabronson
6 Replies
Login or Register to Ask a Question

Featured Tech Videos