How do I create a spreadsheet containing the coordinates and predicted bond angles, molecular properties, torsion properties, etc of a molecule?

On a previous answer here (https://chemistry.stackexchange.com/a/30841/876) I was informed in the comments that OpenBabel & Python terminal could be used to print the data contained in the property matrices of Avogadro (more info can be found here How do I print property matrices in Avogadro?). I should be clear, however, most of the files I want to be able to do this for are CIF files where the bond angles and lengths are not explicitly mentioned in the files, so these properties may need to be predicted by the software. So I felt I couldn't be the only person interested in this so I thought I'd ask this question on this site.

I would like any answer to be suitable for those operating on Windows machines, if possible. I have Python & OpenBabel installed on my 64 bit Windows 7 PC, so I believe this shouldn't be a major issue, hopefully.

You want the Open Babel "report" format, e.g.:

obabel benzene.cml -oreport

FILENAME: benzene.g03
FORMULA: C6H6
MASS: 78.1118
EXACT MASS: 78.0469502
INTERATOMIC DISTANCES

C   1      C   2      C   3      C   4      C   5      C   6
------------------------------------------------------------------
C   1    0.0000
C   2    1.3975     0.0000
C   3    2.4207     1.3975     0.0000
C   4    2.7952     2.4205     1.3976     0.0000
C   5    2.4205     2.7948     2.4205     1.3975     0.0000
C   6    1.3976     2.4205     2.7952     2.4207     1.3975     0.0000
H   7    1.0841     2.1549     3.4030     3.8793     3.4029     2.1549
H   8    2.1548     1.0841     2.1548     3.4028     3.8789     3.4028
H   9    3.4030     2.1549     1.0841     2.1549     3.4029     3.8793
H  10    3.8793     3.4029     2.1549     1.0841     2.1549     3.4030
H  11    3.4028     3.8789     3.4028     2.1548     1.0841     2.1548
H  12    2.1549     3.4029     3.8793     3.4030     2.1549     1.0841

H   7      H   8      H   9      H  10      H  11      H  12
------------------------------------------------------------------
H   7    0.0000
H   8    2.4816     0.0000
H   9    4.2984     2.4816     0.0000
H  10    4.9634     4.2982     2.4817     0.0000
H  11    4.2982     4.9630     4.2982     2.4816     0.0000
H  12    2.4817     4.2982     4.9634     4.2984     2.4816     0.0000

ATOMIC CHARGES
C   1   -0.1860370000
C   2   -0.1859930000
C   3   -0.1860370000
C   4   -0.1860370000
C   5   -0.1859930000
C   6   -0.1860370000
H   7    0.1860240000
H   8    0.1860180000
H   9    0.1860240000
H  10    0.1860240000
H  11    0.1860180000
H  12    0.1860240000

BOND ANGLES
6    1    7  Car  Car    H    120.003
2    1    6  Car  Car  Car    119.993
2    1    7  Car  Car    H    120.004
1    2    3  Car  Car  Car    120.014
1    2    8  Car  Car    H    119.993
3    2    8  Car  Car    H    119.993
4    3    9  Car  Car    H    120.003
2    3    4  Car  Car  Car    119.993
2    3    9  Car  Car    H    120.004
5    4   10  Car  Car    H    120.004
3    4    5  Car  Car  Car    119.993
3    4   10  Car  Car    H    120.003
4    5   11  Car  Car    H    119.993
6    5   11  Car  Car    H    119.993
4    5    6  Car  Car  Car    120.014
5    6   12  Car  Car    H    120.004
1    6    5  Car  Car  Car    119.993
1    6   12  Car  Car    H    120.003

TORSION ANGLES
11    5    4   10     -0.000
11    5    4    3   -180.000
6    5    4   10   -180.000
6    5    4    3     -0.000
11    5    6   12      0.000
11    5    6    1   -180.000
4    5    6   12   -180.000
4    5    6    1     -0.000
5    4    3    9   -180.000
5    4    3    2     -0.000
10    4    3    9     -0.000
10    4    3    2   -180.000
5    6    1    7   -180.000
5    6    1    2     -0.000
12    6    1    7     -0.000
12    6    1    2   -180.000
6    1    2    3     -0.000
6    1    2    8   -180.000
7    1    2    3   -180.000
7    1    2    8     -0.000
4    3    2    1      0.000
4    3    2    8   -180.000
9    3    2    1   -180.000
9    3    2    8     -0.000


UPDATE:

You mentioned wanting a CSV-parsable output, e.g., from Python.

I wrote up a fairly quick tool for this molcsv.py

It should be pretty obvious about modifying as needed in the different for loops.

• Thanks, one thing though is there a way to create a CSV file of this, in an automated manner (i.e., without me having to copy-paste everything)? May 11 '15 at 15:37
• Yes, I can probably whip up a script, it'll probably be tonight though. May 11 '15 at 16:00
• @BrentonHorne OK, give it a shot. It's not entirely obvious how you want the output but with a bit of Python it's easy for you to modify as needed. May 14 '15 at 18:38
• OK, please forgive my ignorance when it comes to programming & Avogadro but how do I use this to create a csv? Do I like open a CIF in Avogadro & run the file from the Python terminal therein? If so where do I place the file such that the avogadro python terminal will recognize it? May 14 '15 at 23:01
• This is not an Avogadro script. If you have Open Babel and Python/Pybel support installed, you'd run python molcsv.py file.cif from the command-line. If you have other questions, it's probably easier to handle by e-mail. May 14 '15 at 23:36

Load your CIF file in PLATON and run CALC-GEO

In the *.LIS file generated, you'll find all the tables that you need, namely

• Bond Lengths (Angstrom)
• Bond/Valence Angles (Degrees)
• Torsion/Dihedral Angles (Deg.)

You can still use the regex module of Python to parse the data,\s*(?P<atoms>([A-Z][a-z]?$$\d*\w$$\s*){4})\s*(?P<value>-?\d*\.\d$$\d+$$) will split entries in the torsion table into two named groups, which you can further process and write to a CSV file.

EDIT

I think that OpenBabel is an excellent tool! In this case, however, I suggest to use the approach above, rather than parsing the OpenBabel report file for the following reasons:

1. In a couple of cases, I noticed that the report generator of OpenBabel had some problems with CIF files that were checked both using the internal check routines of PLATON, as well as CheckCIF, an online tool provided by the IUCr, and were found to be valid.
2. In the table for the dihedral angles, the OpenBabel report does not use descriptors like C(5), H(10A) to denote atoms in a dihedral, but uses the atom indices. If you would want to write the decriptors to a CSV file, you would have to look them up at another table.

If you can live with the indices, the following Python snippet should suffice:

#!/usr/bin/env python3

import csv
import argparse

def get_torsions_from_report(report_path):
'''Read all rows with he indices to four consecutive atoms and
the corresponding dihedral angle from an OpenBabel report file.'''
try:
torsion_list = []
with open(report_path) as infile:
for line in infile:
if line.startswith('TORSION ANGLES'):
break

for line in infile:
if line.strip():
torsion_list.append(line.split())

except IOError:
print(e)

def write_torsions_to_csv(csv_path, torsion_list):
'''Write torsion data to CSV.'''
with open(csv_path, 'wt') as fout:
writer = csv.writer(fout)
writer.writerow(('Atom1', 'Atom 2', 'Atom 3', 'Atom 4', 'Torsion'))
writer.writerows(torsion_list)

if __name__ == '__main__':
from OpenBabel report and write them to a CSV file.')
help="OpenBabel report file")
help="CSV file")

args = parser.parse_args()
torsions = get_torsions_from_report(args.infile)
write_torsions_to_csv(args.outfile, torsions)

• An outstanding answer; isn't it CALC GEOM PLATON command instead of CALC-GEO? Oct 4 '17 at 14:49