Pandas dataframe mapping one value to another in a row

Question

I have a pandas two dataframes, one with columns 'Name', 'Year' and 'Currency Rate'.

The other with columns named 'Name', and a long list of years(1950, 1951,...,2019,2020).

And in the column 'Year', it is storing value 2000, 2001,...,2015. The years(1950, 1951,...,2019,2020) columns are storing income of the year.

I want to merge these two dataframes but map the income of the year according to the 'Year' column to a new column named 'Income' and drop all the other years, is there a convenient way to do this?

What I am thinking is splitting the second dataframe into 16 different years, and then joining it to the first dataframe.

Answer 1

UPDATED to reflect OP's comment clarifying the question:

To restate the question based on my updated understanding:

• There is a dataframe with columns Name, Year and Currency Rate (3 columns in total). Each row contains a Currency Rate in a given Year for a given Name. Each row is unique by (Name, Year) pair.
• There is a second dataframe with columns Name, 1950, 1951, ..., 2020 (72 columns in total). Each cell contains an Income value for the corresponding Name for the row and the Year corresponding to the column name. Each row is unique by Name.
• Question: How do we add an Income column to the first dataframe with each row containing the Income value from the second dataframe in the cell with (row, column) corresponding to the (Name, Year) pair of such row in the first dataframe?

Test case assumptions I have made:

• Name in the first dataframe is a letter from 'a' to 'n' with some duplicates.
• Year in the first dataframe is between 2000 and 2015 (as in the question).
• Currency Rate in the first dataframe is arbitrary.
• Name in the second dataframe is a letter from 'a' to 'z' (no duplicates).
• The values in the second dataframe (which represent Income) are arbitrarily constructed using the ASCII offsets of the characters in the corresponding Name concatenated with the Year of the corresponding column name. This way we can visually "decode" them in the test results to confirm that the value from the correct location in the second dataframe has been loaded into the new Income column in the first dataframe.

table1 = [
    {'Name': 'a', 'Year': 2000, 'Currency Rate': 1.1},
    {'Name': 'b', 'Year': 2001, 'Currency Rate': 1.2},
    {'Name': 'c', 'Year': 2002, 'Currency Rate': 1.3},
    {'Name': 'd', 'Year': 2003, 'Currency Rate': 1.4},
    {'Name': 'e', 'Year': 2004, 'Currency Rate': 1.5},
    {'Name': 'f', 'Year': 2005, 'Currency Rate': 1.6},
    {'Name': 'g', 'Year': 2006, 'Currency Rate': 1.7},
    {'Name': 'h', 'Year': 2007, 'Currency Rate': 1.8},
    {'Name': 'i', 'Year': 2008, 'Currency Rate': 1.9},
    {'Name': 'j', 'Year': 2009, 'Currency Rate': 1.8},
    {'Name': 'k', 'Year': 2010, 'Currency Rate': 1.7},
    {'Name': 'l', 'Year': 2011, 'Currency Rate': 1.6},
    {'Name': 'm', 'Year': 2012, 'Currency Rate': 1.5},
    {'Name': 'm', 'Year': 2013, 'Currency Rate': 1.4},
    {'Name': 'n', 'Year': 2014, 'Currency Rate': 1.3},
    {'Name': 'n', 'Year': 2015, 'Currency Rate': 1.2}
]
table2 = [{'Name': name} | {str(year): (income := sum(ord(c) - ord('a') + 1 for c in name)*10000 + year) for year in range(1950, 2021)} for name in set(['x', 'y', 'z']) | set(map(lambda row: row['Name'], table1))]

import pandas as pd
df1 = pd.DataFrame(table1)
df2 = pd.DataFrame(table2).sort_values(by='Name')

print(df1)
print(df2)

df1['Income'] = df1.apply(lambda x: int(df2[df2['Name'] == x['Name']][str(x['Year'])]), axis=1)
print(df1.to_string(index=False))

Output:

   Name  Year  Currency Rate
