Standardizes Rows of a Pandas.DataFrame Object

Question

Please take a look at this little snippet of code and explicate on whether there are an efficiency enhancements that you'd make for it. It standards a row of a pandas.DataFrame by adding zeros to it so that the total length of the datum is the same as the longest datum in the column

Sample "solubility.csv" file for testing

SMILES,Solubility
[Br-].CCCCCCCCCCCCCCCCCC[N+](C)(C)C,-3.6161271205000003
O=C1Nc2cccc3cccc1c23,-3.2547670983
[Zn++].CC(c1ccccc1)c2cc(C(C)c3ccccc3)c(O)c(c2)C([O-])=O.CC(c4ccccc4)c5cc(C(C)c6ccccc6)c(O)c(c5)C([O-])=O,-3.9244090954
C1OC1CN(CC2CO2)c3ccc(Cc4ccc(cc4)N(CC5CO5)CC6CO6)cc3,-4.6620645831

def generate_standard():
    dataframe = pd.read_csv('solubility.csv', usecols = ['SMILES','Solubility'])
    dataframe['standard'],longest = '',''
    for _ in dataframe['SMILES']:
        if len(str(_)) > len(longest):
            longest = str(_)
        continue
    # index,row in dataframe
    for index,row in dataframe.iterrows():
        # datum from column called 'SMILES'
        smi = row['SMILES']
        # zeros = to difference between longest datum and current datum
        zeros = (0 for x in range(len(longest) - len(str(smi))))
        # makes the zeros into type str
        zeros_as_str = ''.join(str(x) for x in zeros)
        # concatenate the two str
        std = str(smi) + zeros_as_str
        # and place it in a new column called standard at the current index
        dataframe.at[index,'standard'] = std                                                                                                                                
    return dataframe,longest

Answer 1

Ways to improve and optimize:

• function generate_standard is named too general whereas it loads concrete csv file 'solubility.csv' and processes a particular column 'SMILES'.
  A more maintainable and flexible way is to make the function more decoupled and unified such that it accepts an input csv file name and the crucial column name.
  The main purpose can be described as "aligning a specific column's values length by the longest value".
  Let's give a function the appropriate name with the following signature:

  def align_col_length(fname, col_name):
      """Align a specified column's values length by the longest value"""
      df = pd.read_csv(fname)
      ...
  

• finding the longest string value in SMILES column.
  Instead of for loop, since you are dealing with pandas which is powerful enough and allows str accessor on pd.Series objects (points to column values in our case):

Series and Index are equipped with a set of string processing methods that make it easy to operate on each element of the array. Perhaps most importantly, these methods exclude missing/NA values automatically. These are accessed via the str attribute and generally have names matching the equivalent (scalar) built-in string methods

Thus, applying a flexible chain df[col_name].str.len() + pd.Series.idxmax we would be able to get the row label/position of the maximum value.
Then, just easily get the column's longest value by the row position using:

longest = df.loc[df[col_name].str.len().idxmax(), col_name]

• New dataframe['standard'] column with padded values.
  It only remains to apply a flexible one-liner using pd.Series.str.pad routine (to pad the col_name values up to width of len(longest))

---

The final optimized function now becomes a more concise and pandas-flavored:

import pandas as pd

def align_col_length(fname, col_name):
    """Align a specified column's values length by the longest value"""
    df = pd.read_csv(fname)
    longest = df.loc[df[col_name].str.len().idxmax(), col_name]
    df['standard'] = df[col_name].str.pad(len(longest), side='right', fillchar='0')

    return df, longest

---

Testing:

df, longest = align_col_length('solubility.csv', col_name='SMILES')

print('longest SMILE value:', longest)
print('*' * 30)   # just visual separator
print(df)

The output:

longest SMILE value: [Zn++].CC(c1ccccc1)c2cc(C(C)c3ccccc3)c(O)c(c2)C([O-])=O.CC(c4ccccc4)c5cc(C(C)c6ccccc6)c(O)c(c5)C([O-])=O
******************************
                                                                                                     SMILES  ...                                                                                                  standard
0  [Br-].CCCCCCCCCCCCCCCCCC[N+](C)(C)C                                                                       ...  [Br-].CCCCCCCCCCCCCCCCCC[N+](C)(C)C000000000000000000000000000000000000000000000000000000000000000000000
1  O=C1Nc2cccc3cccc1c23                                                                                      ...  O=C1Nc2cccc3cccc1c23000000000000000000000000000000000000000000000000000000000000000000000000000000000000
2  [Zn++].CC(c1ccccc1)c2cc(C(C)c3ccccc3)c(O)c(c2)C([O-])=O.CC(c4ccccc4)c5cc(C(C)c6ccccc6)c(O)c(c5)C([O-])=O  ...  [Zn++].CC(c1ccccc1)c2cc(C(C)c3ccccc3)c(O)c(c2)C([O-])=O.CC(c4ccccc4)c5cc(C(C)c6ccccc6)c(O)c(c5)C([O-])=O
3  C1OC1CN(CC2CO2)c3ccc(Cc4ccc(cc4)N(CC5CO5)CC6CO6)cc3                                                       ...  C1OC1CN(CC2CO2)c3ccc(Cc4ccc(cc4)N(CC5CO5)CC6CO6)cc300000000000000000000000000000000000000000000000000000

[4 rows x 3 columns]

Answer 2

Here are a few comments:

• In pandas it is customary to use df as a generic name for a dataframe. It is well understood and a lot shorter than dataframe, which is why I will use it in the rest of this answer.
• Use _ only as an unused placeholder. If you want to do something with the object, give it a name. Even a single character name like s for a string is more meaningful than _.
• You don't need to initialize variables before using them. longest = '' is unneeded.

• The first for loop could be implemented using the built-in max function using the key parameter:

  longest = max(dataframe['SMILES'], key=len)`.
  

• But pandas has vectorized string methods. Instead of max, you can use

  longest = df.SMILES[df.SMILES.str.len().idxmax()]
  

• Instead of str.joining a generator of zeros, use string multiplication:

  zeros_as_str = "0" * (len(longest) - len(smi))
  

• But what you really want to do is to use str.ljust, which is also vectorized by pandas. This makes the whole function a lot shorter (and probably faster, too, because the iteration does not necessarily happen in Python anymore). You also don't actually need the longest string, you just need its length:

  def generate_standard():
      df = pd.read_csv('solubility.csv', usecols=['SMILES','Solubility'])
      longest = df.SMILES.str.len().max()
      df["standard"] = df.SMILES.str.ljust(longest, "0")
      return df, longest
  

• Python has an official style-guide, PEP8. It recommends using a space after a comma in a tuple, but no spaces around the = when using it for keyword arguments.