Automatize tarefas do Excel com Python

Aprenda a automatizar tarefas do Excel com Python. Este tutorial mostrará algumas maneiras úteis de criar, atualizar e analisar planilhas do Excel usando a programação Python. 

A interface amigável e a funcionalidade robusta do Excel o tornaram uma ferramenta essencial para análise de dados.

Os analistas de dados podem modificar, examinar e exibir prontamente grandes quantidades de dados com o Excel, o que torna mais simples obter insights e fazer escolhas sábias.

A versatilidade do Excel permite que os usuários realizem uma variedade de atividades de análise de dados, desde operações matemáticas diretas até análises estatísticas complexas. Além disso, o Excel oferece automação por meio do uso de programas de terceiros, como Python ou a linguagem de programação integrada VBA.

O Excel é frequentemente usado para análise de dados em vários setores, incluindo bancos, saúde e marketing, graças à sua versatilidade e usabilidade.
Mas, como analista de dados, muitas vezes você pode repetir tarefas mundanas diariamente ao trabalhar com o Excel.

Essas tarefas podem incluir copiar e colar dados, formatar células e criar gráficos, entre outros. Com o tempo, isso pode se tornar monótono e demorado, deixando você com menos tempo para se concentrar em aspectos mais importantes da análise de dados, como identificar tendências, outliers e insights.

É por isso que automatizar o Excel usando Python pode mudar o jogo, ajudando você a simplificar seus fluxos de trabalho e liberar tempo para análises mais significativas.

Neste tutorial, mostrarei algumas maneiras úteis de criar, atualizar e analisar planilhas do Excel usando a programação Python. Vamos mergulhar.

Como mesclar duas planilhas separadas com Python

Os analistas de dados geralmente precisam trabalhar em muitas planilhas, o que pode se tornar agitado quando você precisa mesclar esses arquivos.

O código abaixo ajuda você a mesclar dois arquivos separados.

import pandas as pd

# Read in the two Excel files

file1 = pd.read_excel('file1.xlsx')file2 = pd.read_excel('file2.xlsx')

# Merge the two files using the concat() method
merged_file = pd.concat([file1, file2], ignore_index=True)

# Write the merged file to a new Excel file
merged_file.to_excel('merged_file.xlsx', index=False)

Neste código, primeiro importamos a biblioteca Pandas, que usaremos para ler e manipular os arquivos do Excel.

Em seguida, usamos o read_excel()método para ler em ambos file1.xlsxe file2.xlsx. Em seguida, usamos o concat()método para mesclar os dois arquivos. O ignore_index=Trueargumento garante que os valores de índice de ambos os arquivos sejam redefinidos, para que não acabemos com valores de índice duplicados no arquivo mesclado.

Finally, we use the to_excel() method to write the merged file to a new Excel file named merged_file.xlsx. We also set index=False to ensure that the index column is not included in the output file.

How to Import and Export Data with Python

This task involves using Python libraries such as Pandas to read Excel files into a DataFrame object. You can then manipulate it and analyze it using Python.

You can also export data from Python back into an Excel file using the same libraries.

import pandas as pd
# Import Excel file

df = pd.read_excel('filename.xlsx', sheet_name='Sheet1')

# Export to Excel file
df.to_excel('new_filename.xlsx', index=False)

The given code imports the Pandas library and reads an Excel file named "filename.xlsx" from Sheet1 of the workbook, storing the data in a Pandas dataframe named "df". The dataframe is then exported to a new Excel file named "new_filename.xlsx" using the "to_excel" method. The "index=False" parameter is used to exclude row indexing in the output file.

Essentially, the code copies the contents of the original Excel file to a new file using Pandas.

How to Clean and Transform Data using Python

This task involves using Python libraries such as Pandas to clean and transform data in Excel.

This may include removing duplicates, filtering data based on specific criteria, and performing calculations on the data.

import pandas as pd

# Remove duplicates
df = df.drop_duplicates()

# Filter data
df = df[df['column_name'] > 10]

# Perform calculations
df['new_column'] = df['column1'] + df['column2']

The code snippet above performs data cleaning and manipulation tasks on a Pandas dataframe named 'df' using the Pandas library.

Firstly, it removes duplicate rows from 'df' using the "drop_duplicates" method. Secondly, it filters the 'df' dataframe by selecting rows where the value in the 'column_name' column is greater than 10 and assigns the filtered result to a new dataframe called 'data_df'.

Lastly, a new column named 'new_column' is added to 'df' which contains the sum of values from 'column1' and 'column2'.

Overall, the code effectively cleans and manipulates the data by removing duplicates, filtering specific rows, and adding a new calculated column to the original dataframe.

How to Perform Data Analysis with Python

Esta tarefa envolve o uso de bibliotecas Python, como Pandas e NumPy, para realizar análises de dados em dados do Excel.

Isso pode incluir o cálculo de estatísticas resumidas, como média e desvio padrão, ou a criação de relatórios personalizados agrupando dados com base em critérios específicos.

import pandas as pd
import numpy as np

# Calculate summary statistics
df.describe()
# Create custom reports
df.pivot_table(values='column_name', index='category_name', columns='date')

O código utiliza as bibliotecas Pandas e NumPy e executa tarefas de análise e relatório de dados em um dataframe Pandas chamado "df".

Em primeiro lugar, ele calcula estatísticas resumidas para os dados numéricos no dataframe usando o método "descrever". Esse método gera informações valiosas sobre a distribuição, tendência central e dispersão dos dados.

Em segundo lugar, o código usa o método "pivot_table" para criar relatórios personalizados a partir do dataframe. Este método resume e agrega os dados no dataframe e pode produzir tabelas em vários formatos.

Nesse código, ele gera um novo dataframe onde os valores 'column_name' são agrupados pelas colunas 'category_name' e 'date'.

No geral, o código executa análises estatísticas e tarefas de relatório no dataframe para obter insights dos dados.

Como criar gráficos com Python

Esta tarefa envolve o uso de bibliotecas Python, como matplotlib ou seaborn, para criar tabelas e gráficos a partir de dados do Excel.

Você pode personalizar esses gráficos para exibir dados específicos e formatá-los para atender a requisitos específicos.

import pandas as pd
import matplotlib.pyplot as plt
# Create a bar chart
df.plot(kind='bar', x='category_name', y='sales')
plt.show()
# Create a scatter plot
df.plot(kind='scatter', x='column1', y='column2')plt.show()

O código importa duas bibliotecas, Pandas e matplotlib.pyplot usando os aliases 'pd' e 'plt', respectivamente.

O método "plot" do Pandas é então usado para criar dois tipos de gráficos. O primeiro tipo de gráfico é um gráfico de barras que mostra a relação entre as colunas 'category_name' e 'sales' no dataframe "df".

O segundo tipo de gráfico é um gráfico de dispersão que mostra a relação entre as colunas 'coluna1' e 'coluna2' no mesmo dataframe. O código usa os parâmetros "kind='bar'" para o gráfico de barras e "kind='scatter'" para o gráfico de dispersão para criar os respectivos gráficos.

Por fim, o método "show" é chamado para exibir os gráficos na tela. Em resumo, o código utiliza Pandas e matplotlib para criar um gráfico de barras e um gráfico de dispersão para visualizar os dados no dataframe "df".

Como fazer visualização de dados em Python

Esta tarefa envolve o uso de bibliotecas Python, como Plotly e bokeh, para criar visualizações interativas de dados a partir de dados do Excel.

Essas visualizações permitem que os usuários explorem dados de novas maneiras, como ampliando pontos de dados específicos ou filtrando dados com base em critérios específicos.

import pandas as pd
import plotly.express as px
# Create a heatmap
fig = px.imshow(df.corr())
fig.show()
# Create a line chart
fig = px.line(df, x='date', y='sales', color='category')
fig.show()

O código usa as bibliotecas Pandas e plotly.express para criar dois tipos de visualizações. Primeiro, um gráfico de mapa de calor é criado usando o método "imshow" do plotly.express que visualiza a correlação entre as colunas no quadro de dados "df".

Second, a line chart is created using plotly.express's "line" method that displays the relationship between the 'date' and 'sales' columns while differentiating between categories based on the 'category' column of the dataframe. Both plots are displayed using the "show" method.

How to Automate Report Generation with Python

This task involves using Python scripts to automate the process of generating reports from Excel data.

You can set up these scripts to run on a regular schedule, such as daily or weekly. They can also automatically update as new data becomes available.

import pandas as pd
# Create daily report
df_daily = df[df['date'] == '2022-01-01']
df_daily.to_excel('daily_report.xlsx', index=False)
# Create weekly report
df_weekly = df.groupby('category').sum()
df_weekly.to_excel('weekly_report.xlsx', index=False)

The code creates a daily report by creating a new dataframe "df_daily" that includes only the rows where the 'date' column equals '2022-01-01'. This is achieved by using Pandas' boolean indexing feature.

Afterward, the "to_excel" method is used to export the filtered data to an Excel file named "daily_report.xlsx", without including the index column.

Next, the code creates a weekly report by grouping the "df" dataframe by the 'category' column and summing the values of all other columns. This is accomplished using the Pandas "groupby" and "sum" methods.

The result is saved in a new dataframe named "df_weekly". Lastly, the "to_excel" method is used to export the aggregated data to an Excel file named "weekly_report.xlsx", without including the index column.

In summary, the code creates two reports using the Pandas library. The first report is a daily report that includes only data from a specific date, and the second report is a weekly report that aggregates data by category. Both reports are exported to Excel files using the "to_excel" method.

How to Automate Repetitive Tasks with Macros and Scripts in Python

This task involves using Python to automate repetitive tasks in Excel, such as data entry or formatting. You can do this by creating macros or scripts that can execute automatically, or by using Python to interact with the Excel application directly.

