Mapping Fields to Pull values within the same pandas dataframe-CodePudding

I'm not sure what's the best way to describe this in words so a picture is worth a thousand words (in this case an example is worth a thousand words :)) I have this table on Python as a Pandas Dataframe

id	Math	Physics	Morning Class	Night Class
1	math100	phys300	[Math]	[Physics]
2	math500	phys250A	[Physics]	[Math]

and I'm trying to use the values in the "Morning Class" and "Night Class" fields to see which column to look at and pull data from, and based on that I'll replace the "Morning Class" and "Night Class" column values with the mapped values from "Math" and "Physics" field. So this is what the final table should look like

id	Math	Physics	Morning Class	Night Class
1	math100	phys300	math100	phys300
2	math500	phys250A	phys250A	math500

I want to use Python to achieve this, I'm able to do this on SQL, I feel like this is a simple Python question but I just can't seem to figure it out in Python and when I tried to look up online I couldn't find any post that describes and answer my problem, if one already exists feel free to refer me to it. Thank!!!

CodePudding user response：

Use indexing lookup by both columns:

cols1 = ['Morning Class','Night Class']

def f(x):
    #if string columns
    idx, cols = pd.factorize(x.str.strip('[]'))
    #if one element list columns
    #idx, cols = pd.factorize(x.str[0])
    return df.reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]

df[cols1] = df[cols1].apply(f)
print (df)
   id     Math   Physics Morning Class Night Class
0   1  math100   phys300       math100     phys300
1   2  math500  phys250A      phys250A     math500