Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Duplicate rows in CSV files based on values

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Duplicate rows in CSV files based on values
# 1  
Old 04-15-2011
Duplicate rows in CSV files based on values
I am new to this forum and this is my first post.

I am looking at an old post with exactly the same name. Can not paste URL because I do not have 5 posts

My requirement is exactly opposite.

I want to get rid of duplicate rows and try to append the values of columns in those rows

Input
Code:
abc, first line, value1
def, second line, value2
def, second line, value3
ghi, third line, value4

Output
Code:
abc, first line, value1
def, second line, "value2,value3"
ghi, third line, value4

Last edited by Scott; 04-18-2011 at 05:55 PM..
# 2  
Old 04-15-2011
sort and use sed, with two lines in the buffer, to fold them together.
# 3  
Old 04-15-2011
Thanks a lot for your quick reply. I appreciate your help but I am new to scripting could you please add some sample code. I can modify it as per my requirement.
# 4  
Old 04-15-2011
See if this works for you:

Code:
#!/usr/bin/ksh
typeset -i mCnt=0
mPFld1="First_Time"
IFS=','
while read mFld1 mFld2 mValue
do
  if [[ "${mFld1}" != "${mPFld1}" || "${mFld2}" != "${mPFld2}" ]]; then
    if [[ "${mPFld1}" != "First_Time" ]]; then
      if [[ ${mCnt} -gt 1 ]]; then
        echo ${mPFld1}'COMMA'${mPFld2}'COMMA"'${mOutValue}'"'
      else
        echo ${mPFld1}'COMMA'${mPFld2}'COMMA'${mOutValue}
      fi
    fi
    mOutValue=''
    mCnt=0
  fi
  if [[ "${mOutValue}" = "" ]]; then
    mOutValue=${mValue}
  else
    mOutValue=${mOutValue}'COMMA'${mValue}
  fi
  mPFld1=${mFld1}
  mPFld2=${mFld2}
  mCnt=${mCnt}+1
done < Inp_File
if [[ "${mPFld1}" != "First_Time" ]]; then
  if [[ ${mCnt} -gt 1 ]]; then
    echo ${mPFld1}'COMMA'${mPFld2}'COMMA"'${mOutValue}'"'
  else
    echo ${mPFld1}'COMMA'${mPFld2}'COMMA'${mOutValue}
  fi
fi

Run it as follows:
Code:
>my_script > Out_File
>sed 's/COMMA/,/g' Out_File

# 5  
Old 04-15-2011
Thanks a lot for your reply. Really appreciate your quick help
The output is as follows

Code:
 
abc, first line, value1
def, second line," value2, value3"
ghi, third line, value4
,,

I am sorry but one more question. If file has 3 more columns

Input
Code:
 
abc, first line, value1, col1,col2,col3
def, second line, value2, col4,col5,col6
def, second line, value3, col4,col5,col6
ghi, third line, value4, col7,col8,col9

output

Code:
 
abc, first line, value1, col1,col2,col3

def, second line," value2, value3",col4,col5,col6
ghi, third line, value4,  col7,col8,col9

will there be a major change in this code? I am trying it now. Also I am trying to get rid of those extra commas on the last line of the output file.
# 6  
Old 04-15-2011
Code:
echo "abc, first line, value1, col1,col2,col3
def, second line, value2, col4,col5,col6
def, second line, value3, col4,col5,col6
ghi, third line, value4, col7,col8,col9" |sed -n -r '1h;{2,$H;x;s/(.*), "?(.*), ([^\n]*)\n\1, (.*), \3/\1 "\2, \4",\3/;h};${s/", /, /g;p}'
abc, first line, value1, col1,col2,col3
def, second line "value2, value3",col4,col5,col6
ghi, third line, value4, col7,col8,col9

---------- Post updated at 06:02 PM ---------- Previous update was at 05:52 PM ----------

awk would be much more controllable
Code:
echo "abc, first line, value1, col1,col2,col3
def, second line, value2, col4,col5,col6
def, second line, value3, col4,col5,col6
ghi, third line, value4, col7,col8,col9" |awk '{sub(",","",$4);x=$1 FS $2 FS $3 FS $5;a[x]=a[x]?a[x] FS $4:$4}END{for(i in a) {split(i,b,FS);print b[1],b[2],b[3],a[i]~FS?"\""a[i]"\",":a[i]",",b[4]}}'
abc, first line, value1, col1,col2,col3
def, second line, "value2 value3", col4,col5,col6
ghi, third line, value4, col7,col8,col9

