Filtering out a chemical dataset according to information in columns

user_14824108 0
Viewed 96

I'm working with a chemical dataset and I was wondering about the smartest way to do the following thing. My dataset looks something like this:

formula      Temperature (Kelvin)      (Physical) Property Value
  CO2             298                       5
  CO2             298                       7.6
  CO2             300                       3.2
  NaCl            300                       3.4
  NaCl            296                       1.4
  H2O             298                       7.2
  H2O             298                       8.3
  H2O             293                       6.4
  ZnO             300                       3.10
  ZnO             290                       1.2
  FeO             295                       4.6
  FeO             290                       3.6

Given that Room Temperature := 298K,

what I would like to accomplish is to filter the original dataset in order to have only values reported with Room Temperature when it is available, and if there's no value reported at room temperature, I would like to keep the closest value to the room temperature that is available. According to what I would like to achieve, the sample initial dataset above would become something like

formula      Temperature (Kelvin)      (Physical) Property Value
  CO2             298                       5
  CO2             298                       7.6
  NaCl            300                       3.4
  H2O             298                       7.2
  H2O             298                       8.3
  ZnO             300                       3.10
  FeO             295                       4.6

Maybe I should use a lambda expression?

Any suggestions on how to achieve something like this?

Many thanks,

James

pandas dataframe
4 Answers

We can first filter the "good" ones, i.e., those that have temp 298 K. Then we can sort the remaining rows with respect to their distance to 298 K and then drop the duplicates to keep only the closests. We lastly merge good ones and these:

# room temp in K
rt = 298

# taking those that have `rt` K temp
good_ones = df[df["Temperature (Kelvin)"].eq(rt)]
good_names = good_ones.formula.unique()

# getting others
others = df[~df.formula.isin(good_names)]

# filtering others according to distance to `rt`
sorter = lambda s: s.sub(rt).abs()
others_filtered = (others
                     .sort_values("Temperature (Kelvin)", key=sorter)
                     .drop_duplicates("formula", keep="first"))

# merging them all
result = pd.concat([good_ones, others_filtered]).sort_index(ignore_index=True)

to get

>>> result

  formula  Temperature (Kelvin)  (Physical) Property Value
0     CO2                   298                        5.0
1     CO2                   298                        7.6
2    NaCl                   300                        3.4
3     H2O                   298                        7.2
4     H2O                   298                        8.3
5     ZnO                   300                        3.1
6     FeO                   295                        4.6

There's also the apply way:

def filter_temp(gr):
    # get he temp column and a bool series where it equals `rt`
    temps = gr["Temperature (Kelvin)"]
    rt_temps = temps.eq(rt)
    # does any temp match `rt`?
    if rt_temps.any():
        # then return the locations it matches
        return gr[rt_temps]
    else:
        # otherwise return the closest one
        return gr.loc[[gr.temps.sub(rt).abs().idxmin()]]

result = (df.groupby("formula", as_index=False, group_keys=False)
            .apply(filter_temp).sort_index(ignore_index=True))
user_93321870

The idea here is to group the rows by formula. Each group is then filtered to keep all rows with the required room temperature if any or the unique row with the closest temperature. Let's define this function:

def temperature_filter(df, room_temp, temp_col="Temperature (Kelvin)"):
   if room_temp in df[temp_col].values:
       return df[df[temp_col] == room_temp]
   else:
       return df.loc[[abs(df[temp_col] - room_temp).idxmin()]]

It only remains to apply this function to each group:

ROOM_TEMP = 298
df.groupby("formula", sort=False).apply(temperature_filter, ROOM_TEMP).droplevel("formula")

Note that temperature_filter has been written with clarity in mind, bun can also be included as a lambda function to reach a one-line solution!

user_134103970

How about this:

df['val'] = np.abs(df['Temperature (Kelvin)'] - 298)
df = df.sort_values(['formula', 'val'], ascending=[True, True])
df = df.drop_duplicates(subset='formula', keep="first")

To make sure you don't lose any 298 duplicates another solution is:

df['val'] = np.abs(df['Temperature (Kelvin)'] - 298)
the_298s = df[df['Temperature (Kelvin)'] == 298]
others = df[df['Temperature (Kelvin)'] != 298]
others = others.sort_values(['formula', 'val'], ascending=[True, True])
others = others.drop_duplicates(subset='formula', keep="first")
the_298s_formulas = the_298s.formula.unique()
others = others[~ others.formula.isin(the_298s_formulas)]
final_df = the_298s.append(others)
user_24291870 
def filter_closest_to_rt(df, rt=298):
    df['tmrt'] = df['Temperature (Kelvin)'].sub(rt).abs()
    return df[df['tmrt'] == df.groupby('formula')['tmrt'].transform('min')].drop(columns='tmrt')

filter_closest_to_rt(df)

   formula  Temperature (Kelvin)  (Physical) Property Value
0      CO2                   298                        5.0
1      CO2                   298                        7.6
3     NaCl                   300                        3.4
4     NaCl                   296                        1.4
5      H2O                   298                        7.2
6      H2O                   298                        8.3
8      ZnO                   300                        3.1
10     FeO                   295                        4.6
user_85165130
Related