I am trying to extract data from an excel spreadsheet, then find a percent change between adjacent rows. The columns that I would like to do this manipulation on is column 1 and 4. I would like to then graph these percent changes in two different bar charts using subplots using column 0 as the x axis.
I am able to do everything except extract the data and formulate a percent change between adjacent rows. The formula for the percent change is Current/previous-1 or (r,0)/(r-1,0)-1. Below is my current script:
import xlrd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as tkr
import matplotlib.dates as mdates
import datetime
from matplotlib import rc
rc('mathtext', default='regular')
file_location = "/Users/adampatel/Desktop/psw01.xls"
workbook = xlrd.open_workbook(file_location, on_demand = False)
worksheet = workbook.sheet_by_name('Data 1')
x = [worksheet.cell_value(i+1699, 0) for i in range(worksheet.nrows-1699)]
y1 = [worksheet.cell_value(i+1699, 1) for i in range(worksheet.nrows-1699)]
y2 = [worksheet.cell_value(i+1699, 4) for i in range(worksheet.nrows-1699)]
fig = plt.figure()
ax1 = fig.add_subplot(211)
ax2 = fig.add_subplot(212, sharex = ax1)
start_date = datetime.date(1899, 12, 30)
dates=[start_date + datetime.timedelta(xval) for xval in x]
ax1.xaxis.set_major_locator(mdates.MonthLocator((), bymonthday=1, interval=2))
ax1.xaxis.set_minor_locator(mdates.MonthLocator((), bymonthday=1, interval=1))
ax1.xaxis.set_major_formatter(mdates.DateFormatter("%b'%y"))
ly1 = ax1.bar(dates, y1, 0.9)
ly2 = ax2.bar(dates, y2, 0.9)
ax1.grid()
ax2.grid()
ax1.set_ylim(-3,3)
ax2.set_ylim(-3,3)
fig.text(0.5, 0.04, 'Inventory Weekly Percent Change', ha='center', va='center', size = '14')
fig.text(0.06, 0.5, 'Weekly Percent Change', ha='center', va='center', size = '14', rotation='vertical')
ax1.set_title('Oil', size = '12')
ax2.set_title('Gasoline', size = '12')
plt.savefig('Gasoline Inventories Weekly Percent Change.png', bbox_inches='tight', dpi=300)
plt.show()
Given list of values:
y1 = [1000,1010,950,1050,1100,1030]
Pure python solution:
Use the zip function to create tuples of the numerator and denominator. Then use list comprehension to get a list of the percent changes.
pct_chg = [1.0*num / den - 1 for num, den in zip(y1[1:], y1)]
Numpy solution:
Convert list to numpy array, then perform computation using array slices.
a1 = np.array(y1)
pct_chg = np.divide(a1[1:],a1[:-1])-1
Pandas package solution:
Convert list to Pandas series and use the built-in percent change function
s1 = pd.Series(y1)
pct_chg = s1.pct_change()
Now, pct_chg is a series too. You can get its values in a numpy array via pct_chg.values. Matplotlib should accept numpy arrays as containers in most cases.