Assume we have two DataFrames:
DF1:
spec u_g target
G1 4.8 0.88
G2 2.1 0.76
WG2 1.4 0.71
WG2 1.2 0.68
WG2 1.0 0.52
WG3 0.8 0.65
WG3 0.7 0.53
SWG3 0.7 0.31
DF2:
id type u_g_1
1 WG2 1.4
2 WG2 1.4
3 WG2 1.0
4 G1 4.8
5 G1 4.9
6 G2 2.1
7 SWG3 0.7
8 WG3 0.8
9 WG3 0.7
10 WG2 1.2
11 nan 0
For every row in DF2 I'd like to look up if the type match with an entry in DF1, if yes, I'd like to look if there is an entry in DF1 with the corresponding value u_g_1 == u_g, if yes, choose the target value and assign this to DF2. If not, assign the next bigger value in u_g for the same type to the new 'target' column.
DF2_modified:
id type u_g_1 target
1 WG2 1.4 0.71
2 WG2 1.4 0.71
3 WG2 1.0 0.52
4 G1 4.8 0.88
5 G1 4.9 0.88
6 G2 2.1 0.76
7 SWG3 0.7 0.31
8 WG3 0.8 0.65
9 WG3 0.7 0.53
10 WG2 1.1 0.68
11 nan 0 nan
I tried it with:
df2['target'] = np.where(df2['type'].isin(df1['spec']), (np.where(df1['u_g_1'].isin(df1['u_g']), df1['g'], 'no value for u_g, therefore no target-value')), 'no specification')
This didn't work, besides, as far as I know, the code can't handle the NaNs as well this doesn't use the next bigger value for u_g.
and
df2.merge(df1, left_on=['type', 'u_g_1'], right_on=['spec', 'u_g'], how='left')
Doesn't give me the max value for u_g
Can someone please help?