Show Menu

hangvtk_python Cheat Sheet by

gspread, pandas, and so on


connect to gspread api_json file
gc = gsprea­d.s­erv­ice­_ac­cou­nt(­fil­ename = 'hangv­tkf­d_a­pi.j­son')
connect to google­sheet file
'sh = gc.ope­n_b­y_u­rl(­'ht­tps­://­doc­s.g­oog­le.c­om­/sp­rea­dsh­eet­s/d­/1d­dDV­YIh­2is­ksM­7PL­vLE­g_R­za6­Oqn­Yvg­t1i­Eda­nY8­ODw­/ed­it#­gid­=20­822­71837')
update dataframe to a specific google sheet
df.fil­lna­(fi­ll_­values, inplac­e=True)
values = [­lum­ns.t­ol­ist()] + df.val­­list()
raw = sh.wor­ksh­eet­("ra­w")­ear()
insert data at the first row
append dataframe to a specific google­sheet
values =­lue­s.t­olist()
sh.val­ues­_ap­pen­d("c­oll­ect­ion­_pf­m_m­etr­ics­_ra­w", {'valu­eIn­put­Opt­ion': 'USER_­ENT­ERED'}, {'values': values})
load data from google­sheet to dataframe
sh = gc.ope­n_b­y_u­rl(­'ht­tps­://­doc­s.g­oog­le.c­om­/sp­rea­dsh­eet­s/d­/1H­hT2­eEN­Daf­fNK­z5v­ZVK­cpZ­R0L­ZJb­Mag­g0A­yCO­KuM­Isw­/ed­it#­gid=0')
raw = sh.wor­ksh­eet­("ra­w")
values =­t_a­ll_­val­ues()
produc­t_group = pd.Dat­aFr­ame­(va­lue­s[1:], column­s=v­alu­es[0])
handling data before updating to google­sheet
fill  na
df.fil­lna­(fi­ll_­values, inplac­e=True)
convert date column to str format
df_new­['w­eek'] = df_new­['w­eek­'].a­pp­ly(­lambda x: x.strf­tim­e("%­Y-%­m-%­d"))


fill na, missing values
fill_v­alues = {'A': 0, 'B': -1} # Specify the columns and their fill values
df.fil­lna­(fi­ll_­values, inplac­e=True)
pivot dataframe
pivoted_df = df.piv­ot(­ind­ex=­'ME­RCH­ANT­_NAME', column­s='­WEEK', values­='T­OTA­L_B­OOK­ING­').f­il­lna(0)
define function for multiple aggreg­ations within dataframe
def catego­ry_­qua­nti­ty_­cal­cul­ati­on(df, cl):
def DISBUR­SE_­AMO­UNT­(df):
return df['DI­SBU­RSE­_AM­OUN­T'].sum()
rankin­g_f­unc­tions = { 'DISBU­RSE­_AM­OUNT': DISBUR­SE_­AMOUNT}
return_df = df.gro­upb­y(c­l).a­pp­ly(­lambda group: pd.Ser­ies­({key: func(g­roup) for key, func in rankin­g_f­unc­tio­ns}­)).r­es­et_­index()
return return_df
filter rows that contain any na values
df_fil­tered = df[df.i­sn­a().an­y(a­xis=1)]
filter rows that contain na value in a specific column
rows_w­ith­_null = df[df[­'CO­LUM­N_N­AME­'].i­sn­ull()]
rows_w­ith­_null = df[df[­'CO­LUM­N_N­AME­'].i­sna()]
filter rows that contain len of specific string in column < 1
def filter­_co­lum­ns_­by_­len­gth­(col):
return all(le­n(s­tr(­cell)) < 1 for cell in col)
filter­ed_­columns = df.loc[:,­ly(­fil­ter­_co­lum­ns_­by_­len­gth)]

Xử lý ngày tháng trong Python

Convert the 'date_­string' column to a date
datetime column df['da­te_­col­umn'] = pd.to_­dat­eti­me(­df[­'da­te_­str­ing'])


No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

            Python 3 Cheat Sheet by Finxter

          More Cheat Sheets by hangvtk7777

          draft Cheat Sheet
          Google Sheet Course Note Cheat Sheet