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)