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

Python Notebook Download

Exporting to Excel

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 CrCaTs_Pred_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.0 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.0 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.0 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.0 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.0 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 K2O_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 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 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 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 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 0.09

5 rows × 28 columns

  • We can now save this combined dataframe to excel

[15]:
Combined.to_excel('Calcs5.xlsx')
[ ]: