Answers.Body: <p>One way to do this is to use a dictionary. You can read the first file line by line, and for each line, use "chr:position" as a key, and the corresponding line as the value. If you want to handle duplicates, you can make the value a list and store multiple lines with duplicated "chr:position" as elements in that list.</p>
  
  <p>After constructing this dictionary, read the second file line by line. For each line, set "chr:position" as the so-called name of that line. Then using this "chr:position", you can easily find the corresponding line in the dictionary. Append the line from the second file to that line from the first file.</p>
  
  <p>After finishing reading the second file, you are ready to write the new file to output. To do so, iterate over the keys in the dictionary, and write the value to each key to a new file. Remember that each value in that dictionary is now a new line.</p>
  
  <p>I choose to use a dictionary because they are basically a hash table, meaning that using the key you can instantly read that value in the dictionary, instead of iterating every element in a list to find that element. This is very handy when you have a large dictionary.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-25T00:04:42.733Z
Answers.Text: Well, after writing that long paragraph I realized that you are basically trying to merge two VCF files. Why not checking out [vcf-merge](https://vcftools.github.io/perl_module.html#vcf-merge) from VCFtools.
Answers.UserDisplayName: Phoenix Mu
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-24T06:17:08.110Z
Answers.Text: This can be done entirely in pandas, there's bits missing. Its quite a complicated database problem to read
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-24T13:38:05.460Z
Answers.Text: @Michael, I agree with you totally. Sometimes in R I use `left_join` and many other joining functions. I think Pandas has the same functionality. But sometimes I need to join millions of lines together (VCF files can be 700000000x1000), reading the data all at once seems unrealistic. I do not know much about database but may be this can be handled by some special tools?
Answers.UserDisplayName: Phoenix Mu
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-25T00:00:56.073Z
Answers.LastActivityDate: 2020-03-25T00:00:56.073Z
Answers.UserDisplayName: Phoenix Mu
Answers.Body: <p>if you don't need python, how about just using <code>join</code> ? create a key CHROM/POS with awk , sort each file, and join. The final output is just a matter of using awk.</p>
  <pre><code>LC_ALL=C join -t <span class="math-container">$'\t'  -1 1 -2 1 -e 'NA' -a1 -a 2 --header -o auto  \
     &lt;(gunzip -c  file1.vcf.gz | awk -F '\t' '/^##/ {next;} {printf("%s:%s\t%s\n",$1,$2,$0);}' | LC_ALL=C sort -T . -k1,1 -t $</span>'\t') \
     &lt;(gunzip -c  file2.vcf.gz | awk -F '\t' '/^##/ {next;} {printf(&quot;%s:%s\t%s\n&quot;,<span class="math-container">$1,$</span>2,<span class="math-container">$0);}' | LC_ALL=C sort -T . -k1,1 -t $</span>'\t')
  </code></pre>
  <p>othewise you should have a look at bcftools isec.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-08-01T15:36:45.037Z
Answers.LastActivityDate: 2024-08-01T15:36:45.037Z
Answers.UserDisplayName: Pierre
Body: <p>I have  very large sizes tab-delimited .vcf files and want to match these two / or 3 files based on their position and print to a new .csv file  </p>
  
  <p><strong>File structures:</strong>  </p>
  
  <p>File_1: tab-delimited file (.vcf) and its as column names as follows
  <code>
  (line number 3439) #CHROM     POS     ID       REF      ALT      QUAL    FILTER 
   INFO</code></p>
  
  <p><strong>File 2: same as file_1 column names</strong>  </p>
  
  <p><code>(line number 3407) #CHROM     POS     ID      REF     ALT     QUAL    FILTER 
   INFO  FORMAT  SAMPLE_1  SAMPLE_2 .....</code>  </p>
  
  <p>In file_2, column 7 (INFO) contain many substrings like
  <code>AC=46,2;AF=0.958,0.042;AN=48;DP=269;ExcessHet=3.0103;FS=0.000;InbreedingCoeff=0.5411;MLEAC=92,4;MLEAF=1.00,0.083;QD=25.36;SOR=2.488  from these strings , have to print  only information of AC=, AF=, AN=, DP=</code></p>
  
  <p><strong>Desired output files to generate:</strong></p>
  
  <ol>
  <li>position matched in two of files: common_position_matched.csv  </li>
  </ol>
  
  <p>if I have more than 3 files also, the output should be one file and important thing, if a position column (1-POS) only matched only in 2 files and 1st file line should be NA NA NA   </p>
  
  <pre><code>file_1  CHROM  POS    REF  ALT     file_2 #CHROM  POS      REF  ALT  INFO
  
  1       22    10511521  C     T         1    22  10511521   C    T   AC=46,2 AF=0.958,0.042 AN=48 DP=269  
  2       22   10510544   G     A         2    22  10510544   G    A   AC=49,2 AF=0.958,0.042 AN=89 DP=536  
  3       22   10515068  AGAT,T AGAT,AT   3    22  10515068   AAA  AAAGG,A,GAA AC=100  AF=0.958,0.042 AN=62 DP=123  
  4       22   10515118  A G,   TAA       4    22  10515118   AG,  TAA AC=32   AF=0.958,0.042 AN=45 DP=500  
  5       22   10515118  AAAG   A         5    22  10515118   AATG A   AC=50   AF=0.958,0.042 AN=49 DP=129
  </code></pre>
  
  <p><strong>note:</strong> while doing matching, not removing the duplicates, because in the same position there may be an addition or sometimes it may be deletion.      </p>
  
  <ol start="2">
  <li>unique position of each file, in tab-delimited  </li>
  </ol>
  
  <p>output: File1_unique.csv and File2_unique.csv etc.  </p>
  
  <p>so far was able to read the file and match them according to position and print the output, but was not able to write efficient code   </p>
  
  <pre><code>import pandas as pd
  df1 = pd.read_csv("File1_3.vcf",sep='\t',usecols = ['POS']) ## Reading file1
  df2 = pd.read_csv("file2_3.vcf", sep="\t", usecols = ['POS']) ## Reading file2
  df3 = pd.concat([df1,df2], sort=True) ## Combining both the dataframes
  df4 = df3.drop_duplicates(keep=False) ## Dropping the duplicates (intersect)
  df4.to_csv("c3-UniquePosition_of_bothData.csv", sep="\t", index=False, header=True) ## Writing the unique to both
  df1_Uni_file1_c3Posi = pd.merge(df4, df1, on='POS', how='inner') ## Identifying the unique position of File1 
  df2_Uni_File2_c3Posi = pd.merge(df4, df2, on='POS', how='inner') ## Identifying the unique position of File2
  df_File1_File2_common_c3Posi = pd.merge(df1, df2, on='POS', how='inner') # Identifying the common chr-position of File1 and File2```  
  
  Program 2: (giving original file without editting)
  import pandas as pd
  df1= pd.read_csv("File1_22.vcf.gz", sep="\t", skiprows=3438, usecols = [0,1,2,3,4])
  df2 = pd.read_csv("File2_22.vcf.gz", sep="\t", skiprows=3406, usecols = [0,1,3,4,7])
  #writing the output files
  #df1.to_csv("File1_c22.csv")
  #df2.to_csv("File2_c22.csv")
  #mergeing
  df3 = pd.merge(df1, df2, on='POS', how='inner', sort=True)
  df3.to_csv("common_position.csv", sep=",", index=False, header=True)
  #df3 = pd.concat([df1,df2], axis=1).to_csv('check1.csv') # this command join multiple output to single output
  </code></pre>
  
  <p>Could any one give efficient python pandas script, to do this</p>
  
  <p>Thanks All</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-12T18:03:50.527Z
Comments.Text: Beyond reformatting, it would be great if you can clarify the question. What are the tab-delimited files? They appear to be lists of mutations, but can you confirm that? And what exactly do you mean by position matching? You provide many examples but it would be great if you can clarify in text exactly what those examples are meant to show.
Comments.UserDisplayName: alizabr
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-14T05:57:34.843Z
Comments.Text: @ATpo and alizbar,  I had reformated the question as much I can, could you people give me some solution
Comments.UserDisplayName: Nitha
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-24T06:15:31.273Z
Comments.Text: The code looks fine, a few things appear missing, assigning headers/indexing. It would be useful to see the output you are getting, because it a complex flatfile database and it will take me a while to look between all the columns and code
Comments.UserDisplayName: M__
Tags: <python><perl>
Title: Finding common and unique data set by comparing two files based on their column and to split the columns multiple strings to print in output
ThreadText: # Finding common and unique data set by comparing two files based on their column and to split the columns multiple strings to print in output
  
  (Asked by: username_0 on 2020-02-12)
  
  I have very large sizes tab-delimited .vcf files and want to match these two / or 3 files based on their position and print to a new .csv file
  
  **File structures:**
  
  File\_1: tab-delimited file (.vcf) and its as column names as follows
  `(line number 3439) #CHROM POS ID REF ALT QUAL FILTER
  INFO`
  
  **File 2: same as file\_1 column names**
  
  `(line number 3407) #CHROM POS ID REF ALT QUAL FILTER
  INFO FORMAT SAMPLE_1 SAMPLE_2 .....`
  
  In file\_2, column 7 (INFO) contain many substrings like
  `AC=46,2;AF=0.958,0.042;AN=48;DP=269;ExcessHet=3.0103;FS=0.000;InbreedingCoeff=0.5411;MLEAC=92,4;MLEAF=1.00,0.083;QD=25.36;SOR=2.488 from these strings , have to print only information of AC=, AF=, AN=, DP=`
  
  **Desired output files to generate:**
  
  1. position matched in two of files: common\_position\_matched.csv
  
  if I have more than 3 files also, the output should be one file and important thing, if a position column (1-POS) only matched only in 2 files and 1st file line should be NA NA NA
  
  ```
  file_1  CHROM  POS    REF  ALT     file_2 #CHROM  POS      REF  ALT  INFO
  
  1       22    10511521  C     T         1    22  10511521   C    T   AC=46,2 AF=0.958,0.042 AN=48 DP=269  
  2       22   10510544   G     A         2    22  10510544   G    A   AC=49,2 AF=0.958,0.042 AN=89 DP=536  
  3       22   10515068  AGAT,T AGAT,AT   3    22  10515068   AAA  AAAGG,A,GAA AC=100  AF=0.958,0.042 AN=62 DP=123  
  4       22   10515118  A G,   TAA       4    22  10515118   AG,  TAA AC=32   AF=0.958,0.042 AN=45 DP=500  
  5       22   10515118  AAAG   A         5    22  10515118   AATG A   AC=50   AF=0.958,0.042 AN=49 DP=129
  
  ```
  
  **note:** while doing matching, not removing the duplicates, because in the same position there may be an addition or sometimes it may be deletion.
  
  2. unique position of each file, in tab-delimited
  
  output: File1\_unique.csv and File2\_unique.csv etc.
  
  so far was able to read the file and match them according to position and print the output, but was not able to write efficient code
  
  ```
  import pandas as pd
  df1 = pd.read_csv("File1_3.vcf",sep='\t',usecols = ['POS']) ## Reading file1
  df2 = pd.read_csv("file2_3.vcf", sep="\t", usecols = ['POS']) ## Reading file2
  df3 = pd.concat([df1,df2], sort=True) ## Combining both the dataframes
  df4 = df3.drop_duplicates(keep=False) ## Dropping the duplicates (intersect)
  df4.to_csv("c3-UniquePosition_of_bothData.csv", sep="\t", index=False, header=True) ## Writing the unique to both
  df1_Uni_file1_c3Posi = pd.merge(df4, df1, on='POS', how='inner') ## Identifying the unique position of File1 
  df2_Uni_File2_c3Posi = pd.merge(df4, df2, on='POS', how='inner') ## Identifying the unique position of File2
  df_File1_File2_common_c3Posi = pd.merge(df1, df2, on='POS', how='inner') # Identifying the common chr-position of File1 and File2```  
  
  Program 2: (giving original file without editting)
  import pandas as pd
  df1= pd.read_csv("File1_22.vcf.gz", sep="\t", skiprows=3438, usecols = [0,1,2,3,4])
  df2 = pd.read_csv("File2_22.vcf.gz", sep="\t", skiprows=3406, usecols = [0,1,3,4,7])
  #writing the output files
  #df1.to_csv("File1_c22.csv")
  #df2.to_csv("File2_c22.csv")
  #mergeing
  df3 = pd.merge(df1, df2, on='POS', how='inner', sort=True)
  df3.to_csv("common_position.csv", sep=",", index=False, header=True)
  #df3 = pd.concat([df1,df2], axis=1).to_csv('check1.csv') # this command join multiple output to single output
  
  ```
  
  Could any one give efficient python pandas script, to do this
  
  Thanks All
  
  **Tags:** python, perl
  
  > **Comments:**
  > **username_1**: Beyond reformatting, it would be great if you can clarify the question. What are the tab-delimited files? They appear to be lists of mutations, but can you confirm that? And what exactly do you mean by position matching? You provide many examples but it would be great if you can clarify in text exactly what those examples are meant to show.  
  > **username_0**: @ATpo and alizbar,  I had reformated the question as much I can, could you people give me some solution  
  > **username_2**: The code looks fine, a few things appear missing, assigning headers/indexing. It would be useful to see the output you are getting, because it a complex flatfile database and it will take me a while to look between all the columns and code  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_3 on 2020-03-25. Score: 0
  
  One way to do this is to use a dictionary. You can read the first file line by line, and for each line, use "chr:position" as a key, and the corresponding line as the value. If you want to handle duplicates, you can make the value a list and store multiple lines with duplicated "chr:position" as elements in that list.
  
  After constructing this dictionary, read the second file line by line. For each line, set "chr:position" as the so-called name of that line. Then using this "chr:position", you can easily find the corresponding line in the dictionary. Append the line from the second file to that line from the first file.
  
  After finishing reading the second file, you are ready to write the new file to output. To do so, iterate over the keys in the dictionary, and write the value to each key to a new file. Remember that each value in that dictionary is now a new line.
  
  I choose to use a dictionary because they are basically a hash table, meaning that using the key you can instantly read that value in the dictionary, instead of iterating every element in a list to find that element. This is very handy when you have a large dictionary.
  
  > **Comments:**
  > **username_3**: Well, after writing that long paragraph I realized that you are basically trying to merge two VCF files. Why not checking out [vcf-merge](https://vcftools.github.io/perl_module.html#vcf-merge) from VCFtools.  
  > **username_2**: This can be done entirely in pandas, there's bits missing. Its quite a complicated database problem to read  
  > **username_3**: @Michael, I agree with you totally. Sometimes in R I use `left_join` and many other joining functions. I think Pandas has the same functionality. But sometimes I need to join millions of lines together (VCF files can be 700000000x1000), reading the data all at once seems unrealistic. I do not know much about database but may be this can be handled by some special tools?  
  
  ---
  
  ### Answer by username_4 on 2024-08-01. Score: 0
  
  if you don't need python, how about just using `join` ? create a key CHROM/POS with awk , sort each file, and join. The final output is just a matter of using awk.
  
  ```
  LC_ALL=C join -t $'\t'  -1 1 -2 1 -e 'NA' -a1 -a 2 --header -o auto  \
     <(gunzip -c  file1.vcf.gz | awk -F '\t' '/^##/ {next;} {printf("%s:%s\t%s\n",$1,$2,$0);}' | LC_ALL=C sort -T . -k1,1 -t $'\t') \
     <(gunzip -c  file2.vcf.gz | awk -F '\t' '/^##/ {next;} {printf("%s:%s\t%s\n",$1,$2,$0);}' | LC_ALL=C sort -T . -k1,1 -t $'\t')
  
  ```
  
  othewise you should have a look at bcftools isec.
  
  
  ---
  
  