-2

For example u have 1 excel file and it consist of 10000 data in it. Later when we import that excel file in pycharm or jupiter notebook. If i run that file i will get an Index range also know as Row labels. my python code should be able to read that ten thousand row labels and should be able to separate / split into 10 different excel sheet files which will have 1000 data in each of the 10 separated sheet. Other example is, if there is 9999 data in 1 sheet/file then my python code should divide 9000 data in 9 sheet and other 999 in other sheet without any mistakes.{This is important Question}

i am asking this because in my data there is not any unique values for my code to split the files using .unique

Neville Doke
  • 1
  • 1
  • Do share some examples by pasting your code and data as text. And share the codes you have tried in a [minimal and reproducible manner](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). – greco May 04 '22 at 08:53

1 Answers1

0

You could use Pandas to read your file, chunk it then re-write it :

import pandas as pd

df = pd.read_excel("/path/to/excels/file.xlsx")

n_partitions = 3

for i in range(n_partitions):
    sub_df = df.iloc[(i*n_paritions):((i+1)*n_paritions)]
    sub_df.to_excel(f"/output/path/to/test-{i}.xlsx", sheet_name="a")

EDIT: Or if you prefere to set the number of lines per xls files :

import pandas as pd

df = pd.read_excel("/path/to/excels/file.xlsx")

rows_per_file = 4

n_chunks = len(df) // rows_per_file

for i in range(n_chunks):
    start = i*rows_per_file
    stop = (i+1) * rows_per_file
    sub_df = df.iloc[start:stop]
    sub_df.to_excel(f"/output/path/to/test-{i}.xlsx", sheet_name="a")
if stop < len(df):
    sub_df = df.iloc[stop:]
    sub_df.to_excel(f"/output/path/to/test-{i}.xlsx", sheet_name="a")

You'll need openpyxl to read/write Excel files

fweber
  • 192
  • 1
  • 6
  • The 1st one was now working, and the 2 was good and it also worked at a point where my 9999 data was in a separate excel file with 1000 data in each excel file, but the problem went where the remaining 999 data was not printed in another excel file coz of Floor division that is // i guess. – Neville Doke May 04 '22 at 11:48
  • Event with the last condition `if stop < len(df)` ? This was here to deal with these last 999 rows – fweber May 04 '22 at 13:47
  • ok got uh......... – Neville Doke May 09 '22 at 06:20