CODEWITHSUNDEEP
pythonpandas
Stats DUB01
Stats DUB01

Reputation: 441

Creating new column by indexing on row / column in a different table. Error in code

I have a table with a column for actor ID and a column for week end date that looks something like this:

      actor_id   week_end_date   
1     11          14/01/2020      
2     12          21/01/2020      
3     13          14/01/2020      
4     14          28/01/2020

I also have another lookup table that looks like this:

      01/01/2020   07/01/2020   14/01/2020    21/01/2020   28/01/2020          
11     0.8          0.8          0.95          0.66         0.9
12     0.9          0.6          0.55          0.56         0.88
13     1.0          0.4          0.66          null          0.67
14     0.5          0.2          null          0.8          0.45

The ID values in the first table match to the index in the second table, and the week end date column in the first table matches to the column headings in the second. I want to create a new column in the first table that matches the ID column to the index and the date column to the column header to access the score value. For example, if the ID value is 13 and the date is 14/01/2020 then the value should be 0.66.

The resulting should look like:

      actor_id   week_end_date  score  
1     11          14/01/2020     0.95 
2     12          21/01/2020     0.56 
3     13          14/01/2020     0.66
4     14          28/01/2020     0.45

I have done the following but am getting an error when the rows dont appear in the second table

table1['score'] = table2.lookup(table1.actor_id, table1.week_end_date)

KeyError: 'One or more column labels was not found'

Heres the data for table 2:

{'2020-09-18': {100000151529577: 0.7884615384615381,
  100000168029563: 0.9047619047619041,
  100000217919567: 1.0},
 '2020-09-25': {100000151529577: 0.8685897435897432,
  100000168029563: 0.9023809523809521,
  100000217919567: 1.0},
 '2020-10-02': {100000151529577: 0.8636126745882837,
  100000168029563: 0.899459642012833,
  100000217919567: 1.0},
 '2020-10-09': {100000151529577: 0.9070984680740773,
  100000168029563: 0.9079498598053762,
  100000217919567: nan},
 '2020-10-16': {100000151529577: 0.8796030343796227,
  100000168029563: 0.9087264428400236,
  100000217919567: nan},
 '2020-10-23': {100000151529577: 0.9062535083532426,
  100000168029563: 0.9298645190811586,
  100000217919567: nan},
 '2020-10-30': {100000151529577: 0.9241415262412603,
  100000168029563: 0.9470803179346093,
  100000217919567: nan},
 '2020-11-06': {100000151529577: 0.9206960425319156,
  100000168029563: 0.9373835471065347,
  100000217919567: nan},
 '2020-11-13': {100000151529577: 0.9331921449951585,
  100000168029563: 0.9092228036424824,
  100000217919567: nan}}

