Split row by two columns and keep other columns the same

You could use pivot_longer from pyjanitor to reshape the data:

(df.pivot_longer(index = ['col1', 'col2', 'Unique_Col'], 
                 names_to = ['Name_new', 'Gender_new', 'Age_new'],  
                 names_pattern = ['Name', 'Gender', 'Age'],
                 sort_by_appearance = True)
)
 
  col1  col2 Unique_Col Name_new Gender_new  Age_new
0   A1    50     Key_01      Amy          F     20.0
1   A1    50     Key_01      NaN        NaN      NaN
2   A2    20     Key_02     Judy          F     35.0
3   A2    20     Key_02     Andy          M     37.0
4   A3    10     Key_03    James          M     45.0
5   A3    10     Key_03    Alice          F     45.0
6   A4   150     Key_04      Sam          M     23.0
7   A4   150     Key_04      NaN        NaN      NaN
8   A5   200     Key_05    Annie          F     40.0
9   A5   200     Key_05     John        NaN      NaN

In the code above, you pass a list of new column names to names_to. For each new column name, you pass a pattern(regular expression) to names_pattern. So, for Names_new, the function will search for any column that contains Name and pull all the data into that column, same goes for Gender_new and Age_new.

You could also stick to pandas only and use wide_to_long; first reorder the columns so that Gender, Name, and Age are at the front:

new_df = df.rename(columns = lambda col: "_".join(col.split("_")[::-1]) 
                              if "#" in col else col)
new_df
  col1  col2 Name_1 Gender_1  Age_1 Name_I#2 Gender_I#2  Age_I#2 Unique_Col
0   A1    50    Amy        F     20      NaN        NaN      NaN     Key_01
1   A2    20   Judy        F     35     Andy          M     37.0     Key_02
2   A3    10  James        M     45    Alice          F     45.0     Key_03
3   A4   150    Sam        M     23      NaN        NaN      NaN     Key_04
4   A5   200  Annie        F     40     John        NaN      NaN     Key_05

Let's reshape:

(pd.wide_to_long(new_df, 
                 stubnames = ['Name', 'Gender', 'Age'], 
                 i = ['col1', 'col2', 'Unique_Col'], 
                 j = 'suffix', 
                 sep = "_", 
                 suffix = ".+")
   .droplevel("suffix")
   .reset_index()
)



  col1  col2 Unique_Col   Name Gender   Age
0   A1    50     Key_01    Amy      F  20.0
1   A1    50     Key_01    NaN    NaN   NaN
2   A2    20     Key_02   Judy      F  35.0
3   A2    20     Key_02   Andy      M  37.0
4   A3    10     Key_03  James      M  45.0
5   A3    10     Key_03  Alice      F  45.0
6   A4   150     Key_04    Sam      M  23.0
7   A4   150     Key_04    NaN    NaN   NaN
8   A5   200     Key_05  Annie      F  40.0
9   A5   200     Key_05   John    NaN   NaN

Create two dateframes with columns cols1 and cols2(see the values in code).

In the second dataframe, drop the values where all the Name_x, Gender_x, Age_x are nan, which means second name doesn't exist. Rename Name_x, Gender_x, Age_x to Name_new, Gender_new, Age_new in both the dataframes.

Then use pd.concat to concat the dateframes vertically.

Use:

cols1 = ['col1', 'col2', 'Name_1', 'Gender_1', 'Age_1', 'Unique_Col']
cols2 = ['col1', 'col2', 'Name', 'Gender_2', 'Age_2', 'Unique_Col']

def rename_col(x):
   return x.split("_")[0] + "_new" if '_' in x else x

df1 = df[cols1].rename(rename_col, axis = 'columns')
df2 = (df[cols2]
          .dropna(subset = ['Name_2', 'Gender_2', 'Age_2'], how = 'all')
          .rename(rename_col, axis = 'columns'))

Output:

>>> out
  col1  col2 Name_new Gender_new  Age_new Unique_new
0   A1    50      Amy          F     20.0     Key_01
1   A2    20     Judy          F     35.0     Key_02
1   A2    20     Andy          M     37.0     Key_02
2   A3    10    James          M     45.0     Key_03
2   A3    10    Alice          F     45.0     Key_03
3   A4   150      Sam          M     23.0     Key_04
4   A5   200    Annie          F     40.0     Key_05
4   A5   200     John        NaN      NaN     Key_05

If your gender and name do not have same patterns then use a dictionary mapping to rename:

cols1 = ['col1', 'col2', 'Name_1', 'Gender_1', 'Age_1', 'Unique_Col']
cols2 = ['col1', 'col2', 'I#2_Name', 'I#2_Gender', 'I#2_Age', 'Unique_Col']

new_cols = ['col1', 'col2', 'Name_New', 'Gender_New', 'Age_New', 'Unique_Col']

df1 = df[cols1].rename(dict(zip(cols1, new_cols)), axis = 'columns')
df2 = (df[cols2]
          .dropna(subset = cols2[2:5], how = 'all')
          .rename(dict(zip(cols2, new_cols)), axis = 'columns'))

out = pd.concat([df1, df2]).sort_values(by = ['col1'])

try the pandas melt function. Something like

pd.melt(df, id_vars = ['Name_1','Name_2'])