def zd_api(request):
config = conf.get_config("./gspread_pandas/")
articles = []
url = "https://collegetrack.zendesk.com//api/v2/help_center/categories/360000085163/articles.json"
# also not used, but if we wanted to add in users the api URL is: /api/v2/users.json
while url:
response = requests.get(url, auth=(ZD_USERNAME, ZD_PASSWORD))
if response.status_code != 200:
print("Status:", response.status_code, "Problem with the request. Exiting.")
exit()
data = json.loads(response.text)
for article in data["articles"]:
articles.append(
[
article["id"],
article["title"],
article["html_url"],
article["updated_at"],
]
)
url = data["next_page"]
# converting the date column into a usable form
# to_write_updated = [datetime.date(parse(i[3])) for i in to_write]
df = pd.DataFrame(articles)
df.columns = ["ID", "Title", "URL", "Date"]
df.Date = pd.to_datetime(df.Date).dt.date
df = df.sort_values(by="Date", ascending=True)
df.loc[:, "ID"] = df["ID"].astype("int").astype("str")
# df = df.set_index("ID")
# articles.sort(key = lambda item: item[3], reverse=False)
# articles.sort(key = lambda item: item[3], reverse=True)
spread = Spread(SPREADSHEET_ID, config=config)
spread.open_sheet(0)
existing_sheet = spread.sheet_to_df(sheet="Sheet1")
existing_sheet_subset = existing_sheet[
["Internal / Staff Facing", "Person Responsible"]
].reset_index()
merged_df = df.merge(existing_sheet_subset, on="ID", how="left")
spread.df_to_sheet(
merged_df, index=False, sheet="Sheet1", start="A1", replace=False
)
class PandasGoogleSpreadsheetWrapper(PandasWrapperBase):
def __init__(self, credentialsManager, spreadsheetId, dataFrame=None):
super().__init__(dataFrame)
self.__credentialsManager = credentialsManager
self.__id = spreadsheetId
self.__spreadsheet = Spread(
spread=self.__id, creds=self.__credentialsManager.credentials)
@property
def credentialsManager(self):
return self.__credentialsManager
@property
def spreadsheetId(self):
return self.__id
@property
def spreadsheet(self):
return self.__spreadsheet
@property
def active_sheet(self):
return self.__spreadsheet.sheet
def load(self, filePath):
# filePath = SheetName
print("Load GoogleSpreadsheet: " + str(self.__id) + " [" +
str(filePath) + "]")
self.onLoadPreprocessing(self.df)
df = self.__spreadsheet.sheet_to_df(index=False, sheet=filePath)
self.setDataFrame(df)
print(" Loaded Length: " + str(len(self.df.index)))
self.onLoadPostprocessing(self.df)
return self
def save(self, filePath):
# filePath = SheetName
print("Save GoogleSpreadsheet: " + str(self.__id) + " [" +
str(filePath) + "]")
self.onSavePreprocessing(self.df)
self.__spreadsheet.open_sheet(filePath, create=True)
self.__spreadsheet.df_to_sheet(df=self.df,
index=False,
headers=True,
start='A1',
replace=True)
print(" Saved Length : " + str(len(self.df.index)))
self.onSavePostprocessing(self.df)
return self
def get_sheet(SHEET, CSV_SPREADSHEET='Asistensi PBO Jumat 2019'):
spread = Spread(CSV_SPREADSHEET)
spread.open_sheet(SHEET)
df = spread.sheet_to_df()
df['Nilai'] = pd.to_numeric(df['Nilai'], errors='coerce')
links = []
for link in tqdm(df.REPO, desc='issue'):
try:
links.append(get_issues(link))
except:
links.append(None)
print(links)
df['Issues'] = links
spread.update_cells('H2', 'H28', links)
df.drop(['Catatan', 'REPO'], axis=1, inplace=True)
return df
repo.index.commit("backup of check_DB before modification")
origin = repo.remotes.origin
origin.push()
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
'C:/Keys/mongoDB_secret.json', scope)
client = gspread.authorize(creds)
head_ind = 8
sheet_g = Spread(g_sheet_name)
sheet_g.open_sheet("Sheet1", create=True)
sheet_g.sheet.resize(rows=head_ind)
DB_col_list = [
'device_name', 'Type', 'checkstatus', 'description', 'servertime',
'last_fail', 'client_name', 'site_name', 'extra', 'dsc247', 'deviceid',
'checkid', 'consecutiveFails', 'Label'
]
check_DB = pd.DataFrame(columns=DB_col_list)
## writing to excell file
#excel_path = 'check_list.xlsx'
#if os.path.isfile(excel_path):
# os.remove(excel_path)
i = 0
#TIER PRICING CALCULATOR
import pandas as pd
from gspread_pandas import Spread
pricing = Spread('price', 'Plexchat Pricing')
pricing.open_sheet(0)
price_input = pricing.sheet_to_df(header_rows=1,
index=True).apply(pd.to_numeric,
errors='ignore')
tier_1_MAU = int(price_input.loc["Tier 1", "MAU"])
tier_2_MAU = int(price_input.loc["Tier 2", "MAU"])
tier_3_MAU = int(price_input.loc["Tier 3", "MAU"])
tier_4_MAU = int(price_input.loc["Tier 4", "MAU"])
tier1_cloud_cost = price_input.loc["Tier 1", "COST_USER"]
tier2_cloud_cost = price_input.loc["Tier 2", "COST_USER"]
tier3_cloud_cost = price_input.loc["Tier 3", "COST_USER"]
tier4_cloud_cost = price_input.loc["Tier 4", "COST_USER"]
estimated_MAU = int(price_input.loc["ESTIMATED MAU", "MAU"])
tiers_MAU = {
"Tier 1": tier_1_MAU,
"Tier 2": tier_2_MAU,
"Tier 3": tier_3_MAU,
"Tier 4": tier_4_MAU
}
tiers_price_per_user = {
"Tier 1": price_input.loc["Tier 1", "PRICE_USER"],
class GoogleSpread(object):
def __init__(self,
spread,
sheet=0,
creds=None,
create_sheet=False,
conf_file=None):
if creds:
self.creds = creds
else:
credentials = ParseConfiguration(conf_file).get_google_creds()
self.creds = get_creds(config=credentials)
self.spread = Spread(spread,
sheet=sheet,
creds=self.creds,
create_sheet=create_sheet)
@property
def spread_url(self):
return self.spread.url
@property
def worksheet_name(self):
return self.spread.sheet
@property
def spread_name(self):
return self.spread.spread
def df_to_sheet(self,
df,
index=True,
headers=True,
start_cell=(1, 1),
sheet=None,
replace=False):
self.spread.df_to_sheet(df,
index=index,
headers=headers,
start=start_cell,
sheet=sheet,
replace=replace)
def sheet_to_df(self, index=1, header_rows=1, start_row=1, sheet=None):
return self.spread.sheet_to_df(index=index,
header_rows=header_rows,
start_row=start_row,
sheet=sheet)
def find_sheet(self, sheet):
return self.spread.find_sheet(sheet)
def open_sheet(self, sheet, create=False):
self.spread.open_sheet(sheet, create=create)
def update_cells(self, start, end, vals, sheet=None):
self.spread.update_cells(start, end, vals, sheet=sheet)
def get_sheet_dims(self, sheet=None):
return self.spread.get_sheet_dims(sheet=sheet)
def clear_sheet(self, rows=1, cols=1, sheet=None):
self.spread.clear_sheet(rows=rows, cols=cols, sheet=sheet)
def create_sheet(self, name, rows=1, cols=1):
self.spread.create_sheet(name, rows=rows, cols=cols)
def delete_sheet(self, sheet):
self.spread.delete_sheet(sheet)
Path2Pro
This code is used to organize data from daily tournaments
"""
import numpy as np
import pandas as pd
import gspread_pandas as gp
from oauth2client.service_account import ServiceAccountCredentials
from gspread_pandas import Spread
from collections import OrderedDict
tournament = Spread('league', 'Path2Pro League (Season 1)')
tournament.sheets
### pull tournament data into dataframes
### PLAYER NAMES
tournament.open_sheet(5)
tournament
players = tournament.sheet_to_df(header_rows=1, index=True)
### PLAYER HASHTAGS
tournament.open_sheet(6)
tournament
hashtags = tournament.sheet_to_df(header_rows=1, index=True)
### PLAYER TROPHIES
tournament.open_sheet(7)
tournament
trophies = tournament.sheet_to_df(header_rows=1, index=True)
### PLAYER CLANS
tournament.open_sheet(8)
# ['checkid','servertime'])#, ascending = False)
temp = fails_select.loc[
row_ind, ['device_name', 'client_name', 'site_name', 'Type']]
check_SQL[['device_name', 'client_name', 'site_name',
'Type']] = check_SQL.apply(lambda row: temp, axis=1)
check_SQL_last = check_SQL[[
'device_name', 'Type', 'checkstatus', 'description', 'servertime',
'client_name', 'site_name', 'extra', 'dsc247', 'deviceid', 'checkid',
'consecutiveFails'
]]
print('Saving', len(check_SQL_last),
'extracted trajectory to the SQL table...')
sheet_T = Spread(traj_sheet_name)
sheet_T.open_sheet("Sheet1", create=False)
if row_ind == 0:
sheet_T.df_to_sheet(check_SQL_last,
index=False,
sheet='Sheet1',
replace=True)
else:
row = sheet_T.sheet.row_count + 2
sheet_T.df_to_sheet(check_SQL_last,
index=False,
headers=False,
sheet='Sheet1',
start=(row, 1))
#------- save to excel file
# excel_path = 'Check_trajectory.xlsx'
