How to cross checking 2 pandas dataframes file and use 1 dataframe's value as a variable?

Question

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   0   0   0   0
2   602 0   2   1   0   2   1   0   2   1   0   2   1
3   603 1   0   2   1   0   2   1   0   2   1   0   2
11  802 0   11  10  9   8   7   6   5   4   3   2   1

df_ia:

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

the logic is compare each row of df_ia to each row of modal2,if row of df_ia has the same Mode number as row of modal2 , then add the column month1 of modal2 to df_ia.

I can successfully complete this part by the following code:

 for index, row in modal2.iterrows():
        conditions.append(df_ia['Mode'] == row['Mode'])


    for col in ['month1']: #for loop list is because sometime need more than 1 column  
        col_v = modal2[col]
        df_ia[col] = np.select(conditions, col_v, default=None)

the output is:

      RevalMonth_plus  Mode  month1
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

But the challenge part is:

how can I replace 'month1' to the corresponding 'RevalMonth_plus' from df_ia, just like this:

for col in [f'month{df_ia['RevalMonth_plus']}']: #for loop list is because sometime need more than 1 column  
    col_v = modal2[col]
    df_ia[col] = np.select(conditions, col_v, default=None)

The ideal output should like:

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

I have spent a whole day on it.But cannot make it.

Andrej Kesely · Accepted Answer · 2021-07-08 22:21:28Z

2

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

answered Jul 8, 2021 at 22:21

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

William Over a year ago

Thank you so much for your help, what if I need get another column at the same time for example month7 ? can I do something like: x = pd.merge(df_ia, modal2, on="Mode", how="left",'month7') x["dynamic_month_value"] = x.apply( lambda x: x["month" + str(x["RevalMonth_plus"])], axis=1 ) print(x[["RevalMonth_plus", "Mode", "dynamic_month_value"]])

Henry Ecker Over a year ago

x already has all of the month columns from merge. x[["RevalMonth_plus", "Mode", "month7", "dynamic_month_value"]]

William Over a year ago

Hi friend can you help me check this:stackoverflow.com/questions/68370860/…

William Over a year ago

Hi friend can you help me with this very similar question:stackoverflow.com/questions/68371165/…

William Over a year ago

Hi friend can you help me with this question?stackoverflow.com/questions/68476193/…

Henry Ecker · Accepted Answer · 2021-07-08 22:39:12Z

2

Try with pd.wide_to_long to reshape the DataFrame modal2, then merge left 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

edited Jul 8, 2021 at 22:39

answered Jul 8, 2021 at 22:17

Henry Ecker♦

35.9k19 gold badges48 silver badges67 bronze badges

7 Comments

William Over a year ago

Hi friend,thank you so much for your help,what if I want to get 2 columns?

Henry Ecker Over a year ago

Dynamically? or just month value columns? mapped by Mode? As the others have mentioned, you can merge to bring the columns over.

William Over a year ago

Not dynamically just hard coding for example month7

Henry Ecker Over a year ago

See the merge code at the end then just filter new_df to contain the columns desired. Something like -> new_df[["RevalMonth_plus", "Mode", "month7", "dynamic_month_value"]]

William Over a year ago

Hi friend can you help me check this:stackoverflow.com/questions/68370860/…

|

DYZ · Accepted Answer · 2021-07-08 22:19:53Z

1

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

edited Jul 8, 2021 at 22:19

answered Jul 8, 2021 at 22:15

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

4 Comments

William Over a year ago

Thank you for your reply but the Mode need be same.

DYZ Over a year ago

It is the same.

William Over a year ago

Hi friend can you help me check this:stackoverflow.com/questions/68370860/…

William Over a year ago

Hi friend can you help me with this question?stackoverflow.com/questions/68476193/…

Collectives™ on Stack Overflow

How to cross checking 2 pandas dataframes file and use 1 dataframe's value as a variable?

3 Answers 3

5 Comments

7 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related