def test_multiheader_w_unnamed_multiindex_flatten(
self, df_multiheader_w_unnamed_multiindex):
expected = [["", "", "col1 subcol1", "col1 subcol2"]]
assert (util.parse_df_col_names(df_multiheader_w_unnamed_multiindex,
True,
2,
flatten_sep=" ") == expected)
def test_parse_df_col_names_multiheader_w_multiindex(
df_multiheader_w_multiindex
):
expected = [["", "", "col1", "col1"], ["l1", "l2", "subcol1", "subcol2"]]
assert (
util.parse_df_col_names(df_multiheader_w_multiindex, True, 2)
== expected
)
def df_to_sheet(
self,
df,
index=True,
headers=True,
start=(1, 1),
replace=False,
sheet=None,
raw_column_names=[],
freeze_index=False,
freeze_headers=False,
fill_value="",
add_filter=False,
merge_headers=False,
flatten_headers_sep=None,
):
"""
Save a DataFrame into a worksheet.
Parameters
----------
df : DataFrame
the DataFrame to save
index : bool
whether to include the index in worksheet (default True)
headers : bool
whether to include the headers in the worksheet (default True)
start : tuple,str
tuple indicating (row, col) or string like 'A1' for top left
cell (default (1,1))
replace : bool
whether to remove everything in the sheet first (default False)
sheet : str,int,Worksheet
optional, if you want to open or create a different sheet
before saving,
see :meth:`open_sheet <gspread_pandas.spread.Spread.open_sheet>`
(default None)
raw_column_names : list, str
optional, list of columns from your dataframe that you want
interpreted as RAW input in google sheets
freeze_index : bool
whether to freeze the index columns (default False)
freeze_headers : bool
whether to freeze the header rows (default False)
fill_value : str
value to fill nulls with (default '')
add_filter : bool
whether to add a filter to the uploaded sheet (default False)
merge_headers : bool
whether to merge cells in the header that have the same value
(default False)
flatten_headers_sep : str
if you want to flatten your multi-headers to a single row,
you can pass the string that you'd like to use to concatenate
the levels, for example, ': ' (default None)
Returns
-------
None
"""
self._ensure_sheet(sheet)
header = df.columns
index_size = df.index.nlevels
header_size = df.columns.nlevels
if index:
df = df.reset_index()
df = fillna(df, fill_value)
df_list = df.values.tolist()
if headers:
header_rows = parse_df_col_names(df, index, index_size,
flatten_headers_sep)
df_list = header_rows + df_list
start = get_cell_as_tuple(start)
sheet_rows, sheet_cols = self.get_sheet_dims()
req_rows = len(df_list) + (start[ROW] - 1)
req_cols = len(df_list[0]) + (start[COL] - 1) or 1
end = (req_rows, req_cols)
if replace:
# this takes care of resizing
self.clear_sheet(req_rows, req_cols)
else:
# make sure sheet is large enough
self.sheet.resize(max(sheet_rows, req_rows),
max(sheet_cols, req_cols))
if raw_column_names != []:
mapped = map_cols_to_spread(start, end, df.columns.tolist())
raw_columns = [i[0] for i in mapped if i[1] in raw_column_names]
else:
raw_columns = []
self.update_cells(
start=start,
end=end,
vals=[str(val) for row in df_list for val in row],
raw_columns=raw_columns,
)
self.freeze(
None if not freeze_headers else header_size + start[ROW] - 1,
None if not freeze_index else index_size + start[COL] - 1,
)
if add_filter:
self.add_filter((header_size + start[ROW] - 2, start[COL] - 1),
(req_rows, req_cols))
if merge_headers:
self.spread.batch_update({
"requests":
create_merge_headers_request(self.sheet.id, header, start,
index_size)
})
self.refresh_spread_metadata()
def test_multiheader_no_index_flatten(self, df_multiheader):
expected = [["col1 subcol1", "col1 subcol2"]]
assert (util.parse_df_col_names(df_multiheader, False,
flatten_sep=" ") == expected)
def test_multiheader_w_index(self, df_multiheader_w_index):
expected = [["", "col1", "col1"], ["test_index", "subcol1", "subcol2"]]
assert util.parse_df_col_names(df_multiheader_w_index,
True) == expected
def test_multiheader_no_index(self, df_multiheader):
expected = [["col1", "col1"], ["subcol1", "subcol2"]]
assert util.parse_df_col_names(df_multiheader, False) == expected
def test_normal_no_index(self, df):
assert util.parse_df_col_names(df, False) == [["col1", "col2"]]
def test_empty_no_index(self, df_empty):
assert util.parse_df_col_names(df_empty, False) == [[]]
def test_parse_df_col_names_multiheader_w_multiindex(
df_multiheader_w_multiindex):
expected = [['', '', 'col1', 'col1'], ['l1', 'l2', 'subcol1', 'subcol2']]
assert util.parse_df_col_names(df_multiheader_w_multiindex, True,
2) == expected
def test_parse_df_col_names_multiheader_no_index(df_multiheader):
expected = [['col1', 'col1'], ['subcol1', 'subcol2']]
assert util.parse_df_col_names(df_multiheader, False) == expected
def test_parse_df_col_names_normal_no_index(df):
assert util.parse_df_col_names(df, False) == [['col1', 'col2']]
def df_to_sheet(self,
df,
index=True,
headers=True,
start=(1, 1),
replace=False,
sheet=None,
freeze_index=False,
freeze_headers=False,
fill_value='',
start_row=1,
start_col=1):
"""
Save a DataFrame into a worksheet.
:param DataFrame df: the DataFrame to save
:param bool index: whether to include the index in worksheet (default True)
:param bool headers: whether to include the headers in the worksheet (default True)
:param tuple,str start: tuple indicating (row, col) or string like 'A1' for top left
cell
:param bool replace: whether to remove everything in the sheet first (default False)
:param str,int,Worksheet sheet: optional, if you want to open or create a different sheet
before saving,
see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None)
:param bool freeze_index: whether to freeze the index columns (default False)
:param bool freeze_headers: whether to freeze the header rows (default False)
:param str fill_value: value to fill nulls with (default '')
:param int start_row: (DEPRECATED - use `start`) row number for first row of headers or data (default 1)
:param int start_col: (DEPRECATED - use `start`) column number for first column of headers or data (default 1)
"""
if sheet:
self.open_sheet(sheet, create=True)
if not self.sheet:
raise Exception("No open worksheet")
index_size = df.index.nlevels
header_size = df.columns.nlevels
if index:
df = df.reset_index()
df = fillna(df, fill_value)
df_list = df.values.tolist()
if headers:
header_rows = parse_df_col_names(df, index)
df_list = header_rows + df_list
start = self._get_cell_as_tuple(start)
# Check deprecated params.. will be removed in 1.0
if start == (1, 1) and (start_row > 1 or start_col > 1):
deprecate(
"start_col and start_row params are deprecated, use start instead"
)
start = (start_row, start_col)
sheet_rows, sheet_cols = self.get_sheet_dims()
req_rows = len(df_list) + (start[ROW] - 1)
req_cols = len(df_list[0]) + (start[COL] - 1) or 1
if replace:
# this takes care of resizing
self.clear_sheet(req_rows, req_cols)
else:
# make sure sheet is large enough
self.sheet.resize(max(sheet_rows, req_rows),
max(sheet_cols, req_cols))
self.update_cells(start=start,
end=(req_rows, req_cols),
vals=[val for row in df_list for val in row])
self.freeze(None if not freeze_headers else header_size,
None if not freeze_index else index_size)
def df_to_sheet(
self,
df,
index=True,
headers=True,
start=(1, 1),
replace=False,
sheet=None,
freeze_index=False,
freeze_headers=False,
fill_value="",
add_filter=False,
):
"""Save a DataFrame into a worksheet.
Parameters
----------
df : DataFrame
the DataFrame to save
index : bool
whether to include the index in worksheet (default True)
headers : bool
whether to include the headers in the worksheet (default True)
start : tuple,str
tuple indicating (row, col) or string like 'A1' for top left
cell (default (1,1))
replace : bool
whether to remove everything in the sheet first (default False)
sheet : str,int,Worksheet
optional, if you want to open or create a different sheet
before saving,
see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>`
(default None)
freeze_index : bool
whether to freeze the index columns (default False)
freeze_headers : bool
whether to freeze the header rows (default False)
fill_value : str
value to fill nulls with (default '')
add_filter : bool
whether to add a filter to the uploaded sheet (default
False)
Returns
-------
None
"""
if sheet:
self.open_sheet(sheet, create=True)
if not self.sheet:
raise NoWorksheetException("No open worksheet")
index_size = df.index.nlevels
header_size = df.columns.nlevels
if index:
df = df.reset_index()
df = fillna(df, fill_value)
df_list = df.values.tolist()
if headers:
header_rows = parse_df_col_names(df, index, index_size)
df_list = header_rows + df_list
start = get_cell_as_tuple(start)
sheet_rows, sheet_cols = self.get_sheet_dims()
req_rows = len(df_list) + (start[ROW] - 1)
req_cols = len(df_list[0]) + (start[COL] - 1) or 1
if replace:
# this takes care of resizing
self.clear_sheet(req_rows, req_cols)
else:
# make sure sheet is large enough
self.sheet.resize(max(sheet_rows, req_rows),
max(sheet_cols, req_cols))
self.update_cells(
start=start,
end=(req_rows, req_cols),
vals=[str(val) for row in df_list for val in row],
)
self.freeze(
None if not freeze_headers else header_size,
None if not freeze_index else index_size,
)
if add_filter:
self.add_filter(header_size + start[0] - 2, req_rows, start[1] - 1,
req_cols)
