CODEWITHSUNDEEP
pythonpandasdataframelookup-tables
densekernel
densekernel

Reputation: 1351

Python Pandas: DataFrame as a Lookup Table

This is a preprocessed DataFrame, with columns representing frequency and success values for specific column. For example: Column A is associated with FREQ_A and SUCCESS_A respectively.

   A  B  Gold  FREQ_A  SUCCESS_A  FREQ_B  SUCCESS_B
0  1  B     0       1       0.00       1       0.00
1  2  A     1       1       0.01       1       0.01

I have another DataFrame, like the following:

   A  B
0  1  A
1  2  B

Now I want to add the associated frequency and success columns (FREQ_* and SUCCESS_*, * : {A,B}), looking up the values from the preprocessed DataFrame. An important observation is that the preprocessed DataFrame has an identical set of (non freq/success) columns, but not a complete set of keys. (See row 2, A:3 and B:C are not located in the preprocessed frame)

For example:

The first row in the dataframe, has values A = 1, B = A, so:

FREQ_A will take the value of the original dataframe of FREQ_A where A == 1

and

FREQ_B will take the value of the original dataframe of FREQ_B where B == A

Ideal output

   A  B  FREQ_A  SUCCESS_A  FREQ_B  SUCCESS_B
0  1  A       1       0.00       1       0.01
1  2  B       1       0.01       1       0.00

Test case

   A  B
0  1  A
1  2  B
2  1  C
3  4  A

Upvotes: 1

Views: 7774

Answers (1)

Alexander
Alexander

Reputation: 109546

df1 = pd.DataFrame({
 'A': [1, 2],
 'B': ['B', 'A'],
 'FREQ_A': [1, 1],
 'FREQ_B': [1, 1],
 'Gold': [0, 1],
 'SUCCESS_A': [0.0, 0.01],
 'SUCCESS_B': [0.0, 0.01]})

df2 = pd.DataFrame({'A': [1, 2], 'B': ['A', 'B']})

result = (df2
          .merge(df1[['A', 'FREQ_A', 'SUCCESS_A']], on='A')
          .merge(df1[['B', 'FREQ_B', 'SUCCESS_B']], on='B'))
>>> result
   A  B  FREQ_A  SUCCESS_A  FREQ_B  SUCCESS_B
0  1  A       1       0.00       1       0.01
1  2  B       1       0.01       1       0.00

EDIT

For an arbitrary dataframe:

result = pd.concat(
    [df2, pd.concat([df2[[col]].merge(
                         df1[[col, 'FREQ_' + str(col), 'SUCCESS_' + str(col)]], 
                         on=col, how='left').iloc[:, 1:] 
                     for col in df2], axis=1)], 
    axis=1)

Upvotes: 1

Related Questions