Transform multiple rows of data into one based on multiple keys in pandas

One way to solve this is to convert the table into a Pandas dataframe. Filter the main table by 'homeaway', to create 'home' and 'away' dataframes. The columns in the 'away' table are relabelled, and original column of the key is preserved. We then run a join to both to produce the desired output.

import pandas as pd
data = {'game_id': [332410041, 332410041],
        'school': ['Connecticut', 'Towson'],
        'conference':['American Athletic', 'CAA'],
        'homeAway': ['home', 'away'],
        'points': [18, 33]
}
df = pd.DataFrame(data)

home = df[df['homeAway'] == 'home']
del home['homeAway']

away = df[df['homeAway'] == 'away']
del away['homeAway']
away.columns = ['game_id', 'away_school', 'away_conference', 'away_points']

home.merge(away)

1. Create two dataframes selected by the unique values in the 'homeAway' column, 'home' and 'away', using Boolean indexing.
2. Drop the obsolete 'homeAway' column
3. Rename the appropriate columns with a 'home_', and 'away_' prefix.

• This can be done in a for-loop, with each dataframe added to a list, which can be consolidated into a simple list-comprehension.
• Use pd.merge to combine the two dataframes on the common 'game_id' column.
  • See Merge, join, concatenate and compare and Pandas Merging 101 for additional details.

import pandas as pd

# test dataframe
data = {'game_id': [332410041, 332410041, 662410041, 662410041, 772410041, 772410041],
        'school': ['Connecticut', 'Towson', 'NY', 'CA', 'FL', 'AL'],
        'conference': ['American Athletic', 'CAA', 'a', 'b', 'c', 'd'],
        'homeAway': ['home', 'away', 'home', 'away', 'home', 'away'], 'points': [18, 33, 1, 2, 3, 4]}
df = pd.DataFrame(data)

# create list of dataframes
dfl = [(df[df.homeAway.eq(loc)]
        .drop('homeAway', axis=1)
        .rename({'school': f'{loc}_school',
                 'conference': f'{loc}_conference',
                 'points': f'{loc}_points'}, axis=1))
       for loc in df.homeAway.unique()]

# combine the dataframes
df_new = pd.merge(dfl[0], dfl[1])

# display(df_new)
     game_id  home_school    home_conference  home_points away_school away_conference  away_points
0  332410041  Connecticut  American Athletic           18      Towson             CAA           33
1  662410041           NY                  a            1          CA               b            2
2  772410041           FL                  c            3          AL               d            4