def test_value_error(self):
wrong_data_type = dict(foo='bar')
with pytest.raises(ValueError) as err:
helpers._make_list_of_lists(wrong_data_type, index=False)
assert err.match(
'Input data must be a pandas.DataFrame, a list of dicts, or a list of lists'
)
def insert_data(self, data, index=True, autoformat=True):
"""Overwrite all data in this tab with the provided data.
All existing data in the tab will be removed, even if it might not have been overwritten
(for example, if there is 4x2 data already in the tab and only 2x2 data is being inserted).
If the dimensions of `data` are larger than the tab's current dimensions,
the tab will automatically be resized to fit it.
Args:
data (pandas.DataFrame or dict or list): The data to be uploaded, formatted as a
pandas.DataFrame, a dict of lists, or a list of lists
index (bool): If `data` is a pandas.DataFrame, whether to upload the index as well
Returns:
None
"""
# Convert everything to lists of lists, which Google Sheets requires
headers, values = helpers._make_list_of_lists(data, index)
values = headers + values # Include headers for inserts but not for appends
self.clear_data()
values = helpers._convert_nan_and_datelike_values(values)
body = {'values': values}
self.sheets_svc.values().update(spreadsheetId=self.workbook.file_id,
range=self.tabname,
valueInputOption='USER_ENTERED',
body=body).execute()
if autoformat:
self.autoformat(len(headers))
self._update_tab_properties()
def append_data(self, data, index=True, autoformat=True):
"""Append data to the existing data in this tab.
If the new data exceeds the tab's current dimensions the tab will be resized to
accommodate it. Data headers will not be included among the appended data as they are
assumed to already be among the existing tab data.
If the dimensions of `data` are larger than the tab's current dimensions,
the tab will automatically be resized to fit it.
Args:
data (pandas.DataFrame or dict or list): The data to be uploaded, formatted as a
pandas.DataFrame, a dict of lists, or a list of lists
index (bool): If `data` is a pandas.DataFrame, whether to upload the index as well
Returns:
None
"""
# Convert everything to lists of lists, which Google Sheets requires
headers, values = helpers._make_list_of_lists(data, index)
values = helpers._convert_nan_and_datelike_values(values)
body = {'values': values}
self.sheets_svc.values().append(spreadsheetId=self.workbook.file_id,
range=self.tabname,
valueInputOption='USER_ENTERED',
body=body).execute()
if autoformat:
self.autoformat(len(headers))
self._update_tab_properties()
def test_list_of_dicts(self):
data = [
{
'name': 'bubbles',
'age': 3
},
{
'name': 'fishsticks',
'age': 2.5
},
{
'name': 'Mr. Whiskers',
'age': 6
},
]
headers, values = helpers._make_list_of_lists(data, index=False)
assert len(headers) == 1
assert set(headers[0]) == set(['name', 'age'])
expected_values = [
['bubbles', 3],
['fishsticks', 2.5],
['Mr. Whiskers', 6],
]
reversed_expected_values = [row[::-1] for row in expected_values]
# We have to check both orderings since dict keys aren't ordered
assert values == expected_values or values == reversed_expected_values
def test_df_named_column_multiindex_index_true(self):
headers = list(
map(list,
zip(('index0', 'index0'),
*self.df_col_multiidx_named.columns)))
values = [[i] + row for i, row in enumerate(self.data)]
assert helpers._make_list_of_lists(self.df_col_multiidx_named,
index=True) == (headers, values)
def test_df_unnamed_row_multiindex_index_true(self):
headers = [['index0', 'index1'] +
list(self.df_row_multiidx_unnamed.columns)]
values = [
list(i) + j
for i, j in zip(self.df_row_multiidx_unnamed.index, self.data)
]
assert helpers._make_list_of_lists(self.df_row_multiidx_unnamed,
index=True) == (headers, values)
def test_df_named_row_and_column_multiindexes_index_true(self):
add_named_entries = lambda row: list(self.df_dual_multiidx_named.index.
names) + list(row)
headers = list(
map(add_named_entries, zip(*self.df_dual_multiidx_named.columns)))
values = [
list(i) + j
for i, j in zip(self.df_dual_multiidx_named.index, self.data)
]
assert helpers._make_list_of_lists(self.df_dual_multiidx_named,
index=True) == (headers, values)
def test_df_named_row_and_column_multiindexes_index_false(self):
headers = list(map(list, zip(*self.df_dual_multiidx_named.columns)))
values = self.data
assert helpers._make_list_of_lists(self.df_dual_multiidx_named,
index=False) == (headers, values)
def test_df_named_row_multiindex_false(self):
headers = [list(self.df_row_multiidx_named.columns)]
values = self.data
assert helpers._make_list_of_lists(self.df_row_multiidx_named,
index=False) == (headers, values)
def test_df_simple_row_index_index_false(self):
headers = [list(self.df_index.columns)]
values = self.data
assert helpers._make_list_of_lists(self.df_index,
index=False) == (headers, values)
def test_df_simple_row_index_index_true(self):
headers = [['myindex'] + list(self.df_index.columns)]
values = [[i] + j for i, j in zip(self.df_index.index, self.data)]
assert helpers._make_list_of_lists(self.df_index,
index=True) == (headers, values)
def test_df_no_index_index_false(self):
expected = ([self.columns], self.data)
assert helpers._make_list_of_lists(self.df_noindex,
index=False) == expected
def test_df_no_index_index_true(self):
headers = [['index0'] + self.columns]
values = [[i] + row for i, row in enumerate(self.data)]
assert helpers._make_list_of_lists(self.df_noindex,
index=True) == (headers, values)
def test_list_of_lists(self):
data = [[None, 'foo'], [1, 'bar']]
headers, values = helpers._make_list_of_lists(data, index=False)
assert headers == [] and values == [[None, 'foo'], [1, 'bar']]
