dumping large pandas dataframe (~ >10k rows) times out

[notacoat]

a check to see how large the array is and then some code that chunks it into <=10k sections would work. I wrote a function that does that and it is working fine.

[fzumstein]

Great - can you open a pull request or do want to paste the function here? although the issue will not be dataframe specific but also apply to lists and numpy arrrays (they are all being transformed into lists before transferred over).

[notacoat]

here's the function I wrote.

def dump_largeDF(df, startcell='A1', chunk_size=10000):
    if len(df) <= (chunk_size +1): Range(startcell,index=False).value = df
    else: # chunk df and and dump each (default is 10k)\n",
        c = re.match(r"([a-z]+)([0-9]+)", startcell, re.I); cL = c.group(1); cN = int(c.group(2));n=0
        for i in (df[rw:rw + chunk_size] for rw in xrange(0, len(df), chunk_size)):
            if n==0: Range(cL + str(cN+n),index=False).value = i; cN += chunk_size
            else: Range(cL + str(cN+n)).value = i.values; cN += chunk_size
            n+=1

[fzumstein]

See also: http://stackoverflow.com/questions/27212643/how-can-i-insert-a-dataframe-in-excel-using-xlwings-without-pywintypes-com-error?noredirect=1#comment42912461_27212643

[Zenadix]

The function posted by EmilyBarbour is bugged and causes an empty row to appear between chunks.

I modified the function and now it works fine. I posted it on StackOverflow, but I'm posting it here too for easy access.

# Dumps a large DataFrame in Excel via xlwings
# Does not include headers.
def dump_largeDF(df, startcell='A1', chunk_size=100000):
    if len(df) <= (chunk_size + 1):
        Range(startcell, index=False, header=False).value = df
    else: # Chunk df and and dump each
        c = re.match(r"([a-z]+)([0-9]+)", startcell, re.I) # A1
        row = c.group(1) # A
        col = int(c.group(2)) # 1
        for chunk in (df[rw:rw + chunk_size] for rw in
                      range(0, len(df), chunk_size)):
            print("Dumping chunk in %s%s" %(row, col))
            Range(row + str(col), index=False, header=False).value = chunk
            col += chunk_size

For me, a chunk size of 100k is fine, however you may change it depending on your needs.

[fzumstein]

Great thanks! Still hope it'll make it into one of the next releases, but as pointed out above, we'll need to implement this further down to cover numpy arrays and lists also.

[aschleg]

Thanks a lot for the function Emily and Zenadix, really helpful for moving large datasets between Excel and Python.

I wanted to share a slight modification of Zenadix's function that copies in the column headers as well. Seems to be working well for my purposes so thought it might help others as well :)

def dump_largeDF(df, sheet, startcell, chunk_size = 10000):
    if len(df) <= (chunk_size + 1):
        Range(sheet, startcell, index = False, header = True).value = df
    else:
        Range(sheet, startcell, index = False).value = list(df.columns)
        c = re.match(r"([a-z]+)([0-9]+)", startcell[0] + str(int(startcell[1]) + 1), re.I)
        row = c.group(1)
        col = int(c.group(2))

        for chunk in (df[rw:rw + chunk_size] for rw in 
                      range(0, len(df), chunk_size)):
            Range(sheet, row + str(col), index = False, header = False).value = chunk
            col += chunk_size

[fzumstein]

The Excel cursor might need to be switched to "busy" to prevent editing cells while xlwings is still trying to write out data.

[fzumstein]

see also: ericremoreynolds/excelpython#60

[robertschulze]

For everyone who is keen on also correctly handling the header and not relying on the Range, I modified the code a bit:

def dumpLargeDf(wb, df, startcell='A1', chunk_size=50000):
    # Dumps a large DataFrame in Excel via xlwings. Takes care of header.
    if len(df) <= (chunk_size + 1):
        wb.sheets.active.range(startcell).options(index=False).value = df
    else:                                       # Chunk df and and dump each
        c = re.match(r"([a-z]+)([0-9]+)", startcell, re.I)      # A1
        row = c.group(1)                                        # A
        col = int(c.group(2))                                   # 1
        useHeader = True
        for chunk in (df[rw:rw + chunk_size] for rw in
                      range(0, len(df), chunk_size)):
            print("Dumping chunk in %s%s" % (row, col))
            wb.sheets.active.range(row + str(col)) \
                .options(index=False, header=useHeader).value = chunk
            useHeader = False
            col += chunk_size

[fzumstein]

With 0.23.1 you can now do: myrange.options(chunksize=10_000).value = df