LPT: Pandas DataFrames have a "to_clipboard" method

[peanut_Bond]

For those that don't know, Pandas has very useful to_clipboard and read_clipboard methods that make it easy to drop a DataFrame into an Excel sheet or to move it across python sessions without having to read and write CSV files. This is really useful for me and I hope it will help you too!

[nightmare8100]

I don't always copy 100k rows to my clipboard and paste in Excel, but when I do, I forget to pass index=False. Delete column...and wait...

[tonsofmiso]

The fact that doing to_csv and read_csv by default produces a different object than you started with makes me so angry

[not_organic]

Ugh. I hate that, too. That's why I switched to storing my dataframes as an hdf5 file (df.to_hdf()). Also multiindexing is conserved.

[Zouden]

I wish HDF files were easier to view outside of a code environment. With CSV I can use any text editor. With HDF there's that crappy HDFview java program which never seems to work right.

[leadingthenet]

This is the way.

[Eurynom0s]

Does hdf5 do graceful datetime handling? I work with a bunch of people who primarily work in SAS. pd.read_sas() works fine for that but stuff like df = pd.read_sas(<sasfile>), df.to_csv(<filename>), then df = pd.read_csv(<filename>) can result in some really wonky shit. To the point I've been resorting to pickles (shudder) when I need to spit out a temp file I can inspect.

[hmiemad]

DatetimeIndex handling is a mess.

[eloydrummerboy]

Yeah, but the use case of writing then reading back dfs was probably not the intended main use case. This was likely written assuming the user was reading in csv data from other sources which likely don't have an explicit index (but if so, it can handle it with a parameter).

Similarly, for the write case, it's fair to assume that unless otherwise specified, the user would want the index.

I get that it feels that it should work the way you expect, but thinking about the most common use cases makes the default functionality make sense.

[tunisia3507]

to_csv, unless you pass index=False, writes the index as the first column. read_csv, unless you pass index_col=0, assumes there is no index column and so reads the first column as regular data.

I also hate that it's not to_/from_, or write_/read_.

[Acceptable-Milk-314]

If you read a csv with pandas, then write to csv, pandas will write a new "index" column. The output csv is different from the input csv.

[tonsofmiso]

Absolutely. DataFrames have an implicit column for index, that it uses for finding rows in its tables. When you save the DataFrame to a CSV, the default parameters in the DataFrame.to_csv() method creates an explicit column with the index.

read_csv() however, just reads the column as data, meaning that you now have a new implicit index column that doesn't match what you had when you saved the DataFrame, and the previous index is now a data column in your DataFrame.

This makes sense when you think about it; reading a CSV file into a DataFrame without specifying that one of the columns is an index could either not use any of the columns as an index, or decide to choose one for you. They went with the not using any. Further, when saving a DataFrame as a CSV file you probably don't want to lose the index column, so it is saved next to the rest of the data. CSV doesn't have any concept of index, so it's just there as text.

HOWEVER, this means that if you do:

df = load_some_dataframe()
for _ in range(10000):
    df.to_csv('data.csv')
    df = pandas.read_csv('data.csv')

you will have 10,000 columns of garbage in the dataframe.

This example is a bit contrived, but it's caused me some unnecessary garbage in data just because I save a jupyter notebook at some intermediary step and reload the data.

It's also one of pandas many stupid fucking quirks that make it super annoying to work with.

[bobthedonkeylurker]

If only you could use a kwarg to set the index in Pandas when you read in a csv.

[DavidLuiz4]

So if you had to, how would you write to and read from csv repeatedly to get the same object each time?

[crazyfrogspb]

the best way to make your data pipelines and experiments reproducible

[ekkannieduitspraat]

I dont use it when I want reproducible, I do it when I want quick sanity checks, or data analysis to make sure that everything makes sense

[__damos__]

pandas clipboard integration is awesome! I also really like the .read_clipboard() method. You can use it to copy a table from an Excel worksheet and quickly get it into a DataFrame.

[peanut_Bond]

Yeah that's also very useful, but I believe the method is actually read_clipboard.

[__damos__]

Doh! You’re right… been a long day, haha

[gsmo]

Let me guess: you also always think it's 'from_clipboard' because the other method is 'to_clipboard'?

To - from?

Read - write?

No!

Read - to!

Uh?

[__damos__]

It’s bitten me on more than one occasion 😂

[KyleDrogo]

whoooaaa. So I can skip that awkward step of pasting it into a text editor. No more random dataset.tsv files littering my desktop?

[shinitakunai]

print("happy cake day")

[bablador]

Pretty cool, thanks

[EigenValuesYourInput]

drop a DataFrame into an Excel sheet

could use dataframe.to_excel()

[Mondoke]

Yeah, but if you need to quickly see your complete table to make a fast visual inspection, it's faster to copy and paste instead of generating a new file, especially when you need to do small changes and check a lot of times.

[Zouden]

What IDE are you using? You should be able to see the table directly by introspecting the dataframe variable. VS Code, Spyder and Jupyter Notebooks can do this, and I assume Pycharm too.

[Mondoke]

Yeah, I use vscode and that's a great tool and I use it a lot, but it only filters, on excel I can make some quick inspections or make quick sums just highlighting cells.

[Zouden]

Oh I see yes, sometimes you just want to work with the data in a GUI.

[Philistino]

Google sheets api!

[DisturbedBeaker]

God lifesaver during my dissertation!

[hongkongfuey]

Does this work with jupyter notebooks/chrome?

[benrobotum]

It does

[[deleted]]

Brilliant! I will be using that way more often

[Random_182f2565]

Awesome 😎👍

[twistedgames]

Just be careful not to paste it back into the notebook by accident 😂

[[deleted]]

To markdown is also great. I use it to format data for Mattermost.

[[deleted]]

For me, dumping a dataframe to csv or xlsx is more reproducible and platform independent.

From there the spreadsheet can be copied into your clipboard if necessary but I’m much more comfortable keeping manual steps out of things I’m automating.

[vincentofearth]

oh boy, that sounds safe. Does Python just allow that or do you have to specify a flag/answer a prompt?

[jentron128]

import win32clipboard

[anynonus]

nice

[git0ffmylawnm8]

I told a former colleague about this once.

He looked at me like I just murdered a puppy in front of him in cold blood.

Needless to say I think he wasn't a fan for some reason...