How To Cross Checking 2 Pandas Dataframes File And Use 1 Dataframe's Value As A Variable?

I have 2 pandas dataframes: modal2: Mode month1 month2 month3 month4 month5 month6 month7 month8 month9 month10 month11 month12 0 100 0 0 0 0 0 0 0 0

Solution 1:

Another solution:

x = pd.merge(df_ia, modal2, on="Mode", how="left")
x["dynamic_month_value"] = x.apply(
    lambda x: x["month" + str(x["RevalMonth_plus"])], axis=1
)
print(x[["RevalMonth_plus", "Mode", "dynamic_month_value"]])

Prints:

   RevalMonth_plus  Mode  dynamic_month_value
0                1   602                    0
1                1   100                    0
2                4   100                    0
3                1   802                    0
4                4   603                    1
5                4   603                    1
6                4   603                    1
7                1   100                    0

Solution 2:

Try with pd.wide_to_long to reshape the DataFrame modal2, then mergeleft to keep order of df_ia:

df_long = pd.wide_to_long(modal2, stubnames='month',
                          i='Mode', j='RevalMonth_plus').reset_index()
new_df = df_ia.merge(
    df_long,
    how='left',
    on=['RevalMonth_plus', 'Mode']
).rename(columns={'month': 'dynamic_month_value'})

new_df:

   RevalMonth_plus  Mode  dynamic_month_value
0                1   602                    0
1                1   100                    0
2                4   100                    0
3                1   802                    0
4                4   603                    1
5                4   603                    1
6                4   603                    1
7                1   100                    0

---

An example with unique values to show more clearly the mapping:

modal2 = pd.DataFrame({
    'Mode': [100, 602, 603, 802],
    'month1': [1, 2, 3, 4],
    'month4': [5, 6, 7, 8]
})

df_ia = pd.DataFrame({
    'RevalMonth_plus': [1, 1, 4, 1, 4, 4, 4, 1],
    'Mode': [602, 100, 100, 802, 603, 603, 603, 100]
})

modal2:

   Mode  month1  month4
0   100       1       5
1   602       2       6
2   603       3       7
3   802       4       8

df_ia:

   RevalMonth_plus  Mode
0                1   602
1                1   100
2                4   100
3                1   802
4                4   603
5                4   603
6                4   603
7                1   100

new_df:

   RevalMonth_plus  Mode  dynamic_month_value
0                1   602                    2
1                1   100                    1
2                4   100                    5
3                1   802                    4
4                4   603                    7
5                4   603                    7
6                4   603                    7
7                1   100                    1

---

All Month columns can be mapped with merge first:

# Merge left to bring all Month columns into new_df
new_df = df_ia.merge(modal2, on='Mode', how='left')

df_long = pd.wide_to_long(modal2, stubnames='month',
                          i='Mode', j='RevalMonth_plus').reset_index()
new_df = new_df.merge(
    df_long,
    how='left',
    on=['RevalMonth_plus', 'Mode']
).rename(columns={'month': 'dynamic_month_value'})

   RevalMonth_plus  Mode  month1  month4  dynamic_month_value
0                1   602       2       6                    2
1                1   100       1       5                    1
2                4   100       1       5                    5
3                1   802       4       8                    4
4                4   603       3       7                    7
5                4   603       3       7                    7
6                4   603       3       7                    7
7                1   100       1       5                    1

Solution 3:

It is almost always wrong to iterate over dataframe rows. You need to merge the dataframes:

df_ia['dynamic_month_value'] = modal2.merge(df_ia).set_index(df_ia.index)['month1']

#    RevalMonth_plus  Mode  dynamic_month_value#0                 1   602       0#35                1   100       0#52                4   100       0#79                1   802       0#94                4   603       1#95                4   603       1#96                4   603       1#98                1   100       0