Sponsored Content
Top Forums Shell Programming and Scripting return a list of unique values of a column from csv format file Post 302361291 by phoeberunner on Monday 12th of October 2009 11:17:43 PM
Old 10-13-2009
return a list of unique values of a column from csv format file

Hi all,

I have a huge csv file with the following format of data,

[HEADER]
Num SNPs, 549997
Total SNPs,555352
Num Samples, 157
[Data]
SNP, SampleID, Allele1, Allele2
A001,AB1,A,A
A002,AB1,A,A
A003,AB1,A,A
...
...
...


I would like to write out a list of unique SNP (column 1). Could you let me know how to do this with UNIX command? Do I need to at firstl convert csv file to text file?

Thank you for your attention!

phoebe
 

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

print unique values of a column and sum up the corresponding values in next column

Hi All, I have a file which is having 3 columns as (string string integer) a b 1 x y 2 p k 5 y y 4 ..... ..... Question: I want get the unique value of column 2 in a sorted way(on column 2) and the sum of the 3rd column of the corresponding rows. e.g the above file should return the... (6 Replies)
Discussion started by: amigarus
6 Replies

2. Shell Programming and Scripting

AWK, Perl or Shell? Unique strings and their maximum values from 3 column data file

I have a file containing data like so: 2012-01-02 GREEN 4 2012-01-02 GREEN 6 2012-01-02 GREEN 7 2012-01-02 BLUE 4 2012-01-02 BLUE 3 2012-01-02 GREEN 4 2012-01-02 RED 4 2012-01-02 RED 8 2012-01-02 GREEN 4 2012-01-02 YELLOW 5 2012-01-02 YELLOW 2 I can't always predict what the... (4 Replies)
Discussion started by: rich@ardz
4 Replies

3. Shell Programming and Scripting

List unique values and count instances in .csv file

I need to take the second column of a .csv file and count the number of instances of each unique value in that same second column. I'd like the output to be value,count sorted by most instances. Thanks for any guidance! Data example: 317476,317756,0 816063,318861,0 313123,319091,0... (4 Replies)
Discussion started by: batcho
4 Replies

4. UNIX for Dummies Questions & Answers

Grep to find matching patern and return unique values

Request: grep to find given matching patern and return unique values, eliminate the duplicate values I have to retrieve the unique folder on the below file contents like; /app/oracle/build_lib/pkg320.0_20120927 /app/oracle/build_lib/pkg320.0_20121004_prof... (5 Replies)
Discussion started by: Siva SQL
5 Replies

5. Shell Programming and Scripting

Remove the values from a certain column without deleting the Column name in a .CSV file

(14 Replies)
Discussion started by: dhruuv369
14 Replies

6. Linux

To get all the columns in a CSV file based on unique values of particular column

cat sample.csv ID,Name,no 1,AAA,1 2,BBB,1 3,AAA,1 4,BBB,1 cut -d',' -f2 sample.csv | sort | uniq this gives only the 2nd column values Name AAA BBB How to I get all the columns of CSV along with this? (1 Reply)
Discussion started by: sanvel
1 Replies

7. Shell Programming and Scripting

Extracting unique values of a column from a feed file

Hi Folks, I have the below feed file named abc1.txt in which you can see there is a title and below is the respective values in the rows and it is completely pipe delimited file ,. ... (4 Replies)
Discussion started by: punpun66
4 Replies

8. Shell Programming and Scripting

Using grep and a parameter file to return unique values

