How to transform a large data frame

Transform the columns to MultiIndex by using replace followed by split, then stack to reshape the frame to convert it into the desired format

df = df1.set_index(['A', 'B'])
df.columns = df.columns\
               .str.replace(r'^(\D+)(\d+)(.*)', r'\1\3_\2')\
               .str.split('_', expand=True)

df = df.stack().droplevel(2).reset_index()

    A      B     C  C.A
0  PC  11001  Core  Old
1  PC  11001    SE  New

With a little column reformatting, pd.wide_to_long can be used:

df.columns = df.columns.str.replace(r'C(\d+).A', r'C.A\1', regex=True)
df = (
    pd.wide_to_long(df, stubnames=['C', 'C.A'], i=['A', 'B'], j='drop')
        .droplevel(axis=0, level='drop')
        .reset_index()
)

df:

    A      B     C  C.A
0  PC  11001  Core  Old
1  PC  11001    SE  New

Change the columns in df from:

    A      B    C1 C1.A  C2 C2.A
0  PC  11001  Core  Old  SE  New

To

df.columns = df.columns.str.replace(r'C(\d+).A', r'C.A\1', regex=True)

    A      B    C1 C.A1  C2 C.A2
0  PC  11001  Core  Old  SE  New

Then wide_to_long on the stubnames C and C.A

An alternative without affecting df:

new_df = (
    pd.wide_to_long(
        df.rename(columns=dict(
            zip(df.columns,
                df.columns.str.replace(r'C(\d+).A', r'C.A\1', regex=True)))
        ),
        stubnames=['C', 'C.A'],
        i=['A', 'B'],
        j='drop')
        .droplevel(axis=0, level='drop')
        .reset_index()
)

new_df:

    A      B     C  C.A
0  PC  11001  Core  Old
1  PC  11001    SE  New

df:

    A      B    C1 C1.A  C2 C2.A
0  PC  11001  Core  Old  SE  New

In addition to the other solutions already proferred, pivot_longer from pyjanitor is another option -> your columns have a pattern that we could take advantage of (some end with numbers, others end with A):

# pip install pyjanitor
import janitor
import pandas as pd
df.pivot_longer(index=['A', 'B'], 
                names_to = ['C', 'C.A'], 
                names_pattern = ['.+\d$', '.+A$'])
 
    A      B     C  C.A
0  PC  11001  Core  Old
1  PC  11001    SE  New

the columns in names_to become the new column names and are mapped to the old columns that match the regular expression in names_pattern.

thanks for your code typing. I will take it. :)
Here are some ideas:

• you already hardcode the one-row result, it's a good start.
• we can use that code with a custom function, then use groupy.apply to generate a new two-row result (dataframe) from one-row data. groupby.apply will take care of the combination with your new dataframes.
• after that, reindex if needed.

first, we make a custom apply function for groupby:

def make_new_df_from_row(df):
    row = df.iloc[0, :] # df is each df split by groupby
    dd = pd.DataFrame({ #configure your hardcode
        'A': row.A, 'B': row.B,
        'C':[row.C1, row.C2],
        'C.A':[row['C1.A'], row['C2.A']] #column name with '.' cannot use row.C1.A
    })
    return dd

then, use df2 = df1.groupby(lambda x:x, group_keys=False).apply(make_new_df_from_row) to get result your want.

there is a screenshot from colab:

some notes:

• groupby accept a function which use index as input and split dataframe from the function output, here just use a lambda function, return index itself, to split dataframe by each single row (one-row dataframe).
• group_keys=False means don't return the key (grouped index)
• groupby.apply accept a function which input is each groupby dataframe, you can return a pd.Series or pd.Dataframe depend on your need. Here we return the two-row dataframe from your code typing (with some variable change)