[Python] How to read only non-adjacent columns copied from Excel into a pandas DF via...

I love using pd.read_clipboard() to quickly import tabular data from Excel into a DataFrame in pandas.

However, if I have highlighted non-adjacent columns in Excel before I hit CTRL+C to copy them to the clipboard, what gets imported into my DF will include all of the intervening columns that were sandwiched between the non-adjacent columns that I had selected for copying. (I'm currently using Windows 10 & Excel 2013).

This is annoying and incovenient. I suspect that the problem may emanate from Excel or the Windows clipboard, since I also get the intervening (tab-separated) columns when I paste into Sublime Text 3.

If I paste into a blank worksheet, I only get the highlighted columns I want, and this has been my work-around (creating an interim Excel sheet that I then copy and import into pandas). It's a decent workaround, but I'm looking for something faster, since I go back and forth between Excel and pandas MANY times per day.

I am aware that the problem goes away if I HIDE the columns in Excel before copying them (whether by the CTRL+0 shortcut or by grouping the intervening columns I want to hide), but neither of these are suitable, since it manipulates my current view/design of my Excel worksheet (and anyway Windows 10 has clobbered the "unhide columns" shortcut (CTRL+SHIFT+0)... although ALT+H+O+U+L still works).

The problem does not go away when the intervening columns that I don't want are visible (thus it doesn't matter if I hit ALT+; to select only visible cells before I hit CTRL+C).

I'm looking for a solution that is simple and fast, preferably an alternative shortcut to use in Excel, or a universal kwarg I can use for pd.read_clipboard().

The documentation for pd.read_clipboard() says to look at the keywords from pd.read_table(), but I can't figure out which might help. Again, I suspect the problem has to do with the clipboard on the Windows end, but I have searched and searched online and can't find anything except third-party commercial Excel plugins which claim to be able to help with copying non-contiguous cells.

Continue reading...