How to align/sort the column pairs of an csv file, based on keyword word specified in another file? Post: 303034663

Sponsored Content

Top Forums UNIX for Beginners Questions & Answers How to align/sort the column pairs of an csv file, based on keyword word specified in another file? Post 303034663 by RudiC on Thursday 2nd of May 2019 03:59:33 AM

05-02-2019

Registered User

No surprise things are going amiss. You changed both the field separator AND the file structure several times. In post #1, you had <TAB> field separators, and an empty field between the key string and the numerical value pertaining to it. In post #3, there were just a handful spaces separating the fields. And then, in post #7, comma separators, and no empty field. As Don Cragun already pointed out, that won't fly without the necessary code adaptions.
With your last input sample file, try

Code:

awk -F"," '
NR==FNR {for (i=1; i<=3; i++)   {IX = (i-1)*2+1
                                 split ($IX, T, "_")
                                 O[T[1] FS i] = $IX FS $(IX+1)
                                }
         next
        }
        {for (i=1; i<=3; i++)  printf "%s%s", O[$1 FS i] (O[$1 FS i]?_:FS) , i==3?ORS:FS
        }
'  org1.csv key.txt
xop_thy,80,xop_nmg,50,xop_nth,40
avr_irt,70,avr_njk,50,avr_ngt,50
str_tgt,80,str_nhj,60,str_nyu,60
cyv_gty,40,,,,
vir_plo,20,vir_thk,40,vir_tyk,80
cop_thy,70,cop_thl,40,,
,,ijk_yuc,80,ijk_yuc,70
 ,,,,irt_hgt,80

And, don't change neither code nor input structure without exactly knowing what you are doing!

EDIT: With your "new and simpler" file structure, above can be simplified to

Code:

awk -F"," '
NR==FNR {for (i=1; i<=5; i+=2)  {split ($i, T, "_")
                                 O[T[1] FS i] = $i FS $(i+1)
                                }
         next
        }
        {for (i=1; i<=5; i+=2)  printf "%s%s", O[$1 FS i] (O[$1 FS i]?_:FS) , i==5?ORS:FS
        }
' org1.csv key.txt

Last edited by RudiC; 05-02-2019 at 05:50 AM..

RudiC

View Public Profile for RudiC

Find all posts by RudiC

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

sorting csv file based on column selected

Hi all, in my csv file it'll look like this, and of course it may have more columns US to UK;abc-hq-jcl;multimedia UK to CN;def-ny-jkl;standard DE to DM;abc-ab-klm;critical FD to YM;la-yr-tym;standard HY to MC;la-yr-ytm;multimedia GT to KJ;def-ny-jrt;critical I would like to group...

2. Shell Programming and Scripting

Sort file based on column

Hi, My input file is $cat samp 1 siva 1 raja 2 siva 1 siva 2 raja 4 venkat i want sort this name wise...alos need to remove duplicate lines. i am using cat samp|awk '{print $2,$1}'|sort -u it showing raja 1

3. Shell Programming and Scripting

Pick the column value based on another column from .csv file

My scenario is that I need to pick value from third column based on fourth column value, if fourth column value is 1 then first value of third column.Third column (2|3|4|6|1) values are cancatenated. Main imp point, in my .csv file, third column is having price value with comma (1,20,300), it has...

4. UNIX for Dummies Questions & Answers

Sort csv file by duplicated column value

hello, I have a large file (about 1gb) that is in a file similar to the following: I want to make it so that I can put all the duplicates where column 3 (delimited by the commas) are shown on top. Meaning all people with the same age are listed at the top. The command I used was ...

5. Linux

Filter a .CSV file based on the 5th column values

I have a .CSV file with the below format: "column 1","column 2","column 3","column 4","column 5","column 6","column 7","column 8","column 9","column 10 "12310","42324564756","a simple string with a , comma","string with or, without commas","string 1","USD","12","70%","08/01/2013",""...

6. Shell Programming and Scripting

Fetching values in CSV file based on column name

input.csv: Field1,Field2,Field3,Field4,Field4 abc ,123 ,xyz ,000 ,pqr mno ,123 ,dfr ,111 ,bbb output: Field2,Field4 123 ,000 123 ,111 how to fetch the values of Field4 where Field2='123' I don't want to fetch the values based on column position. Instead want to...

7. Shell Programming and Scripting

Get maximum per column from CSV file, based on date column

Hello everyone, I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this: 20170628-23:25:01,1,0,0,1,1,1,1,55,55,1 20170628-23:30:01,1,0,0,1,1,1,1,56,56,1 20170628-23:35:00,1,0,0,1,1,2,1,57,57,2 20170628-23:40:00,1,0,0,1,1,1,1,58,58,2...

8. UNIX for Beginners Questions & Answers

Filtering records of a csv file based on a value of a column

Hi, I tried filtering the records in a csv file using "awk" command listed below. awk -F"~" '$4 ~ /Active/{print }' inputfile > outputfile The output always has all the entries. The same command worked for different users from one of the forum links. content of file I was...

9. UNIX for Beginners Questions & Answers

How to sort a column in excel/csv file?

I have to sort the 4th column of an excel/csv file. I tried the following command sort -u --field-separator=, --numeric-sort -k 2 -n dinesh.csv > test.csv But, it's not working. Moreover, I have to do the same for more than 30 excel/csv file. So please help me to do the same.

LEARN ABOUT BSD

join

JOIN(1) 						      General Commands Manual							   JOIN(1)

NAME

       join - relational database operator

SYNOPSIS

       join [ options ] file1 file2

DESCRIPTION

       Join  forms,  on the standard output, a join of the two relations specified by the lines of file1 and file2.  If file1 is `-', the standard
       input is used.

       File1 and file2 must be sorted in increasing ASCII collating sequence on the fields on which they are to be joined, normally the  first	in
       each line.

       There  is  one line in the output for each pair of lines in file1 and file2 that have identical join fields.  The output line normally con-
       sists of the common field, then the rest of the line from file1, then the rest of the line from file2.

       Fields are normally separated by blank, tab or newline.	In this case, multiple separators count as one, and leading  separators  are  dis-
       carded.

       These options are recognized:

       -an    In addition to the normal output, produce a line for each unpairable line in file n, where n is 1 or 2.

       -e s   Replace empty output fields by string s.

       -jn m  Join on the mth field of file n.	If n is missing, use the mth field in each file.

       -o list
	      Each output line comprises the fields specified in list, each element of which has the form n.m, where n is a file number and m is a
	      field number.

       -tc    Use character c as a separator (tab character).  Every appearance of c in a line is significant.

SEE ALSO

       sort(1), comm(1), awk(1)

BUGS

       With default field separation, the collating sequence is that of sort -b; with -t, the sequence is that of a plain sort.

       The conventions of join, sort, comm, uniq, look and awk(1) are wildly incongruous.

7th Edition							  April 29, 1985							   JOIN(1)