Vectorizing or speed up for loop in Pandas for data transformation

I have a dataframe in the following format:

df = pd.DataFrame({'Parent_username': ['Bob1', 'Ron23', 'Lisa00', 'Joe_'],
                   'Parent_age': [38, None, 40, 26],
                   'Child1_name': ['Mike', 'John', 'Curt', 'Kelly'],
                   'Child1_age': [2, None, 1, 2],
                   'Child2_name': ['Pat', 'Dennis', None, None],
                   'Child2_age': [4, None, None, None]}) 

  Parent_username  Parent_age Child1_name  Child1_age Child2_name  Child2_age
0            Bob1        38.0        Mike         2.0         Pat         4.0
1           Ron23         NaN        John         NaN      Dennis         NaN
2          Lisa00        40.0        Curt         1.0        None         NaN
3            Joe_        26.0       Kelly         2.0        None         NaN

As you can see above, each row corresponds to a parent (unique ID), and each parents can have multiple children. There can be many children but I have 2 listed, and each child can have many attributes, but I only have 2 (name, age) in this example. The child attribute columns follow the same convention.

I'd like to transform it to such:

df2 = pd.DataFrame({'Child_name': ['Mike', 'Pat', 'John', 'Dennis', 'Curt', 'Kelly'],
                    'Child_number': [1, 2, 1, 2, 1, 1],
                    'Child_age': [2, 4, None, None, 1, 2],
                    'Parent_username': ['Bob1', 'Bob1', 'Ron23', 'Ron23', 'Lisa00', 'Joe_'],
                    'Parent_age': [38, 38, None, None, 40, 26]})

  Child_name  Child_number  Child_age Parent_username  Parent_age
0       Mike             1        2.0            Bob1        38.0
1        Pat             2        4.0            Bob1        38.0
2       John             1        NaN           Ron23         NaN
3     Dennis             2        NaN           Ron23         NaN
4       Curt             1        1.0          Lisa00        40.0
5      Kelly             1        2.0            Joe_        26.0

Each row corresponds to a child, and the Child_number indicates if it's the first child or second child, etc.

In order to speed things up, I pre-allocated space for df2 by making an empty dataframe of the right size, rather than doing concatenation. I first looped through df1 by counting how many children each parent has, to get the number of rows needed for df2.

Then, I built dictionaries of indexes to map each child/parent to its row/rows in df2. I figure since dictionary lookup is fast, this is better than trying to find the row in df2 each time using where(). Again, a for loop was used for this.

Those actually does not take long. But the actual copying of the data from df to df2 takes a long time using for loop:

for index in df.index:
    for col in df.columns:
        // copy df.loc[index, col] into the corresponding position in df2 using dataframe.loc

I'm really hoping there is a faster way to do this. I don't understand vectorization very well and I'm not sure if it works well for string columns.

Please advise.
Thanks

Your code is slow because you are working on each element one at a time. You can speed it up by working on a column at a time. The code below finds all child name columns, finds the indexes where they have a value (i.e. not null) and operates on all those fields at once.

I also added ways to list all the attributes ahead of time so that you don't have to manually rename them individually.

cnames =  [i for i in df.columns if i.startswith('Child') and i.endswith('name')]
cattrs = ['_name', '_age']
newnames = ['Child' + i for i in cattrs]
dflist = []


for childcol in cnames:
    cid = childcol.split('_')[0]
    cnum = int(cid[-1])
    attrs  = [cid + i for i in cattrs] # get all the attributes
    attrs.extend(['Parent_username', 'Parent_age'])
    
    cdf = df.loc[df[childcol].dropna().index, attrs]
    cdf['Child_number'] = cnum
    
    cdf = cdf.rename(columns=dict(zip(attrs, newnames)))
    dflist.append(cdf)
    
newdf = pd.concat(dflist)
newdf = newdf.reset_index(drop=True)