# 7  
Old 04-18-2011
Is this csv code robust against quoted commas?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Get duplicate rows from a csv file

How can i get the duplicates rows from a file using unix, for example i have data like a,1 b,2 c,3 d,4 a,1 c,3 e,5 i want output to be like a,1 c,3 (4 Replies)
Discussion started by: ggupta
4 Replies

2. Shell Programming and Scripting

Extract and exclude rows based on duplicate values

Hello I have a file like this: > cat examplefile ghi|NN603762|eee mno|NN607265|ttt pqr|NN613879|yyy stu|NN615002|uuu jkl|NN607265|rrr vwx|NN615002|iii yzA|NN618555|ooo def|NN190486|www BCD|NN628717|ppp abc|NN190486|qqq EFG|NN628717|aaa HIJ|NN628717|sss > I can sort the file by... (5 Replies)
Discussion started by: CHoggarth
5 Replies

3. Shell Programming and Scripting

Average values of duplicate rows

I have this file input.txt. I want to take average column-wise for the rows having duplicate gene names. Gene Sample_1 Sample_2 Sample_3 gene_A 2 4 5 gene_B 1 2 3 gene_A 0 5 7 gene_B 4 5 6 gene_A 11 12 13 gene_C 2 3 4 Desired output: gene_A 4.3 7 8.3 gene_B 2.5 3.5 4.5 gene_C 2 3 4... (6 Replies)
Discussion started by: Sanchari
6 Replies

4. Shell Programming and Scripting

Remove duplicate rows based on one column

Dear members, I need to filter a file based on the 8th column (that is id), and does not mather the other columns, because I want just one id (1 line of each id) and remove the duplicates lines based on this id (8th column), and does not matter wich duplicate will be removed. example of my file... (3 Replies)
Discussion started by: clarissab
3 Replies

5. Shell Programming and Scripting

How to generate a csv files by separating the values from the input file based on position?

Hi All, I need help for doing the following. I have a input file like: aaaaaaaaaabbbbbbbbbbbbbbbbbbbb cccbbbbbaaaaaadddddaaaabbbbbbb now I am trying to generate a output csv file where i will have for e.g. 0-3 chars of each line as the first column in the csv, 4-10 chars of the line as... (3 Replies)
Discussion started by: babom
3 Replies

6. Shell Programming and Scripting

printing 3 files side by side based on similar values in rows

Hi I'm trying to compare 3 or more files based on similar values and outputting them into 3 columns. For example: file1 ABC DEF GHI file2 DEF DER file3 ABC DER The output should come out like this file1 file2 file3 ABC ABC (4 Replies)
Discussion started by: zerofire123
4 Replies

7. UNIX for Dummies Questions & Answers

forming duplicate rows based on value of a key

if the key (A or B or ...others) has 4 in its 3rd column the 1st A row has to form 4 dupicates along with the all the values of A in 4th column (2.9, 3.8, 4.2) . Hope I explain the question clearly. Cheers Ruby input "A" 1 4 2.9 "A" 2 5 ... (7 Replies)
Discussion started by: ruby_sgp
7 Replies

8. Shell Programming and Scripting

how to delete duplicate rows based on last column

hii i have a huge amt of data stored in a file.Here in this file i need to remove duplicates rows in such a way that the last column has different data & i must check for greatest among last colmn data & print the largest data along with other entries but just one of other duplicate entries is... (16 Replies)
Discussion started by: reva
16 Replies

9. Shell Programming and Scripting

Duplicate rows in CSV files based on values

I want to duplicate a row if found two or more values in a particular column for corresponding row which is delimitted by comma. Input abc,line one,value1 abc,line two, value1, value2 abc,line three,value1 needs to converted to abc,line one,value1 abc,line two, value1 abc,line... (8 Replies)
Discussion started by: Incrediblian
8 Replies

10. UNIX for Dummies Questions & Answers

Remove duplicate rows of a file based on a value of a column

Hi, I am processing a file and would like to delete duplicate records as indicated by one of its column. e.g. COL1 COL2 COL3 A 1234 1234 B 3k32 2322 C Xk32 TTT A NEW XX22 B 3k32 ... (7 Replies)
Discussion started by: risk_sly
7 Replies
Login or Register to Ask a Question