Partial Multiindexing with a Pandas DataFrame

Question

I have a dataframe as follows:

df = pd.DataFrame(columns=['New Category', 'Sample1', 'Sample2'],
         data=[
               ['Pathogenic/Likely Pathogenic', '0/0:240', '1/0:100'],
               ['Likely Benign', '1/1:0,237', '1/0:700'],
               ['Likely Benign', '0/0:239', '0/0:234'],
               ['Likely Benign', '1/1:1,238', '0/1:890'],
               ['Likely Benign', '0/1:156,79', '1/1:767'],
               ['VUS', '1/1:0,241', '0/1:21']
               ])

Which looks like this:

               New Category       Sample1   Sample2
0  Pathogenic/Likely Pathogenic   0/0:240   1/0:100
1                 Likely Benign   1/1:237   1/0:700
2                 Likely Benign   0/0:239   0/0:234
3                 Likely Benign   1/1:238   0/1:890
4                 Likely Benign   0/1:156   1/1:767
5                           VUS   1/1:241   0/1:21

I want to do some multiindexing so that the Sample1 and Sample2 values are split by the colon and placed underneath as a sub-column name. However, I do not want these sub-column names to apply to the New Category column. Basically I want it to look like this:

               New Category       Sample1   Sample2
                                  GT   GQ    GT   GQ
0  Pathogenic/Likely Pathogenic   0/0  240   1/0  100
1                 Likely Benign   1/1  237   1/0  700
2                 Likely Benign   0/0  239   0/0  234
3                 Likely Benign   1/1  238   0/1  890
4                 Likely Benign   0/1  156   1/1  767
5                           VUS   1/1  241   0/1  21

I really am stumped on how to do this. The multiindexing page of the pandas docs contains no example of multiindexing on selected columns only. This is making we wonder whether this is even possible.

Answer 1

This is not really a matter of "indexing", but rather of manipulating data, in particular splitting the columns. The following should do:

df_new_category = pd.DataFrame(
    df[['New Category']].values,
    columns=pd.MultiIndex.from_tuples([('New Category', '')])
)
sample_data_dfs = \
    [pd.DataFrame(list(df[col].str.split(':')),
                  columns=pd.MultiIndex.from_product([[col], ['GT', 'GQ']]))
     for col in ['Sample1', 'Sample2']]

pd.concat([df_new_category] + sample_data_dfs, axis=1)

Notice that you could do the splitting all at once (i.e. without a loop on each column), like follows:

df[['Sample1', 'Sample2']].applymap(lambda s : s.split(':'))

... but

• this is way slower, because you are implicitly looping on every cell
• you would still need another loop to extract the single newly created columns