how about splitting into separate excel files based on multiple columns? e.g. extract a file when data in excel column A (go to zoo?) is "yes", extract a file when data in excel column B (go to museum?) is "yes" and so on. So we will have separate excel files "go to zoo", "go to museum" and so on. Thank you!

@@cendrabinarto Thanks for your video suggestion!

Hi, I did saw all your videos and be honest help me a lot. One of them was about sending text / WhatsApp messages from excel using API’s. Here comes suggestion; can we send a sheet (as personal attachment) to different email address at once using excel & python. Each person will get his attachment base on email address and sheet location.

@@faisalmehmood6889 Thank you very much for the suggestion!

How to average the Gross sale column by country or by segment or by product and return result on the same page but on different location?

Glad I could help! Thanks for watching and your comment! :)

Glad you liked it. Thanks for watching and taking the time to leave a comment! 👍

Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video! Cheers, Sven ✌️

Glad to hear you liked it! Thank you for commenting and watching.

I am super happy to hear that my videos are helping you on your Python journey :) Cheers, Sven :)

*Hello Bassel Khaled, thanks for watching the video and your comment.*

*Welcome aboard!* Thank you for watching the video & for taking the time to leave a comment! 😃

*Hi Ricardo López-Herrera. I am glad you liked the video; thanks for watching and for the comment!*

Thank you, glad it is helpful! 😃

Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video!

Glad you liked it. Thanks for watching and taking the time to leave a comment!

*I am glad you liked the video, thanks for watching and for the comment!*

Glad it was useful! Thank you for watching the video & your comment! :)

Thank you for watching the video & your comment! :)

Thanks!

Thank you for watching the video & your video suggestion. Both methods .itertuples() and .iterrows() are used to loop over a dataframe. Instead of using "for i in range(len(df))", you should/could use .itertuples() or .iterrows(). The difference is that Pandas’ iterrows() returns the index of each row and the data in each row as a *Series*. itertuples() on the other side, loops through rows of a dataframe and return a *named tuple*. itertuples is generally faster than iterrows() Here you can also find a great example of how to use both methods: cmdlinetips.com/2018/12/how-to-loop-through-pandas-rows-or-how-to-iterate-over-pandas-rows/ *.to_dict()* is used to convert the DataFrame to a dictionary. Example below: col1 col2 row1 1 0.50 row2 2 0.75 df.to_dict() {'col1': {'row1': 1, 'row2': 2}, 'col2': {'row1': 0.5, 'row2': 0.75}} Hope this helps 🙏

My pleasure! Appreciate you taking the time to watch and leave a comment. 👍

*Hi Sean Faherty. I am glad you liked the video; thanks for watching and for the comment!*

You're very welcome! Thank you for watching and for the comment! ✌

Thanks for watching. That is be possible with openpyxl or xlwings. Happy Coding! :)

ah, I was able to import column as object and that fixed it.

Thanks for watching. You could convert the respective column first to strings: stackoverflow.com/a/22006514 I hope it helps! Happy Coding!

Thanks for watching. Instead of 'df.to_excel()'. use ''df.to_csv()''. I hope it helps! Happy Coding!

I am not entirely sure if I understand your use case correctly or what you are trying to achieve. Yet, based on the information given, you might want to look into xlwings to help you with the automation. E.g. here is a video on separating each sheet as a separate file (kzfaq.info/get/bejne/qc5oo6ijuJiVonk.html). Once you have the different files, you could combine the code from this video to split your table further (based on the country) column. I hope this helps!

Thanks for watching the video & your great suggestion! Unfortunately, I cannot make any promises to create the requested solution.

Thanks for watching the video. Please find below an example of how to filter multiple columns. I hope this helps! _______ from pathlib import Path import pandas as pd EXCEL_FILE_PATH = Path.cwd() / "YOUR_EXCEL_WORKBOOK.xlsx" # Load financial data into dataframe data = pd.read_excel(EXCEL_FILE_PATH, sheet_name="Data") # Get unique values from any particular column column_name = "YOUR_COLUMN_NAME" unique_values = data[column_name].unique() # Query/Filter the dataframe and export the filtered dataframe as an Excel file for unique_value in unique_values: data_output = data.query(f"{column_name} == @unique_value & Year==2021") output_path = Path.cwd() / f"{unique_value}.xlsx" data_output.to_excel(output_path, sheet_name=unique_value, index=False)

Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!

Thanks for watching the video. Have you watched it until the end? In the last example, we took care of the special characters: kzfaq.info/get/bejne/hL10Za-EuNvdmnk.html Code: github.com/Sven-Bo/split-workbooks-python/blob/master/3rd_Example/Split_Workbook.py I hope this helps!

Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!

Thanks for watching and for your question. Hard to tell from a distance why you are facing an error. Sorry that I cannot help.

You could do it like this: NOTE: YOU NEED TO ADJUST THE CODE SNIPPET BELOW (change the placeholders) import pandas as pd # pip install pandas import os df = pd.read_excel('NAME_OF_WORKBOOK.xlsx') column_name = 'NAME_OF COLUMN' unique_values = df[column_name].unique() for unique_value in unique_values: df_output = df[df[column_name].str.contains(unique_value)] # Select the ones you want df_output = df_output[['column1','column2', 'column3']] output_path = os.path.join(os.getcwd(), str(unique_value) + '.xlsx') df_output.to_excel(output_path, sheet_name=unique_value[:31], index=False)

Thanks for watching the video. An easy way would be to convert the existing code into a function. See an example below: import pandas as pd # pip install pandas import os df = pd.read_excel('Financial_Sample.xlsx') def split_by_columns(column_name): unique_values = df[column_name].unique() for unique_value in unique_values: df_output = df[df[column_name].str.contains(unique_value)] output_path = os.path.join('output', str(unique_value) + '.xlsx') df_output.to_excel(output_path, sheet_name=unique_value[:31], index=False) split_by_columns('Segment') split_by_columns('Product')

@@CodingIsFun you’re awesome thanks alot 👍👍👍

Hey there! Thank you so much for your kind words. Regarding your question: It basically just boils down to practice. The more you code, the more you'll develop a deeper understanding and intuition for it. Interestingly, creating these KZfaq videos has helped me a lot in getting better at coding as well. Keep working on projects, experimenting, and learning from your mistakes. Good luck on your journey! 🚀

@@CodingIsFun Thanks for replying sir. I will start practicing. Thank you!

Thanks for watching. Are you using this code? github.com/Sven-Bo/split-workbooks-python/blob/master/3rd_Example/Split_Workbook.py And which line is causing the error?

You can use the XlsxWriter package: xlsxwriter.readthedocs.io/working_with_pandas.html

Thanks for watching the video. You could use the pandas library for this. Example below: df['First Name'] = df['Name'].str.split(',', expand=True)[1] df['Last Name'] = df['Name'].str.split(',', expand=True)[0] Here are some more examples: www.geeksforgeeks.org/python-pandas-split-strings-into-two-list-columns-using-str-split/ Hope this helps!

Thank you for watching! Unfortunately, the question you asked is not related to the video content. If you have any questions or need help, I suggest you join my Discord channel at pythonandvba.com/discord . Just make sure to read the forum rules before posting any questions.

Like so: pastebin.com/D3rQHQMz Happy Automating! :)

Please use the updated code from GitHub: github.com/Sven-Bo/split-workbooks-python/blob/master/1st_Example/Split_Workbook.py What I have added to prevent this error: www.screencast.com/t/stjRN53vC

You could do that with 'openpyxl' or 'xlwings'

Thanks for watching. Hard to tell from a distance as I do not know your code.

Thanks for watching the video. What exactly is the error? Is the file path incorrect?

@@CodingIsFun [Errno 22] Invalid argument: 'output\\

@@dcdien07 Have you watched the video until the end? In the last example, we took care of the special characters: kzfaq.info/get/bejne/hL10Za-EuNvdmnk.html Code: github.com/Sven-Bo/split-workbooks-python/blob/master/3rd_Example/Split_Workbook.py I hope this helps!

Great catch! I have committed changes to the repo. Many thanks for your solution - I appreciate it! :)

Do you mean like inserting 'DropDown' Menus in Excel?

@@CodingIsFun yes the insertion of drop down in specific cells in excel

Thanks for watching, and glad to hear that you find the solution useful. Regarding your question, instead of using all unique values, you could do the selection as follows: import pandas as pd # pip install pandas import os df = pd.read_excel('NAME_OF_WORKBOOK.xlsx') column_name = 'NAME_OF COLUMN' unique_values = ['Japan', 'USA'] for unique_value in unique_values: df_output = df[df[column_name].str.contains(unique_value)] output_path = os.path.join(os.getcwd(), str(unique_value) + '.xlsx') df_output.to_excel(output_path, sheet_name=unique_value[:31], index=False) I hope this helps!

@@CodingIsFun Thanks! That worked....

@@CodingIsFunIn your example, the table begins from row 1. In my file, the table begins from row 5. This code by default detects row 1 as the column header. I will have to delete first few rows to get things done. Are there any solutions for it? Thank you once again...

@@jefrin, You can specify more arguments in pd.read_excel, like how many rows pandas should skip. See also the official pandas documentation: pandas.pydata.org/docs/reference/api/pandas.read_excel.html Here is also a video where I am going through the different pd.read_excel options: kzfaq.info/get/bejne/ecySY6SJqNu0oas.html To make your life easier, you could also use my Excel Add-in, which will fill out the pd.read_excel arguments for you: kzfaq.info/get/bejne/ptOjaJWQksnRk2w.html

@@CodingIsFun Thank you very much. Love from India 🇮🇳

Thanks for watching the video & your question. Could you try to provide the string with a leading 'r', see example below: df = pd.read_excel(r'C:\Users\blablabla\OneDrive\Desktop\Python\Work_test.xlsx') I hope this helps!

@@CodingIsFun thanks for replying I’ll test this later. I eventually replaced back slash (\) with forward slash (/) and it worked but unsure why.

@@dominospizzach probably because you are on a MacOS?! :)

Perhaps the following answer might help: stackoverflow.com/a/59574470

Thanks for watching the video! Excellent question 👍 Unfortunately, Pandas does not keep track of the excel format when loading/importing data. As a workaround, you could apply some styling when exporting the dataframe, e.g. by using ''xlsxwriter" xlsxwriter.readthedocs.io/example_pandas_column_formats.html Taking the example for this tutorial, I applied some styling to the header: pastebin.com/ZNnUgpJr Hope it helps. __________________________ import pandas as pd #pip install pandas import os df = pd.read_excel('Financial_Sample.xlsx') column_name = 'Country' replace_symbols = ['>', '

@@CodingIsFun Hi, can you tell how to keep dates format when doing the split? I suppose it would also lose the original formatting and in some cases it might be important to also keep dates in the same manner as in the master file (and as a date, not string). THX

@@EmekKov In that case, you want to use xlwings and apply an Excel autofilter. Once you have filtered the data, you can export it to a new workbook. I spend this evening coding out an example for you. Please find the GitHub repository here: github.com/Sven-Bo/apply-autofilter-to-excel Keep in mind, xlwings in version 0.25.3 DOES NOT NATIVELY SUPPORT autofilter. Therefore, I am using the underlying API as a workaround. Apart from looking ugly, keep in mind that it makes your code platform-specific (!). This code works only on Windows. Learn more about here: docs.xlwings.org/en/stable/missing_features.html I hope this helps!

@@CodingIsFun Thank you so much. I will defenetly try this out. That's so nice you've spent time on this case

Do you mean something like this? kzfaq.info/get/bejne/mtp-obBquZjDonU.html

Before using str.contains, please convert your column to string values: df['ColumnName'] = df['ColumnName'].astype(str) I hope this helps!

@@CodingIsFun thanks its worked

Thanks for watching the video. Instead of pd.read_excel(), you could use pd.read_csv(). I hope this helps!

Here you are: from pathlib import Path import pandas as pd # pip install pandas EXCEL_FILE_PATH = Path.cwd() / "Financial_Sample.xlsx" df = pd.read_excel(EXCEL_FILE_PATH) segments = df["Segment"].unique() replace_symbols = [">", "

@@CodingIsFun thanks for your help.

Thanks for watching the video. Can you perhaps elaborate on what it is you're trying to achieve? Please provide some additional info. Thanks!

@@CodingIsFun Hey figured this part out, But I am struggling with how to subtotal on column and append the total to the bottom of the output files, do you have a video that covers this?

@@mcso204 I do not have a video about it, but here is the code to do it. I hope it helps! :) pastebin.com/JYv3inX7 from pathlib import Path import pandas as pd # Define & create output directory output_dir = Path(__file__).parent / "output" output_dir.mkdir(parents=True, exist_ok=True) # Define Excel file path excel_file = Path(__file__).parent / "Financial_Sample.xlsx" df = pd.read_excel(excel_file) column_name = "Segment" unique_values = df[column_name].unique() # Iterate over unique values and export to Excel for unique_value in unique_values: # Subset dataframe by unique value df_output = df[df[column_name].str.fullmatch(unique_value)].copy() # Select only numeric columns numeric_cols = df_output.select_dtypes(include=[float, int]).columns # Compute the row and column totals row_totals = df_output[numeric_cols].sum(axis=1) col_totals = df_output[numeric_cols].sum() # Add the row and column totals to the dataframe df_output.loc['Total'] = col_totals total_row = pd.Series(sum(row_totals), index=[f'Total ({unique_value})']) df_output.loc[:, 'Total'] = pd.concat([row_totals, total_row]) # Export to Excel with total row output_path = output_dir / f"{unique_value}.xlsx" df_output.to_excel(output_path, sheet_name=unique_value[:31], index=False)

@@CodingIsFun That works, only one thing. I'd like it to calculate only one of the columns not all numeric columns. if possible.

@@mcso204 Feel free to adjust the code. It was just an example;)

Thanks for watching. Please clone the GitHub repo and try again. Thanks!

With this bit of information, I cannot really help.

@@CodingIsFun and this is the code import pandas as pd #pip install pandas from pathlib import Path import os from pyecharts import options as opts #pip install pyecharts from pyecharts.charts import Bar, Tab data_frame = pd.read_csv(r'C:\\Users\\Nesrine\\Desktop\\Game\\SteamCharts.csv',encoding= 'unicode_escape') data_frame.to_excel(r'C:\\Users\\Nesrine\\Desktop\\Game\\StreamCharts.xlsx', index=None, header=True) #separate the data into multiple workbooks based on the game name df = pd.read_excel(r'C:\\Users\\Nesrine\\Desktop\\Game\\StreamCharts.xlsx') #reading the dataframe column_name = 'gamename' replace_symbols = ['', ':', '"', '/', '\\\\', '|', '\?', '\!', '\*', '\]', '\+', '\(', '\)', '®', '_', '-','.'] df[column_name] = df[column_name].replace(replace_symbols, '', regex=True).str.strip().str.title() unique_values = df[column_name].unique() #list of unique values of that column for unique_value in unique_values: df_output = df[df[column_name].str.contains(unique_value)] output_path = os.path.join('output', unique_value +'.xlsx') with pd.ExcelWriter(output_path) as writer: # xlsx file type which is file extension for Microsoft Excel Open XML Spreadsheet df_output.to_excel(writer) #df_output.to_excel(output_path, sheet_name=unique_value, index=False)

Then you need to adjust the code like so: pastebin.com/aFh4FeKp

Thanks for watching the video. I have adjusted the code to limit the sheet name length to a maximum of 31 characters: import pandas as pd # pip install pandas import os df = pd.read_excel('NAME_OF_WORKBOOK.xlsx') column_name = 'NAME_OF COLUMN' unique_values = df[column_name].unique() for unique_value in unique_values: df_output = df[df[column_name].str.contains(unique_value)] output_path = os.path.join(os.getcwd(), str(unique_value) + '.xlsx') df_output.to_excel(output_path, sheet_name=unique_value[:31], index=False)

@@CodingIsFun Thanks, that helped me a lot. i request you please make a video adding pivot table for each file that we split, for example if we split based on clients name and we want to show pivot for that particular client in sheet2. and split based on client name.

@@sudharshan574 Thanks for your video suggestion. Sounds like an exciting video topic. I cannot make any promises, but l will see what I can do.

@@CodingIsFun i would like to increase mine because the column is more than 31 character which i can delete any, please how do i go about it?

@@ahmadmojeed5507 31 characters is the maximum length set by Excel. There is no workaround

Thanks for watching. Are you using this code? github.com/Sven-Bo/split-workbooks-python/blob/master/3rd_Example/Split_Workbook.py And which line is causing the error?

@@CodingIsFun Traceback (most recent call last): File "C:\Users\Mrityunjayan R G\PycharmProjects\excelss\main.py", line 21, in df_output = df[df[column_name].str.fullmatch(unique_value)] File "C:\Users\Mrityunjayan R G\PycharmProjects\excelss\venv\lib\site-packages\pandas\core\frame.py", line 3495, in __getitem__ if com.is_bool_indexer(key): File "C:\Users\Mrityunjayan R G\PycharmProjects\excelss\venv\lib\site-packages\pandas\core\common.py", line 144, in is_bool_indexer raise ValueError(na_msg) ValueError: Cannot mask with non-boolean array containing NA / NaN values

@@CodingIsFun this is error i am facing can u able to help me please

@@mrityunjayanrg787 Can you try to change the line: df_output = df[df[column_name].str.fullmatch(unique_value)] to this: df_output = df[df[column_name].str.fullmatch(unique_value, na=False)]

@@CodingIsFun nope not happening and it throws so much error

import pandas as pd import xlwings as xw # Open the workbook and sheet wb = xw.Book('path_to_workbook.xlsx') sheet = wb.sheets['Sheet1'] # Read the table data into a pandas dataframe df = sheet.range('TableName').options(pd.DataFrame, index=False, header=True).value # Close the workbook wb.close()

@@CodingIsFun It is okay. But this code doesn't include the Table Headers. It is considering the first row as my header. The counting starts from 2 second row

@@CodingIsFun Thank you for your response. That is AWESOME

Thanks for watching. Do you mean how to remove, in general, bold text and borders for a specific Excel cell range using Python?