Heres the data for table 1:

    {'actor_id_intial_review': {13136: 100000217919567,
  36458: 100000217919567,
  44911: 100000217919567,
  46438: 100000217919567,
  81365: 100000217919567,
  85164: 100000217919567,
  90500: 100000217919567,
  90615: 100000217919567,
  96797: 100000217919567,
  117933: 100000217919567,
  122622: 100000217919567,
  134176: 100000217919567,
  136793: 100000217919567,
  141525: 100000217919567,
  170583: 100000217919567,
  173947: 100000217919567,
  174717: 100000217919567,
  202140: 100000217919567,
  218028: 100000217919567,
  246709: 100000217919567,
  253509: 100000217919567,
  296325: 100000217919567,
  299911: 100000217919567,
  305295: 100000217919567,
  348334: 100000217919567,
  377124: 100000217919567,
  394816: 100000217919567,
  442361: 100000217919567,
  473015: 100000217919567,
  568644: 100000217919567,
  577591: 100000217919567,
  580509: 100000217919567,
  720516: 100000217919567,
  745977: 100000217919567,
  769787: 100000217919567,
  778840: 100000217919567,
  782353: 100000217919567,
  810651: 100000217919567,
  813219: 100000217919567,
  826088: 100000217919567,
  840808: 100000217919567,
  841324: 100000217919567,
  906253: 100000217919567,
  909337: 100000217919567,
  985510: 100000217919567,
  1018408: 100000217919567,
  1050752: 100000217919567,
  1051113: 100000217919567,
  1052263: 100000217919567,
  1075794: 100000217919567,
  1083932: 100000217919567,
  1108915: 100000217919567,
  1117019: 100000217919567,
  1122138: 100000217919567,
  1127150: 100000217919567,
  1132493: 100000217919567,
  1134987: 100000217919567,
  1152049: 100000217919567,
  1152106: 100000217919567,
  1183398: 100000217919567,
  1189886: 100000217919567,
  1190308: 100000217919567,
  1203364: 100000217919567,
  1205628: 100000217919567,
  1218831: 100000217919567,
  1223462: 100000217919567,
  1226553: 100000217919567,
  1255579: 100000217919567,
  1281205: 100000217919567,
  1291587: 100000217919567,
  1304344: 100000217919567,
  1308887: 100000217919567,
  1317417: 100000217919567,
  1338837: 100000217919567,
  1383861: 100000217919567,
  1390182: 100000217919567,
  1398967: 100000217919567,
  1440998: 100000217919567,
  1511552: 100000217919567,
  1544349: 100000217919567,
  1552965: 100000217919567,
  1583477: 100000217919567,
  1600032: 100000217919567,
  1605280: 100000217919567,
  1639079: 100000217919567,
  1693180: 100000217919567,
  1712713: 100000217919567,
  1771492: 100000217919567,
  1783085: 100000217919567,
  1814725: 100000217919567,
  1814823: 100000217919567,
  1852084: 100000217919567,
  1910314: 100000217919567,
  1910485: 100000217919567,
  1967600: 100000217919567,
  2004368: 100000217919567,
  2013439: 100000217919567,
  2048851: 100000217919567,
  2081665: 100000217919567,
  2081803: 100000217919567,
  2092362: 100000217919567,
  2140335: 100000217919567,
  2152391: 100000217919567,
  2211613: 100000217919567,
  2242767: 100000217919567,
  2247293: 100000217919567,
  2261948: 100000217919567,
  2285583: 100000217919567,
  2295521: 100000217919567,
  2300441: 100000217919567,
  2314442: 100000217919567,
  2403792: 100000217919567,
  2425970: 100000217919567,
  2595653: 100000217919567,
  2640928: 100000217919567,
  2756237: 100000217919567,
  2818931: 100000217919567,
  2929998: 100000217919567},
 'week_end_date': {13136: Timestamp('2020-09-25 00:00:00'),
  36458: Timestamp('2020-09-18 00:00:00'),
  44911: Timestamp('2020-09-25 00:00:00'),
  46438: Timestamp('2020-09-18 00:00:00'),
  81365: Timestamp('2020-09-18 00:00:00'),
  85164: Timestamp('2020-09-18 00:00:00'),
  90500: Timestamp('2020-09-25 00:00:00'),
  90615: Timestamp('2020-09-25 00:00:00'),
  96797: Timestamp('2020-09-18 00:00:00'),
  117933: Timestamp('2020-09-25 00:00:00'),
  122622: Timestamp('2020-09-25 00:00:00'),
  134176: Timestamp('2020-09-18 00:00:00'),
  136793: Timestamp('2020-09-18 00:00:00'),
  141525: Timestamp('2020-09-18 00:00:00'),
  170583: Timestamp('2020-09-18 00:00:00'),
  173947: Timestamp('2020-09-18 00:00:00'),
  174717: Timestamp('2020-09-18 00:00:00'),
  202140: Timestamp('2020-09-18 00:00:00'),
  218028: Timestamp('2020-09-18 00:00:00'),
  246709: Timestamp('2020-09-18 00:00:00'),
  253509: Timestamp('2020-09-18 00:00:00'),
  296325: Timestamp('2020-09-18 00:00:00'),
  299911: Timestamp('2020-09-18 00:00:00'),
  305295: Timestamp('2020-09-25 00:00:00'),
  348334: Timestamp('2020-09-18 00:00:00'),
  377124: Timestamp('2020-09-25 00:00:00'),
  394816: Timestamp('2020-09-18 00:00:00'),
  442361: Timestamp('2020-09-25 00:00:00'),
  473015: Timestamp('2020-09-18 00:00:00'),
  568644: Timestamp('2020-09-25 00:00:00'),
  577591: Timestamp('2020-09-25 00:00:00'),
  580509: Timestamp('2020-09-18 00:00:00'),
  720516: Timestamp('2020-09-18 00:00:00'),
  745977: Timestamp('2020-09-18 00:00:00'),
  769787: Timestamp('2020-09-18 00:00:00'),
  778840: Timestamp('2020-09-18 00:00:00'),
  782353: Timestamp('2020-09-18 00:00:00'),
  810651: Timestamp('2020-09-18 00:00:00'),
  813219: Timestamp('2020-09-18 00:00:00'),
  826088: Timestamp('2020-09-18 00:00:00'),
  840808: Timestamp('2020-09-25 00:00:00'),
  841324: Timestamp('2020-09-18 00:00:00'),
  906253: Timestamp('2020-09-18 00:00:00'),
  909337: Timestamp('2020-09-18 00:00:00'),
  985510: Timestamp('2020-09-25 00:00:00'),
  1018408: Timestamp('2020-09-18 00:00:00'),
  1050752: Timestamp('2020-09-25 00:00:00'),
  1051113: Timestamp('2020-09-18 00:00:00'),
  1052263: Timestamp('2020-09-18 00:00:00'),
  1075794: Timestamp('2020-09-18 00:00:00'),
  1083932: Timestamp('2020-09-18 00:00:00'),
  1108915: Timestamp('2020-09-25 00:00:00'),
  1117019: Timestamp('2020-09-18 00:00:00'),
  1122138: Timestamp('2020-09-18 00:00:00'),
  1127150: Timestamp('2020-09-25 00:00:00'),
  1132493: Timestamp('2020-09-25 00:00:00'),
  1134987: Timestamp('2020-09-25 00:00:00'),
  1152049: Timestamp('2020-09-25 00:00:00'),
  1152106: Timestamp('2020-09-25 00:00:00'),
  1183398: Timestamp('2020-09-18 00:00:00'),
  1189886: Timestamp('2020-09-25 00:00:00'),
  1190308: Timestamp('2020-09-18 00:00:00'),
  1203364: Timestamp('2020-09-25 00:00:00'),
  1205628: Timestamp('2020-09-25 00:00:00'),
  1218831: Timestamp('2020-09-18 00:00:00'),
  1223462: Timestamp('2020-09-18 00:00:00'),
  1226553: Timestamp('2020-09-25 00:00:00'),
  1255579: Timestamp('2020-09-25 00:00:00'),
  1281205: Timestamp('2020-09-18 00:00:00'),
  1291587: Timestamp('2020-09-18 00:00:00'),
  1304344: Timestamp('2020-09-18 00:00:00'),
  1308887: Timestamp('2020-09-25 00:00:00'),
  1317417: Timestamp('2020-09-25 00:00:00'),
  1338837: Timestamp('2020-09-18 00:00:00'),
  1383861: Timestamp('2020-09-18 00:00:00'),
  1390182: Timestamp('2020-09-18 00:00:00'),
  1398967: Timestamp('2020-09-18 00:00:00'),
  1440998: Timestamp('2020-09-25 00:00:00'),
  1511552: Timestamp('2020-09-18 00:00:00'),
  1544349: Timestamp('2020-09-25 00:00:00'),
  1552965: Timestamp('2020-09-18 00:00:00'),
  1583477: Timestamp('2020-09-18 00:00:00'),
  1600032: Timestamp('2020-09-18 00:00:00'),
  1605280: Timestamp('2020-09-18 00:00:00'),
  1639079: Timestamp('2020-09-18 00:00:00'),
  1693180: Timestamp('2020-09-18 00:00:00'),
  1712713: Timestamp('2020-09-18 00:00:00'),
  1771492: Timestamp('2020-09-18 00:00:00'),
  1783085: Timestamp('2020-09-18 00:00:00'),
  1814725: Timestamp('2020-09-18 00:00:00'),
  1814823: Timestamp('2020-09-18 00:00:00'),
  1852084: Timestamp('2020-09-18 00:00:00'),
  1910314: Timestamp('2020-09-25 00:00:00'),
  1910485: Timestamp('2020-09-18 00:00:00'),
  1967600: Timestamp('2020-09-18 00:00:00'),
  2004368: Timestamp('2020-09-18 00:00:00'),
  2013439: Timestamp('2020-09-18 00:00:00'),
  2048851: Timestamp('2020-09-18 00:00:00'),
  2081665: Timestamp('2020-09-18 00:00:00'),
  2081803: Timestamp('2020-09-18 00:00:00'),
  2092362: Timestamp('2020-09-18 00:00:00'),
  2140335: Timestamp('2020-09-18 00:00:00'),
  2152391: Timestamp('2020-09-18 00:00:00'),
  2211613: Timestamp('2020-09-25 00:00:00'),
  2242767: Timestamp('2020-09-18 00:00:00'),
  2247293: Timestamp('2020-09-18 00:00:00'),
  2261948: Timestamp('2020-09-18 00:00:00'),
  2285583: Timestamp('2020-09-18 00:00:00'),
  2295521: Timestamp('2020-09-18 00:00:00'),
  2300441: Timestamp('2020-09-18 00:00:00'),
  2314442: Timestamp('2020-09-18 00:00:00'),
  2403792: Timestamp('2020-09-25 00:00:00'),
  2425970: Timestamp('2020-09-18 00:00:00'),
  2595653: Timestamp('2020-09-18 00:00:00'),
  2640928: Timestamp('2020-09-18 00:00:00'),
  2756237: Timestamp('2020-09-18 00:00:00'),
  2818931: Timestamp('2020-09-18 00:00:00'),
  2929998: Timestamp('2020-09-25 00:00:00')}}

Upvotes: 1

Views: 34

Answers (1)

Vivek Kalyanarangan
Vivek Kalyanarangan

Reputation: 9081

Use pandas.unstack() and pandas.Index.map-

table1.set_index(['week_end_date', 'actor_id']).index.map(table2.unstack())

Output

    actor_id week_end_date  score
1        11    14/01/2020   0.95
2        12    21/01/2020   0.56
3        13    14/01/2020   0.66
4        14    28/01/2020   0.45

Upvotes: 1

Related Questions