Split values iterating over an unspecific number of columns in a pandas data frame

Question

I have an application that generates data frames with different numbers of columns and their cells contains two values separated by "|".

gene_1             gene_2             ...
ashb|ESNT00011     wsefsf|ENST0008
adecasd|ENST0001   uibib|ENST0008

How can I iterate over columns and split values into two columns called gene_1_name and gene_1_ID

gene_1_name    gene_1_ID           gene_2_name     gene_2_ID         ...
ashb           ESNT00011           wsefsf          ENST0008
adecasd        ENST0001            uibib           ENST0008

Code Different · Accepted Answer · 2022-02-15 21:49:56Z

3

Use stack and unstack:

result = (
    df.stack().str.split('|', expand=True)     # split the strings
        .rename(columns={0: 'name', 1: 'id'})  # rename the columns
        .unstack()                             # unstack
)

# Merge the two levels
result.columns = [f'{gene}_{col}' for col, gene in result.columns]

answered Feb 15, 2022 at 21:49

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Corralien Over a year ago

You can also use result.columns.to_flat_index().str.join('_')

Collectives™ on Stack Overflow

Split values iterating over an unspecific number of columns in a pandas data frame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related