<jupyter_start><jupyter_text>Authenticate the Google SDK and bring in the data<jupyter_code>!pip install -U -q PyDrive

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from google.colab import auth
from oauth2client.client import GoogleCredentials

# Authenticate and create the PyDrive client.
auth.authenticate_user()
gauth = GoogleAuth()
gauth.credentials = GoogleCredentials.get_application_default()
drive = GoogleDrive(gauth)
#!pip install --upgrade -q gspread

import pandas as pd

# This package can read google sheets
import gspread


gc = gspread.authorize(GoogleCredentials.get_application_default())
sh = gc.open_by_url('https://docs.google.com/spreadsheets/d/1LgVqtPS-xlU_w3EPHlzjc53B78QS321owqJe2Y1qZSQ/edit?ts=5d4357f8#gid=0')

# july 'https://docs.google.com/spreadsheets/d/1LgVqtPS-xlU_w3EPHlzjc53B78QS321owqJe2Y1qZSQ/edit?ts=5d4357f8#gid=0'

# aug 'https://docs.google.com/spreadsheets/d/1d1vH8RLG6sycVh-2E1EJeYWGvc9VsIEcn8LUUsbtpsQ/edit?ts=5d4357ae#gid=619122347'
import re

def find_matrix(test_list):
  temp = []
  for i in test_list:
    try:
      temp.append(re.findall("\'(.+Matrix)\'",str(i))[0])
    except:
      continue
  return(temp[0])

find_matrix(sh.worksheets())
sh.worksheet(find_matrix(sh.worksheets()))
import numpy as np

# Put into a dataframe
matrix = pd.DataFrame(sh.worksheet(find_matrix(sh.worksheets())).get_all_values())
del matrix.index.name
matrix = matrix.replace('NA',np.nan)

matrix = matrix.replace('', np.nan).dropna(how='all').dropna(axis=1, thresh =2)
#matrix = matrix.iloc[0:len(matrix)-2,:]

matrix.head()
def replicate_down(raw_list):

  column_names = []

  for i in range(len(raw_list)):
    if str(raw_list[i]) == 'nan':
      if str(raw_list[i-1]) == 'nan' and i != 0:
        column_names.append(column_names[-1])
      else:
        column_names.append(raw_list[i-1])
    else:
      column_names.append(raw_list[i])

  return(column_names)
index_df = pd.DataFrame(list(zip(list(matrix.iloc[0,:]),list(matrix.iloc[1,:])))).dropna(how='all')
index_df[0] = replicate_down(list(index_df[0]))
index = list(index_df.itertuples(index=False, name=None))

new_index = []
for tup in index:
  if str(tup[1]) == 'nan':
    new_index.append(tup[0])
  else:
    new_index.append(tup[0]+' - '+tup[1])

index_df['Label'] = new_index
new_index
index_df
label_dict = dict(zip(index_df['Label'],index_df[0]))
label_dict
platforms = list(set(replicate_down(list(matrix.iloc[1,:]))))

def clean_list(list_of_stuff, list_to_remove):
  for i in list_to_remove:
    try:
      list_of_stuff.remove(i)
    except:
      continue
  return(list_of_stuff)

platforms = clean_list(platforms, ['VISITS','URL',np.nan])

platforms
df_list = []

for i in platforms:
  df_list.append(list())

p_labels = []
for i in platforms:
  p_labels.append(list(index_df.loc[index_df[1]==i,'Label']))

g_labels = list(index_df.loc[index_df[1].isna(),'Label'])
g_labels = g_labels[1:]

b_labels = list(index_df.loc[index_df[0]=='BUDGET','Label'])

#platform: p_labels
#general: g_labels
#budget columns: b_labels

g_labels
p_labels
b_labels
matrix2 = matrix.iloc[2:len(matrix),0:len(new_index)]
matrix2.columns = new_index
matrix2 = matrix2.iloc[:,1:len(list(matrix2))]

matrix2.reset_index(drop=True, inplace=True)

matrix2.tail()
def find_string(column_name):
  for i in list(matrix2.loc[matrix2[column_name].notna(),column_name].unique()):
    if i.startswith('$'):
      continue
    else:
      return(i)

remove = (matrix2.loc[matrix2[b_labels[0]]==find_string(b_labels[0]),b_labels[0]].index)
last_row = int(matrix2.loc[remove,:].reset_index().iloc[:,0])

matrix2 = matrix2.iloc[0:last_row,:]

matrix2.tail()
print('NAs by Column:','\n')
for i in list(matrix2):
  print(i+':'+str(len(matrix2.loc[matrix2[i].isna(),i])))
# populate all rows on the date column
matrix3 = matrix2.copy()
matrix3.iloc[:,0] = replicate_down(list(matrix2.iloc[:,0]))

matrix3.tail()
df_split = []
for i in range(len(platforms)):
  df = matrix3.loc[:,g_labels+p_labels[i]]
  df.rename(columns=label_dict, inplace=True)
  df_split.append(df)
df_split[0].head()
final_df = pd.concat(df_split)
final_df = final_df.loc[final_df['BUDGET'].notna(),:]
final_df['PLATAFORMA'] = replicate_down(list(final_df['PLATAFORMA']))

final_df = final_df.apply(lambda x: x.str.strip() if x.dtype == "object" else x)

final_df.head()
from google.colab import files

# e.g. save pandas output as csv
final_df.to_csv('SM Matrix.csv', index=False)

files.download('SM Matrix.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15928223305272485, 'fraction_numerical': 0.026140894993354008, 'mean_word_length': 5.872146118721461, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '75171', 'license': 'MIT', 'n_tokens_mistral': 1846, 'n_tokens_neox': 1784, 'n_words': 288, 'path': 'SM_Matrix.ipynb', 'repo_name': 'pmb06d/SM_Cinemax_Matrix', 'size': '4514'}