asharma327’s gists

asharma327 / excel.py

Created July 26, 2019 04:35

Code accompanying tutorial on Intro to Handling Spreadsheets with Python

import

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 / 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 / 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 / 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 / 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 / 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 / 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 / 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")

asharma327 / ContactForm.html

Last active January 10, 2020 20:12

	<!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 (){

Adhaar Sharma asharma327