Create a table in excel using data from Python2.7

Question

I am trying to export my data to an excel file. My problem is that my data comes from a itertools.combination and I cannot figure out how to export all of the combinations and their data into an excel file

My subset comes from an input file that looks like

carbon_1    xcoordinate    ycoordinate   zcoordinate

For n number of carbons

For reference, my code is

subset = cmd[['carbon','x_coord', 'y_coord','z_coord']]
coordinate_values = [tuple(x) for x in subset.values]

atoms = coordinate_values
atomPairs = itertools.combinations(atoms, 2)

atoms_dict = {k:"carbon_{}".format(i) for i,k in enumerate(atoms,1)}
    print("Computing distance between {} and {}".format(atoms_dict[pair[0]],atoms_dict[pair[1]]))

I then have a simple definition to calculate distance, d, and the end of my code is

if d >= 1.4 and d < 1.6:
    bond = 1
elif d >= 1.2 and d < 1.4:
    bond = 2
elif d >= 1 and d < 1.2:
    bond = 3
else:
    bond = 0

The output of my code for each combination is

Computing distance between carbon_1 and carbon_2
2

I would like to be able to export the 'bond' for each combination into an excel sheet. The excel sheet would be able to grow if I add another coordinate to the combination, aka another 'carbon'. The excel sheet would look something like this:

"Carbon pair"                 "Bond"
Carbon 1 to Carbon 2             2
Carbon 2 to Carbon 3             3

I am very new to Python and I am unsure how to export anything into excel, let alone create a table in excel that will satisfy these conditions. Suggestions would be greatly appreciated.

openpyxl is a very good python module: openpyxl.readthedocs.io/en/default — ryugie
– ryugie, Commented Mar 24, 2017 at 21:08

T.W. · Accepted Answer · 2017-03-25 11:37:39Z

1

At first, it seems, that you currently not need any excel features so I guess you can use a csv-file as export. This is much easier to use. See Python 2.7 CSV doc for more information. csv-files can easily be imported in excel.

If you really need to create an excel file, then you should take a look at XlsxWriter. I've used it some time ago and it is really easy to use.

To be able to grow your table, you should think about using a matrix to store your date. So instead of using something like this:

"Carbon pair"                 "Bond"
Carbon 1 to Carbon 2             2
Carbon 2 to Carbon 3             3

a better way to grow your carbon database is this attempt:

Bond     Carbon1     Carbon2     Carbon3
Carbon1     -           5           1
Carbon2     3           -           4
Carbon3     5           1           -

If you use this matrix notation, then you will be able to access your data faster.

edited Mar 25, 2017 at 11:37

answered Mar 24, 2017 at 21:30

T.W.

1493 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank · Accepted Answer · 2017-03-24 21:13:05Z

0

I'm not sure how to help you directly, but I suggest checking out the XLWT module. Documentation can be hard to find, but some Google searches for "XLWT" and "Python Excel" should lead you to some explanation.

answered Mar 24, 2017 at 21:13

Frank

7642 gold badges13 silver badges34 bronze badges

Comments

T.W. · Accepted Answer · 2017-03-25 02:21:54Z

NOTE : This question is open-ended so answers are likely to be experience based. There is no one solution better than others.

I use openpyxl for excel work as I can save files in latest xlsx format.

You can install it using

pip install openpyxl

Since I dont have all the code, I am assuming you will be able to get the needed data in a list of list , or some format that is comma seperated and that can be manipulated.

For example, I have data in a list of list as below. Header information in:

headers = ["Carbon pair","Bond"]

and carbon data in

carbon_data = [["Carbon 1 to Carbon 2",2],["Carbon 2 to Carbon 3",3]]

Once you have that, you can create a new workbook and work with data just as you would in excel. Demo Code:

from openpyxl.workbook import Workbook

headers = ["Carbon pair","Bond"]   

carbon_data= [["Carbon 1 to Carbon 2",2],["Carbon 2 to Carbon 3",3]]
dest_filename = 'carbon_data.xlsx'

#Open new workbook
wb = Workbook()

ws1 = wb.active
ws1.title = "carbon_pairs_bonds"

#Add headers
ws1.append(headers)

#Add data
for row in carbon_data:
    ws1.append(row)

#Save workbook
wb.save(filename = dest_filename)

Once you have created a file , you can add , manipulate columns, rows just as you would in excel. The openpyxl site has detailed tutorial and examples. There is a openpyxl tag within SO that you can refer to for questions.

Hope this helps. carbon_data.xlsx

Collectives™ on Stack Overflow

Create a table in excel using data from Python2.7

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related