Sort unique by several columns, then merge values in non-matching columns

Question

Consider a bed file containing genetic variants:

CHR START STOP RSID REF/ALT PHENOTYPE PVALUE
1 987654321 987654322 rs123456 A/T Height 6E-9
1 987654321 987654322 rs123456 A/T Stroke 8E-15

I want to sort unique by the first 5 columns, and then merge the contents in the columns that have unique values:

Example output:

CHR START STOP RSID REF/ALT PHENOTYPE PVALUE
1 987654321 987654322 rs123456 A/T Height,Stroke 6E-9,8E-15

Is this possible in Python or Unix? Or do I need to write a script?

If it is possible in Python or Unix, what function allows me to do that?

This question was addressed here but never solved.

score 3 · Answer 1 · answered Oct 28 '19 at 03:42

Python

With Python, it can be done using pandas.dataframe.groupby and agg with custom lambda function lambda x: ','.join(x)

import pandas as pd
from io import StringIO

text='''CHR START STOP RSID REF/ALT PHENOTYPE PVALUE
1 987654321 987654322 rs123456 A/T Height 6E-9
1 987654321 987654322 rs123456 A/T Stroke 8E-15'''

df = pd.read_csv(StringIO(text), sep=' ', dtype={'PVALUE': str})
print(df)
   CHR      START       STOP      RSID REF/ALT PHENOTYPE PVALUE
0    1  987654321  987654322  rs123456     A/T    Height   6E-9
1    1  987654321  987654322  rs123456     A/T    Stroke  8E-15

df_res = (df.groupby(['CHR', 'START', 'STOP', 'RSID', 'REF/ALT'])
          .agg({'PHENOTYPE': lambda x: ','.join(x),
                'PVALUE': lambda x: ','.join(x)})
          .reset_index())
print(df_res)
   CHR      START       STOP      RSID REF/ALT      PHENOTYPE      PVALUE
0    1  987654321  987654322  rs123456     A/T  Height,Stroke  6E-9,8E-15

Use sort_values to sort df_res with the orders you want.

score 2 · Answer 2 · answered Oct 28 '19 at 03:53

import pandas as pd
data = pd.read_csv('file_name.txt',dtype={'PVALUE':'object'}, sep = ' ' )
PVALUE = data.groupby(['CHR', 'START', 'STOP', 'RSID', 'REF/ALT'])['PVALUE'].apply(','.join).reset_index()['PVALUE']
data = data.groupby(['CHR', 'START', 'STOP', 'RSID', 'REF/ALT'])['PHENOTYPE'].apply(','.join).reset_index()
data['PVALUE'] = PVALUE
print(data)

   CHR      START       STOP      RSID REF/ALT      PHENOTYPE      PVALUE
0    1  987654321  987654322  rs123456     A/T  Height,Stroke  6E-9,8E-15

score 2 · Answer 3 · answered Oct 28 '19 at 04:33

Here is the vanilla python way of doing this:

from collections import defaultdict

# Open both files for reading and writing
with open("input.txt") as fin, open("output.txt", mode="w") as fout:
    grouped_columns = defaultdict(list)

    # Extract headers
    headers = next(fin)

    # Collect grouped columns in defaultdict, using first 5 columns as the key
    for line in fin:
        line = line.strip().split()
        grouped_columns[tuple(line[:5])].append(line[5:])

    # Write out result from dictionary
    fout.write(headers)
    for key, value in grouped_columns.items():
        fout.write(
            "%s %s"
            % (
                " ".join(key),
                " ".join("%s,%s" % (ptype, pval) for ptype, pval in zip(*value)),
            ) 
        )

output.txt

CHR START STOP RSID REF/ALT PHENOTYPE PVALUE
1 987654321 987654322 rs123456 A/T Height,Stroke 6E-9,8E-15

Sort unique by several columns, then merge values in non-matching columns

3 Answers3

Python