Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to align/sort the column pairs of an csv file, based on keyword word specified in another file?
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 303034620 by dineshkumarsrk on Wednesday 1st of May 2019 03:27:01 AM
Old 05-01-2019
How to align/sort the column pairs of an csv file, based on keyword word specified in another file?
I have a csv file as shown below,
Code:
xop_thy		80	avr_njk		50	str_nyu		60
avr_irt		70	str_nhj		60	avr_ngt		50
str_tgt		80	xop_nmg		50	xop_nth		40
cyv_gty		40	cop_thl		40	vir_tyk		80
vir_plo		20	vir_thk		40	ijk_yuc		70		
cop_thy		70	ijk_yuc		80	irt_hgt		80

I need to align/sort the csv file based on the order mentioned in another file (keyword file) as shown below,
Code:
xop
avr
str
cyv
vir
cop
ijk
irt

The desired output is shown below,
Code:
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

The major condition is the column to be arranged pair wise (while rearrangement the string column should take the concerned value column adjacent to it together) like wise the pairs of columns to be rearranged based on the keywords. Another problem the keyword is the starting letters of each column, the keyword file has only starting strings of the columns strings. each column pairs are having common keyword but after the underscore symbol it vary. Therefore, I do not know how to make code for it. If it number or alphabet based sorting I can use
Quote:
sort
function. But here i could not use it due to the complexity of condition. I am not sure, Is it possible to do the same. if it possible please help me.
Thanks in advance.
 

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... (4 Replies)
Discussion started by: tententen
4 Replies

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 Replies)
Discussion started by: rsivasan
3 Replies

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... (2 Replies)
Discussion started by: Ganesh L
2 Replies

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 ... (3 Replies)
Discussion started by: jl487
3 Replies

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",""... (2 Replies)
Discussion started by: dhruuv369
2 Replies

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... (10 Replies)
Discussion started by: bharathbangalor
10 Replies

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... (6 Replies)
Discussion started by: ejianu
6 Replies

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... (3 Replies)
Discussion started by: sunilmudikonda
3 Replies

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. (6 Replies)
Discussion started by: dineshkumarsrk
6 Replies

LEARN ABOUT NETBSD

column

COLUMN(1)						    BSD General Commands Manual 						 COLUMN(1)

NAME

     column -- columnate lists

SYNOPSIS

     column [-tx] [-c columns] [-s sep] [file ...]

DESCRIPTION

     The column utility formats its input into multiple columns.  Rows are filled before columns.  Input is taken from file operands, or, by
     default, from the standard input.	Empty lines are ignored.

     The options are as follows:

     -c      Output is formatted for a display columns wide.

     -s      Specify a set of characters to be used to delimit columns for the -t option.

     -t      Determine the number of columns the input contains and create a table.  Columns are delimited with whitespace, by default, or with
	     the characters supplied using the -s option.  Useful for pretty-printing displays.

     -x      Fill columns before filling rows.

     column exits 0 on success, >0 if an error occurred.

ENVIRONMENT

     COLUMNS  The environment variable COLUMNS is used to determine the size of the screen if no other information is available.

EXAMPLES

	   (echo "PERM LINKS OWNER GROUP SIZE MONTH DAY HH:MM/YEAR NAME"; 
	   ls -l | sed 1d) | column -t

SEE ALSO

     colrm(1), ls(1), paste(1), sort(1)

HISTORY

     The column command appeared in 4.3BSD-Reno.

BSD
								   March 9, 2008							       BSD
All times are GMT -4. The time now is 10:10 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy