This page was generated from docs/Examples/Exporting_to_Excel.ipynb. Interactive online version: .

Exporting to Excel

This workbook demontrates how to export various calculations to Excel
You can download the spreadsheet you need here: https://github.com/PennyWieser/Thermobar/blob/main/docs/Examples/Five_min_intro.xlsx

Pip install Thermobar if you havent already done this

[1]:

#!pip install Thermobar

Importing various python things

[2]:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import Thermobar as pt

Lets load in the Cpx-Liq data from the Into example

[3]:

out=pt.import_excel('Five_min_intro.xlsx', sheet_name="Sheet1")
my_input=out['my_input']
Liqs=out['Liqs']
Cpxs=out['Cpxs']
my_input=out['my_input']

Example 1: Lets calculate Cpx-Liq temps, which returns a pandas series (single column of numbers)

[4]:

# Here performing calculations at 5 kbar
Temp_eq33_5kbar=pt.calculate_cpx_liq_temp(cpx_comps=Cpxs, liq_comps=Liqs,
                                          equationT="T_Put2008_eq33", P=5)

1a - Append this column onto the end of the dataframe

First, we duplicate the user-inputted data (stored in my_input), then append a new column on. By default, it goes at the end.

[5]:

# First, make a copy
my_input_c1=my_input.copy()
# Allocate a new column to this dataframe, LHS give whatever you to call it, RHS is the variable
my_input_c1['Temp_eq33_5kbar']=Temp_eq33_5kbar
# Save to an Excel sheet called "Calc1" using the pandas to excel function
my_input_c1.to_excel('Calc1.xlsx')

1b - Append column in a specific position

Here, the first number specifies the position where we append the new column (here column 0, so at the start)

[6]:

# First, make a copy
my_input_c1b=my_input.copy()
# Insert a new column, position, column name, then variable name you want to append.
my_input_c1b.insert(0, 'Temp_eq33_5kbar', Temp_eq33_5kbar)
# Save to an Excel sheet called "Calc1" using the pandas to excel function
my_input_c1.to_excel('Calc1.xlsx')

Example 2: What happen if our function outputs a pandas dataframe? e.g., if we perform the same calculation using “Eq_Tests=True”

[7]:

# Here performing calculations at 5 kbar
Temp_eq33_5kbar_Eq_tests=pt.calculate_cpx_liq_temp(cpx_comps=Cpxs, liq_comps=Liqs,
                         equationT="T_Put2008_eq33", P=5, eq_tests=True)
Temp_eq33_5kbar_Eq_tests.head()

Using Fe3FeT from input file to calculate Kd Fe-Mg

[7]:

	P_kbar_calc	T_K_calc	Eq Tests Neave2017?	Delta_Kd_Put2008	Delta_Kd_Mas2013	Delta_EnFs_Mollo13	Delta_EnFs_Put1999	Delta_CaTs_Put1999	Delta_DiHd_Mollo13	Delta_DiHd_Put1999	...	Delta_EnFs_I_M_Mollo13	CaTs_Pred_Put1999	Delta_CaTs_I_M_Put1999	Delta_CrCaTs_I_M_Put1999	CaTi_Pred_Put1999	Delta_CaTi_I_M_Put1999	Jd_Pred_Put1999	Delta_Jd_Put1999	Delta_Jd_I_M_Put1999
0	5	1364.712867	False	0.030886	0.104181	0.017331	0.012065	0.017937	0.050264	0.124335	...	-0.017331	0.012438	-0.017937	0.009562	0.044908	0.003894	0.016184	0.000871	0.000871
1	5	1304.755285	True	0.018153	0.107137	0.017648	0.006548	0.025331	0.039975	0.267199	...	-0.017648	0.011486	-0.025331	0.004122	0.060424	0.015486	0.017430	0.000100	0.000100
2	5	1272.453442	False	0.076685	0.189818	0.030151	0.042149	0.076839	0.072766	0.358640	...	0.030151	0.015046	-0.076839	0.003245	0.030850	0.040817	0.018332	0.000247	0.000247
3	5	1305.686881	False	0.015674	0.116551	0.023393	0.010385	0.033121	0.020541	0.259781	...	-0.023393	0.012706	-0.033121	0.003909	0.053902	0.012705	0.019333	0.002998	0.002998
4	5	1252.876983	False	0.004507	0.113085	0.030453	0.035629	0.033377	0.006182	0.300208	...	-0.030453	0.009733	-0.033377	0.001315	0.053721	0.017768	0.027954	0.005739	0.005739

5 rows × 130 columns

Example 2a: Appending a single column

We can export this in a number of ways, if we just want to append a single column (say T_K_calc) onto all our input columns, we can allocate a single column like before

[8]:

# First, make a copy
my_input_c2=my_input.copy()
# Allocate a new column to this dataframe, LHS give whatever you to call it,
#RHS is the variable, but now your must specify the colum
my_input_c2['Temp_eq33_5kbar']=Temp_eq33_5kbar_Eq_tests['T_K_calc']
# Save to an Excel sheet called "Calc1" using the pandas to excel function
my_input_c2.to_excel('Calc2.xlsx')

Example 2b: Common Mistake

If you hadn’t specified a column, python would give you an error, as you can’t allocate a dataframe to a column

[9]:

my_input_c2['Temp_eq33_5kbar']=Temp_eq33_5kbar_Eq_tests

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Input In [9], in <module>
----> 1 my_input_c2['Temp_eq33_5kbar']=Temp_eq33_5kbar_Eq_tests

File ~\anaconda3\lib\site-packages\pandas\core\frame.py:3602, in DataFrame.__setitem__(self, key, value)
   3600     self._setitem_array(key, value)
   3601 elif isinstance(value, DataFrame):
-> 3602     self._set_item_frame_value(key, value)
   3603 elif (
   3604     is_list_like(value)
   3605     and not self.columns.is_unique
   3606     and 1 < len(self.columns.get_indexer_for([key])) == len(value)
   3607 ):
   3608     # Column to set is duplicated
   3609     self._setitem_array([key], value)

File ~\anaconda3\lib\site-packages\pandas\core\frame.py:3729, in DataFrame._set_item_frame_value(self, key, value)
   3727 len_cols = 1 if is_scalar(cols) else len(cols)
   3728 if len_cols != len(value.columns):
-> 3729     raise ValueError("Columns must be same length as key")
   3731 # align right-hand-side columns if self.columns
   3732 # is multi-index and self[key] is a sub-frame
   3733 if isinstance(self.columns, MultiIndex) and isinstance(
   3734     loc, (slice, Series, np.ndarray, Index)
   3735 ):

ValueError: Columns must be same length as key

Example 2c - Appending multiple columns

You can do the same above, and append all the columns you want

[11]:

# First, make a copy
my_input_c3=my_input.copy()
# Allocate a new column to this dataframe, LHS give whatever you to call it,
#RHS is the variable, but now your must specify the colum
my_input_c3['Temp_eq33_5kbar']=Temp_eq33_5kbar_Eq_tests['T_K_calc']
# Append a second column
my_input_c3['Eq Tests Neave2017?']=Temp_eq33_5kbar_Eq_tests['Eq Tests Neave2017?']
# Save to an Excel sheet called "Calc1" using the pandas to excel function
my_input_c3.to_excel('Calc3.xlsx')

Example 2d - Writing the entire dataframe to excel

Here, we save the entire variable to excel.

[12]:

Temp_eq33_5kbar_Eq_tests.to_excel('Calc4.xlsx')

Example 3 - Combining seperate dataframe

Some Thermobar functions return a dataframe that doesnt have any overlap with the input dataframe, that you may wish to merge into your original dataframe
You have to be careful here, that you haven’t reindexed your dataframes (e.g. removed certain rows), and that you aren’t merging dataframes with duplicated columns (e.g., dont merge your my_input with something like the equilibrium test dataframe above where your Cpx comps will be duplicated)

Example 3a - Here, we first calculate equilibrium olivine contents for the liquid composition

[13]:

Eq_Ol_Roeder=pt.calculate_eq_ol_content(liq_comps=Liqs, Kd_model="Roeder1970", Fe3Fet_Liq=0.2)
Eq_Ol_Roeder

[13]:

	Mg#_Liq_Fe2	Mg#_Liq_Fet	Eq Fo (Roeder, Kd=0.3)	Eq Fo (Roeder, Kd=0.33)	Eq Fo (Roeder, Kd=0.27)
0	0.603640	0.549218	0.835432	0.821906	0.849411
1	0.560523	0.505036	0.809576	0.794448	0.825292
2	0.581111	0.526025	0.822198	0.807834	0.837081
3	0.580344	0.525239	0.821737	0.807345	0.836651
4	0.494353	0.438875	0.765197	0.747642	0.783596

Now, we can use the pandas concat function to merge this with our input spreadsheet

[14]:

Combined=pd.concat([Eq_Ol_Roeder, my_input], axis=1)
Combined

[14]:

	Mg#_Liq_Fe2	Mg#_Liq_Fet	Eq Fo (Roeder, Kd=0.3)	Eq Fo (Roeder, Kd=0.33)	Eq Fo (Roeder, Kd=0.27)	Sample_ID	SiO2_Liq	TiO2_Liq	Al2O3_Liq	FeOt_Liq	...	SiO2_Cpx	TiO2_Cpx	Al2O3_Cpx	FeOt_Cpx	MnO_Cpx	MgO_Cpx	CaO_Cpx	Na2O_Cpx	Cr2O3_Cpx
0	0.603640	0.549218	0.835432	0.821906	0.849411	Sisson, T.W., Grove, T.L. (1993)	51.1	0.93	17.5	8.91	...	51.5	0.50	3.70	5.18	0.09	15.8	22.8	0.24	0.66
1	0.560523	0.505036	0.809576	0.794448	0.825292	Sisson, T.W., Grove, T.L. (1993)	51.5	1.19	19.2	8.70	...	50.3	0.73	4.12	5.83	0.00	15.0	22.7	0.24	0.28
2	0.581111	0.526025	0.822198	0.807834	0.837081	Sisson, T.W., Grove, T.L. (1993)	59.1	0.54	19.1	5.22	...	47.3	1.75	7.85	6.51	0.14	13.1	22.5	0.25	0.22
3	0.580344	0.525239	0.821737	0.807345	0.836651	Sisson, T.W., Grove, T.L. (1993)	52.5	0.98	19.2	8.04	...	51.1	0.63	4.41	5.66	0.13	15.6	22.6	0.23	0.27
4	0.494353	0.438875	0.765197	0.747642	0.783596	Sisson, T.W., Grove, T.L. (1993)	56.2	0.34	20.4	5.88	...	51.0	0.56	4.14	7.33	0.20	14.4	22.4	0.31	0.09

5 rows × 28 columns

We can now save this combined dataframe to excel

[15]:

Combined.to_excel('Calcs5.xlsx')

[ ]: