Skip to content

Instantly share code, notes, and snippets.

@asharma327
asharma327 / excel.py
Created July 26, 2019 04:35
Code accompanying tutorial on Intro to Handling Spreadsheets with Python
import
@asharma327
asharma327 / excel.py
Last active July 26, 2019 06:10
Code accompanying tutorial on Intro to Handling Spreadsheets with Python
import pandas as pd
# Read File
customer_sample_file = pd.read_excel("CustomerSample.xlsx", sheet_name="Prospects", parse_dates=[0])
# Get records from 2017 or earlier
customers_2017_or_earlier = customer_sample_file[customer_sample_file["DateTime Recorded"] < "2018-01-01"]
# Output the records
customers_2017_or_earlier.to_excel("Customers2017OrEarlier.xlsx")
@asharma327
asharma327 / append.py
Last active April 28, 2020 19:15
Append (merge) spreadsheets using Python
import pandas as pd
# Read all three files into pandas dataframes
marketing_analyst_names = pd.read_excel("MarketingAnalystNames.xlsx")
sales_rep_names = pd.read_excel("SalesRepNames.xlsx")
senior_leadership_names = pd.read_excel("SeniorLeadershipNames.xlsx")
# Create a list of the files in order you want them appended
all_df_list = [marketing_analyst_names, sales_rep_names, senior_leadership_names]
@asharma327
asharma327 / join.py
Last active August 14, 2019 01:48
Join (merge) spreadsheets using Python
import pandas as pd
# Read both Excel files
customers = pd.read_excel("Customers.xlsx")
calls = pd.read_excel("Calls.xlsx")
# Inner Join
inner_join_df = customers.merge(calls, how="inner", on="Name")
inner_join_df.to_excel("InnerJoin.xlsx")
@asharma327
asharma327 / email_send.py
Last active August 2, 2021 22:53
Automate email sending with spreadsheets and python
import pandas as pd
import smtplib
'''
Change these to your credentials and name
'''
your_name = "Bill Butlicker"
your_email = "[email protected]"
your_password = "bearsbeetsbattlestar"
@asharma327
asharma327 / missing_values.py
Created September 12, 2019 14:34
Remove Missing Values from Spreadsheets
import pandas as pd
# Read the file
apple_stock = pd.read_excel("AppleStock.xlsx")
# Fill in Ticker Missing Values with AAPL
apple_stock["Ticker"] = apple_stock["Ticker"].fillna("AAPL")
# Back Fill Open Column
apple_stock["Open"] = apple_stock["Open"].fillna(method="bfill")
@asharma327
asharma327 / dates.py
Created September 22, 2019 22:33
Convert dates in spreadsheets using python
import pandas as pd
# Read the file and specify which column is the date
customer_calls = pd.read_excel("CustomerCalls.xlsx")
# Output with dates converted to YYYY-MM-DD
customer_calls["DateTime Recorded"] = pd.to_datetime(customer_calls["DateTime Recorded"]).dt.strftime("%Y-%m-%d")
customer_calls.to_excel("CustomerCalls_YYYY_MM_DD.xlsx")
@asharma327
asharma327 / geocode.py
Last active October 22, 2019 11:12
How to Geocode Addresses in Spreadsheets using Python
import pandas as pd
from opencage.geocoder import OpenCageGeocode
key = "REPLACE_WITH_YOUR_API_KEY"
geocoder = OpenCageGeocode(key)
addresses_df = pd.read_excel("Addresses.xlsx")
addresses = addresses_df["Addresses"].values.tolist()
@asharma327
asharma327 / duplicates.py
Created October 30, 2019 22:01
How to Remove and Detect Duplicates in Spreadsheets using Python
import pandas as pd
file_df = pd.read_excel("Duplicates.xlsx")
# Keep only FIRST record from set of duplicates
file_df_first_record = file_df.drop_duplicates(subset=["Name", "Address", "Call Date"], keep="first")
file_df_first_record.to_excel("Duplicates_First_Record.xlsx", index=False)
# Keep only LAST record from set of duplicates
file_df_last_record = file_df.drop_duplicates(subset=["Name", "Address", "Call Date"], keep="last")
<!DOCTYPE html>
<html>
<head>
<title>Save Web Form Data to Spreadsheets - Contact Form</title>
<script
src="https://code.jquery.com/jquery-3.4.1.js"
integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU="
crossorigin="anonymous"></script>
<script>
function SubForm (){