How can I create a column in an actual dataframe by indexing another dataframe using the values in two columns from the actual dataframe

ManuelVillamil

Good day. I have two dataset (df1,df2). I am trying to fill the column 'values' in df2 by using the column 'site_before' as the row index in df1 and the column 'site' as the column index in df1.

The dataset df1:

      ANA01  PHO01  ATL  BAL12  BOS07   
ANA01   0     0      3     3     3
PHO01   0     0      3     3     3
ATL    -3    -3      0     0     0 
BAL12   -3   -3      0     0     0 
BOS07   -3    -3     0     0     0

"The first column is the indexes of the rows"

The dataset df2:

    Game_ID     site_before  site   values
1   ANA199804010    ANA01   ANA01   
3   ANA199804020    ANA01   ATL 
5   ANA199804030    ANA01   BAL12   
7   ANA199804040    ANA01   BOS07   
9   ANA199804050    ANA01   ANA01   
674 BOS199804300    BOS07   BOS07   
31  ANA199805010    BOS07   ANA01   
33  ANA199805020    PHO01   ANA01   
35  ANA199805030    PHO01   PHO01   
37  ANA199805040    PHO01   ATL 
39  ANA199805050    PHO01   BAL12

I tried to do:

df2['values'] = df1.loc[df2['site_before'], df2['site']].values

but I got an error as ValueError: Wrong number of items passed 4864, placement implies 1

The result I am expecting is:

    Game_ID     site_before site    values
1   ANA199804010    ANA01   ANA01   0
3   ANA199804020    ANA01   ATL     3
5   ANA199804030    ANA01   BAL12   3
7   ANA199804040    ANA01   BOS07   3
9   ANA199804050    ANA01   ANA01   0
674 BOS199804300    BOS07   BOS07   0
31  ANA199805010    BOS07   ANA01   -3
33  ANA199805020    PHO01   ANA01   0
35  ANA199805030    PHO01   PHO01   0
37  ANA199805040    PHO01   ATL     3
39  ANA199805050    PHO01   BAL12   3

jezrael

Use DataFrame.join with new MultiIndex Series created by DataFrame.stack:

df2 = df2.join(df1.stack().rename('new').rename_axis(('site_before','site')), 
               on=['site_before','site'])
print (df2)
          Game_ID site_before   site  new
1    ANA199804010       ANA01  ANA01    0
3    ANA199804020       ANA01    ATL    3
5    ANA199804030       ANA01  BAL12    3
7    ANA199804040       ANA01  BOS07    3
9    ANA199804050       ANA01  ANA01    0
674  BOS199804300       BOS07  BOS07    0
31   ANA199805010       BOS07  ANA01   -3
33   ANA199805020       PHO01  ANA01    0
35   ANA199805030       PHO01  PHO01    0
37   ANA199805040       PHO01    ATL    3
39   ANA199805050       PHO01  BAL12    3

Alternative is use DataFrame.melt with DataFrame.merge and left join:

df3 = df1.rename_axis('site_before').reset_index().melt('site_before', var_name='site')

df2 = df2.merge(df3, how='left')
print (df2)
         Game_ID site_before   site  new
0   ANA199804010       ANA01  ANA01    0
1   ANA199804020       ANA01    ATL    3
2   ANA199804030       ANA01  BAL12    3
3   ANA199804040       ANA01  BOS07    3
4   ANA199804050       ANA01  ANA01    0
5   BOS199804300       BOS07  BOS07    0
6   ANA199805010       BOS07  ANA01   -3
7   ANA199805020       PHO01  ANA01    0
8   ANA199805030       PHO01  PHO01    0
9   ANA199805040       PHO01    ATL    3
10  ANA199805050       PHO01  BAL12    3

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-12-5

Comments

0 comments

How can I insert actual values in dataframe as columns in R?

How can I create a column in a dataframe using conditional logic on multiple columns in another dataframe python pandas?

How can I create a column in an actual dataframe by indexing another dataframe using the values in two columns from the actual dataframe

How can I create a column in an actual dataframe by indexing another dataframe using the values in two columns from the actual dataframe

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

Emulator wrong screen resolution in Android Studio 1.3

3D Touch Peek Swipe Like Mail

Double spacing in rmarkdown pdf

Svchost high CPU from Microsoft.BingWeather app errors

How to how increase/decrease compared to adjacent cell

Using Response.Redirect with Friendly URLS in ASP.NET

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

BigQuery - concatenate ignoring NULL

How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Can a 32-bit antivirus program protect you from 64-bit threats

Make a B+ Tree concurrent thread safe

Bootstrap 5 Static Modal Still Closes when I Click Outside

Vector input in shiny R and then use it

Assembly definition can't resolve namespaces from external packages