Unpivot a data-frame that has information of two teams in one row?

user_847773 0
Viewed 53

I have some data that holds information about two opposing teams

              home_x           away_x
0                  7               28
1                 11               10
2                 11               20
3                 12               15
4                 12               16

I know about .melt(), which returns something like this:

            variable  value
0             home_x      7
1             home_x     11
2             home_x     11
3             home_x     12
4             home_x     12

So each value is a row here.

There are several attributes for each team.

I want each row to have all the attributes for the respective team( home or away)

The ultimate goals is to have all the attributes of both teams in one row. This would double the number of rows.

              home_x           away_x
0                  7               28

would be transformed into:

             team1_x          team2_x
0                  7               28
0                 28                7
pandas pivot-table dataframe
2 Answers

Here is an approach:

You might need to group on the last split of the column names and then group on axis=1, then iterate through the groups and reverse the column order and name them same with the suffix:

def myinfo(data):
    c = data.columns.str.split("_").str[-1]
    f = lambda x: pd.DataFrame.set_axis(x, ["team1","team2"],axis=1)
    l = [pd.concat([*map(f , (v,v.iloc[:,::-1]))]).add_suffix(f"_{k}")
         for k,v in data.groupby(c,axis=1)]
    return pd.concat(l,axis=1).sort_index()

print(myinfo(df))

   team1_x  team2_x
0        7       28
0       28        7
1       11       10
1       10       11
2       11       20
2       20       11
3       12       15
3       15       12
4       12       16
4       16       12
user_98406370

sample df:

home_x away_x home_y away_y
0 7 28 7 20
1 28 7 28 13
2 28 7 28 4
3 7 28 7 58
4 11 10 11 10

try:

res = pd.DataFrame()
for c in df.columns.str.split("_").str[1].unique():
    p1 = df.filter(regex=f"{c}$")
    c1,c2 =p1.columns
    
    df_map = {c1:c2, c2:c1}
    swap = p1.rename(columns={**df_map})
    res = pd.concat([res,p1.append(swap).sort_index(ignore_index=True)], axis=1)

then rename the columns.

import re
repl = {'home': 'team1', 'away': 'team2'}
res.columns = [re.sub('|'.join(repl.keys()), lambda x: repl[x.group()], i) for i in res.columns]
team1_x team2_x team1_y team2_y
0 7 28 7 20
1 28 7 20 7
2 28 7 28 13
3 7 28 13 28
4 28 7 28 4
5 7 28 4 28
6 7 28 7 58
7 28 7 58 7
8 11 10 11 10
9 10 11 10 11
user_66603730
Related