Welcome to gspread-dataframe’s documentation!

gspread-dataframe

https://badge.fury.io/py/gspread-dataframe.svg https://travis-ci.com/robin900/gspread-dataframe.svg?branch=master https://img.shields.io/pypi/dm/gspread-dataframe.svg Documentation Status

This package allows easy data flow between a worksheet in a Google spreadsheet and a Pandas DataFrame. Any worksheet you can obtain using the gspread package can be retrieved as a DataFrame with get_as_dataframe; DataFrame objects can be written to a worksheet using set_with_dataframe:

import pandas as pd
from gspread_dataframe import get_as_dataframe, set_with_dataframe

worksheet = some_worksheet_obtained_from_gspread_client

df = pd.DataFrame.from_records([{'a': i, 'b': i * 2} for i in range(100)])
set_with_dataframe(worksheet, df)

df2 = get_as_dataframe(worksheet)

The get_as_dataframe function supports the keyword arguments that are supported by your Pandas version’s text parsing readers, such as pandas.read_csv. Consult your Pandas documentation for a full list of options. Since the 'python' engine in Pandas is used for parsing, only options supported by that engine are acceptable:

import pandas as pd
from gspread_dataframe import get_as_dataframe

worksheet = some_worksheet_obtained_from_gspread_client

df = get_as_dataframe(worksheet, parse_dates=True, usecols=[0,2], skiprows=1, header=None)

Formatting Google worksheets for DataFrames

If you install the gspread-formatting package, you can additionally format a Google worksheet to suit the DataFrame data you’ve just written. See the package documentation for details, but here’s a short example using the default formatter:

import pandas as pd
from gspread_dataframe import get_as_dataframe, set_with_dataframe
from gspread_formatting.dataframe import format_with_dataframe

worksheet = some_worksheet_obtained_from_gspread_client

df = pd.DataFrame.from_records([{'a': i, 'b': i * 2} for i in range(100)])
set_with_dataframe(worksheet, df)
format_with_dataframe(worksheet, df, include_column_header=True)

Installation

Requirements

  • Python 2.7, 3+
  • gspread (>=3.0.0; to use older versions of gspread, use gspread-dataframe releases of 2.1.1 or earlier)
  • Pandas >= 0.24.0

From PyPI

pip install gspread-dataframe

From GitHub

git clone https://github.com/robin900/gspread-dataframe.git
cd gspread-dataframe
python setup.py install

Module Documentation - Version 3.3.1

gspread_dataframe

This module contains functions to retrieve a gspread worksheet as a pandas.DataFrame, and to set the contents of a worksheet using a pandas.DataFrame. To use these functions, have Pandas 0.14.0 or greater installed.

gspread_dataframe.get_as_dataframe(worksheet, evaluate_formulas=False, **options)

Returns the worksheet contents as a DataFrame.

Parameters:
  • worksheet – the worksheet.
  • evaluate_formulas – if True, get the value of a cell after formula evaluation; otherwise get the formula itself if present. Defaults to False.
  • **options – all the options for pandas.io.parsers.TextParser, according to the version of pandas that is installed. (Note: TextParser supports only the default ‘python’ parser engine, not the C engine.)
Returns:

pandas.DataFrame
gspread_dataframe.set_with_dataframe(worksheet, dataframe, row=1, col=1, include_index=False, include_column_header=True, resize=False, allow_formulas=True, string_escaping='default')

Sets the values of a given DataFrame, anchoring its upper-left corner at (row, col). (Default is row 1, column 1.)

Parameters:
  • worksheet – the gspread worksheet to set with content of DataFrame.
  • dataframe – the DataFrame.
  • row – Row at which to start writing the DataFrame. Default is 1.
  • col – Column at which to start writing the DataFrame. Default is 1.
  • include_index – if True, include the DataFrame’s index as an additional column. Defaults to False.
  • include_column_header – if True, add a header row or rows before data with column names. (If include_index is True, the index’s name(s) will be used as its columns’ headers.) Defaults to True.
  • resize – if True, changes the worksheet’s size to match the shape of the provided DataFrame. If False, worksheet will only be resized as necessary to contain the DataFrame contents. Defaults to False.
  • allow_formulas – if True, interprets =foo as a formula in cell values; otherwise all text beginning with = is escaped to avoid its interpretation as a formula. Defaults to True.
  • string_escaping –

    determines when string values are escaped as text literals (by adding an initial ‘ character) in requests to Sheets API. Four parameter values are accepted:

    • ’default’: only escape strings starting with a literal ‘
      character
    • ’off’: escape nothing; cell values starting with a ‘ will be
      interpreted by sheets as an escape character followed by a text literal.
    • ’full’: escape all string values
    • any callable object: will be called once for each cell’s string
      value; if return value is true, string will be escaped with preceding ‘ (A useful technique is to pass a regular expression bound method, e.g. re.compile(r’^my_regex_.*$’).search.)

    The escaping done when allow_formulas=False (escaping string values beginning with =) is unaffected by this parameter’s value. Default value is ‘default’.

Indices and tables