Automating Excel using Python

Question

I've written out code that will concat multiple Excel sheets from a single file into a DataFrame. Then, by using a function, the DF will be split by 1mil rows into mini DFs. Lastly, the mini DFs are converted into CSVs. How can this function work so that it automatically counts how many DFs per 1mil rows to create? Here is my code so far:

data_df = pd.concat(pd.read_excel('file location', sheet_name = None), ignore_index = True)
def automate_excel(): 
    df1 = pd.DataFrame(data_df[0:1000000]) 
    df2 = pd.DataFrame(data_df[1000001:2000000])
    df1.to_csv('data_df1', index = False) 
    df2.to_csv('data_df2', index = False)

automate_excel()

This is a minimized example as the current file is 5mil rows but I can have some up to 10mil rows or broken out into 10 CSV files

yf879 · Accepted Answer · 2021-12-04 05:12:05Z

1

small_df_rows = 1000000


def split_df(n, idx):
    small_df = df.loc[n:n+small_df_rows-1]
    small_df.to_csv(f'data_df{idx+1}.csv', index=False)


for idx, n in enumerate(range(0, len(df), small_df_rows)):
    split_df(n, idx)

edited Dec 4, 2021 at 5:12

answered Dec 4, 2021 at 5:06

yf879

1681 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Automating Excel using Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related