You can use a combination of melt, groupby, and agg(list) to convert each row of FamilyMember names to a list (and use pipe to remove empty ones), then assign the result back to the dataframe, and explode that column:
exploded = df.assign(names=df.filter(like='FamilyMember_').T.melt().pipe(lambda x: x[x['value'] != '']).groupby('variable')['value'].agg(list)).explode('names').drop(df.filter(like='FamilyMember_'), axis=1).reset_index(drop=True)
Output:
>>> exploded
Invited_guest Age Origin names
0 Max 19 US Paul
1 Max 19 US Rene
2 Luca 21 UK Anna
3 Luca 21 UK Ruben
4 Luca 21 UK Olivia
5 John 32 GER Peter
6 John 32 GER Calvin
7 Brian 45 ITA Lewis
8 Brian 45 ITA George
9 Ian 34 FRA Jeremy
10 Ian 34 FRA Silke
11 Ian 34 FRA Selina
Explanation
First, we select the columns that start with FamilyMember_:
>>> family_members = df.filter(like='FamilyMember_')
>>> family_members
FamilyMember_1 FamilyMember_2 FamilyMember_3
0 Paul Rene
1 Anna Ruben Olivia
2 Peter Calvin
3 Lewis George
4 Jeremy Silke Selina
Next, we rotate that 90 degrees (also known as transposing) for later use with melt:
>>> family_members.T
0 1 2 3 4
FamilyMember_1 Paul Anna Peter Lewis Jeremy
FamilyMember_2 Rene Ruben Calvin George Silke
FamilyMember_3 Olivia Selina
Then, we'll melt it:
>>> family_members.T.melt()
variable value
0 0 Paul
1 0 Rene
2 0
3 1 Anna
4 1 Ruben
5 1 Olivia
6 2 Peter
7 2 Calvin
8 2
9 3 Lewis
10 3 George
11 3
12 4 Jeremy
13 4 Silke
14 4 Selina
Now we need to remove the empty items. We could do that like this:
x = family_members.T.melt()
x = x[x['values'] != '']
...but that's multiple lines, which won't do in a one-liner. So we can use pipe with a lambda function to achieve exactly this in a one-liner fashion:
>>> family_members.T.melt().pipe(lambda x: x[x['value'] != ''])
variable value
0 0 Paul
1 0 Rene
3 1 Anna
4 1 Ruben
5 1 Olivia
6 2 Peter
7 2 Calvin
9 3 Lewis
10 3 George
12 4 Jeremy
13 4 Silke
14 4 Selina
Then we can group by the variable column as it groups together perfectly those names that need to go together:
>>> g = family_members.T.melt().pipe(lambda x: x[x['value'] != '']).groupby('variable')
>>> g
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x12b131e50>
# That's not very useful, so we can convert it to a list to have a peek at what's inside:
>>> list(g)
[(0,
variable value
0 0 Paul
1 0 Rene),
(1,
variable value
3 1 Anna
4 1 Ruben
5 1 Olivia),
(2,
variable value
6 2 Peter
7 2 Calvin),
(3,
variable value
9 3 Lewis
10 3 George),
(4,
variable value
12 4 Jeremy
13 4 Silke
14 4 Selina)]
We need to convert each of those groups to a list of the names contained inside them. That's where agg(list) comes in:
>>> g['value'].agg(list)
variable
0 [Paul, Rene]
1 [Anna, Ruben, Olivia]
2 [Peter, Calvin]
3 [Lewis, George]
4 [Jeremy, Silke, Selina]
Name: value, dtype: object
Perfect. Now we need to put that column back on the dataframe. We could just assign it like normal:
g['names'] = g['value'].agg(list)
...but again, that would make a one-liner impossible. Fortunately, there's the assign function, which is built for this use-case:
>>> df.assign(names=g['value'].agg(list))
Invited_guest Age Origin FamilyMember_1 FamilyMember_2 FamilyMember_3 names
0 Max 19 US Paul Rene [Paul, Rene]
1 Luca 21 UK Anna Ruben Olivia [Anna, Ruben, Olivia]
2 John 32 GER Peter Calvin [Peter, Calvin]
3 Biran 45 ITA Lewis George [Lewis, George]
4 Ian 34 FRA Jeremy Silke Selina [Jeremy, Silke, Selina]
(Note that assign is NOT in-place. It modifies a new copy of the dataframe, not the original).
Finally, we use the magic explode (only available in pandas 0.25 and newer):
>>> df.assign(names=g['value'].agg(list)).explode('names')
Invited_guest Age Origin FamilyMember_1 FamilyMember_2 FamilyMember_3 names
0 Max 19 US Paul Rene Paul
0 Max 19 US Paul Rene Rene
1 Luca 21 UK Anna Ruben Olivia Anna
1 Luca 21 UK Anna Ruben Olivia Ruben
1 Luca 21 UK Anna Ruben Olivia Olivia
2 John 32 GER Peter Calvin Peter
2 John 32 GER Peter Calvin Calvin
3 Biran 45 ITA Lewis George Lewis
3 Biran 45 ITA Lewis George George
4 Ian 34 FRA Jeremy Silke Selina Jeremy
4 Ian 34 FRA Jeremy Silke Selina Silke
4 Ian 34 FRA Jeremy Silke Selina Selina
And of course, remove the only FamilyMember_* columns:
>>> family_member_columns = df.filter(like='FamilyMember_').columns
>>> family_member_columns
Index(['FamilyMember_1', 'FamilyMember_2', 'FamilyMember_3'], dtype='object')
>>> df.assign(names=g['value'].agg(list)).explode('names').drop(family_member_columns, axis=1)
