Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Complex data sorting in excel files or text files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Complex data sorting in excel files or text files
# 8  
Old 06-08-2012
Quote:
Originally Posted by AAWT
Thanks a lot,,
but its only giving me counts like, 5432, 0r 2345

for A and C
is it possible to get new file with all A or C or A C pairs

Regards
like this ?
Code:
# awk '!/^ *$/{if(w==1){x1=xy;x2=$1}else{if($1)x1=$1;getline;if($0)x2=$1; else next};if(x1~/^A_/){if(x2~/^C_/){w=0;print x1 FS x2}else{w=1;xy=x2}}
}' file >A_C_pairs
# awk '/^A/{a[x++]=$1}END{for(i=0;i<x;i++)print a[i]}' file >A
# awk '/^C/{a[x++]=$1}END{for(i=0;i<x;i++)print a[i]}' file >C

# cat A
A_ABCD_13208
A_ABCDEF715
A_DBCDI_1353
A_EFGH_9099
A_QRST_9938
A_XVYZ_24808
A_TRST_7849
A_ABCDI_15931
A_ABCDI_15930
A_GHJK_30564
A_STDT_2657

# cat C
C_ABCD_13208
C_DBCDI_1353
C_EFGH_24808
C_QRST_9938
C_GHIH_9099
C_TRST_7849
C_GHJK_30564
C_STDT_2657

# cat A_C_pairs
A_ABCD_13208 C_ABCD_13208
A_ABCDEF715 C_DBCDI_1353
A_DBCDI_1353 C_EFGH_24808
A_EFGH_9099 C_QRST_9938
A_XVYZ_24808 C_GHIH_9099
A_ABCDI_15930 C_GHJK_30564
A_GHJK_30564 C_STDT_2657

# 9  
Old 06-11-2012
Hi Ygemici,

I got still some problem, in A_C_pairs, I am not getting the pairs of ids, I need to have same id only with A or C at start, but I am getting different As and Cs in file. ?????
As and Cs ids which are in pair should not present in A or C file.
Thanks
# 10  
Old 06-11-2012
Quote:
Originally Posted by AAWT
Hi Ygemici,

I got still some problem, in A_C_pairs, I am not getting the pairs of ids, I need to have same id only with A or C at start, but I am getting different As and Cs in file. ?????
As and Cs ids which are in pair should not present in A or C file.
Thanks
hi
what is your desired output ?
# 11  
Old 06-11-2012
Quote:
Originally Posted by AAWT
Dear all,
I have a complex data file shown below,,,,,

Code:
A_ABCD_13208   0   0   4.16735   141044   902449   1293900   168919   
C_ABCD_13208   0   0   4.16735   141044   902449   1293900   168919 
A_ABCDEF715   52410.9   18598.2   10611   10754.7   122535   252426   36631.4 
C_DBCDI_1353   0   26.512   0   93.9469   114151   94382.8   19043.1      
A_DBCDI_1353   0   26.512   0   93.9469   114151   94382.8   19043.1       
C_EFGH_24808   0   0   11.1129   5281.16   108786   146594   49778.1       
A_EFGH_9099   0   0   11.1129   5281.16   108786   146594   49778.1       
C_QRST_9938   0   0   0   2992.88   77887.8   60751.7   5253.41       
A_QRST_9938   0   0   0   2992.88   77887.8   60751.7   5253.41       
A_XVYZ_24808   0   0   0   33505.5   69088.4   167365   90621.9      
C_GHIH_9099   0   0   0   33505.5   69088.4   167365   90621.9      
C_TRST_7849   0   0   22.2259   2107.09   33073.1   42576.2   39891.2       
A_TRST_7849   0   0   22.2259   2107.09   33073.1   42576.2   39891.2       
A_ABCDI_15931   28.998   30.9306   11.1129   17966.2   32947.8   17405.4   3993.58       
A_ABCDI_15930   28.998   30.9306   11.1129   17966.2   32947.8   17405.4   3993.58      
C_GHJK_30564   0   0   0   214.736   30435.4   68135.6   69661.6       
A_GHJK_30564   0   0   0   214.736   30435.4   68135.6   69661.6       
C_STDT_2657   0   0   5.55647   1503.15   27929   101912   63628.2       
A_STDT_2657   0   0   5.55647   1503.15   27929   101912   63628.2

I want to sort to get following information

1: how many are pairs of A and C in the table? (A_ABCD_13208 and C_ABCD_13208)
2: How many only A and only C?
3: As I have many tables , how can I compare two tables for the unique ids in first column and for common ids??
4: Is there any software to create a Venn diagram for seven data sets???

Thanks a lot
Actually I need three outputs 1:which have all ids in pairs
Code:
 A_ABCD_13208  C_ABCD_13208 
A_QRST_9938     C_QRST_9938

2: with all unique C
Code:
C_EFGH_24808
C_ABC_1593

3. with all unique As
Code:
 A_ABCDEF715 
A_ABCDI_15930

Thanks
# 12  
Old 06-11-2012
Quote:
Originally Posted by AAWT
Actually I need three outputs 1:which have all ids in pairs
Code:
 A_ABCD_13208  C_ABCD_13208 
A_QRST_9938     C_QRST_9938

2: with all unique C
Code:
C_EFGH_24808
C_ABC_1593

3. with all unique As
Code:
 A_ABCDEF715 
A_ABCDI_15930

Thanks
1-) A and C ids in the pairs
Code:
# awk '/^A_/{a[$1]++;}/^C_/{c[$1]++}END{for(i in a){split(i,s,"_");for(j in c){split(j,ss,"_");
if(s[3]==ss[3])print i,j}}}' file
A_GHJK_30564 C_GHJK_30564
A_TRST_7849 C_TRST_7849
A_QRST_9938 C_QRST_9938
A_DBCDI_1353 C_DBCDI_1353
A_XVYZ_24808 C_EFGH_24808
A_EFGH_9099 C_GHIH_9099
A_STDT_2657 C_STDT_2657
A_ABCD_13208 C_ABCD_13208

2-) C uniqs (result is nothing because of every C records (ids) have for A ids)
Code:
# awk '{x=$1;split($1,s,"_");sub("[^0-9]*","",$1);a[x]=$1 FS s[1];}END{for(i in a){split(a[i],ss);j=ss[1] FS v2;
if(ss[2]==v1){for(jj in a)if(a[jj]!=j)x++}if(x==NR)print i;x=0}}' file v1="C" v2="A"

3-) A uniqs
Code:
# awk '{x=$1;split($1,s,"_");sub("[^0-9]*","",$1);a[x]=$1 FS s[1];}END{for(i in a){split(a[i],ss);j=ss[1] FS v2;
if(ss[2]==v1){for(jj in a)if(a[jj]!=j)x++}if(x==NR)print i;x=0}}' file v1="A" v2="C"
A_ABCDEF715
A_ABCDI_15930
A_ABCDI_15931

regards
ygemici
# 13  
Old 06-11-2012
Hi Ygemici,

It is still not giving me desired output,
i don't know where is problem
Code:
# awk '/^A_/{a[$1]++;}/^C_/{c[$1]++}END{for(i in a){split(i,s,"_");for(j in c){split(j,ss,"_"); if(s[3]==ss[3])print i,j}}}' file
 A_GHJK_30564 C_GHJK_30564 
A_TRST_7849 C_TRST_7849 
A_QRST_9938 C_QRST_9938 
A_DBCDI_1353 C_DBCDI_1353
 A_XVYZ_24808 C_EFGH_24808 
A_EFGH_9099 C_GHIH_9099 
A_STDT_2657 C_STDT_2657 
A_ABCD_13208 C_ABCD_13208

this command gave me output like
Code:
       
A_DBCDI_1353C_GHIH_9099     
 A_DBCDI_1353C_EFGH_24808      
 A_DBCDI_1353C_STDT_2657       
A_DBCDI_1353C_ABCD_13208,,,,,,,,,,,,,,,,

and it is adding same A id for 1000 times and also for C ids ,
its giving me a huge data file with a lot of repetition of records,

looking for help,,,,,,,,Smilie

Regards
# 14  
Old 06-11-2012
Quote:
Originally Posted by AAWT
Hi Ygemici,

It is still not giving me desired output,
i don't know where is problem
Code:
# awk '/^A_/{a[$1]++;}/^C_/{c[$1]++}END{for(i in a){split(i,s,"_");for(j in c){split(j,ss,"_"); if(s[3]==ss[3])print i,j}}}' file
 A_GHJK_30564 C_GHJK_30564 
A_TRST_7849 C_TRST_7849 
A_QRST_9938 C_QRST_9938 
A_DBCDI_1353 C_DBCDI_1353
 A_XVYZ_24808 C_EFGH_24808 