import win32com.client as win32
# Open Excel file
excel = win32.gencache.EnsureDispatch('Excel.Application')
workbook = excel.Workbooks.Open(r'filename.xlsx')
# Run macro
excel.Application.Run('macro_name')
# Save and close Excel
 fileworkbook.Save()workbook.Close()excel.Quit()

The code uses the win32com.client module to interact with Microsoft Excel via the Windows API.

First, an instance of the Excel application is opened using the EnsureDispatch() method, and the specified Excel file is opened using the Workbooks.Open() method.

Next, a macro is executed using the Application.Run() method, passing the name of the macro as an argument.

Finally, the changes made to the Excel file are saved using the Save() method, the workbook is closed using the Close() method, and the Excel application is terminated using the Quit() method

How to Scrape Data with Python

This task involves using Python libraries such as requests and Beautiful Soup to scrape data from web pages or other sources and import it into Excel.

You can then analyze and manipulate this data using Python libraries such as Pandas.

import pandas as pd
import requests
from bs4 import BeautifulSoup
# Scrape data from web page
url = 'https://www.website.com/data'
response = requests.get(url)
soup = BeautifulSoup(response.text, 'html.parser')
table = soup.find('table')
df = pd.read_html(str(table))[0]
# Export to Excel file
df.to_excel('scraped_data.xlsx', index=False)

Esse código usa a biblioteca de solicitações para enviar uma solicitação HTTP GET para a URL ' https://www.example.com '. Em seguida, ele usa a biblioteca BeautifulSoup para analisar o conteúdo HTML da resposta em um objeto BeautifulSoup chamado 'sopa'.

Você pode então usar métodos BeautifulSoup para find_all()extrair dados específicos do HTML:

links = []for link in soup.find_all('a'):    href = link.get('href')    links.append(href)

Este código encontra todas as tags âncora no HTML e extrai o valor do atributo 'href' para cada uma delas, adicionando-as a uma lista chamada 'links'.

Como usar o Python para integrar o Excel com outros aplicativos

Esta tarefa envolve o uso do Python para integrar o Excel a outros aplicativos, como bancos de dados ou serviços da web.

Você pode fazer isso usando bibliotecas Python, como pyodbc, para conectar-se a bancos de dados ou usando APIs para conectar-se a serviços da web. Isso permite transferência e análise de dados perfeitas entre diferentes aplicativos.

import pandas as pd
import pyodbc
# Connect to database
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=db_name;UID=user_id;PWD=password')
# Read data from database
query = 'SELECT * FROM table_name'
df = pd.read_sql(query, cnxn)
# Export to Excel file
df.to_excel('database_data.xlsx', index=False)

O código estabelece uma conexão com um banco de dados do SQL Server usando pyodbc.connect()o método, onde o driver, nome do servidor, nome do banco de dados, ID do usuário e senha são fornecidos como argumentos.

Em seguida, uma consulta SQL é definida e executada para recuperar dados de uma tabela no banco de dados usando o pd.read_sql()método, onde a consulta SQL e o objeto de conexão são fornecidos como argumentos. Os dados recuperados são então armazenados em um Pandas DataFrame.

Por fim, os dados no DataFrame são exportados para um arquivo Excel chamado "database_data.xlsx" usando o to_excel()método, com a coluna de índice excluída da exportação, definindo o parâmetro de índice como Falso.

Conclusão

Python é uma linguagem versátil que você pode usar para automatizar muitas tarefas do Excel. Você também pode usar várias bibliotecas como Pandas, openpyxl, xlwings e pyautogui para manipular dados, extrair informações, gerar relatórios e automatizar tarefas repetitivas.

A automação pode economizar tempo e esforço, reduzir erros e aumentar a produtividade. A proficiência em Python pode ser uma habilidade valiosa para qualquer profissional que trabalhe com o Excel, seja você um analista de dados ou financeiro. Ao aprender Python, você pode elevar seu trabalho a novos patamares.

Obrigado por ler!

Fonte: https://www.freecodecamp.org

#python #excel

What is GEEK

Buddha Community

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

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

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

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

How to Remove all Duplicate Files on your Drive via Python

Today you’re going to learn how to use Python programming in a way that can ultimately save a lot of space on your drive by removing all the duplicates.

Intro

In many situations you may find yourself having duplicates files on your disk and but when it comes to tracking and checking them manually it can tedious.

Heres a solution

Instead of tracking throughout your disk to see if there is a duplicate, you can automate the process using coding, by writing a program to recursively track through the disk and remove all the found duplicates and that’s what this article is about.

But How do we do it?

If we were to read the whole file and then compare it to the rest of the files recursively through the given directory it will take a very long time, then how do we do it?

The answer is hashing, with hashing can generate a given string of letters and numbers which act as the identity of a given file and if we find any other file with the same identity we gonna delete it.

There’s a variety of hashing algorithms out there such as

• md5
• sha1
• sha224, sha256, sha384 and sha512

#python-programming #python-tutorials #learn-python #python-project #python3 #python #python-skills #python-tips