CODEWITHSUNDEEP
pythonpandasmapping
Brett Ford
Brett Ford

Reputation: 79

Mapping data from a Pandas Dataframe column whose name is given in another column

I have two dataframes and am trying to map data from one dataframe to the next. The first dataframe has player names as its index and a player/game ID as its header.

Dataframe 1:

Date + Game         2015-04-12 PIT@MIL  2015-04-12 SEA@OAK  \
Alcides Escobar                      0                   0
Mike Moustakas                       0                   0
Lorenzo Cain                         0                   0
Eric Hosmer                          0                   0

Dataframe 2:

                   Name         Date + Game
0       Alcides Escobar  2015-04-12 KAN@LAA
1        Mike Moustakas  2015-04-12 KAN@LAA
2          Lorenzo Cain  2015-04-12 KAN@LAA
3           Eric Hosmer  2015-04-12 KAN@LAA
4       Kendrys Morales  2015-04-12 KAN@LAA
5             Alex Rios  2015-04-12 KAN@LAA
6        Salvador Perez  2015-04-12 KAN@LAA

The second dataframe contains information about the player and has the player's name and date/game ID in two columns. I am trying to map the data from the first dataframe to the second based on the player's name and the ID using the following formula:

batter_game_logs_df['R vs SP'] = batter_game_logs_df['Name'].map(play_by_play_run_scored_SP_df[batter_game_logs_df['Date + Game']], na_action='ignore').fillna(0)

Because the column that I am selecting data from in the first dataframe is dependent on the Date + Game column in the second dataframe, the code displays an error that states that "the Dataframe object is not callable." If I replace the column reference with a date/game ID manually, it works. For example,

batter_game_logs_df['R vs SP'] = batter_game_logs_df['Name'].map(play_by_play_run_scored_SP_df['2015-04-12 KAN@LAA'], na_action='ignore').fillna(0)

Does anyone know how I could manage to create a column in the second dataframe that links the data from the first?

Upvotes: 0

Views: 110

Answers (1)

gyoza
gyoza

Reputation: 2152

If I understand your intention correctly, more appropriate example datasets would be:

df1

       Date + Game  2015-04-12 PIT@MIL  2015-04-12 KAN@LAA
0  Alcides Escobar                   1                   5
1   Mike Moustakas                   2                   6
2     Lorenzo Cain                   3                   7
3      Eric Hosmer                   4                   8

df2

              Name         Date + Game
0  Alcides Escobar  2015-04-12 PIT@MIL
1   Mike Moustakas  2015-04-12 PIT@MIL
2     Lorenzo Cain  2015-04-12 KAN@LAA
3      Eric Hosmer  2015-04-12 KID@MIT
4      Eric Hosmer  2015-04-12 KAN@LAA

Anyways, the key point is to map a "wide" dataframe (df1) into a "tall" dataframe (df2). In order to achieve this, I would create a "tall" dataframe using the "wide" dataframe (df1) first and merge it to the other "tall" one (df2).

Code:

# derive "df2-like" dataframe using df1
df1 = (df1.rename(columns={'Date + Game': 'Name'})
       .set_index('Name')
       .stack()
       .reset_index()
       .rename(columns={'level_1': 'Date + Game', 0: 'R vs SP'})
      )

# merge 2 dataframes
df2 = df2.merge(df1, on=['Name', 'Date + Game'], how='left').fillna(0)

Output(df2):

              Name         Date + Game  R vs SP
0  Alcides Escobar  2015-04-12 PIT@MIL     1.0
1   Mike Moustakas  2015-04-12 PIT@MIL     2.0
2     Lorenzo Cain  2015-04-12 KAN@LAA     7.0
3      Eric Hosmer  2015-04-12 KID@MIT     0.0
4      Eric Hosmer  2015-04-12 KAN@LAA     8.0

Upvotes: 1

Related Questions