[FIXED] How to add unique values lists to rows in dataframe?

Issue

In the following code, formation_top variable has formation names along with their top depths:

formation_top = pd.DataFrame('Formation': ['Balakhany', 'Balakhany X', 'Pereriv A', 'Pereviv B'],'Depth': [2200, 2250, 2300, 2340])

    Formation     Depth
0   Balakhany     2200
1   Balakhany X   2250
2   Pereriv A     2300
3   Pereriv B     2340

formation dataframe shows that 'Balakhany' is from 2200 to 2250, 'Balakhany X' is from 2250 to 2300, and so on. I have also fluidcode dataframe has "Depth" column which ranges from 2200 to 2400 with the increment of 1.0 m and "Fluidcode" column which contains unique fluid codes for each depth.

        FLUIDCODE     Depth
    0   3             2200
    1   3             2201
    2   4             2202
    .   .              .
    .   .              .
   149  2             2338
   150  7             2339
   151  np.nan        2340

I want to extract unique fluid codes for each formation depth interval and append those lists of unique fluid codes to new column in formation_top dataframe according to each formation. I tried following code:

self.formation_top['FLUIDCODES'] = np.nan

for i in range(len(self.formation_top) - 1):
    depth_condition = (self.fluidcode['Depth'] >= self.formation_top['Depth'][i]) & (self.fluidcode['Depth'] <= self.formation_top['Depth'][i + 1])
    unique_flcodes = self.fluidcode.loc[depth_condition, 'FLUIDCODE'].unique()
    unique_flcodes = pd.Series([unique_flcodes])
    self.formation_top.at[i, 'FLUIDCODES'] = unique_flcodes

but I get error as ValueError: Incompatible indexer with Series

I would like to see the output like:

    Formation     Depth  FLUIDCODES
0   Balakhany     2200   3, 4, 6
1   Balakhany X   2250   5
2   Pereriv A     2300   np.nan
3   Pereriv B     2340   2, 7

How would you do it?

Solution

IIUC, you can use a merge_asof, then groupby.agg and map:

s = (pd.merge_asof(fluidcode, formation_top)
       .dropna()
       .groupby('Formation')['FLUIDCODE']
       .agg(lambda x: ', '.join(str(int(i)) for i in  dict.fromkeys(x) if pd.notna(i)))
     )

formation_top['FLUIDCODES'] = formation_top['Formation'].map(s)

Output:

     Formation  Depth FLUIDCODES
0    Balakhany   2200       3, 4
1  Balakhany X   2250        NaN
2    Pereriv A   2300       2, 7
3    Pereviv B   2340        NaN

Answered By - mozway