def open_or_create_sheet(self, sheet): """ DEPRECATED, use the `create` param for `open_sheet` instead Open a worksheet. If it doesn't exist, create it first. """ deprecate( "open_or_create_sheet is deprecated, pass create=True to open_sheet" ) self.open_sheet(sheet, True)
def sheet_to_df(self, index=1, headers=1, header_rows=1, start_row=1, sheet=None): """ Pull a worksheet into a DataFrame. :param int index: col number of index column, 0 or None for no index (default 1) :param int headers: (DEPRECATED - use `header_rows`) number of rows that represent headers (default 1) :param int header_rows: number of rows that represent headers (default 1) :param int start_row: row number for first row of headers or data (default 1) :param str,int sheet: optional, if you want to open a different sheet first, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) :returns: a DataFrame with the data from the Worksheet """ if sheet: self.open_sheet(sheet) if not self.sheet: raise Exception("No open worksheet") if headers != 1: deprecate("headers has been deprecated, use header_rows instead") header_rows = headers vals = self._retry_get_all_values() vals = self._fix_merge_values(vals)[start_row - 1:] col_names = parse_sheet_headers(vals, header_rows) # remove rows where everything is null, then replace nulls with '' df = pd.DataFrame(vals[header_rows or 0:])\ .replace('', np.nan)\ .dropna(how='all')\ .fillna('') if col_names is not None: if len(df.columns) == len(col_names): df.columns = col_names elif len(df) == 0: # if we have headers but no data, set column headers on empty DF df = df.reindex(columns=col_names) else: raise Exception( "Column headers don't match number of data columns") return parse_sheet_index(df, index)
def __init__( self, user_creds_or_client, spread, sheet=None, config=None, create_spread=False, create_sheet=False, scope=default_scope, user="******", credentials=None, client=None, ): if user_creds_or_client is not None: deprecate( "user_creds_or_client will be removed in v2. Use optional 'user'," "'credentials' or 'client' params instead and pass None for " "user_creds_or_client to prevent this message. If you only use" "one set of creds, move ``~/.config/gspread_pandas/creds/<user>`` to " "``~/.config/gspread_pandas/creds/default`` to avoid having to pass" "the 'user' param every time" ) if isinstance(user_creds_or_client, Client): self.client = user_creds_or_client elif isinstance(user_creds_or_client, (basestring, OAuth2Credentials)): self.client = Client( user_creds_or_client, config, scope, _deprecation_notice=False ) else: raise TypeError( "user_creds_or_client needs to be a string, " "OAuth2Credentials, or Client object" ) else: if isinstance(client, Client): self.client = client else: self.client = Client( user, config, scope, credentials, _deprecation_notice=False ) monkey_patch_request(self.client) self.open(spread, sheet, create_sheet, create_spread)
def _login(self, user_or_creds, config, credentials, _deprecation_notice): if isinstance(credentials, OAuth2Credentials): creds = credentials elif isinstance(user_or_creds, OAuth2Credentials): if _deprecation_notice: deprecate( "user_or_creds will be changed to 'user' in v2, please use " "'credentials'" ) creds = user_or_creds elif isinstance(user_or_creds, basestring): if _deprecation_notice: deprecate( "user_or_creds will become optional and be renamed to 'user' in v2" ) creds = get_creds(user_or_creds, config, self.scope) else: raise TypeError( "user_or_creds needs to be a string or OAuth2Credentials object" ) super().__init__(creds) super().login()
def test_deprecate(recwarn): with pytest.deprecated_call() as calls: util.DEPRECATION_WARNINGS_ENABLED = False util.deprecate("") assert len(calls) == 1 util.DEPRECATION_WARNINGS_ENABLED = True util.deprecate("") assert warnings.filters[0][0] == "default" assert len(calls) == 2 util.DEPRECATION_WARNINGS_ENABLED = False util.deprecate("") assert warnings.filters[0][0] == "ignore" assert len(calls) == 3
def df_to_sheet( self, df, index=True, headers=True, start=(1, 1), replace=False, sheet=None, raw_column_names=None, raw_columns=None, 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 (DEPRECATED use raw_collumns instead) optional, list of columns from your dataframe that you want interpreted as RAW input in google sheets. raw_columns : list, str optional, list of columns from your dataframe that you want interpreted as RAW input in google sheets. This can be column names or column numbers. 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 if index else 0 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: deprecate( "raw_column_names is deprecated, please use raw_columns instead." ) raw_columns = find_col_indexes(raw_column_names, header, start[COL] + index_size) elif raw_columns: if is_indexes(raw_columns): offset = index_size + start[COL] - 1 raw_columns = [ix + offset for ix in raw_columns] else: raw_columns = find_col_indexes(raw_columns, header, start[COL] + index_size) 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 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 add_filter( self, start_row=None, end_row=None, start_col=None, end_col=None, start=None, end=None, sheet=None, ): """Add filters to data in the open worksheet. Parameters ---------- start_row : int (deprecated, use 'start') First row to include in filter; this will be the filter header (default 0) end_row : int (deprecated, use 'end') Last row to include in filter (default last row in sheet) start_col : int (deprecated, use 'start') First column to include in filter (default 0) end_col : int (deprecated, use 'end') Last column to include in filter (default last column in sheet) start : tuple,str Tuple indicating (row, col) or string like 'A1' (default 'A1') end : tuple, str Tuple indicating (row, col) or string like 'A1' (default last cell in sheet) sheet : str,int,Worksheet optional, if you want to open or create a different sheet before adding the filter, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) Returns ------- None """ if sheet is not None: self.open_sheet(sheet, create=True) if not self.sheet: raise NoWorksheetException("No open worksheet") dims = self.get_sheet_dims() if ( start_row is not None or end_row is not None or start_col is not None or end_col is not None ): deprecate( "start/end_row/col have been deprecated and will be removed in v2. " "Use 'start' and 'end' instead" ) if start is not None: start_row, start_col = get_cell_as_tuple(start) if end is not None: end_row, end_col = get_cell_as_tuple(end) self.spread.batch_update( { "requests": create_filter_request( self.sheet.id, (start_row or 0, start_col or 0), (end_row or dims[ROW], end_col or dims[COL]), ) } )