如何将数据帧从iPython复制/粘贴到Google表格或Excel中？

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.

我最近一直在使用iPython（又名Jupyter）进行数据分析和一些机器学习。但令人头疼的是将笔记本应用程序（浏览器）的结果复制到Excel或Google表格中，这样我就可以操纵结果或与不使用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.

我知道如何将结果转换为csv并保存。但是我必须挖掘我的计算机，打开结果并将它们粘贴到Excel或Google表格中。这花费了太多时间。

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.)

只是突出显示结果数据帧和复制/粘贴通常会完全混淆格式，列溢出。（更不用说在iPython中打印时被截断的长结果数据帧的问题。）

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

如何轻松地将iPython结果复制/粘贴到电子表格中？

3 个解决方案

#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.

尝试使用to_clipboard（）方法。例如，对于数据帧，df：df.to_clipboard（）会将所述数据帧复制到剪贴板。然后，您可以将其粘贴到Excel或Google文档中。

#2

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

如果您能够通过网址使csv或html可用并且可以访问 - 您可以在Google工作表中使用此功能。

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

#3

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

根据我的经验，SpreadSheet使用制表（\ t）将单元格和换行符（\ n）分隔为单独的行。

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:

您可以在Jupiter中定义函数并以这种方式使用它：

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

在SpreadSheet上使用ctrl-c复制，然后调用from_excel_to_list函数，用双括号内的ctrl-v粘贴数据

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

Example

例

Data from ctrl-c:

来自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.

这是进一步阐述的基础。可以使用相同的方法来获取字典，namedtuple等。

#1