How can I Export Pandas DataFrame to Google Sheets using Python?

This might be a little late answer to the original author but will be of a help to others. Following is a utility function which can help write any python pandas dataframe to gsheet.

import pygsheets
def write_to_gsheet(service_file_path, spreadsheet_id, sheet_name, data_df):
    """
    this function takes data_df and writes it under spreadsheet_id
    and sheet_name using your credentials under service_file_path
    """
    gc = pygsheets.authorize(service_file=service_file_path)
    sh = gc.open_by_key(spreadsheet_id)
    try:
        sh.add_worksheet(sheet_name)
    except:
        pass
    wks_write = sh.worksheet_by_title(sheet_name)
    wks_write.clear('A1',None,'*')
    wks_write.set_dataframe(data_df, (1,1), encoding='utf-8', fit=True)
    wks_write.frozen_rows = 1

Steps to get service_file_path, spreadsheet_id, sheet_name:

1. Click Sheets API | Google Developers
2. Create new project under Dashboard (provide relevant project name and other required information)
3. Go to Credentials
4. Click on “Create Credentials” and Choose “Service Account”. Fill in all required information viz. Service account name, id, description et. al.
5. Go to Step 2 and 3 and Click on “Done”
6. Click on your service account and Go to “Keys”
7. Click on “Add Key”, Choose “Create New Key” and Select “Json”. Your Service Json File will be downloaded. Put this under your repo folder and path to this file is your service_file_path.
8. In that Json, “client_email” key can be found.
9. Create a new google spreadsheet. Note the url of the spreadsheet.
10. Provide an Editor access to the spreadsheet to "client_email" (step 8) and Keep this service json file while running your python code.
11. Note: add json file to .gitignore without fail.
12. From url (e.g. https://docs.google.com/spreadsheets/d/1E5gTTkuLTs4rhkZAB8vvGMx7MH008HjW7YOjIOvKYJ1/) extract part between /d/ and / (e.g. 1E5gTTkuLTs4rhkZAB8vvGMx7MH008HjW7YOjIOvKYJ1 in this case) which is your spreadsheet_id.
13. sheet_name is the name of the tab in google spreadsheet. By default it is "Sheet1" (unless you have modified it.

A little outdated answer, but I hope it might help others.

I wrote my own convenient package to connect Google Sheets and pandas dataframes. One can install package using pip:

pip install gsheet-pandas

The short answer is:

from gsheet_pandas import DriveConnection

drive = DriveConnection(credentials_dir='credentials.json')

drive.upload(df, drive_table=table_name, sheet_name=sheet_name)

Full answer: library provides two ways to work with pandas. The first one is pandas extension:

import gsheet_pandas

# To login with a service account
gsheet_pandas.setup(credentials_dir='service_creds.json')
# To login with a user account
gsheet_pandas.setup(credentials_dir='creds.json', token_dir='token.json')

# Uploading df
df.to_gsheet(drive_table=table_name, sheet_name=sheet_name)

# Fetching df
df = pd.from_gsheet(drive_table=table_name, sheet_name=sheet_name)

Second option is to use DriveConnection class:

from gsheet_pandas import DriveConnection

# Init DriveConnection instance
drive = DriveConnection(credentials_dir='credentials.json')

# Uploading dataframe
drive.upload(df, drive_table=table_name, 
             sheet_name=sheet_name,
             range_name='!B1:ZZ900000', # Range in Sheets; Optional
             drop_columns=False) # Upload column names or not; Optional

# Fetching dataframe
df = drive.download(drive_table=table_name, 
                    sheet_name=sheet_name,
                    range_name='!A1:C100', # Range in Sheets; Optional
                    header=0) # Column row

Google Sheets has a nice api you can use from python (see the docs here), which allows you to append single rows or entire batch updates to a Sheet.

Another way of doing it without that API would be to export the data to a csv file using the python csv library, and then you can easily import that csv file into a Google Sheet.