I have two dataframe as shown below.
df1:
Date t_factor plan plan_score
0 2020-02-01 5 NaN 0
1 2020-02-02 23 NaN 0
2 2020-02-03 14 start 0
3 2020-02-04 23 start 0
4 2020-02-05 23 start 0
5 2020-02-06 23 NaN 0
6 2020-02-07 30 foundation 0
7 2020-02-08 29 foundation 0
8 2020-02-09 100 NaN 0
9 2020-02-10 38 learn 0
10 2020-02-11 38 learn 0
11 2020-02-12 38 learn 0
12 2020-02-13 70 NaN 0
13 2020-02-14 70 practice 0
14 2020-02-15 38 NaN 0
15 2020-02-16 38 NaN 0
16 2020-02-17 70 exam 0
17 2020-02-18 70 exam 0
18 2020-02-19 38 exam 0
19 2020-02-20 38 NaN 0
20 2020-02-21 70 NaN 0
21 2020-02-22 70 test 0
22 2020-02-23 38 test 0
23 2020-02-24 38 NaN 0
24 2020-02-25 70 NaN 0
25 2020-02-26 70 NaN 0
26 2020-02-27 70 NaN 0
df2:
From to plan score
2020-02-03 2020-02-05 start 20
2020-02-07 2020-02-08 foundation 25
2020-02-10 2020-02-12 learn 10
2020-02-14 2020-02-16 practice 20
2020-02-15 2020-02-21 exam 30
2020-02-20 2020-02-23 test 10
Explanation:
I have loaded the both data frame and I would like to export this dataframes as 1 excel file with Sheet1 = df1 and Sheet2 = df2.
I tried below.
import pandas as pd
from pandas import ExcelWriter
def save_xls(list_dfs, xls_path):
with ExcelWriter(xls_path) as writer:
for n, df in enumerate(list_dfs):
df.to_excel(writer,'sheet%s' % n)
writer.save()
save_xls([df1, df2], os.getcwd())
And it is giving me following error.
---------------------------------------------------------------------------
OptionError Traceback (most recent call last)
~/admvenv/lib/python3.7/site-packages/pandas/io/excel/_base.py in __new__(cls, path, engine, **kwargs)
630 try:
--> 631 engine = config.get_option(f"io.excel.{ext}.writer")
632 if engine == "auto":
~/admvenv/lib/python3.7/site-packages/pandas/_config/config.py in __call__(self, *args, **kwds)
230 def __call__(self, *args, **kwds):
--> 231 return self.__func__(*args, **kwds)
232
~/admvenv/lib/python3.7/site-packages/pandas/_config/config.py in _get_option(pat, silent)
101 def _get_option(pat, silent=False):
--> 102 key = _get_single_key(pat, silent)
103
~/admvenv/lib/python3.7/site-packages/pandas/_config/config.py in _get_single_key(pat, silent)
87 _warn_if_deprecated(pat)
---> 88 raise OptionError(f"No such keys(s): {repr(pat)}")
89 if len(keys) > 1:
OptionError: "No such keys(s): 'io.excel..writer'"
During handling of the above exception, another exception occurred:
ValueError Traceback (most recent call last)
<ipython-input-16-80bc8a5d0d2f> in <module>
----> 1 save_xls([df1, df2], os.getcwd())
<ipython-input-15-0d1448e7aea8> in save_xls(list_dfs, xls_path)
1 def save_xls(list_dfs, xls_path):
----> 2 with ExcelWriter(xls_path) as writer:
3 for n, df in enumerate(list_dfs):
4 df.to_excel(writer,'sheet%s' % n)
5 writer.save()
~/admvenv/lib/python3.7/site-packages/pandas/io/excel/_base.py in __new__(cls, path, engine, **kwargs)
633 engine = _get_default_writer(ext)
634 except KeyError:
--> 635 raise ValueError(f"No engine for filetype: '{ext}'")
636 cls = get_writer(engine)
637
ValueError: No engine for filetype: ''
