value=0) # .loc[rawDF['name']=='Skoy']
print('2')
highestDonationOfTheDay['dailyDonationPerc'] = (
highestDonationOfTheDay['donations'] -
highestDonationOfTheDay['donations_shifted']) / 260 * 100
print('3')
highestDonationOfTheDay['dailyDonation'] = (
highestDonationOfTheDay['donations'] -
highestDonationOfTheDay['donations_shifted'])
print('4')
highestDonationOfTheDay['dailyDonationPerc'][
highestDonationOfTheDay['dailyDonationPerc'] < 0] = 0
print('5')
highestDonationOfTheDay['dailyDonation'][
highestDonationOfTheDay['dailyDonation'] < 0] = 0
print('6')
highestDonationOfTheDay['dailyDonationPerc'][
highestDonationOfTheDay['dailyDonationPerc'] > 100] = 100
print('7')
highestDonationOfTheDay['dailyDonation'][
highestDonationOfTheDay['dailyDonation'] > 260] = 260
# print(rawDF.sort_values(by=['name', 'date']))
# print (rawDF.shape)
# csvDF=pd.DataFrame.to_csv(highestDonationOfTheDay,path_or_buf='csvtoplotly.csv')
spread = Spread('*****@*****.**', 'clan')
spread.df_to_sheet(highestDonationOfTheDay,
index=True,
sheet='donations',
start='A1',
replace=True)
]).T
print(merged_df)
### group by code, name, and date; sum the scores together if multiple exist for a given code-name-date grouping
grouped_df = merged_df.groupby(['Code', 'Name',
'Date']).sum().sort_values('Score',
ascending=False)
print(grouped_df)
### sum together
summed_df = merged_df.drop('Date', axis = 1) \
.groupby(['Code', 'Name']).sum() \
.sort_values('Score', ascending = False).reset_index()
summed_df['li'] = list(zip(summed_df.Name, summed_df.Score))
print(summed_df)
### REGISTERED USERS
tournament.open_sheet(4)
tournament
registered = tournament.sheet_to_df(header_rows=1, index=False)
registered_list = registered['CR Tag #'].tolist()
print(registered_list)
### Filter total participant by registered users
participants_score = summed_df[summed_df['Code'].isin(registered_list)]
tournament.df_to_sheet(participants_score, sheet='Output')
print(tournament)
participants_score.info()
def dump_replies(sp_url, l_df):
spread = Spread(sp_url)
spread.df_to_sheet(l_df, index=True, sheet=f"Dump {datetime.now()}")
return spread
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'
# if not os.path.isfile(excel_path):
# check_SQL_last.to_excel(excel_path, index = False)
# else: # appending to the excell file
# book = load_workbook(excel_path)
# raise ValueError('update gsheet')
print('Saving', len(check_SQL_last), 'extracted failes to the google sheet...')
# last_row = sheet.row_count
# for i in range(0,len(check_SQL_last)+1):
# sheet.insert_row(list(''), index = last_row+1)
# sheet = client.open("Checks list").sheet1
# sheet.row_count
# sheet_g = Spread("temp1")
sheet_g = Spread("Checks list")
sheet_g.open_sheet("Sheet1", create=False)
last_row = sheet_g.sheet.row_count
sheet_g.df_to_sheet(check_SQL_last, index=False, headers = False,sheet='Sheet1',
start=(last_row+2,1), replace = False)
# sheet_g.sheet.resize(rows=sheet_g.sheet.row_count+1)
check_list.append(device_id)
print("""
=========== tabel saved =====
=============================
"""
)
i += 1
# loaded_data = pickle.load( open(filename, "rb" ))
def job():
print('Updating the clan DataFrame...')
updatedDF = originalDF.append(other=pipeline())
spread = Spread('*****@*****.**', 'clan')
spread.df_to_sheet(updatedDF, index=False, sheet='clan', start='A1', replace=True)
df = pd.DataFrame.from_dict(
islandsDFData[island.name][histogram],
orient='index').reset_index().rename(columns={
'index': 'price',
0: 'count'
})
df['selling_time'] = str(histogram)
df['island'] = island.name
archipielagoDFs.append(df)
archDf = pd.concat(archipielagoDFs, ignore_index=True)
labels = ['Poco probable', 'Nada probable', 'Probable', 'Muy probable']
archDf['rangoPrecio'] = pd.cut(archDf['count'], 4, labels=labels)
priceCutLabels = ['0-100', '100-200', '200-300', '300-400', '500-600', '600+']
archDf['priceCut'] = pd.cut(archDf['price'], 6, labels=priceCutLabels)
archDf['count'].fillna(0, inplace=True)
spread.df_to_sheet(archDf, index=False, sheet='10May-16MayData', replace=True)
groupedCounts = archDf.groupby(['rangoPrecio', 'priceCut',
'selling_time'])['count'].sum().reset_index()
groupedCounts['percentage'] = groupedCounts['count'] / \
groupedCounts['count'].sum()
groupedCounts['count'].fillna(0, inplace=True)
groupedCounts['percentage'].fillna(0, inplace=True)
spread.df_to_sheet(groupedCounts,
index=False,
sheet='10May-16MayDataGrouped',
replace=True)
def importTo_clean_sheet():
from gspread_pandas import Spread, Client
import gspread_pandas as gp
import datetime
import numpy as np
import pandas as pd
import re
import importlib
#import cleaner
#cleaner = importlib.reload(cleaner)
#from cleaner import cleaning_and_to_sql
pd.set_option('display.max_rows', 20)
pd.set_option('display.max_columns', 50)
s = Spread('work', 'Python Autoscrapers')
df1 = s.sheet_to_df(index=0,
start_row=1,
header_rows=1,
sheet="Smartphones-CPH").astype(str)
import time
from datetime import datetime
from datetime import timedelta
for i in ['Model', 'Brand', 'Color', 'Condition']:
df1[i] = df1[i].str.lower()
dups_ids = df1.pivot_table(index=['id'], aggfunc='size')
type(dups_ids)
di = dups_ids.to_frame()
di.head()
di.columns = ['days active']
di.reset_index(inplace=True)
df1 = pd.merge(df1, di, on='id')
df1 = df1.drop_duplicates(subset='id', keep='first')
filter = df1["d scrape"] != " "
df1 = df1[filter]
df1['state'] = "Not sold yet"
#datetime_object = datetime.strptime('Jun 1 2005 1:33PM', '%b %d %Y %I:%M%p')
for stdat, ndays, idx in zip(df1['d scrape'], df1['days active'],
df1.index):
do = datetime.strptime(stdat, '%d/%m/%Y')
ndays -= 1
do = do + timedelta(days=ndays)
tod = datetime.strptime(today.strftime("%d/%m/%Y"), '%d/%m/%Y')
if do < tod:
df1.state[idx] = 'Sold'
# print(do.strftime("%d/%m/%Y"), " ", stdat, " today", today.strftime("%d/%m/%Y"))
s = Spread('work', 'Python Autoscrapers')
s.df_to_sheet(df1,
index=False,
sheet='Smartphones-CPH/Clean',
start='A2',
replace=True)
