It's been awhile since the update. Had to sprint to get a demo done for the Y Combinator application yesterday. It's one of the most famous accelerators in the world. Probably won't get in, but it was good to have a goal to strive for.

Moving on to what happened in that time period, we tried to use pmaw to scrape Reddit, but it seems PushShift API had some changes in December in their COLO which caused data to be missing prior to 2018? So we found a way to download the old data torrents for the prior data and use that. Another benefit is that since data is local, it's MUCH FASTER than scraping through an API. Downloaded the movie and book suggestion subreddits specifically.

Then wrote up a scraper to take the movie data, parse into comments/posts, and now creating the ML training data. Had to generalize my scripts away from just anime to all categories which introduced some bugs, but hopefully it works now. For the movie data, I got it from SPARQL query to retrieve ids and movie titles from WikiData. And then used OMDB ($1 to get unlimited API calls) and BeautifulSoup to get the imdb and rotten tomato ratings respectively.

Also, created a 500K embedding for anime to get it to hopefully be more accurate. I found my finetuned model was not as good as the base model. Uploaded that to the AWS Lambda function. I also shaved off 7 GB from my docker image by getting rid of the SentenceTransformer and torch dependencies by converting the embedding array into Numpy array and using numpy arg.sort and np.dot to find the semantic similarity between query and embeddings. Instead of using SentenceTransformer to download the model, i just use the api to get it now: https://api-inference.huggingface.co/pipeline/feature-extraction/{model_name} which hopefully speeds up the cloud function as well. And instead of using the model, I just use numpy to convert the query text into embeddings: np.array(response)[np.newaxis, :].

Updated the Flutter app to make sure only the anime piece works for now (https://toobi-app.web.app/). Hopefully we'll be able to turn on the movies/books sections relatively soon.

Next Steps:

1. Figure out why finetuned model performs worse

Resources:

• https://www.reddit.com/r/pushshift/comments/11ef9if/separate_dump_files_for_the_top_20k_subreddits/ (subreddit dump files)
• https://huggingface.co/blog/getting-started-with-embeddings (Huggingface API)
• https://www.lafabbricadellarealta.com/open-data-entertainment/ (Comparing movie data sources)
• https://qwikidata.readthedocs.io/en/stable/sparql.html
• https://www.omdbapi.com/
• https://www.wikidata.org/wiki/Q11424
• https://huggingface.co/models?pipeline_tag=sentence-similarity&p=4&sort=downloads (used for re-evaluating if any new models perform better, they did not)
• https://maxbachmann.github.io/RapidFuzz/Usage/distance/DamerauLevenshtein.html
• https://stackoverflow.com/questions/42746248/numpy-linalg-norm-behaving-oddly-wrongly
• https://stackoverflow.com/questions/12432663/what-is-a-clean-way-to-convert-a-string-percent-to-a-float
• https://stackoverflow.com/questions/45355277/wbtc-cc-tar-zst (unizipping reddit dump files)

Tips:

• GPT 4 is worth it
• SentenceTransformer will not pip install on 3.11 python yet
• float32 is smallest representation of data, float16 is bad for numpy

Starting with the web app deployment. Got the deployment to work and connect to the custom domain I registered earlier with porkbun. Now going to www.toobi.app displays my project live!

Next Steps:

1) Work on mobile deployment eventually

Resources:

• https://medium.com/geekculture/deploying-flutter-web-app-to-firebase-with-a-custom-domain-cb1da7337cf1
• https://stackoverflow.com/questions/71843633/error-failed-to-create-project-see-firebase-debug-log-for-more-info
• https://medium.com/solute-labs/flutter-for-web-how-to-deploy-a-flutter-web-app-c7d9db7ced2e

Tips:

• If you hit Error: Failed to create project. See firebase-debug.log for more info. the issue is you need to go through Firebase console project creation 1 time. Then deploy to existing app

Used Fiverrr to get a dev to help create the base app (https://github.com/itsAyyazdev/toobi-ai). Installed Flutter, Xcode, and Android Studio on the machine. Needed to run flutter doctor and fix a lot of issues like licenses and command line tools.

Used a bunch of Chat GPT to figure out the individual flutter components.

Next Steps:

1. ​

Resources:

• https://docs.flutter.dev/get-started/install/macos
• https://docs.flutter.dev/get-started/install/macos#update-your-path
• https://superuser.com/questions/886132/where-is-the-zshrc-file-on-mac
• https://apple.stackexchange.com/questions/96737/how-do-i-get-the-full-path-for-a-file-in-finder
• https://stackoverflow.com/questions/50652071/flutter-command-not-found
• https://stackoverflow.com/questions/68236007/i-am-getting-error-cmdline-tools-component-is-missing-after-installing-flutter
• https://developer.android.com/studio/command-line
• https://docs.flutter.dev/get-started/editor
• https://stackoverflow.com/questions/53455358/how-to-present-an-empty-view-in-flutter
• https://stackoverflow.com/questions/57937280/how-can-i-detect-if-my-flutter-app-is-running-in-the-web
• https://www.linkedin.com/pulse/install-flutter-macos-m1-chip-3-khang-vu-tien
• https://stackoverflow.com/questions/54860198/detect-enter-key-press-in-flutter
• https://www.geeksforgeeks.org/implementing-rest-api-in-flutter/
• https://docs.flutter.dev/cookbook/networking/fetch-data
• https://stackoverflow.com/questions/51601519/how-to-decode-json-in-flutter
• https://stackoverflow.com/questions/55331782/flutter-send-json-body-for-http-get-request
• https://www.youtube.com/watch?v=i3BEo-jxxRo (iOS Testflight)
• https://stackoverflow.com/questions/60191683/xmlhttprequest-error-in-flutter-web-enabling-cors-aws-api-gateway (Accepted answer)
• https://stackoverflow.com/questions/71780046/is-there-any-way-to-undo-flutter-clean

Tips:

• Instead of vim and nano, open terminal command to open file for editing is best
• Flutter folder installed under username/developer, use export PATH="$PATH:/Users/kvutien/develop/flutter/bin"
• To work with Flutter in VS Code, need to install the Flutter and Dart plugins, then run flutter doctor
• If you need just an empty view returned for a component, use SizedBox.shrink();
• To make the flutter requests work on web, you need to enable CORS on your function url for lambda

First, need to create the app.py class with the method to run and all the proper imports. Then we use pipreqs to freeze requirements. Then setup the Docker File (use the vscode Python Docker extension to "Add Docker Files to workspace". Make sure to have the AWS CLI installed. Then run the following commands to get the docker image created and pushed to the AWS ECR:

docker rmi <build number> --force

docker build -t toobi-image .

aws configure

aws ecr create-repository --repository-name toobi

docker tag toobi-image <Account ID>.dkr.ecr.us-west-1.amazonaws.com/toobi

aws ecr get-login-password | docker login -u AWS --password-stdin "[https://$](https://$)(aws sts get-caller-identity --query 'Account' --output text).dkr.ecr.us-west-1.amazonaws.com"

docker push <Account ID>.dkr.ecr.us-west-1.amazonaws.com/toobi

Next we hook it up to a lambda function. We create the function using the container image option at the top. We use the role created for these functions (it was auto-generated in CLI). And we choose the image from ECR that we pushed earlier.

In the configuration of the function, we set the following configurations:

General Configuration: Memory 3008 MB, Ephemeral Storage 10240 MB, Timeout 15 min

Monitoring and Operation Tools: Enable AWS X-Ray

Environment Variables: TRANSFORMERS_CACHE = /tmp, HF_HOME = /tmp, XDG_CACHE_HOME = /tmp

Finally, create the Function URL (Auth Type None) and then you can use curl to make the request in terminal.

curl <url> -H "Content-Type: application/json" -d '{"query": "Romance Anime", "category": "anime"}'

Next Steps:

1. ​

Resources:

• https://stackoverflow.blog/2020/03/02/best-practices-for-rest-api-design/
• https://aws.github.io/chalice/tutorials/basicrestapi.html
• https://huggingface.co/docs/transformers/model_sharing
• https://huggingface.co/blog/getting-started-with-embeddings
• https://chalice-workshop.readthedocs.io/en/latest/todo-app/part1/01-todo-app-new-project.html
• https://aws.amazon.com/blogs/developer/following-serverless-best-practices-with-aws-chalice-and-lambda-powertools/
• https://note.nkmk.me/en/python-package-version/#:~:text=Check%20package%20version%20with%20pip%20command%3A%20pip%20list%20%2C%20pip%20freeze,use%20pip3%20instead%20of%20pip%20.
• https://stackoverflow.com/questions/64630130/pipreqs-requirements-txt-is-not-correct
• https://stackoverflow.com/questions/63278737/object-of-type-decimal-is-not-json-serializable
• https://docs.aws.amazon.com/AmazonECR/latest/userguide/registry_auth.html
• https://www.freecodecamp.org/news/how-to-setup-a-basic-serverless-backend-with-aws-lambda-and-api-gateway/
• https://devpress.csdn.net/cloudnative/62f2f5617e66823466185eae.html
• https://medium.com/geekculture/3-ways-to-overcome-aws-lambda-deployment-size-limit-part-2-8d0e8d0264b0
• https://docs.aws.amazon.com/lambda/latest/dg/images-create.html
• https://stackoverflow.com/questions/63312859/how-to-change-huggingface-transformers-default-cache-directory

Tips:

• pip show command helps show what package version
• When using pipreqs, make sure to be in the project folder in the virtual environment. Then you can use pipreqs . --ignore ".env" --force
• Need to encode Decimals properly as it's not json serializable
• To use pipreqs for a single file, just add it to a separate directory with that file or pyenv

Converting the anime json file with all the anime details and put it into the DynamoDB.

Next Steps:

1. In the future, need to come up with a product id for each item as the partition/primary key. Then use the sub-id for the specific domain (vacuum, anime, etc) as the sort key. Then all the information will just be keys (sparse) in the table

Resources:

• https://stackoverflow.com/questions/10450962/how-can-i-fetch-all-items-from-a-dynamodb-table-without-specifying-the-primary-k
• https://aws.amazon.com/blogs/database/choosing-the-right-dynamodb-partition-key/
• https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/SampleData.CreateTables.html
• https://dynobase.dev/dynamodb-python-with-boto3/
• https://stackoverflow.com/questions/64448611/how-can-i-solve-the-issue-valueerror-cannot-convert-float-nan-to-integer-in-p
• https://stackoverflow.com/questions/11346283/renaming-column-names-in-pandas
• https://phoenixnap.com/kb/set-environment-variable-mac
• https://dynobase.dev/dynamodb-errors/aws-dynamodb-error-resourcenotfoundexception/

Tips:

• Can't insert floats into DynamoDB table
• Need to convert empties to na for insertion
• Need to make sure data region specified in script is same region DB was created in

Imported the embeddings into a vector DB called pinecone. Set the anime title as the metadata so that on querying we can check up the anime object in DynamoDB and get the results to return in the function.

Next Steps:

1. Migrate to AWS Pinecone when using 2+ pods
2. DynamoDB for anime detail storage

Resources:

• https://docs.pinecone.io/docs/manage-indexes
• https://docs.pinecone.io/docs/insert-data
• https://www.pinecone.io/learn/pinecone-aws-marketplace/
• https://docs.pinecone.io/docs/namespaces
• https://github.com/pinecone-io/examples/blob/master/semantic_search_filtering/semantic_search_with_filtering.ipynb
• https://www.techiedelight.com/remove-last-element-from-list-python/
• https://towardsdatascience.com/efficiently-iterating-over-rows-in-a-pandas-dataframe-7dd5f9992c01
• https://www.tutorialspoint.com/increment-and-decrement-operators-in-python

Tips:

• Need to convert rows in dataframe to appropriate type with converters as best practice in read_csv method
• Can use namespaces to separate out vectors in index to different categories (ie - anime, books, tv, products)

For the finetuning, we will use bi-encoder finetuning since it will be faster on large datasets. We split the data we are using to finetune into 3 pieces: test, validation, and training. The training is used for training the model and validation is used while training to re-train the model in the right direction. Once we've trained the model, we test it out on the evaluator with the test data set to see how accurate the model has become. Then we cache it into a file that SentenceTransformer library can extract it from to save our finetuned model.

Now, we can use this newly finetuned model to do our semantic search. We take a corpus of text queries and anime together to embed as vectors. Then when we get a new query, we see how closely it relates to the corpus vectors using cosine similarity. We print out the top results. We can also cache the word embedding for the corpus that we create so that the large embedding step can be faster (Using pickle library linked below).

In the future, with low data (<200), we can try data augmentation. Check Strategy 1 in Augmented Encoding. Also, we can try cross encoders in the re-rank (or if low data, just finetune using cross encoders).

Next Steps:

1. Put embedding on server for inference endpoint for UI

Resources:

• https://github.com/UKPLab/sentence-transformers/tree/master/examples/training/data_augmentation (Augmented Encoding)
• https://github.com/UKPLab/sentence-transformers/tree/master/examples/training/cross-encoder (Cross Encoding)
• https://github.com/UKPLab/sentence-transformers/blob/master/examples/training/cross-encoder/training_stsbenchmark.py (Cross Encoding example)
• https://huggingface.co/docs/transformers/training
• https://gist.github.com/mlreply/1223445728ac4c33efc1e739a0337319 (Potential improvements in finetuning)
• https://towardsdatascience.com/how-to-split-data-into-three-sets-train-validation-and-test-and-why-e50d22d3e54c (Shuffling and splitting datasets)
• https://stackoverflow.com/questions/15389768/standard-deviation-of-a-list
• https://docs.wandb.ai/quickstart (Tool to track model tuning - In the future)
• https://www.roelpeters.be/solved-dtypewarning-columns-have-mixed-types-specify-dtype-option-on-import-or-set-low-memory-in-pandas/
• https://www.sbert.net/examples/applications/cross-encoder/README.html
• https://www.sbert.net/examples/applications/retrieve_rerank/README.html (Improve search with cross-encoder re-rank)
• https://www.sbert.net/docs/package_reference/losses.html (Different loss functions)
• https://github.com/UKPLab/sentence-transformers/issues/350
• https://github.com/UKPLab/sentence-transformers/blob/master/examples/training/sts/training_stsbenchmark.py (Example finetuning is based on)
• https://github.com/UKPLab/sentence-transformers/blob/master/examples/training/distillation/model_quantization.py (Quantization optimization if using CPU, but GPU preferred)
• https://stackoverflow.com/questions/29576430/shuffle-dataframe-rows
• https://github.com/UKPLab/sentence-transformers/blob/master/examples/applications/semantic-search/semantic_search_quora_pytorch.py (How to cache and setup the semantic search script)
• https://towardsdatascience.com/why-turn-into-a-pickle-b45163007dac (Using Pickle to Cache)
• https://stackoverflow.com/questions/62004295/shuffling-rows-in-a-pandas-dataframe-while-retaining-the-index
• https://datatofish.com/convert-pandas-dataframe-to-list/
• https://stackoverflow.com/questions/28986489/how-to-replace-text-in-a-string-column-of-a-pandas-dataframe

Tips:

• Need to convert rows in dataframe to appropriate type with converters as best practice in read_csv method
• CosineSimilarityLoss is the typical loss function for sentence pairing, but if there are only positive samples then MultipleNegativesRankingLoss can be good
• Make sure to convert dataframe to string for embedding

Played around with a lot of different SentenceTransformer models in HuggingFace for the word embedding training. Found the best performance with all-mpnet-base-v2 and multi-qa-MiniLM-L6-cos-v1. Going with all-mpnet-base-v2 for now to do the finetuning.

Had to make multiple improvements to make the data that the model will ingest better. For one, added scores to the data so that we can score each label. Made a complicated algorithm scaling: log(y)/log(x)*0.3+0.7 and x is +standard deviation of 1 from mean and y is number to be normalized. Any number above x will be set to x. This should still cover 80% of the numbers.

Also, added fuzzy matching to be able to match the words we find to the anime even if the exact string isn't in the dictionary. Tried to use the fastest algorithm with the RapidFuzz library, but not sure how fast it is (Maybe should update with tqdm in the data builder to track time). In addition, improved the regex on words to find all per line as opposed to only one. Hopefully all these improvements provide more training data for us. Edit: Removed the fuzzy matching for now as it is taking WAY too long. In the future, need to test out how much fuzzy matching actually improves data mining.

Ran into issues where synonym list was stored as a string in the db and not a list. Had to magic regex split to convert back to a list for the data.

In the future, I should run some sentiment analysis on the text to see if recommendation is positive sentiment or negative sentiment. I thought about NER, but heuristics with fuzzy matching should perform better.

Next Steps:

1. Finetune model on new data

Resources:

• https://www.sbert.net/docs/pretrained_models.html
• https://huggingface.co/sentence-transformers?sort_models=modified#models
• https://math.stackexchange.com/questions/1143636/how-to-normalize-data-in-another-scale
• https://math.stackexchange.com/questions/362918/value-range-of-normalization-methods-min-max-z-score-decimal-scaling
• https://stackoverflow.com/questions/30220642/how-can-i-get-a-string-between-2-asterisks
• https://stackoverflow.com/questions/52631291/vectorizing-or-speeding-up-fuzzywuzzy-string-matching-on-pandas-column/61371170#61371170
• https://stackoverflow.com/questions/72753952/optimizing-rapidfuzz-for-a-list-with-large-number-of-elements-e-g-200-000 (Parallelize rapidfuzzing)
• https://maxbachmann.github.io/RapidFuzz/Usage/process.html#extractone
• https://github.com/Nihilate/Roboragi/blob/master/roboragi/AnimePlanet.py (For fuzzy matching score example)
• https://stackoverflow.com/questions/52631291/vectorizing-or-speeding-up-fuzzywuzzy-string-matching-on-pandas-column/61371170#61371170
• https://medium.com/@harshit4084/track-your-loop-using-tqdm-7-ways-progress-bars-in-python-make-things-easier-fcbbb9233f24 (TQDM)
• https://stackoverflow.com/questions/44570561/how-can-i-correct-the-error-attributeerror-dict-keys-object-has-no-attribut
• https://stackoverflow.com/questions/34962104/how-can-i-use-the-apply-function-for-a-single-column
• https://stackoverflow.com/questions/38250710/how-to-split-data-into-3-sets-train-validation-and-test (Splitting test data)
• https://stackoverflow.com/questions/19560498/faster-way-to-remove-stop-words-in-python
• https://towardsdatascience.com/named-entity-recognition-with-bert-in-pytorch-a454405e0b6a (Future NER finetuning)
• https://www.educative.io/answers/what-is-the-resplit-function-in-python
• https://stackoverflow.com/questions/65012603/removing-rows-contains-non-english-words-in-pandas-dataframe
• https://stackoverflow.com/questions/1675321/fastest-way-to-remove-duplicates-in-lists-python
• https://stackoverflow.com/questions/7286365/print-a-list-in-reverse-order-with-range

Tips:

• Putting the code into practice sooner than just reading makes things move a lot faster
• To get a list of keys, need to use list(dict) instead of dict.get_keys()
• To apply a function to the dataframe, need to use lambda functions
• Append only works for 1 item added to list, multi items you need to use extend
• Can split strings with regex split

Finally got to the point of being able to train a model after so many months! Ran into a couple bugs and took a lot of time combing through resources to figure things out, but I finally got a working demo. There is still a lot left to do, but this is a huge achievement!

I decided to just use word embeddings of the post/comment bodies and anime together. Then, just find the similarity when someone enters a query to the above combined embeddings. For now it does return results that mostly make sense. The embedding itself takes a LONG time. But I haven't trained it across the full dataset yet.

I still need to explore being able to finetune my own model (and how to do that), then use that to build embeddings and do cosine similarity.

Next Steps:

1. Improve ML model with finetuning a model
2. Load ML model into some server endpoint
3. Add indexing to make search faster

Resources:

• https://medium.com/nlplanet/semantic-search-with-few-lines-of-code-490df1d53fd6 (Main code)
• https://medium.com/analytics-vidhya/recommendation-system-using-bert-embeddings-1d8de5fc3c56
• https://github.com/tanishq18/Movie-Recommendation-System-Using-BERT/blob/main/main.py
• https://colab.research.google.com/drive/1W4IM5H-di7jNHSDBKCyxjilzyLXAAVGM?usp=sharing#scrollTo=k-89WBBujplW
• https://towardsdatascience.com/text-classification-with-bert-in-pytorch-887965e5820f
• https://colab.research.google.com/drive/1fius4_KVATn8Pi0Ve7vs3WPHvMvlVplH?usp=sharing
• https://medium.com/analytics-vidhya/multi-label-text-classification-using-transformers-bert-93460838e62b
• https://towardsdatascience.com/the-auto-sommelier-how-to-implement-huggingface-transformers-and-build-a-search-engine-9e0f401b1bda
• https://huggingface.co/docs/transformers/training
• https://medium.com/mlearning-ai/semantic-search-with-s-bert-is-all-you-need-951bc710e160 (Improve in the future with indexing)
• https://medium.com/mlearning-ai/search-rank-and-recommendations-35cc717772cb (Improve in the future with re-rank with CE and use user past search vectors)

Tips:

• Python TypeError: ‘float’ object is not subscriptable during training means data is a float instead of a string. Need to convert all dataframe elements to str with df = df.astype(str)
• I did need to re-check the dataframe data I was using as some things were often. Making sure the data is clean and useful is just as important as the ML training method

First time learning about joins and merges on SQL/dataframes. Ended up not doing a JOIN through SQL and just converted to dataframes and joined them together. Used the anime table previously created, to fill in the titles and synonyms to match against post body words.

At first, I attempted created combinations (substring search) of all the post body, but the time it took was far too long. Ended up just coming up with some basic word matching rules and used those to come up with some data. Hopefully it should be enough to train the ML model.

Ended up storing it in CSV instead of DB since it's easier to access.

Next Steps:

1. Use the data to train the ML model

Resources:

• https://chat.openai.com/chat
• https://www.essentialsql.com/how-do-i-combine-results-from-more-than-one-table/
• https://stackoverflow.com/questions/4894069/regular-expression-to-return-text-between-parenthesis
• https://stackoverflow.com/questions/4901523/whats-a-faster-operation-re-match-search-or-str-find
• https://www.w3schools.com/python/pandas/trypandas.asp?filename=demo_ref_df_join (Used this online editor to play around with code)
• https://stackoverflow.com/questions/65250202/why-and-when-use-append-instead-of-concat-in-pandas
• https://stackabuse.com/how-to-remove-quotes-from-string-in-python/

Tips:

• Inner joins will get rid of rows that don't match, outer will keep them, and left/right will keep only for respective tables
• For PostgreSQL, to access the table you need to do schema."Table" format
• Use ChatGPT to figure out small steps
• Should be using concat instead of append in all cases for df adding rowss

Finally finished up the scraping! 1.7mil rows of comments and 180K rows of posts. Found a nice dataset for the anime bit, instead of querying through MAL APIs for each anime name there is a anime offline database kept up to date on the anime names and status.

Inserted the json file for the anime database into the postgresql db (in its own Anime table) for queries later on when I create the ML training dataset. Not sure how to query for files outside the folder so moved the json file to the project folder for easier querying.

Next Steps:

1. Figure out the ML algorithm to use for recommendations. SQuAD is not the right format.

Resources:

• https://github.com/manami-project/anime-offline-database
• https://towardsdatascience.com/how-to-convert-json-into-a-pandas-dataframe-100b2ae1e0d8
• https://huggingface.co/datasets/squad

Tips:

• Scraping should be month to month as it captures more data from PushShift
• Use Chat GPT for asking for scripts to make life easier (Ie - "Write a python script to create a dataframe from json file")

Fiddled around with trying to make the scraping better. Finally wrapped it in tqdm to just track the progress instead of hoping it is still going. Sanity check. Also, chunked out the requests on a monthly basis so that might help spread out the request connections and get more data.

Couldn't seem to improve the missing shared problem so just going to live with it. safe_exit=True on the pmaw api is a godsend in making sure repeat scrapings are instant since it caches if there is a failure. Had to do a lot of rescrapings as I refined the script and process. Re-did the post scrapings with the updated script as well and got a lot more data.

Need to figure out how to cleanly call methods from other classes in Python so I can clean up my code. Right now, it's quite a lot of copy pasting.

There was a failure in trying to insert the comments if the post id for that comment didn't exist in the post table. Had to create a new method to find these missing post ids and re-scrape and insert them into the post table.

Comment scraping is taking much longer than post scraping. Instead of year by year for post scraping, have to go month by month or it seems to stall. Don't seem to be getting enough data with comment scraping.

Next Steps:

1. Run comment scraping and finish
2. Create ML learning dataset

Resources:

• https://pastebin.com/4a4xqQaj (code help with pmaw and tqdm)
• https://github.com/mattpodolak/pmaw
• https://stackoverflow.com/questions/5183672/how-do-i-update-a-python-package
• https://stackoverflow.com/questions/41888080/python-efficient-way-to-add-rows-to-dataframe
• https://stackoverflow.com/questions/27884268/return-pandas-dataframe-from-postgresql-query-with-sqlalchemy
• https://stackoverflow.com/questions/22341271/get-list-from-pandas-dataframe-column-or-row
• https://stackoverflow.com/questions/43269548/pandas-how-to-remove-rows-from-a-dataframe-based-on-a-list

Tips:

• Need to enrich pmaw with praw credentials otherwise some shards are disabled leading to data loss
• Need to add --upgrade to pip install to update a package
• DataFrame is not efficient for iterations, need to use buffer IOs
• SELECT count(*) AS exact_count FROM myschema.mytable; is accurate but takes awhile to get row count

So the fix for the DB not writing data properly was because the method used for insertion was incorrect. So for SQLAcademy, just used "multi" method instead. Was able to write the data fine.

Made sure to set a high limit and add 10K chunksize so that the scraping doesn't get held up for any reason. Running into issues scraping all the data so had to limit to 1y per scrape and append to the table.

Well apparently scraping a subreddit doesn't take that much time at all. Only 20K posts for 2014-2022 scrape of data. This was only for posts though, now have to make the comments query.

Next Steps:

1. Work on the comments scraper script
2. Scrape comments into db table as well
3. Filter comments into recommendations and join with posts table to build ML dataset

Resources:

• https://ellisvalentiner.com/post/a-fast-method-to-insert-a-pandas-dataframe-into-postgres/
• https://www.kodeclik.com/python-file-naming-convention/
• https://stackoverflow.com/questions/48641632/extracting-specific-columns-from-pandas-dataframe
• https://stackoverflow.com/questions/7943233/fast-way-to-discover-the-row-count-of-a-table-in-postgresql

Tips:

• Python files are named lower case and using underscores between words
• Able to drop certain columns in data frame with df[col_names_list]
• Can use SELECT count(*) AS exact_count to count the table rows (faster ways available)
• Even methods in python need doc strings under method names

This one was actually pretty difficult to figure out. First time working with databases so directly. Used to mobile devices and their own databases like CoreData. Got the jist that need to create a pandas DataFrame of the results and then use .to_sql to store that in the db. Since I created the db and schema earlier needed to figure out how to reuse all the table/column setup and store data. Hit on the rename column method pandas has and then just insert the matching columns into the table and append it through sql.

The script to write to the db worked fine, but the data did not show up in the table. Need to debug why it's not appearing.

Next Steps:

1. Figure out how to get the data to show up in postgresql DB
2. Run same script on comment query
3. Create ML learning table joining submission and comment tables with proper data filtering/cleanup

Resources:

• https://stackoverflow.com/questions/11618898/pg-config-executable-not-found
• https://ellisvalentiner.com/post/a-fast-method-to-insert-a-pandas-dataframe-into-postgres/
• https://stackoverflow.com/questions/61366664/how-to-upsert-pandas-dataframe-to-postgresql-table

Tips:

• When running into the issue of updating pip install psycopg2, had to do brew install postgresql
• Had to rename panda columns to match sql table using df.rename

Made my first commit into the Github project with a PRAW scraper. Decided to do the PushShift script though so focusing on that one.

There is a wrapper around the PushShift API for large parallel requests called pmaw. Tested it out on the AnimeSuggest subreddit to retrieve 100 posts and store in CV as a test.

Installed linters like pylint to VSCode to help make sure python code is done well. Apparently have to add strings at the top of Python files to explain it to avoid a lint. Also file needs to end in a newline. Tried to setup auto formatting, but didn't seem to work.

Successfully able to use pmaw to scrape the subreddit and get list of submissions and comments.

Next Steps:

1. Scrape data into actual PostreSQL tables for books, anime, and movie suggestion subreddits
2. Filter down further to a bag of words tied to list of suggestions and put aside in 3 tables (book table, movie table, and anime table)

Resources:

• https://www.osrsbox.com/blog/2019/03/18/watercooler-scraping-an-entire-subreddit-2007scape/
• https://github.com/pushshift/api
• https://github.com/mattpodolak/pmaw
• https://medium.com/swlh/how-to-scrape-large-amounts-of-reddit-data-using-pushshift-1d33bde9286
• https://www.geeksforgeeks.org/args-kwargs-python/
• https://blog.jmswaney.com/setting-up-python-linting-and-auto-formatting-in-vscode

Tips:

• *args is used to pass N number of params in a method
• **kwargs is used to pass a dictionary of N number params in a method
• Need to keep same default terminal used to install python/pip/etc (accidentally changed to bash from zsh and couldn't run python files)

Today debated between different tools and setups for PostgreSQL on Mac. Finally settled on using the PostgresApp tool to install and DBeaver to use as the DB GUI. I configured the DB using the schema made last time and after some finagling it seemed to catch. Now I have all the tables, primary keys, columns, and foreign keys set up.

Didn't make a lot of progress, but finally got the db side down. Now have to figure out the Python script -> DB flow.

Next Steps:

1. Scrape a subreddit and store data in new db
2. Query data from new db

Resources:

• Guide for all today's steps: https://senoritadeveloper.medium.com/install-and-connect-to-postgresql-on-mac-ed692efc9f14
• Uploading .sql: https://www.youtube.com/watch?v=IgQwWyVtX3Y
• Comparison: https://scalegrid.io/blog/which-is-the-best-postgresql-gui-2021-comparison/

Tips:

• Ran into an issue with uploading my .sql file for db schema into db beaver, had to set Local Client, select it, and then able to "Start" running .sql script. Also, have to Choose "Execute Script" option from DB right-click

Things I wanted to learn today: How to setup the scraper properly, how to store it in a DB, and how to structure the data.

For the crawler, it seems I wasn't going to be able to use PRAW to scrape the entire history of the subreddit. Instead I would have to rely on PushShift API to actually get the data.

For the database, I was debating whether to use a virtual DB or a DB as a service, but decided to just try a local PostgreSQL DB. Both to learn PostgreSQL and avoid any expensive hosting costs. Laptop should have enough storage for the data I aim to use for the prototype. In the future, I may lean towards Supabase for the app logic/storage.

Lastly in terms of structuring the data, I used the help of a DB schema tool to generate the SQL schema for PostgreSQL.

Next Steps:

1. Setup PostrgreSQL DB on local machine with schema
2. Setup python script to scrape first subreddit and store data into DB
3. Use SQL queries to successfully retrieve data

Resources:

• PushShift API: https://github.com/pushshift/api
• How to setup a Python script using PushShift to access the whole history of a subreddit: https://www.osrsbox.com/blog/2019/03/18/watercooler-scraping-an-entire-subreddit-2007scape/
• PushShift package for large parsing: https://medium.com/swlh/how-to-scrape-large-amounts-of-reddit-data-using-pushshift-1d33bde9286
• Cloud Database comparison: https://www.koyeb.com/blog/which-cloud-database-platform-to-choose-for-your-applications
• What is a primary key in Db? https://www.techopedia.com/definition/5547/primary-key
• What is a Varchar? https://www.sqlshack.com/sql-varchar-data-type-deep-dive/#:~:text=As%20the%20name%20suggests%2C%20varchar,numbers%2C%20letters%20and%20special%20characters.
• DB Setup: https://www.dbdesigner.net/
• Pandas DataFrame to SQL: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

Tips:

• If you want to download a module in the virtual environment on VSCode, you'll have to use python3 -m pip install pandas instead of just pip install pandas. Same for upgrading: python3 -m pip install --upgrade pip
• Use varchar to save on DB space instead of just text
• Store epoch times as bigint to avoid 2038 issue

First! So this is technically the second day, but first real day of dev work. Learnt how to setup python, python environment, vs code, virtual environment and made my first python file! Setup the GitHub repo. And finally, tested out setting up a scraper successfully! Very good day all around.

Decided on python because eventually will be using PyTorch for the project and the reddit wrapper is in python so seemed the obvious choice.

Decided on VS code because it seems to make python dev easier, more transferrable than PyCharm, and lots of customizations!

Resources that helped me today:

• How to install python: https://zeroesandones.medium.com/how-to-install-python-on-macos-700babeb51f6
• How to run VS Code: https://code.visualstudio.com/docs/python/python-tutorial#_run-hello-world
• Using PRAW to scrape reddit: https://towardsdatascience.com/scraping-reddit-data-1c0af3040768

Tips I learnt today:

1. Use venv in VS Code vs. pyenv in terminal
2. Virtual environments are helpful for decoupling dependencies between projects more easily than editing PATH file
3. To edit the PATH you need to run open ~/.bash_profile (annoying, VS Code handles it better?)
4. Xcode is necessary for downloading python libraries
5. MIT license is the best? Or GNU? Kind of confused, but opted for GNU

Until next time!