A_EFGH_9099 C_GHIH_9099 
A_STDT_2657 C_STDT_2657 
A_ABCD_13208 C_ABCD_13208

this command gave me output like
Code:
       
A_DBCDI_1353C_GHIH_9099     
 A_DBCDI_1353C_EFGH_24808      
 A_DBCDI_1353C_STDT_2657       
A_DBCDI_1353C_ABCD_13208,,,,,,,,,,,,,,,,

and it is adding same A id for 1000 times and also for C ids ,
its giving me a huge data file with a lot of repetition of records,

looking for help,,,,,,,,Smilie

Regards
* what is your O.S ?
* your full input file ? (can you attach it ?)
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merge and Sort tabular data from different text files

I have 42 text files; each containing up to 34 lines with following structure; file1 H-01 23 H-03 5 H-05 9 H-02 14 . . file2 H-01 17 H-02 43 H-04 7 H-05 8 H-03 7 . . file3 (6 Replies)
Discussion started by: Syeda Sumayya
6 Replies

2. UNIX for Dummies Questions & Answers

Unexplained text in data files

Has anyone ever encountered text from other files suddenly appearing in another data file that is not being used. There does not seem to be any reason for it, any thoughts would be useful. Thanks (14 Replies)
Discussion started by: SRoberts
14 Replies

3. Shell Programming and Scripting

Sorting indented text files

Hello, I'm trying to find a solution or a proper tool for the following job: I need to sort a text document with indented sections, so all levels of indentation are sorted independently for each section. Particularly, I need this for Cisco routers' running config files to compare them with... (2 Replies)
Discussion started by: kobel
2 Replies

4. Shell Programming and Scripting

Perl script to Merge contents of 2 different excel files in a single excel file

All, I have an excel sheet Excel1.xls that has some entries. I have one more excel sheet Excel2.xls that has entries only in those cells which are blank in Excel1.xls These may be in different workbooks. They are totally independent made by 2 different users. I have placed them in a... (1 Reply)
Discussion started by: Anamika08
1 Replies

5. Shell Programming and Scripting

How to write text file data to excel using UNIX shell script?

Hi All, I have the requirement in unix shell script. I want to write the "ls -ltr" command out put to excel file as below. Input :text file data : drwxr-xr-x 5 root root 4096 Oct 2 12:26 drwxr-xr-x 2 apx aim 4096 Nov 29 18:40 drwxr-xr-x 5 root root 4096 Oct 2 12:26 drwxr-xr-x... (10 Replies)
Discussion started by: Balasankar
10 Replies

6. Shell Programming and Scripting

Need help on inserting data from text file to excel using shell script

Hi, Please help me on this. I want to insert data from text file to excel using shell script nawk -v r=4 -v c=4 -v val=$a -F, 'BEGIN{OFS=","}; NR != r; NR == r {$c = val; print}' "file.csv" I used above one to insert $a value in 4th row, 4th column in an excel file.csv and it... (3 Replies)
Discussion started by: suman.frnz
3 Replies

7. Shell Programming and Scripting

Attaching two text files in two different sheet in same excel

Hi, My requirement is to get attach two different text file contents to two different sheets in same excelsheet. Also, is there any way we can name the tabs as desired ? Kindly assist. (2 Replies)
Discussion started by: sanjaydubey2006
2 Replies

8. Shell Programming and Scripting

sorting from several files for a specific data

Please assist: I have several files and all of the files have the same data format like following: All I need to get item next to "name" field and the "address" field from each file which has only 8 characters in "name" field. so the output should be: ams00ark(spcae)10.1.1.12... (3 Replies)
Discussion started by: amir07
3 Replies

9. Shell Programming and Scripting

PERL: Split Excel Workbook to Indiv Excel files

Hi, I am trying to find a way to read an excel work book with multiple worksheets. And write each worksheet into a new excel file using perl. My environment is Unix. For example: I have an excel workbook TEST.xls and it has Sheet1, Sheet2, Sheet3 worksheets. I would like to create... (2 Replies)
Discussion started by: sandeep78
2 Replies

10. UNIX for Dummies Questions & Answers

sorting files with find command before sending to text file

i need help with my script.... i am suppose to grab files within a certain date range now i have done that already using the touch and find command (found them in other threads) touch -d "$date_start" ./tmp1 touch -d "$date_end" ./tmp2 find "$data_location" -maxdepth 1 -newer ./tmp1 !... (6 Replies)
Discussion started by: deking
6 Replies
Login or Register to Ask a Question