Hello Everyone! I have updated the first post so that my intentions are easier to understand, and also attached sample files (post #18). I have over 500 text files in a directory. Over 1 GB of data. The data in those files is organised in lines: My intention is to return one line per... (23 Replies)
Discussion started by: clippertm
23 Replies

9. Shell Programming and Scripting

CSV File:Filter duplicate records from column1 & another column having unique record

Hi Experts, I have csv file with 30, 40 columns Pasting just 2 column for problem description. Need to print error if below combination is not present in file check for column-1 (DocumentNumber) and filter columns where value in DocumentNumber field is same. For all such rows, the field... (7 Replies)
Discussion started by: as7951
7 Replies
Bio::PopGen::HtSNP(3pm) 				User Contributed Perl Documentation				   Bio::PopGen::HtSNP(3pm)

NAME
Bio::PopGen::HtSNP.pm- Select htSNP from a haplotype set SYNOPSIS
use Bio::PopGen::HtSNP; my $obj = Bio::PopGen::HtSNP->new($hap,$snp,$pop); DESCRIPTION
Select the minimal set of SNP that contains the full information about the haplotype without redundancies. Take as input the followin values: - the haplotype block (array of array). - the snp id (array). - family information and frequency (array of array). The final haplotype is generated in a numerical format and the SNP's sets can be retrieve from the module. considerations: - If you force to include a family with indetermination, the SNP's with indetermination will be removed from the analysis, so consider before to place your data set what do you really want to do. - If two families have the same information (identical haplotype), one of them will be removed and the removed files will be stored classify as removed. - Only are accepted for calculation A, C, G, T and - (as deletion) and their combinations. Any other value as n or ? will be considered as degenerations due to lack of information. RATIONALE On a haplotype set is expected that some of the SNP and their variations contribute in the same way to the haplotype. Eliminating redundancies will produce a minimal set of SNP's that can be used as input for a taging selection process. On the process SNP's with the same variation are clustered on the same group. The idea is that because the tagging haplotype process is exponential. All redundant information we could eliminate on the tagging process will help to find a quick result. CONSTRUCTORS my $obj = Bio::PopGen::HtSNP->new (-haplotype_block => @haplotype_patterns, -snp_ids => @snp_ids, -pattern_freq => @pattern_name_and_freq); where $hap, $snp and $pop are in the format: my $hap = [ 'acgt', 'agtc', 'cgtc' ]; # haplotype patterns' id my $snp = [qw/s1 s2 s3 s4/]; # snps' Id's my $pop = [ [qw/ uno 0.20/], [qw/ dos 0.20/], [qw/ tres 0.15/], ]; # haplotype_pattern_id Frequency OBJECT METHODS See Below for more detailed summaries. DETAILS
How the process is working with one example Let's begin with one general example of the code. Input haplotype: acgtcca-t cggtagtgc cccccgtgc cgctcgtgc The first thing to to is to split the haplotype into characters. a c g t c c a - t c g g t a g t g c c c c c c g t g c c g c t c g t g c Now we have to convert the haplotype to Upercase. This will produce the same SNP if we have input a or A. A C G T C C A - T C G G T A G T G C C C C C C G T G C C G C T C G T G C The program admit as values any combination of ACTG and - (deletions). The haplotype is converted to number, considering the first variation as zero and the alternate value as 1 (see expanded description below). 0 0 0 0 0 0 0 0 0 1 1 0 0 1 1 1 1 1 1 0 1 1 0 1 1 1 1 1 1 1 0 0 1 1 1 1 Once we have the haplotype converted to numbers we have to generate the snp type information for the haplotype. SNP code = SUM ( value * multiplicity ^ position ); where: SUM is the sum of the values for the SNP value is the SNP number code (0 [generally for the mayor allele], 1 [for the minor allele]. position is the position on the block. For this example the code is: 0 0 0 0 0 0 0 0 0 1 1 0 0 1 1 1 1 1 1 0 1 1 0 1 1 1 1 1 1 1 0 0 1 1 1 1 ------------------------------------------------------------------ 14 10 12 4 2 14 14 14 14 14 = 0*2^0 + 1*2^1 + 1*2^2 + 1*2^3 12 = 0*2^0 + 1*2^1 + 0*2^2 + 1*2^3 .... Once we have the families classify. We will take just the SNP's not redundant. 14 10 12 4 2 This information will be passed to the tag module is you want to tag the htSNP. Whatever it happens to one SNPs of a class will happen to a SNP of the same class. Therefore you don't need to scan redundancies Working with fuzzy data. This module is designed to work with fuzzy data. As the source of the haplotype is diverse. The program assume that some haplotypes can be generated using different values. If there is any indetermination (? or n) or any other degenerated value or invalid. The program will take away This SNP and will leave that for a further analysis. On a complex situation: a c g t ? c a c t a c g t ? c a - t c g ? t a g ? g c c a c t c g t g c c g c t c g t g c c g g t a g ? g c a c ? t ? c a c t On this haplotype everything is happening. We have a multialelic variance. We have indeterminations. We have deletions and we have even one SNP which is not a real SNP. The buiding process will be the same on this situation. Convert the haplotype to uppercase. A C G T ? C A C T A C G T ? C A - T C G ? T A G ? G C C A C T C G T G C C G C T C G T G C C G G T A G ? G C A C ? T ? C A C T All columns that present indeterminations will be removed from the analysis on this Step. hapotype after remove columns: A C T C C T A C T C - T C G T G G C C A T G G C C G T G G C C G T G G C A C T C C T All changes made on the haplotype matrix, will be also made on the SNP list. snp_id_1 snp_id_2 snp_id_4 snp_id_6 snp_id_8 snp_id_9 now the SNP that is not one SNP will be removed from the analysis. SNP with Id snp_id_4 (the one with all T's). because of the removing. Some of the families will become the same and will be clustered. A posteriori analysis will diference these families. but because of the indetermination can not be distinguish. A C C C T A C C - T C G G G C C A G G C C G G G C C G G G C A C C C T The result of the mergering will go like: A C C C T A C C - T C G G G C C A G G C Once again the changes made on the families and we merge the frequency (to be implemented) Before to convert the haplotype into numbers we consider how many variations we have on the set. On this case the variations are 3. The control code will use on this situation base three as mutiplicity 0 0 0 0 0 0 0 0 1 0 1 1 1 2 1 1 2 1 2 1 ----------------------------------- 36 63 36 75 36 And the minimal set for this combination is 0 0 0 0 0 1 1 1 2 1 2 2 NOTE: this second example is a remote example an on normal conditions. This conditions makes no sense, but as the haplotypes, can come from many sources we have to be ready for all kind of combinations. FEEDBACK
Mailing Lists User feedback is an integral part of the evolution of this and other Bioperl modules. Send your comments and suggestions preferably to the Bioperl mailing list. Your participation is much appreciated. bioperl-l@bioperl.org - General discussion http://bioperl.org/wiki/Mailing_lists - About the mailing lists Support Please direct usage questions or support issues to the mailing list: bioperl-l@bioperl.org rather than to the module maintainer directly. Many experienced and reponsive experts will be able look at the problem and quickly address it. Please include a thorough description of the problem with code and data examples if at all possible. Reporting Bugs Report bugs to the Bioperl bug tracking system to help us keep track of the bugs and their resolution. Bug reports can be submitted via the web: https://redmine.open-bio.org/projects/bioperl/ AUTHOR - Pedro M. Gomez-Fabre Email pgf18872-at-gsk-dot-com APPENDIX
The rest of the documentation details each of the object methods. Internal methods are usually preceded with a _ new Title : new Function: constructor of the class. Usage : $obj-> Bio::PopGen::HtSNP->new(-haplotype_block -snp_ids -pattern_freq) Returns : self hash Args : input haplotype (array of array) snp_ids (array) pop_freq (array of array) Status : public haplotype_block Title : haplotype_block Usage : my $haplotype_block = $HtSNP->haplotype_block(); Function: Get the haplotype block for a haplotype tagging selection Returns : reference of array Args : reference of array with haplotype pattern snp_ids Title : snp_ids Usage : my $snp_ids = $HtSNP->$snp_ids(); Function: Get the ids for a haplotype tagging selection Returns : reference of array Args : reference of array with SNP ids pattern_freq Title : pattern_freq Usage : my $pattern_freq = $HtSNP->pattern_freq(); Function: Get the pattern id and frequency for a haplotype tagging selection Returns : reference of array Args : reference of array with SNP ids _check_input Title : _check_input Usage : _check_input($self) Function: check for errors on the input Returns : self hash Args : self Status : internal _haplotype_length_error Title : _haplotype_length_error Usage : _haplotype_length_error($self) Function: check if the haplotype length is the same that the one on the SNP id list. If not break and exit Returns : self hash Args : self Status : internal _population_error Title : _population_error Usage : _population_error($self) Function: use input_block and pop_freq test if the number of elements match. If doesn't break and quit. Returns : self hash Args : self Status : internal _do_it Title : _do_it Usage : _do_it($self) Function: Process the input generating the results. Returns : self hash Args : self Status : internal input_block Title : input_block Usage : $obj->input_block() Function: returns input block Returns : reference to array of array Args : none Status : public hap_length Title : hap_length Usage : $obj->hap_length() Function: get numbers of SNP on the haplotype Returns : scalar Args : none Status : public pop_freq Title : pop_freq Usage : $obj->pop_freq() Function: returns population frequency Returns : reference to array Args : none Status : public deg_snp Title : deg_snp Usage : $obj->deg_snp() Function: returns snp_removes due to indetermination on their values Returns : reference to array Args : none Status : public snp_type Title : snp_type Usage : $obj->snp_type() Function: returns hash with SNP type Returns : reference to hash Args : none Status : public silent_snp Title : silent_snp Usage : $obj->silent_snp() Function: some SNP's are silent (not contibuting to the haplotype) and are not considering for this analysis Returns : reference to a array Args : none Status : public useful_snp Title : useful_snp Usage : $obj->useful_snp() Function: returns list of SNP's that are can be used as htSNP. Some of them can produce the same information. But this is not considered here. Returns : reference to a array Args : none Status : public ht_type Title : ht_type Usage : $obj->ht_type() Function: every useful SNP has a numeric code dependending of its value and position. For a better description see description of the module. Returns : reference to a array Args : none Status : public ht_set Title : ht_set Usage : $obj->ht_set() Function: returns the minimal haplotype in numerical format. This haplotype contains the maximal information about the haplotype variations but with no redundancies. It's the minimal set that describes the haplotype. Returns : reference to an array of arrays Args : none Status : public snp_type_code Title : snp_type_code Usage : $obj->snp_type_code() Function: returns the numeric code of the SNPs that need to be tagged that correspond to the SNP's considered in ht_set. Returns : reference to an array Args : none Status : public snp_and_code Title : snp_and_code Usage : $obj->snp_and_code() Function: Returns the full list of SNP's and the code associate to them. If the SNP belongs to the group useful_snp it keep this code. If the SNP is silent the code is 0. And if the SNP is degenerated the code is -1. Returns : reference to an array of array Args : none Status : public deg_pattern Title : deg_pattern Usage : $obj->deg_pattern() Function: Returns the a list with the degenerated haplotype. Sometimes due to degeneration some haplotypes looks the same and if we don't remove them it won't find any tag. Returns : reference to a hash of array Args : none Status : public split_hap Title : split_hap Usage : $obj->split_hap() Function: simple representation of the haplotype base by base Same information that input haplotype but base based. Returns : reference to an array of array Args : none Status : public _split_haplo Title : _split_haplo Usage : _split_haplo($self) Function: Take a haplotype and split it into bases Returns : self Args : none Status : internal _to_upper_case Title : _to_upper_case Usage : _to_upper_case() Function: make SNP or in-dels Upper case Returns : self Args : an AoA ref Status : private _remove_deg Title : _remove_deg Usage : _remove_deg() Function: when have a indetermination or strange value this SNP is removed Returns : haplotype family set and degeneration list Args : ref to an AoA and a ref to an array Status : internal _rem_silent_snp Title : _rem_silent_snp Usage : _rem_silent_snp() Function: there is the remote possibilty that one SNP won't be a real SNP on this situation we have to remove this SNP, otherwise the program won't find any tag Returns : nonthing Args : ref to an AoA and a ref to an array Status : internal _find_silent_snps Title : _find_silent_snps Usage : Function: list of snps that are not SNPs. All values for that SNPs on the set is the same one. Look stupid but can happend and if this happend you will not find any tag Returns : nothing Args : Status : _find_indet Title : _find_indet Usage : Function: find column (SNP) with invalid or degenerated values and store this values into the second parameter supplied. Returns : nothing Args : ref to AoA and ref to an array Status : internal _remove_col Title : _remove_col Usage : Function: remove columns contained on the second array from the first arr Returns : nothing Args : array of array reference and array reference Status : internal _remove_snp_id Title : _remove_snp_id Usage : Function: remove columns contained on the second array from the first arr Returns : nothing Args : array of array reference and array reference Status : internal _find_deg_pattern Title : _find_deg_pattern Usage : Function: create a list with the degenerated patterns Returns : @array Args : a ref to AoA Status : public _keep_these_patterns Title : _keep_these_patterns Usage : Function: this is a basic approach, take a LoL and a list, keep just the columns included on the list Returns : nothing Args : an AoA and an array Status : public compare_arrays Title : compare_arrays Usage : Function: take two arrays and compare their values Returns : 1 if the two values are the same 0 if the values are different Args : an AoA and an array Status : public _convert_to_numbers Title : _convert_to_numbers Usage : _convert_to_numbers() Function: tranform the haplotype into numbers. before to do that we have to consider the variation on the set. Returns : nonthing Args : ref to an AoA and a ref to an array Status : internal _snp_type_code Title : _snp_type_code Usage : Function: we have to create the snp type code for each version. The way the snp type is created is the following: we take the number value for every SNP and do the following calculation let be a SNP set as follow: 0 0 1 1 1 2 and multiplicity 3 on this case the situation is: sum (value * multiplicity ^ position) for each SNP 0 * 3 ^ 0 + 1 * 3 ^ 1 + 1 * 3 ^ 2 = 12 0 * 3 ^ 0 + 1 * 3 ^ 1 + 2 * 3 ^ 2 = 21 Returns : nothing Args : $self Status : private _alleles_number Title : _alleles_number Usage : Function: calculate the max number of alleles for a haplotype and if the number. For each SNP the number is stored and the max number of alleles for a SNP on the set is returned Returns : max number of alleles (a scalar storing a number) Args : ref to AoA Status : public _htSNP Title : _htSNP Usage : _htSNP() Function: calculate the minimal set that contains all information of the haplotype. Returns : nonthing Args : ref to an AoA and a ref to an array Status : internal _snp_and_code_summary Title : _snp_and_code_summary Usage : _snp_and_code_summary() Function: compile on a list all SNP and the code for each. This information can be also obtained combining snp_type and snp_type_code but on these results the information about the rest of SNP's are not compiled as table. 0 will be silent SNPs -1 are degenerated SNPs and the rest of positive values are the code for useful SNP Returns : nonthing Args : ref to an AoA and a ref to an array Status : internal perl v5.14.2 2012-03-02 Bio::PopGen::HtSNP(3pm)
All times are GMT -4. The time now is 04:13 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy