How To Automate Excel Using Python | Combine Files & Create Charts 🤓

Sdílet
Vložit
  • čas přidán 21. 07. 2024
  • 👉 Explore All My Excel Solutions: pythonandvba.com/solutions
    𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
    ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
    In this tutorial, I will show you how you can automate your excel tasks using python.
    In particular, I am going to show you how you can combine excel files & create charts out of them.
    ✅ Chapters:
    0:00 Intro & Overview
    2:27 Python Code
    10:20 Outro
    Python Code and files used in the tutorial [GitHub]:
    👉 github.com/Sven-Bo/combine-ex...
    Pandas Cheat Sheet:
    👉 bit.ly/33K8DXp
    𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
    ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
    🆓【𝗙𝗥𝗘𝗘】Excel Add-in (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): pythonandvba.com/mytoolbelt
    📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): pythonandvba.com/grafly
    🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): pythonandvba.com/cuteplots
    🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): pythonandvba.com/emojify
    📑 Excel Templates: pythonandvba.com/go/excel-tem...
    🎓 My Courses: pythonandvba.com/go/courses
    📚 Books, Tools, and More: pythonandvba.com/resources
    𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
    ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
    🔗 LinkedIn: / sven-bosau
    📸 Instagram: / codingisfun_official
    💻 GitHub: github.com/Sven-Bo
    💬 Discord: pythonandvba.com/discord
    📬 Contact: pythonandvba.com/contact
    ☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
    If you want to support this channel, you can buy me a coffee here: pythonandvba.com/coffee-donation

Komentáře • 137

  • @mohammadjavadderakhshan6655

    Thanks to your tutorial and codes 15 hour work done Automatically without human interference.

  • @niuean22kid
    @niuean22kid Před 2 lety +3

    Thanks quite straightforward . I am a business analyst looking to expand on my excel knowledge

    • @CodingIsFun
      @CodingIsFun  Před 2 lety

      Thank you for watching the video & taking the time to leave a comment.
      Perhaps the following video might also be interesting for you: czcams.com/video/ZI9T2O7XYxY/video.html
      In the video, I am going to solve a real-world data science/analysis project with Python.
      Happy Coding! 👍

  • @calmlife5471
    @calmlife5471 Před 3 lety +6

    I was using string function to seperate the month,year data into a column . This is very useful sir !! Looking forward more informative videos🔥

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +3

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

  • @anasalhariri5474
    @anasalhariri5474 Před 3 lety +1

    Awesome, thank you, I subscribed. this is my first time visiting your page. Please, keep it up!

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +1

      Thank you! Glad you enjoyed the video. Welcome aboard! 😃

  • @SaulEduardoo2
    @SaulEduardoo2 Před 3 lety +1

    Great example!, this is exactly the kind of content I was looking for 😊☺

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      Thank you for watching the video. I am happy to hear that you like video :)

  • @dominospizzach
    @dominospizzach Před 2 lety +1

    This is amazing! I love your channel. You're super helpful!!

    • @CodingIsFun
      @CodingIsFun  Před 2 lety

      Very happy to hear that you found it useful! Thanks for watching!

  • @corentindurand6283
    @corentindurand6283 Před 3 lety +2

    Great ! Keep up the good work !

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      Thank you very much! 🙏 I greatly appreciate your support! 👍

  • @user-ig4mg7go1s
    @user-ig4mg7go1s Před 2 lety +1

    Great job! Thank you!

    • @CodingIsFun
      @CodingIsFun  Před 2 lety

      My pleasure! Thanks for watching the video and your comment!

  • @bak1970
    @bak1970 Před 3 lety +1

    Beautiful coding. I love Python.

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      *Hello Bassel Khaled, thanks for watching the video and your comment.*
      Me too, I also love using Python ❤

    • @bak1970
      @bak1970 Před 3 lety

      @@CodingIsFun You are welcome my fried. I started learning Python for more than 1 year through different platforms. It is very fun and powerful language specially if you are using Python's libraries. I already subscribed to you channel and looking forward to more coding . Thank you again .

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +1

      @@bak1970 I fully agree - the available libraries are making Python very powerful. Thanks for subscribing & Welcome aboard 😃

  • @anasalhariri5474
    @anasalhariri5474 Před 3 lety

    I have a question if you don't mind. I would like to reformat the date to MM /DD/YYY how can I do that, please?

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +1

      Inside the loop, you could adjust the date format to your liking, e.g. MM /DD/YYY would translate to "%m/%d/%Y"
      Please see below the adjusted code snippet.
      I also uploaded it on GistHub for you:
      gist.github.com/Sven-Bo/1388dda5edc4a16ee8b5f6f351a66e24
      The output would look like this:
      prnt.sc/12f53n0
      Hope you like this solution 🙏
      _________________________________________
      for file in files:
      df = pd.read_excel(file)
      df['Date'] = df['Date'].dt.strftime("%m/%d/%Y") # CHANGE HERE
      df['Day'] = pd.DatetimeIndex(df['Date']).day
      df['Month'] = pd.DatetimeIndex(df['Date']).month
      df['Year'] = pd.DatetimeIndex(df['Date']).year
      df.dropna(inplace=True)
      df['Month_Name'] = df['Month'].apply(lambda x: calendar.month_abbr[int(x)])
      combined = combined.append(df,ignore_index = True)

  • @AjinkyaBayani
    @AjinkyaBayani Před rokem

    this was most unique video and helpfull plz guide us more

    • @CodingIsFun
      @CodingIsFun  Před rokem

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

  • @ahmadmojeed5507
    @ahmadmojeed5507 Před 2 lety +1

    Thanks for the amazing work.
    Haven't gotten a reply yet though, what if there's a different folder directory, the both have same column name but different content, how do we merge?
    For example, there's folder A that contains January to March sales and folder B has January to March sales also but different sales input. How do we merge per column, merge folder then have the January in one xlsx file, February in one and the whole of march in one xlsx also

    • @CodingIsFun
      @CodingIsFun  Před 2 lety +1

      Hi,
      To iterate over all folders, check out this video: czcams.com/video/w6-28jcr09Q/video.html
      Then store each excel file in a separate dataframe and use pd.append.
      The additional columns will be added to the dataframe (as mentioned in the docs: pandas.pydata.org/docs/reference/api/pandas.DataFrame.append.html
      After merging the dataframes, you can still drop the unwanted columns. I hope this helps! Happy Coding!

    • @ahmadmojeed5507
      @ahmadmojeed5507 Před 2 lety

      @@CodingIsFun gonna try it out. THANKS 😊

  • @joiepatsy4991
    @joiepatsy4991 Před 3 lety +1

    Thankyou sir, very good explanation. Sir you told that we can import to power point? how can we make it? can we import the table as well to ppt? thankyou so much, i subscribed

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +2

      Thank you for watching the video & welcome aboard :)
      Creating PowerPoint Presentation would be a video on its own. Nonetheless, I have created the following example for you:
      To export static images using plotly, make sure to install Kaleido (pip install -U kaleido)
      from pptx import Presentation # pip install python-pptx
      import pandas as pd
      import plotly.express as px
      df = pd.read_csv("raw.githubusercontent.com/mwaskom/seaborn-data/master/tips.csv")
      # CREATE BAR CHART
      fig = px.bar(df, x="day", y="tip")
      fig.write_image("bar_chart.png") # pip install -U kaleido
      # CREATE PPT
      prs = Presentation()
      slide = prs.slides.add_slide(prs.slide_layouts[8])
      placeholder = slide.placeholders[1]
      picture = placeholder.insert_picture("bar_chart.png")
      prs.save("Example.pptx")
      Hope it helps 😃

  • @lorenzoladejobi8701
    @lorenzoladejobi8701 Před 3 lety +3

    Very informative !!. Thank you for this.

  • @usamajunaid2989
    @usamajunaid2989 Před rokem

    Great!
    If you are also the user of R Language then please make videos like this in RStudio to automate excel like data manipulation and conditional formulation for excel in RStudio

    • @CodingIsFun
      @CodingIsFun  Před rokem

      Thanks for watching and your suggestion. Yet, I have never used R 😬

  • @anonymousm4328
    @anonymousm4328 Před rokem

    is there anyway to preserve formatting after splitting excel files ? VBA can but i cant find a way with python.

    • @CodingIsFun
      @CodingIsFun  Před rokem

      Yes, check out "xlwings" as shwon in this video: czcams.com/video/JoonRjMsSdY/video.html

  • @user-rr7yi3ru2p
    @user-rr7yi3ru2p Před 7 měsíci

    Hello! Tell me how to automatically select a function for data in Excel, for example, for the R^2 criterion: polynomial, linear or cubic? Or automatically compose a step function. Are there any built-in functions for this in Excel? Or maybe in Python? Sincerely!

    • @CodingIsFun
      @CodingIsFun  Před 6 měsíci

      Thanks for watching. Sorry, but I don't have time for custom solutions. I hope you can understand.

  • @abhisheksonawane2997
    @abhisheksonawane2997 Před rokem

    Hello Sven, I had a small problem in the code mentioned in the video,
    After writing the line of code (combined=combined.append(df), I am getting error code saying - 'AttributeError: 'DataFrame' object has no attribute 'append'' Can you please tell me what I can do for this?

    • @CodingIsFun
      @CodingIsFun  Před rokem

      Thanks for watching. Please use the latest code from GitHub: github.com/Sven-Bo/combine-excel-files-with-python/blob/master/combine_files.py

    • @abhisheksonawane2997
      @abhisheksonawane2997 Před rokem

      @@CodingIsFun Thanks Sven, this is indeed very helpful.

  • @onurelmas8427
    @onurelmas8427 Před rokem

    Hi ! Can anyone help me ? I want to automate excel with python. But Excel is common used by my collugues. If we want to do it same time. I get a error message like this-> discard the changes or save as. How can I automate common used excel ?

    • @CodingIsFun
      @CodingIsFun  Před rokem +1

      Thanks for watching. To avoid conflicts when multiple users try to access and edit the same Excel file, you can consider using a database, a cloud-based solution, or a shared drive like Dropbox, but you may still encounter conflicts if multiple users try to edit the same cell simultaneously.

  • @rohithd
    @rohithd Před 3 lety +1

    Which is the editor tool that you have used to write python code?

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +1

      I am using the free text editor 'Atom': atom.io/

  • @vitus2537
    @vitus2537 Před 3 lety +1

    Good tutorial. Thank you for this, just awesome

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      *Thank you.* I am glad you enjoyed the video 😃

    • @vitus2537
      @vitus2537 Před 3 lety

      @@CodingIsFun Can you help with one question? How combine excel files which have more one sheet?
      Using first script in this tutorial combines only first sheet.

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      @@vitus2537 You could also iterate over each worksheet for the given Workbook.
      I have adjusted the Excel Files from the tutorial, split the data into different worksheets and adjusted my code accordingly.
      Please find the example I have created for you on my Google Drive:
      drive.google.com/file/d/1sBGjN_rg8ieg9Cfh1MQl1vZg5nAmhq-Q/view?usp=sharing
      ____________________________________________________
      files = ["January.xlsx", "February.xlsx", "March.xlsx"]
      # Create Empty DataFrame
      combined = pd.DataFrame()
      for file in files:
      excel_file = pd.ExcelFile(file)
      # Get all sheet names for each Excel File
      sheet_names = excel_file.sheet_names
      # Iterate over each sheet name & create append DataFrame
      for sheet_name in sheet_names:
      df = pd.read_excel(file, sheet_name=sheet_name)
      df["Date"] = df["Date"].dt.date
      df["Day"] = pd.DatetimeIndex(df["Date"]).day
      df["Month"] = pd.DatetimeIndex(df["Date"]).month
      df["Year"] = pd.DatetimeIndex(df["Date"]).year
      combined = combined.append(df, ignore_index=True)
      # Clean Up NaN Values
      combined.dropna(inplace=True)
      ____________________________________________________
      Hope it helps 🙏
      Cheers,
      Sven ✌

    • @vitus2537
      @vitus2537 Před 3 lety

      @@CodingIsFun Thank you for help! it's nice code :) You combine all sheets in one but me need combine all sheets with save names of sheets. For example: we have two workbook ("first.xlsx" and "second.xlsx") and in every workbook we have two sheet ("Sheet1" and "Sheet2"). After combine i will have workbook "Combine.xlsx" with two sheet "sheet1" and "sheet2" and it is my big problem because i don't know pandas))
      Sorry for my english because i'm Not a native language.

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      @@vitus2537 Ok understood. Please find your new example here 😃
      👉 drive.google.com/file/d/1zeKW6pfd5KojB99XR-t8S1cZaSg-z7VT/view?usp=sharing
      Please install xlwings before running the code: pip install xlwings.
      Below is the code:
      import pandas as pd
      import xlwings as xw
      # Specify Files Names [ff the files are NOT in the same directory as the script, please use the absolute path to the files]
      files = ["first.xlsx", "second.xlsx"]
      wb = xw.Book("combined.xlsx")
      for file in files:
      # Create Empty DataFrame
      combined = pd.DataFrame()
      # Use Excel File to get access to Worksheets
      excel_file = pd.ExcelFile(file)
      # Get all sheet names for each Excel File
      sheet_names = excel_file.sheet_names
      # Iterate over each sheet name & create append DataFrame
      for sheet_name in sheet_names:
      df = pd.read_excel(file, sheet_name=sheet_name)
      df["Date"] = df["Date"].dt.date
      df["Day"] = pd.DatetimeIndex(df["Date"]).day
      df["Month"] = pd.DatetimeIndex(df["Date"]).month
      df["Year"] = pd.DatetimeIndex(df["Date"]).year
      combined = combined.append(df, ignore_index=True)
      # Take DataFrame (which includes data for all sheets) and append to new Workbook
      wb.sheets.add().range("A1").value = combined

  • @anasofia4048
    @anasofia4048 Před rokem +1

    Hello there, I have a problem ! I want to get information from excel per client. Meaning, I'd like to input the client's name and then extract some of the data from THAT client (I have an excel report with different data from different clients) - not sure what library / module / functions to use. Any ideas? Many thanks. Excellent videos !

    • @CodingIsFun
      @CodingIsFun  Před rokem

      It's a good question but difficult to answer because it depends on many factors. I am unsure what your input form looks like (GUI, Excel Sheet, Web application, ..) and how your data is structured. Sorry that I cannot help here!

    • @anasofia4048
      @anasofia4048 Před rokem

      @@CodingIsFun it's a quite basic question (shame on me!). I have an excel sheet with different client's names. I'd like to select one of them and retrieve the data from it. Can i use an input form (whichever?) that will then filter through that excel sheet? (perhaps you have a video about it ?)

    • @CodingIsFun
      @CodingIsFun  Před rokem

      @@anasofia4048 Ok got it! I do not have a full tutorial on this. Yet, I have once created a similar demo application. You can find the demo video and code here: czcams.com/video/RnTqlKzQhRY/video.html
      I hope it helps as a starting point. Happy Coding!

  • @kariza87
    @kariza87 Před 3 lety

    Nicely elaborate

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

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

  • @maramsrinureddy
    @maramsrinureddy Před 2 lety +1

    Great content in the video, how to create multiple sheets in the excel doc using pandas, is there a way ?(example: - create 3 sheets for 3 months data), is it possible ?

    • @CodingIsFun
      @CodingIsFun  Před 2 lety

      Thanks!
      For creating worksheets, I would recommend using the library 'openpyxl'. In fact, pandas uses 'openpyxl' under the hood when dealing with Excel files.
      That said, you could also use Pandas to create workbooks with 3 sheets; see the example below:
      import pandas as pd
      df = pd.DataFrame()
      with pd.ExcelWriter('output.xlsx') as writer:
      df.to_excel(writer, sheet_name='Sheet_name_1')
      df.to_excel(writer, sheet_name='Sheet_name_2')
      df.to_excel(writer, sheet_name='Sheet_name_3')
      I hope this helps!

  • @AmitSingh-pe9ss
    @AmitSingh-pe9ss Před 3 lety

    I want to know how we will run the automation task? Like we go to excel,, click on macro and run macro build by VBA but for python how and from where to run the automation.

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +1

      Hi Amit Singh. Thanks for watching the video and your question.
      You could execute a Python Script (by clicking a button) from Excel using VBA. Hopefully, the following video will help you:
      czcams.com/video/MgL9IOCFZ0U/video.html
      Thank you!

    • @AmitSingh-pe9ss
      @AmitSingh-pe9ss Před 3 lety

      Thank you for your response. I will check.. keep showing python to automate ms office application. VBA is not in demand

  • @MacGyver90
    @MacGyver90 Před 3 lety

    Pls need help. How can I explote data detail from a excel pivot table then save in a new file with Python? I need automate a excel report.

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      Thank you for watching the video.
      I have created the following example for you:
      drive.google.com/file/d/1MdrTimYTbdf1rGgcV3vjk-sWy_RGSncd/view?usp=sharing
      Before running the script, please make sure that you have xlwings installed -> pip install xlwings.
      I have inserted also comments for each line in the code. Hope it is clear what each line of the does 🙏
      _____________________________________________
      import xlwings as xw # pip install xlwings
      # -- Connect to Workbook/Worksheet
      wb = xw.Book('Data_with_pivot.xlsx')
      sht = wb.sheets('PIVOT')
      # -- Store values from Pivot Table in variable
      data = sht.range('A3:B21').value
      # -- Create new Workbook, Insert Values & Save/Close Workbook
      wb_new = xw.Book()
      sht = wb_new.sheets[0] #First Worksheet
      sht.range('A1').value = data #Insert data from Pivot Table
      wb_new.save('My_New_Workbook.xlsx')

    • @MacGyver90
      @MacGyver90 Před 3 lety

      @@CodingIsFun Thanks you. I will test it. I have a excel report with 2 pivot table without source db. I have to explode (show details) some pivot table and get 2 dbs, then I have to rename the sheet of that 2 bds and save changes the report. Sorry for my english.

  • @ciccychen5911
    @ciccychen5911 Před rokem

    thanks for the video, how does py change a text file to py file? mine is not changing

    • @CodingIsFun
      @CodingIsFun  Před rokem

      Thanks for watching. To change the file extension on Windows:
      - Open the folder containing your text file.
      - Click on the "View" tab at the top of the File Explorer window and make sure the "File name extensions" checkbox is checked. This will allow you to see and change the extensions of files in File Explorer.
      - Right-click on your text file and select "Rename".
      - Change the extension from .txt to .py.
      - A warning will pop up, explaining that changing a file extension might make the file unusable. Since you're intentionally changing the extension to .py so that it can be run as a Python script, you can safely click "Yes" to proceed.
      I hope it helps! Happy Coding!

  • @user-do3nl9fq6n
    @user-do3nl9fq6n Před 2 lety +1

    thank you sir,
    Question please:
    your method will take too long executing because the code will go to each cell in the rows?
    why you didn't use "OpenPyXL" which one is better?

    • @CodingIsFun
      @CodingIsFun  Před 2 lety +1

      Under the hood, pandas is using openpyxl when dealing with Excel files. Pandas makes it easier to manipulate the data, e.g. combine rows or use a Data frame to plot the data

    • @user-do3nl9fq6n
      @user-do3nl9fq6n Před 2 lety

      @@CodingIsFun you are kind, thank you very much

  • @asensiodias1593
    @asensiodias1593 Před 2 lety

    Which editor you use for Python or what ide is good for it

    • @CodingIsFun
      @CodingIsFun  Před 2 lety

      I am using Atom (atom.io/), which is a lightweight text editor.
      My other recommendation would be VS Code: code.visualstudio.com/

  • @Joe-ps7em
    @Joe-ps7em Před 2 lety

    Hi. The .append is deprecated, how would we use .concat in this example?

    • @CodingIsFun
      @CodingIsFun  Před 2 lety +1

      Thanks for the hint. I have updated the Github repo.
      *OLD:*
      combined = combined.append(df, ignore_index=True)
      *NEW:*
      combined = pd.concat([combined, df], ignore_index=True)

    • @CodingIsFun
      @CodingIsFun  Před 2 lety

      @@Joe-ps7em Thanks for your feedback. Due to my current workload, I do not offer 1:1 lessons. That said, you might want to check out my favourite resources to learn Python:
      www.pythonandvba.com/resources/#Python
      Happy Learning & Coding!

  • @asankacool1
    @asankacool1 Před 3 lety +1

    Hi, i get an error when tried to get aggregate % of 2 different variable-equation for Reject %.
    # df['%'] = df['Reject / (Pcs)'].sum()/df['Dip Qty / (Prs)'].sum() ; this gives error.
    Do you have any clue for this? Thanks :)

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      What does the error message say?

    • @asankacool1
      @asankacool1 Před 2 lety

      @@CodingIsFun Thanks, It's as follows.
      pivot_4 = join.pivot_table(index=['M/C'], values=['Dip Qty / (Prs)'], columns=['Month'], margins=True, aggfunc='sum')
      pivot_4['%'] = pivot_4['Reject / (Pcs)'] / pivot_4['Dip Qty / (Prs)']
      ------------------------------------------------------------------------------
      KeyError Traceback (most recent call last)
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
      3079 try:
      -> 3080 return self._engine.get_loc(casted_key)
      3081 except KeyError as err:
      pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()
      pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()
      pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()
      pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()
      KeyError: 'Reject / (Pcs)'
      The above exception was the direct cause of the following exception:
      KeyError Traceback (most recent call last)
      in
      ----> 1 pivot_4['%'] = pivot_4['Reject / (Pcs)'] / pivot_4['Dip Qty / (Prs)']
      ~\Anaconda3\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
      3021 if is_single_key:
      3022 if self.columns.nlevels > 1:
      -> 3023 return self._getitem_multilevel(key)
      3024 indexer = self.columns.get_loc(key)
      3025 if is_integer(indexer):
      ~\Anaconda3\lib\site-packages\pandas\core\frame.py in _getitem_multilevel(self, key)
      3072 def _getitem_multilevel(self, key):
      3073 # self.columns is a MultiIndex
      -> 3074 loc = self.columns.get_loc(key)
      3075 if isinstance(loc, (slice, np.ndarray)):
      3076 new_columns = self.columns[loc]
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in get_loc(self, key, method)
      2874
      2875 if not isinstance(key, tuple):
      -> 2876 loc = self._get_level_indexer(key, level=0)
      2877 return _maybe_to_slice(loc)
      2878
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in _get_level_indexer(self, key, level, indexer)
      3156 else:
      3157
      -> 3158 idx = self._get_loc_single_level_index(level_index, key)
      3159
      3160 if level > 0 or self.lexsort_depth == 0:
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in _get_loc_single_level_index(self, level_index, key)
      2807 return -1
      2808 else:
      -> 2809 return level_index.get_loc(key)
      2810
      2811 def get_loc(self, key, method=None):
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
      3080 return self._engine.get_loc(casted_key)
      3081 except KeyError as err:
      -> 3082 raise KeyError(key) from err
      3083
      3084 if tolerance is not None:
      KeyError: 'Reject / (Pcs)'

    • @CodingIsFun
      @CodingIsFun  Před 2 lety

      @@asankacool1 According to the error traceback, it seems that column 'Reject / (Pcs)' in your 'pivot4' does not exist. You might wanna check/print out your column names: print(pivot4.columns)

    • @asankacool1
      @asankacool1 Před 2 lety

      @@CodingIsFun Thanks bro, corrected it & now rejects exists. and I now get this. I cannot figure out what I have done wrong here fundamentally.
      -------------------------------------
      KeyError Traceback (most recent call last)
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
      3079 try:
      -> 3080 return self._engine.get_loc(casted_key)
      3081 except KeyError as err:
      pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()
      pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()
      pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()
      pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()
      KeyError: '%'
      The above exception was the direct cause of the following exception:
      KeyError Traceback (most recent call last)
      ~\Anaconda3\lib\site-packages\pandas\core\generic.py in _set_item(self, key, value)
      3825 try:
      -> 3826 loc = self._info_axis.get_loc(key)
      3827 except KeyError:
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in get_loc(self, key, method)
      2875 if not isinstance(key, tuple):
      -> 2876 loc = self._get_level_indexer(key, level=0)
      2877 return _maybe_to_slice(loc)
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in _get_level_indexer(self, key, level, indexer)
      3157
      -> 3158 idx = self._get_loc_single_level_index(level_index, key)
      3159
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in _get_loc_single_level_index(self, level_index, key)
      2808 else:
      -> 2809 return level_index.get_loc(key)
      2810
      ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
      3081 except KeyError as err:
      -> 3082 raise KeyError(key) from err
      3083
      KeyError: '%'
      During handling of the above exception, another exception occurred:
      ValueError Traceback (most recent call last)
      in
      ----> 1 pivot_4['%'] = pivot_4['Reject / (Pcs)'] / pivot_4['Dip Qty / (Prs)']
      ~\Anaconda3\lib\site-packages\pandas\core\frame.py in __setitem__(self, key, value)
      3161 else:
      3162 # set column
      -> 3163 self._set_item(key, value)
      3164
      3165 def _setitem_slice(self, key: slice, value):
      ~\Anaconda3\lib\site-packages\pandas\core\frame.py in _set_item(self, key, value)
      3241 self._ensure_valid_index(value)
      3242 value = self._sanitize_column(key, value)
      -> 3243 NDFrame._set_item(self, key, value)
      3244
      3245 # check if we are modifying a copy
      ~\Anaconda3\lib\site-packages\pandas\core\generic.py in _set_item(self, key, value)
      3827 except KeyError:
      3828 # This item wasn't present, just insert at end
      -> 3829 self._mgr.insert(len(self._info_axis), key, value)
      3830 return
      3831
      ~\Anaconda3\lib\site-packages\pandas\core\internals\managers.py in insert(self, loc, item, value, allow_duplicates)
      1201 value = safe_reshape(value, (1,) + value.shape)
      1202
      -> 1203 block = make_block(values=value, ndim=self.ndim, placement=slice(loc, loc + 1))
      1204
      1205 for blkno, count in _fast_count_smallints(self.blknos[loc:]):
      ~\Anaconda3\lib\site-packages\pandas\core\internals\blocks.py in make_block(values, placement, klass, ndim, dtype)
      2740 values = DatetimeArray._simple_new(values, dtype=dtype)
      2741
      -> 2742 return klass(values, ndim=ndim, placement=placement)
      2743
      2744
      ~\Anaconda3\lib\site-packages\pandas\core\internals\blocks.py in __init__(self, values, placement, ndim)
      140
      141 if self._validate_ndim and self.ndim and len(self.mgr_locs) != len(self.values):
      --> 142 raise ValueError(
      143 f"Wrong number of items passed {len(self.values)}, "
      144 f"placement implies {len(self.mgr_locs)}"
      ValueError: Wrong number of items passed 14, placement implies 1

    • @CodingIsFun
      @CodingIsFun  Před 2 lety

      @@asankacool1 It is a little hard for me to debug your code via the CZcams comments. Regarding your error message, check out the following Stackoverflow post:
      stackoverflow.com/questions/43196907/valueerror-wrong-number-of-items-passed-meaning-and-suggestions

  • @sagarmhta
    @sagarmhta Před 3 lety +2

    Your videos are really helpful;
    How we can learn python from scratch?

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +6

      Thank you very much. 🙏 Glad the videos are helpful to you.
      In regards to your questions, it depends on some factors. For instance, if you know any other programming languages or your preferred media to learn a programming language.
      If you do not have any programming experience, I would start by learning the ‘basics’ such as variables, lists, functions, etc.
      However, I would not spend too much time learning the basics. I rather would suggest picking a small project.
      📚 My book recommendation to learn Python:
      “Automate the boring stuff” from Al Sweigart
      Why do I think the book is a great resource for beginners in Python?
      1. PRACTICAL examples to automate Excel, Word, PDF, E-Mail, ...
      2. Concepts are clearly explained
      3. Each section is broken down into consumable bits
      The content of the book can be found for FREE here:
      👉 automatetheboringstuff.com/
      🎞 If you prefer a video-based course, I can recommend the one from Angela Yu on Udemy:
      👉 www.udemy.com/course/100-days-of-code/
      Usually, Udemy offers regularly promotions, where you can get each course for around 10$ USD.
      📢Last but not least, please feel free to also reach out to me via the channel discussion board:
      👉 czcams.com/channels/ZjRcM1ukeciMZ7_fvzsezQ.htmldiscussion
      I will be very happy to help you out in case of any questions😊
      Happy Coding!
      Cheers
      Sven

  • @Joe-ps7em
    @Joe-ps7em Před 2 lety

    Excellent

  • @RajneeshKumar-vr1qj
    @RajneeshKumar-vr1qj Před 2 lety +1

    Nice tutorial btw same work could be done by power query or vba much faster

    • @CodingIsFun
      @CodingIsFun  Před 2 lety

      Thanks for watching and your comment! :)

  • @cool89ful
    @cool89ful Před 3 lety

    combined = combined.append(df, ignore_index = True) this line giving me error "append() takes at least 2 arguments (2 given)". Can anybody help me solving this.

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      Thanks for watching the video.
      Did you try to use the code from my example (👉 bit.ly/2JnRhZ7), or have you modified the code base? If so, code you share the full code here? Thank you 😃

    • @cool89ful
      @cool89ful Před 3 lety

      @@CodingIsFun Hi I have used the same base code as yours..., I have not modified anything..

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      @@cool89ful Sorry for my late reply. I am very sorry, but I am not sure why this error occurs on your side 😕

  • @asankacool1
    @asankacool1 Před 3 lety +2

    The date,month segregation shown in the video is very useful for analysis. 💥💥💥

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      Thank you, glad it is useful! ❤😃👩‍💻

  • @jefrin
    @jefrin Před 2 lety

    Hey, that is a superb video. I am a newbie to python. When I try to execute, it is showing this error.
    ValueError: Invalid file path or buffer object type:

    • @CodingIsFun
      @CodingIsFun  Před 2 lety +1

      Thank you for watching the video and trying out the solution. It is hard to tell what the issue could be without seeing the full error trace log.
      I assume that you have stored the Excel files in the same directory as the Python script?! If so, you could try to change the current working directory by including the following lines on top of the script:
      import os, sys
      os.chdir(sys.path[0])
      Hope this helps!

    • @jefrin
      @jefrin Před 2 lety

      @@CodingIsFun Thanks! I have tried it and it worked. I also have sent you a mail regarding this error message in detail. This video helped me a lot!!

    • @CodingIsFun
      @CodingIsFun  Před 2 lety +1

      @@jefrin Thank you, I have just seen your email and replied :)

  • @sudharshan574
    @sudharshan574 Před 3 lety

    Please make vdeo on progress bar, to show much file is uploaded after pd_read.excel

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      Thanks for watching the video & your video suggestion 👍😀

  • @user-qp8ei8lc3d
    @user-qp8ei8lc3d Před 3 lety

    it was amazing

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +1

      *Thank you.* I am glad you enjoyed the video 😃

    • @user-qp8ei8lc3d
      @user-qp8ei8lc3d Před 3 lety

      If you can post more videos in that direction. Using Python to automate Excel routines. Thank you very much!

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +1

      @@user-qp8ei8lc3d Thank you for the video suggestion. In fact, I am planning to create more videos around Excel Automation 👍👩‍💻

  • @Jurginho10
    @Jurginho10 Před 8 měsíci

    How can I do for sending the file via mail or whatsapp?

    • @CodingIsFun
      @CodingIsFun  Před 7 měsíci +1

      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!

  • @akshathbharathi7376
    @akshathbharathi7376 Před 2 lety

    Can you make video explaining all three activities like sending excel through whatsapp, saving it on cloud and sending through web app?

    • @CodingIsFun
      @CodingIsFun  Před 2 lety +1

      Thanks for watching the video & your suggestions. I cannot make any promises, but l will see what I can do.

    • @akshathbharathi7376
      @akshathbharathi7376 Před 2 lety

      @@CodingIsFun it would be of great help. Take your time Master.

  • @jaaviis46
    @jaaviis46 Před 3 lety

    How do you install libraries in Mac computers?

    • @CodingIsFun
      @CodingIsFun  Před 3 lety

      I am not really familiar with Mac, but I found the following video:
      czcams.com/video/yBdZZGPpYxg/video.html&ab_channel=AutomatingOSINT
      Hope it helps you 🙏

  • @sachin.tandon
    @sachin.tandon Před 2 lety +1

    Do you know Django? With your skills, you could definitely create some amazing softwares!

  • @sofuhuseyin1952
    @sofuhuseyin1952 Před 2 měsíci

    I have a specific question:
    I want to automate our material inquiries. We always send an Excel list to, for example, building material suppliers or provide a link from which they can download the list.
    Here's how I'd like it solved:
    The supplier offers me materials. For instance, they might offer some materials but not all. For each material, a cell should be assigned with the price (B10….Bx), and in column A, there's the description of the material, etc. The supplier sends or uploads the file.
    I have X suppliers who provide me with offers. How can I automate this? I'd like the offers from different providers to be displayed in separate columns in an Excel sheet and evaluated based on criteria such as the cheapest price (ideally marked in green) and the most expensive price (marked in red).
    At the end, I'd like to have an Excel file where all suppliers are listed in one column, with the evaluation of the cheapest and most expensive offers in the last columns, and the ideal prices (cheapest) in another column. The challenge lies in automatically entering the offers into a new column, in the order they're provided by the suppliers.
    I look forward to your support and feedback.
    Regards from Augsburg

    • @CodingIsFun
      @CodingIsFun  Před 2 měsíci

      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! Cheers, Sven ✌️

  • @sulalithasannasgala
    @sulalithasannasgala Před rokem

    Using libre calc same as Excel

    • @CodingIsFun
      @CodingIsFun  Před rokem +2

      Pandas can read data from Excel files, including those created using LibreOffice Calc, using the pd.read_excel() function. The function can read both .xls and .xlsx file formats.

  • @DarkoVlajkovic
    @DarkoVlajkovic Před rokem

    You could do it with Python, but i feel its so much easier to do it in Power Query, you dont even need programming skills to do it...Maybe im missing something

    • @CodingIsFun
      @CodingIsFun  Před rokem +1

      Thanks for watching and for your comment! Power Query is a great tool. If Power Query meets your needs, there's no need to switch. :)

  • @kristinapaseru2518
    @kristinapaseru2518 Před rokem

    i tried your code but didnt succeed. it said no such file or directory. any idea?

    • @CodingIsFun
      @CodingIsFun  Před rokem

      Hey there, thanks for watching the video! I'm sorry I can't help you with your problem based on the information you provided. To give me a better idea of what's going on, it would be super helpful if you could write down which line of code is causing the error, let me know if you modified the code from the tutorial, and explain in more detail what you did to troubleshoot the problem. Don't forget to also give me some context about your setup and environment.
      If you're having trouble figuring things out, another option is to join our Discord server at pyhtonandvba.com/discord. You can ask your question there and maybe someone in the community can help out.
      Thanks for understanding.

    • @kristinapaseru2518
      @kristinapaseru2518 Před rokem

      # imports
      import pandas as pd
      import datetime as dt
      files = ['January','February','March']
      combined = pd.DataFrame()
      for file in files:
      df=pd.read_excel(file)
      combined = combined.append(df,ignore_index=True)

      combined.to_excel('Admits_Q1_2022',index=False,sheet_name='Q1 2022')

    • @kristinapaseru2518
      @kristinapaseru2518 Před rokem

      i only modified the name of the file with its sheet name, the rest remain same. thank you anw for your reply

    • @CodingIsFun
      @CodingIsFun  Před rokem

      @@kristinapaseru2518 Use the latest code from GitHub: github.com/Sven-Bo/combine-excel-files-with-python/blob/master/combine_files.py

    • @kristinapaseru2518
      @kristinapaseru2518 Před rokem

      @@CodingIsFun File "", line 7
      BASE_DIR = Path(C:\Users\KRISTINA\Desktop\Combine_Excel_Files).parent
      ^
      SyntaxError: invalid syntax

  • @igormaricic7093
    @igormaricic7093 Před rokem

    noice

  • @rprasathr
    @rprasathr Před 3 lety

    Any tutorial sending the file via WhatsApp?

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +1

      Thanks for watching the tutorial. Currently, I do not have a tutorial on how to send files via WhatsApp. Unfortunately, WhatsApp does not offer a free API. To send files via WhatsApp without using any paid service, you might want to look at browser automation tools/libraries like 'Selenium' to automate the WebWhatsApp interface.
      Cheers,
      Sven ✌

    • @rprasathr
      @rprasathr Před 3 lety

      Thanks a lot Sven, really appreciate it.
      Does telegram have any API that allows similar automation?

    • @CodingIsFun
      @CodingIsFun  Před 3 lety +1

      @@rprasathr I went ahead and checked it - Yes, Telegram offers a free API:
      core.telegram.org/api/terms
      However, I have never used it before. But I found the following video, which shows how to send a file using Telegram using Python (via post request):
      czcams.com/video/NYT1KFE1X2o/video.html
      Hope it helps :)

    • @rprasathr
      @rprasathr Před 3 lety

      @@CodingIsFun thanks a lot