-
Notifications
You must be signed in to change notification settings - Fork 0
/
appstore_calendar.py
168 lines (119 loc) · 7.21 KB
/
appstore_calendar.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
"""Apple and Google Payment Calendars
Apple pulls calendar from Google Sheets
Google is a pure function
payment outputs the next payment date from the app stores
calendar outputs annual table with period start, end and pay dates"""
import pandas as pd
import datetime
import calendar
import os
from gspread_pandas import Spread
import numpy as np
# Path to CSV file
file_path = os.path.abspath('apple_fiscal_calendar_2018_2021.csv')
print(file_path)
dir_path = os.path.dirname(file_path)
print(dir_path)
csv_path = os.path.join(dir_path, 'apple_fiscal_calendar_2018_2021.csv')
# Apple Calendar, uses start - first date, revenue - list of daily sales
def apple_calendar(apple_start, apple_end):
apple_fiscal = pd.read_csv(csv_path, parse_dates=True, index_col=None)
# convert date, start and end date range, pay date and month duration to datetime
apple_fiscal['date'] = pd.to_datetime(apple_fiscal['date'])
apple_fiscal['start_date'] = pd.to_datetime(apple_fiscal['start_date'])
apple_starts = apple_fiscal[['start_date', 'date']]
apple_fiscal['end_date'] = pd.to_datetime(apple_fiscal['end_date'])
apple_ends = apple_fiscal[['end_date', 'date']]
apple_fiscal['pay_date'] = pd.to_datetime(apple_fiscal['pay_date'])
apple_pays = apple_fiscal['pay_date']
apple_duration = apple_fiscal[['date', 'next_month_duration']]
# set parse format / parse date entry string
year, month, day = map(int, apple_start_date.split('-'))
apple_start = datetime.date(year, month, day)
year, month, day = map(int, apple_end_date.split('-'))
apple_end = datetime.date(year, month, day)
# create list of annual dates, based on the start date from date entry variable and convert list to data frame
apple_range = apple_end - apple_start
apple_range = apple_range.days + 1
apple_revenue = list(range(0, apple_range))
start_datelist = pd.date_range(apple_start, apple_end).tolist()
start_apple_table = pd.DataFrame(start_datelist)
# name index 'days' and column 'date'
start_apple_table.index.name = 'days'
start_apple_table.columns = ['date']
# merge csv columns 'start_date', 'end_date', and 'next_month_duration' with data frame
start_apple_table = start_apple_table.merge(apple_starts, how='left', on='date', left_index=True)
start_apple_table = start_apple_table.merge(apple_ends, how='left', on='date', left_index=True)
start_apple_table = start_apple_table.merge(apple_duration, how='left', on='date', left_index=True)
# add 'pay_date' column to list only the days when receive payment from Apple
start_apple_table['date'] = start_apple_table.loc[(start_apple_table['date'].isin(apple_pays)), 'pay_date'] = start_apple_table['date']
# assign 'sample_sales' column to second input revenue
start_apple_table['sample_sales'] = apple_revenue
# change index to column 'date'
start_apple_table = start_apple_table.set_index('date')
# convert 'next_month_duration' from integer to datetime days
start_apple_table['next_month_duration'] = pd.to_timedelta(start_apple_table['next_month_duration'], unit='D')
# create 'monthly_sales' column
start_apple_table['monthly_sales'] = start_apple_table.apply(lambda x: start_apple_table.loc[(start_apple_table['start_date']
<= x.name) & (x.name <= start_apple_table['end_date']), ['sample_sales']].sum(), axis=1)
# create 'monthly_adj' column to move the sales up by next month fiscal duration period
start_apple_table['monthly_adj'] = start_apple_table.apply(lambda x: start_apple_table.loc[(start_apple_table['start_date']
+ start_apple_table['next_month_duration'] <= x.name) & (x.name <= start_apple_table['end_date'] +
start_apple_table['next_month_duration']), ['sample_sales']].sum(), axis=1)
# shift 'monthly_adj' by 7 rows to be captured by 'pay_date'
start_apple_table['monthly_shift'] = start_apple_table['monthly_adj'].shift(7)
# add 'monthly_payment' and show only on 'pay_date' dates
start_apple_table['monthly_payment'] = start_apple_table['monthly_shift'].loc[start_apple_table['pay_date'].notnull()]
# add 'cumulative_payment' column
start_apple_table['cumulative_payment'] = start_apple_table['monthly_payment'].cumsum()
return start_apple_table
# Google Calendar, uses start - first date, revenue - list of daily sales
def google_calendar(google_start, google_end):
# set parse format and parse date entry string
year, month, day = map(int, google_start_date.split('-'))
google_start = datetime.date(year, month, day)
year, month, day = map(int, google_end_date.split('-'))
google_end = datetime.date(year, month, day)
# create list of annual dates and convert list to data frame
start_datelist = pd.date_range(google_start, google_end).tolist()
start_google_table = pd.DataFrame(start_datelist)
# name index 'days' and column 'date'
start_google_table.index.name = 'days'
start_google_table.columns = ['date']
# create 'start_date', 'end_date' columns, and use front and back fill
start_google_table.loc[(start_google_table['date'].dt.day == 1), 'start_date'] = start_google_table['date']
start_google_table['start_date'] = start_google_table['start_date'].fillna(method='ffill')
start_google_table['date'] = start_google_table.loc[start_google_table['date'].dt.is_month_end, 'end_date'] = start_google_table['date']
start_google_table['end_date'] = start_google_table['end_date'].fillna(method='bfill')
# create 'pay_date' and 'pay_period' columns
start_google_table.loc[(start_google_table['date'].dt.day == 15), 'pay_date'] = start_google_table['date']
start_google_table.loc[(start_google_table['date'].dt.day == 15), 'pay_period'] = start_google_table['date'] - pd.DateOffset(months=1)
# assign 'sample_sales' column to second input revenue
google_range = google_end - google_start
google_range = google_range.days + 1
google_revenue = list(range(0, google_range))
start_google_table['sample_sales'] = google_revenue
# change index to column 'date'
start_google_table = start_google_table.set_index('date')
# add 'monthly_payment', set it to only when 'pay_period' is on and then add 'cumulative_payment' column
start_google_table['monthly_payment'] = start_google_table.apply(lambda x: start_google_table.loc[(start_google_table['start_date'] +
pd.DateOffset(months=1) <= x.name) & (x.name <= start_google_table['end_date'] + pd.DateOffset(months=1)), ['sample_sales']].sum(), axis=1)
start_google_table['monthly_payment'] = start_google_table['monthly_payment'].loc[start_google_table['pay_period'].notnull()]
start_google_table['cumulative_payment'] = start_google_table['monthly_payment'].cumsum()
return start_google_table
# add Apple and Google start and end dates to run calendars
apple_start_date = '2017-12-01'
apple_end_date = '2018-12-03'
google_start_date = '2017-12-01'
google_end_date = '2018-12-03'
# simple check to see if calendars are workings
print(apple_calendar(apple_start_date, apple_end_date))
print(google_calendar(google_start_date, google_end_date))
# Output to Google Sheets, using gspread to check in more detail
apple_fiscal = apple_calendar(apple_start_date, apple_end_date)
fiscal_calendar = Spread('calculator', 'fiscal_calendar')
fiscal_calendar.df_to_sheet(apple_fiscal, sheet='apple_output')
print(fiscal_calendar)
google_fiscal = google_calendar(google_start_date, google_end_date)
fiscal_calendar.df_to_sheet(google_fiscal, sheet='google_output')
print(fiscal_calendar)