pandas - nearest value lookup involving categorical groupings

user_7685544 0
Viewed 97

I am looking for an easy solution for the following: I have two dataframes. The first one has three columns 'Var', 'Date' and 'Type':

df1

    date    type    var
0   2015-01-01  A   0.014370
1   2015-01-02  A   30.835206
2   2015-01-03  A   60.311195
3   2015-01-04  A   90.716415
4   2015-01-05  A   120.589583
...     ...     ...     ...
85  2015-01-26  C   750.286017
86  2015-01-27  C   780.010769
87  2015-01-28  C   810.491360
88  2015-01-29  C   840.598564
89  2015-01-30  C   870.811063

It acts as a lookup table for df2. This dataframe consists of some variable columns (omitted here vor readability) alongside 'Var' and 'Type' corresponding to the same columns in df1.

df2 = pd.DataFrame({'Type': np.random.choice(['A', 'B', 'C'], 10),
                   'Var': np.random.sample(10) * 800})

    Type    Var
0   C   317.399765
1   A   774.845006
2   B   260.912633
3   B   170.722395
4   C   433.842763
5   C   626.290970
6   A   347.950419
7   C   770.953878
8   B   477.139350
9   B   134.014690

I now want to take each row of df2, lookup the combination of ['Type', 'Var'] in df1 and fill in the respective date value in df2. I routinely use this operation and am looking for a fast, short, easy and "best practice way".

I often used complicated argsort constructs for this. merge_asof() sometimes also works, but most of the time I only want one column filled in.

I feel like the data structure involving different types (as well as unsorted values) complicates this easy operation. Any recommendations on improvements are welcome :)

pandas dataframe lookup merge python
1 Answers

I solved this with:

df1 = df1.sort_values(by='Var')
df2 = df2.sort_values(by='Var')

m = pd.merge_asof(df2, df1, on='Var', by='Type',  direction='nearest')

This is already quite short, but if there is a better solution please let me know. I forgot the by= operator is actually or "grouping" the data. I tried to use multiple keys in on= which threw errors.

user_76855440
Related