Filling values in a new df column based on values in another df

Question

I have two data frames: First:

Job = {'Name': ["Ron", "Joe", "Dan"],
        'Job': [[2000, 2001], 1998, [2000, 1999]]
        }

df = pd.DataFrame(Job, columns = ['Name', 'Job'])
  Name           Job
0  Ron  [2000, 2001]
1  Joe          1998
2  Dan  [2000, 1999]

Second:

Empty = {'Name': ["Ron", "Ron", "Ron", "Ron", "Joe", "Joe", "Joe", "Joe", "Dan", "Dan", "Dan", "Dan"],
        'Year': [1998, 1999, 2000, 2001, 1998, 1999, 2000, 2001, 1998, 1999, 2000, 2001]
        }

df2 = pd.DataFrame(Empty, columns = ['Name', 'Year'])

    Name Year
0   Ron 1998
1   Ron 1999
2   Ron 2000
3   Ron 2001
4   Joe 1998
5   Joe 1999
6   Joe 2000
7   Joe 2001
8   Dan 1998
9   Dan 1999
10  Dan 2000
11  Dan 2001

I want to add a column to df2 (let's call it 'job_status'), where each year that is associated with a name in df1 will recieve 1 in df2 and 0 otherwise. This should be the output:

   Name  Year   job_status
0   Ron 1998      0
1   Ron 1999      0
2   Ron 2000      1
3   Ron 2001      1
4   Joe 1998      1
5   Joe 1999      0
6   Joe 2000      0
7   Joe 2001      0
8   Dan 1998      0
9   Dan 1999      1
10  Dan 2000      1
11  Dan 2001      0

How can I accomplish this?

Answer 1

First explode the dataframe df on Job then left merge it with df2, finally using Series.notna + view assign the labels from [0, 1] to job_status:

d = df2.merge(df.explode('Job'), left_on=['Name', 'Year'], right_on=['Name', 'Job'], how='left')
d['job_status'] = d.pop('Job').notna().view('i1')

Result:

print(d)

   Name  Year  job_status
0   Ron  1998           0
1   Ron  1999           0
2   Ron  2000           1
3   Ron  2001           1
4   Joe  1998           1
5   Joe  1999           0
6   Joe  2000           0
7   Joe  2001           0
8   Dan  1998           0
9   Dan  1999           1
10  Dan  2000           1
11  Dan  2001           0