Is there a way to apply a function to all the values in a column and then replace the column values with the new values?

Question

Essentially I'm reading a csv file containing a bunch of chemical compounds and I'm trying to apply the pubchempy.get_properties function to the CID column that contains the CID (identifier) number of each of the chemical compounds.

However, I can't get it to work. The weird thing is I'm not getting any errors (I did get some initially and kind of tinkered with it to try to fix them), it's just that the column(CID) values are remaining the same and are not changing after the function runs.

This is the code I've written for now:

import pandas as pd
import pubchempy

df = pd.read_csv("Chemical datavase.tsv.txt", sep="\t")
pd.set_option('display.width', 1000)
pd.set_option('display.max_columns', 12)
pd.set_option('display.max_rows', 12)

from pubchempy import get_compounds, Compound, get_properties

if (df['CID']>0).all():
    df['CID'] = pubchempy.get_properties(df['CID'], 'MolecularWeight')

print(df['CID'])

I'd appreciate any help on this!

Answer 1

IIUC, you can try:

from pubchempy import get_properties

if (df['CID']>0).all():
    df['CID'] = df['CID'].map(lambda x: get_properties(x, 'MolecularWeight'))

Or without the test, and the correct parameters:

df['CID'] = df['CID'].map(lambda x: get_properties(identifier=x, properties='MolecularWeight') if x>0 else pd.NA)