1665117000
Configure o Python Excel Automation em 5 etapas fáceis
Excel é um aplicativo de planilha que foi desenvolvido pela Microsoft no ano de 1987. É oficialmente suportado por quase todos os sistemas operacionais como Windows, Macintosh, Android, etc. Ele vem pré-instalado com o sistema operacional Windows e pode ser facilmente integrado com outras plataformas de SO. O Microsoft Excel é a melhor e mais acessível ferramenta para trabalhar com dados estruturados.
No tutorial de hoje, aprenderemos as etapas para configurar o Python Excel Automation. Você pode seguir as etapas abaixo para configurar o Python Excel Automation:
Índice
- Etapa 1: analisando o conjunto de dados do Excel
- Etapa 2: criando tabelas dinâmicas usando Pandas
- Etapa 3: projetando os relatórios usando Openpyxl
- Etapa 4: automatizar o relatório com Python
- Etapa 5: Agendando o script Python
Etapa 1: analisando o conjunto de dados do Excel
A primeira etapa na automação do Python Excel é analisar o conjunto de dados. O conjunto de dados usado neste tutorial é um conjunto de dados de vendas. Este conjunto de dados também está disponível no Kaggle . Como este Dataset está no formato .csv , você precisa alterá-lo para o formato .xslx . Os dados serão usados para criar o relatório abaixo após configurar nosso Python Excel Automation.
Etapa 2: criando tabelas dinâmicas usando Pandas
A próxima etapa na automação do Python Excel é projetar tabelas dinâmicas. Antes de fazer isso, você precisa importar as seguintes bibliotecas:
import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import stringPandas é usado para ler o arquivo do Excel, criar a tabela dinâmica e exportá-la para o Excel. Você pode então usar a biblioteca Openpyxl em Python para escrever fórmulas do Excel, fazer gráficos e planilhas em Python.
Para ler seu arquivo do Excel, verifique se o arquivo está no mesmo local em que o script Python está localizado e execute o seguinte código no Excel:
excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]Para criar a tabela dinâmica, você precisa acessar o quadro de dados excel_file que você criou anteriormente. Você pode usar o “ .pivot_table() ” para criar a tabela. Se você quiser criar uma tabela dinâmica para mostrar o total de dinheiro dividido para homens e mulheres, você pode executar o código abaixo:
report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)Por fim, para exportar a Tabela Dinâmica, usaremos o método “ .to_excel() ” conforme mostrado abaixo:
report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)A pasta do Excel agora é exportada no mesmo local que seus scripts Python.
Etapa 3: projetando os relatórios usando Openpyxl
A próxima etapa no Python Excel Automation é projetar os relatórios. Para fazer o relatório, você precisa usar o método “load_workbook” , que é importado do Openpyxl e salvá-lo usando o método “ .save()” . Isso é mostrado abaixo:
wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_rowPython Excel Automation permite que você crie gráficos do Excel usando tabelas dinâmicas. Para criar um gráfico do Excel usando uma Tabela Dinâmica, você precisa usar o Módulo Barchart e para identificar a posição dos dados e valores de categoria, você pode usar o Módulo Referência. Ambos foram importados antes na Etapa 1. Você pode escrever fórmulas baseadas no Excel em Python, da mesma forma que as escreve no Excel. Um exemplo disso é mostrado abaixo:
sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'CurrencyEtapa 4: automatizar o relatório com Python
A próxima etapa na automação do Python Excel é automatizar seu relatório. Você pode escrever todo o código em uma função para que seja fácil automatizar o relatório. Esse código é mostrado abaixo:
import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string
def automate_excel(file_name):
"""The file name should have the following structure: sales_month.xlsx"""
# read excel file
excel_file = pd.read_excel(file_name)
# make pivot table
report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
# splitting the month and extension from the file name
month_and_extension = file_name.split('_')[1]
# send the report table to excel file
report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
# loading workbook and selecting sheet
wb = load_workbook(f'report_{month_and_extension}')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row
# adding a chart
barchart = BarChart()
data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
barchart.add_data(data, titles_from_data=True)
barchart.set_categories(categories)
sheet.add_chart(barchart, "B12") #location chart
barchart.title = 'Sales by Product line'
barchart.style = 2 #choose the chart style
# applying formulas
# first create alphabet list as references for cells
alphabet = list(string.ascii_uppercase)
excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
# sum in columns B-G
for i in excel_alphabet:
if i!='A':
sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
sheet[f'{i}{max_row+1}'].style = 'Currency'
sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
# getting month name
month_name = month_and_extension.split('.')[0]
# formatting the report
sheet['A1'] = 'Sales Report'
sheet['A2'] = month_name.title()
sheet['A1'].font = Font('Arial', bold=True, size=20)
sheet['A2'].font = Font('Arial', bold=True, size=10)
wb.save(f'report_{month_and_extension}')
returnEtapa 5: Agendando o script Python
A etapa final na automação do Python Excel é executar o script Python em horários diferentes, conforme os requisitos de dados. Você só precisa usar o agendador de tarefas ou o cron no Windows e no Mac, respectivamente.
É isso! Você configurou com sucesso o Python Excel Automation em 5 etapas fáceis!
What is GEEK
Buddha Community
1619510796
Lambda, Map, Filter functions in python
Welcome to my Blog, In this article, we will learn python lambda function, Map function, and filter function.
Lambda function in python: Lambda is a one line anonymous function and lambda takes any number of arguments but can only have one expression and python lambda syntax is
Syntax: x = lambda arguments : expression
Now i will show you some python lambda function examples:
#python #anonymous function python #filter function in python #lambda #lambda python 3 #map python #python filter #python filter lambda #python lambda #python lambda examples #python map
1626775355
Why use Python for Software Development
No programming language is pretty much as diverse as Python. It enables building cutting edge applications effortlessly. Developers are as yet investigating the full capability of end-to-end Python development services in various areas.
By areas, we mean FinTech, HealthTech, InsureTech, Cybersecurity, and that's just the beginning. These are New Economy areas, and Python has the ability to serve every one of them. The vast majority of them require massive computational abilities. Python's code is dynamic and powerful - equipped for taking care of the heavy traffic and substantial algorithmic capacities.
Programming advancement is multidimensional today. Endeavor programming requires an intelligent application with AI and ML capacities. Shopper based applications require information examination to convey a superior client experience. Netflix, Trello, and Amazon are genuine instances of such applications. Python assists with building them effortlessly.
5 Reasons to Utilize Python for Programming Web Apps
Python can do such numerous things that developers can't discover enough reasons to admire it. Python application development isn't restricted to web and enterprise applications. It is exceptionally adaptable and superb for a wide range of uses.
Robust frameworks
Python is known for its tools and frameworks. There's a structure for everything. Django is helpful for building web applications, venture applications, logical applications, and mathematical processing. Flask is another web improvement framework with no conditions.
Web2Py, CherryPy, and Falcon offer incredible capabilities to customize Python development services. A large portion of them are open-source frameworks that allow quick turn of events.
Simple to read and compose
Python has an improved sentence structure - one that is like the English language. New engineers for Python can undoubtedly understand where they stand in the development process. The simplicity of composing allows quick application building.
The motivation behind building Python, as said by its maker Guido Van Rossum, was to empower even beginner engineers to comprehend the programming language. The simple coding likewise permits developers to roll out speedy improvements without getting confused by pointless subtleties.
Utilized by the best
Alright - Python isn't simply one more programming language. It should have something, which is the reason the business giants use it. Furthermore, that too for different purposes. Developers at Google use Python to assemble framework organization systems, parallel information pusher, code audit, testing and QA, and substantially more. Netflix utilizes Python web development services for its recommendation algorithm and media player.
Massive community support
Python has a steadily developing community that offers enormous help. From amateurs to specialists, there's everybody. There are a lot of instructional exercises, documentation, and guides accessible for Python web development solutions.
Today, numerous universities start with Python, adding to the quantity of individuals in the community. Frequently, Python designers team up on various tasks and help each other with algorithmic, utilitarian, and application critical thinking.
Progressive applications
Python is the greatest supporter of data science, Machine Learning, and Artificial Intelligence at any enterprise software development company. Its utilization cases in cutting edge applications are the most compelling motivation for its prosperity. Python is the second most well known tool after R for data analytics.
The simplicity of getting sorted out, overseeing, and visualizing information through unique libraries makes it ideal for data based applications. TensorFlow for neural networks and OpenCV for computer vision are two of Python's most well known use cases for Machine learning applications.
Summary
Thinking about the advances in programming and innovation, Python is a YES for an assorted scope of utilizations. Game development, web application development services, GUI advancement, ML and AI improvement, Enterprise and customer applications - every one of them uses Python to its full potential.
The disadvantages of Python web improvement arrangements are regularly disregarded by developers and organizations because of the advantages it gives. They focus on quality over speed and performance over blunders. That is the reason it's a good idea to utilize Python for building the applications of the future.
#python development services #python development company #python app development #python development #python in web development #python software development
1622622360
Data Validation in Excel
Data Validation in Excel
In this tutorial, let’s discuss what data validation is and how it can be implemented in MS-Excel. Let’s start!!!
What Is Data Validation in Excel?
Data Validation is one of the features in MS-Excel which helps in maintaining the consistency of the data in the spreadsheet. It controls the type of data that can enter in the data validated cells.
Data Validation in MS Excel
Now, let’s have a look at how data validation works and how to implement it in the worksheet:
To apply data validation for the cells, then follow the steps.
1: Choose to which all cells the validation of data should work.
2: Click on the DATA tab.
3: Go to the Data Validation option.
4: Choose the drop down option in it and click on the Data Validation.
Once you click on the data validation menu from the ribbon, a box appears with the list of data validation criteria, Input message and error message.
Let’s first understand, what is an input message and error message?
Once, the user clicks the cell, the input message appears in a small box near the cell.
If the user violates the condition of that particular cell, then the error message pops up in a box in the spreadsheet.
The advantage of both the messages is that the input and as well as the error message guide the user about how to fill the cells. Both the messages are customizable also.
Let us have a look at how to set it up and how it works with a sample
#ms excel tutorials #circle invalid data in excel #clear validation circles in excel #custom data validation in excel #data validation in excel #limitation in data validation in excel #setting up error message in excel #setting up input message in excel #troubleshooting formulas in excel #validate data in excel
1624615020
Excelython — Part 4: Read Excel Files in Python
Project Description
You are working on a purchasing team and you want to check your sales and inventory, the pending orders you might have, where you need to place new orders and what are the products that need your attention as they are slow movers.
After you have done all the downloading, the processing and evaluation, you need to send to another colleague a file by email, that has all the products, split by supplier and volume to be ordered per different sheet and you need a separate excel file with the products that you have stock greater than 4 weeks, ordered by the top 20 cost value, in order to create a plan of push their sales or return them back to supplier.
#excel #python #python-beginner #excelython — part 4: read excel files in python #excelython #read excel files in python
1602968400
Python Tricks Every Developer Should Know
Python is awesome, it’s one of the easiest languages with simple and intuitive syntax but wait, have you ever thought that there might ways to write your python code simpler?
In this tutorial, you’re going to learn a variety of Python tricks that you can use to write your Python code in a more readable and efficient way like a pro.
Let’s get started
Swapping value in Python
Instead of creating a temporary variable to hold the value of the one while swapping, you can do this instead
>>> FirstName = "kalebu"
>>> LastName = "Jordan"
>>> FirstName, LastName = LastName, FirstName
>>> print(FirstName, LastName)
('Jordan', 'kalebu')
#python #python-programming #python3 #python-tutorials #learn-python #python-tips #python-skills #python-development