Write SQL / Athena Query that extracts and binarizes hashtags from table of tweets. Convert python code into SQL query.
Budget: $30 – $250 USD
I need some SQL queries that will more or less mimic the attached jupyter notebook / python code.
These queries will be used to manipulate an AWS Athena database. That may be a blocker for certain SQL functions.
The ideal candidate will have great familiarity with SQL and Data Science, and decent familiarity with Python, AWS and Athena.
If you're unfamiliar with python the steps are roughly this
1) Download From AWS Athena SQL Server (41k rows)
2) Isolate Text Column
3) Use Regex to extract lists of hashtags from each tweet (if there is only one hashtag, the list will have only one member)
4) Build a column of lists, where each cell contains a list of the hashtags
5) Input Column of Hashtag Lists into a Multi Label Binarizer/ Dummy Col generator that will output a binary matrix where there is a column for every hashtag and a row for every index/tweet. There should be a 1 in the rows that contain that hashtag.
6) Save binary matrix to new SQL table
I have performed all the above steps in Python, but you may have to find work around for the same steps to work in SQL. I'm ultimately concerned about the binary output matrix, and less about your fidelity to the steps above.
CSV dataset is attached along with jupyter notebooks, and screenshots of the tables in the project.
Let me know if you have questions
Thank you
Kenan Spruill, Optima Holdings
These queries will be used to manipulate an AWS Athena database. That may be a blocker for certain SQL functions.
The ideal candidate will have great familiarity with SQL and Data Science, and decent familiarity with Python, AWS and Athena.
If you're unfamiliar with python the steps are roughly this
1) Download From AWS Athena SQL Server (41k rows)
2) Isolate Text Column
3) Use Regex to extract lists of hashtags from each tweet (if there is only one hashtag, the list will have only one member)
4) Build a column of lists, where each cell contains a list of the hashtags
5) Input Column of Hashtag Lists into a Multi Label Binarizer/ Dummy Col generator that will output a binary matrix where there is a column for every hashtag and a row for every index/tweet. There should be a 1 in the rows that contain that hashtag.
6) Save binary matrix to new SQL table
I have performed all the above steps in Python, but you may have to find work around for the same steps to work in SQL. I'm ultimately concerned about the binary output matrix, and less about your fidelity to the steps above.
CSV dataset is attached along with jupyter notebooks, and screenshots of the tables in the project.
Let me know if you have questions
Thank you
Kenan Spruill, Optima Holdings