0

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?

Nick ODell
  • 15,465
  • 3
  • 32
  • 66
Aukru
  • 135
  • 6

0 Answers0