1

Consider I have a huge excel sheet, with multiple columns and entries. However, there exists a particular column (COLUMN A) containing boolean values 0s and 1s. Now I wish to split my parent excel sheet into 2 sheets, based on the values of the COLUMN A. I already know that this can be done using VBA codes. However, I wanna try this on python. My idea is that we can iterate through the said column values, and if a condition is satisfied, pick up the whole row and write it in a new sheet. I am learning the language, can use numpy and pandas a bit to create linear regression models and the like. I'd like to work on this 'personal-project'. Would be glad if anyone would help me with this, provide a few hints or something to start with. Thank you.

Improve this question

1 Answer 1

Reset to default
1

How I would go about it:

  1. Read the full excel sheet into a pandas dataframe df = pd.from_excel("file_name.xlsx")

  2. Filter the dataframe by values in that columns 

df1 = df[df["COLUMN A"]==1]
df0 = df[df["COLUMN A"]==0]
  1. Read those new dataframes to a new excel workbook, or new excel sheet on an exisiting workbook, using the pandas ExcelWriter: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html

Don't forget to handle missing data in column A, if there is any.

I am just a student, so perhaps there are more efficient ways to do this, but I use pandas quite a bit in my undergraduate research and this is what I would do. Best of luck you :)

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

3 Comments

wow thank you. Also, I wanted to know if it's possible to make it real time? like as I'm entering the values 0 and 1, they are being split in those 2 sheets?
@YamiKanashi I believe that would require it to be done through VBA as Python cannot access the file if something else has it open.
seems there is a way, in python's xlwings module you can work and run python codes simultaneously, the best way to do your task is to have 3 sheets in your workbook 2 sheet save data and 1 to write data from user. Add your column A at last that specifies whether it goes to Sheet 1 or Sheet 2 if Column A entry is 0 take the whole row paste it to Sheet 0 else entry is 1 paste it to Sheet 2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.