0     a  2000            1.1
1     b  2001            1.2
2     c  2002            1.3
3     d  2003            1.4
4     e  2004            1.5
5     f  2005            1.6
6     g  2006            1.7
7     h  2007            1.8
8     i  2008            1.9
9     j  2009            1.8
10    k  2010            1.7
11    l  2011            1.6
12    m  2012            1.5
13    m  2013            1.4
14    n  2014            1.3
15    n  2015            1.2
   Name    1950    1951    1952    1953    1954    1955    1956    1957    1958    1959    1960  ...    2009    2010    2011    2012    2013    2014    2015    2016    2017    2018    2019    2020
11    a   11950   11951   11952   11953   11954   11955   11956   11957   11958   11959   11960  ...   12009   12010   12011   12012   12013   12014   12015   12016   12017   12018   12019   12020
15    b   21950   21951   21952   21953   21954   21955   21956   21957   21958   21959   21960  ...   22009   22010   22011   22012   22013   22014   22015   22016   22017   22018   22019   22020
0     c   31950   31951   31952   31953   31954   31955   31956   31957   31958   31959   31960  ...   32009   32010   32011   32012   32013   32014   32015   32016   32017   32018   32019   32020
10    d   41950   41951   41952   41953   41954   41955   41956   41957   41958   41959   41960  ...   42009   42010   42011   42012   42013   42014   42015   42016   42017   42018   42019   42020
9     e   51950   51951   51952   51953   51954   51955   51956   51957   51958   51959   51960  ...   52009   52010   52011   52012   52013   52014   52015   52016   52017   52018   52019   52020
1     f   61950   61951   61952   61953   61954   61955   61956   61957   61958   61959   61960  ...   62009   62010   62011   62012   62013   62014   62015   62016   62017   62018   62019   62020
4     g   71950   71951   71952   71953   71954   71955   71956   71957   71958   71959   71960  ...   72009   72010   72011   72012   72013   72014   72015   72016   72017   72018   72019   72020
3     h   81950   81951   81952   81953   81954   81955   81956   81957   81958   81959   81960  ...   82009   82010   82011   82012   82013   82014   82015   82016   82017   82018   82019   82020
2     i   91950   91951   91952   91953   91954   91955   91956   91957   91958   91959   91960  ...   92009   92010   92011   92012   92013   92014   92015   92016   92017   92018   92019   92020
13    j  101950  101951  101952  101953  101954  101955  101956  101957  101958  101959  101960  ...  102009  102010  102011  102012  102013  102014  102015  102016  102017  102018  102019  102020
14    k  111950  111951  111952  111953  111954  111955  111956  111957  111958  111959  111960  ...  112009  112010  112011  112012  112013  112014  112015  112016  112017  112018  112019  112020
12    l  121950  121951  121952  121953  121954  121955  121956  121957  121958  121959  121960  ...  122009  122010  122011  122012  122013  122014  122015  122016  122017  122018  122019  122020
5     m  131950  131951  131952  131953  131954  131955  131956  131957  131958  131959  131960  ...  132009  132010  132011  132012  132013  132014  132015  132016  132017  132018  132019  132020
7     n  141950  141951  141952  141953  141954  141955  141956  141957  141958  141959  141960  ...  142009  142010  142011  142012  142013  142014  142015  142016  142017  142018  142019  142020
8     x  241950  241951  241952  241953  241954  241955  241956  241957  241958  241959  241960  ...  242009  242010  242011  242012  242013  242014  242015  242016  242017  242018  242019  242020
6     y  251950  251951  251952  251953  251954  251955  251956  251957  251958  251959  251960  ...  252009  252010  252011  252012  252013  252014  252015  252016  252017  252018  252019  252020
16    z  261950  261951  261952  261953  261954  261955  261956  261957  261958  261959  261960  ...  262009  262010  262011  262012  262013  262014  262015  262016  262017  262018  262019  262020

[17 rows x 72 columns]
Name  Year  Currency Rate  Income
   a  2000            1.1   12000
   b  2001            1.2   22001
   c  2002            1.3   32002
   d  2003            1.4   42003
   e  2004            1.5   52004
   f  2005            1.6   62005
   g  2006            1.7   72006
   h  2007            1.8   82007
   i  2008            1.9   92008
   j  2009            1.8  102009
   k  2010            1.7  112010
   l  2011            1.6  122011
   m  2012            1.5  132012
   m  2013            1.4  132013
   n  2014            1.3  142014
   n  2015            1.2  142015