How to copy/paste a dataframe from iPython into Google Sheets or Excel?

Question

I've been using iPython (aka Jupyter) quite a bit lately for data analysis and some machine learning. But one big headache is copying results from the notebook app (browser) into either Excel or Google Sheets so I can manipulate results or share them with people who don't use iPython.

I know how to convert results to csv and save. But then I have to dig through my computer, open the results and paste them into Excel or Google Sheets. That takes too much time.

And just highlighting a resulting dataframe and copy/pasting usually completely messes up the formatting, with columns overflowing. (Not to mention the issue of long resulting dataframes being truncated when printed in iPython.)

How can I easily copy/paste an iPython result into a spreadsheet?

Answer 1

Try using the to_clipboard() method. E.g., for a dataframe, df: df.to_clipboard() will copy said dataframe to your clipboard. You can then paste it into Excel or Google Docs.

Answer 2

If df.to_clipboard doesn't work. This will work.

import io
with io.StringIO() as buffer:
    df.to_csv(buffer, sep=' ', index=False)
    print(buffer.getvalue())

Then, you can copy the printed dataframe and paste it in Excel or Google Sheets.

Answer 3

I use display() instead of print() and it works fine for me. Example:

from IPython.display import display
import pandas as pd

dict = {'Name' : ['Alice', 'Bob', 'Charlie'],
        'English' : [73, 55, 90],
        'Math' : [78, 100, 33],
        'Geography' : [92, 87, 72]}

df = pd.DataFrame(dict)

display(df)

The result can easily be copied and pasted into Excel and formatting won't be messed up. This method also works with Colab.

Answer 4

Paste the output to an IDE like Atom and then paste in Google Sheets/Excel

Answer 5

If you are able to make the csv or html available and reachable by a url - you can use this in google sheets.

=IMPORTDATA("url to the csv/html file")

Answer 6

In my experience SpreadSheet uses tabulation (\t) to separate cells and newline (\n) to separate rows.

Assuming this I wrote a simple function to convert from clipboard data:

def from_excel_to_list(copy_text):
    """Use it to copy and paste data from SpreadSheet software
    (MS Excel, Libreoffice) and convert to a list
    """
    if isinstance(copy_text, str):
        array = []
        rows = copy_text.split("\n")  # splits rows
        for row in rows:
            if len(row):  # removes empty lines
                array.append(row.split("\t"))
        return array
    else:
        raise TypeError("text must be string")

You can define the function inside Jupiter and use it in this way:

Copy with ctrl-c on the SpreadSheet and than call the function from_excel_to_list pasting the data with ctrl-v inside the double brackets

my_excel_converted = from_excel_to_list("""Paste here with ctrl-v the text""")

Example

Data from ctrl-c:

N   U   tot
1   18,236  18,236
17  20,37   346,29
5   6,318   31,59

Call The function:

from_excel_to_list("""N U   tot
1   18,236  18,236
17  20,37   346,29
5   6,318   31,59
""")

Result in Jupiter:

[['N', 'U', 'tot'],
 ['1', '18,236', '18,236'],
 ['17', '20,37', '346,29'],
 ['5', '6,318', '31,59']]

This is a base for further elaboration. The same approach can be used to obtain dictionary, namedtuple and so on.

Answer 7

For a small table, you can print the dataframe, use mouse to select the table, copy the table using Ctrl/Cmd + C, go to spreadsheet and paste the table, and you will get the following:

click on the first cell and insert a cell to fix the header:

Done.

PS: for a bigger table, some rows/columns will show as '...', refer to How do I expand the output display to see more columns of a Pandas DataFrame? to show all rows and columns. For a even bigger table (that is difficult to select using the mouse), this method is not so convenient.