
# Assignment 2

Hey there! Welcome to Knowledge Lens Intern Training Program.

This Assignment will serve as a quick refresher on the usage of NoSQL and Time-series databases.
There are three tasks in this assignment, on completion of which you'll learn:
*  How to interact with Mongo DB
*  Using Pandas Dataframe and generating your own excel reports
*  Leveraging Kairos Time-series database for data ingestion and querying the same
*  Publishing and Consuming messages via MQTT protocol
*  Caching mechanism using Redis DB 

Happy Coding! :tada:

## :pushpin: Task 1: Working with Mongo - Advanced


### :golf: Areas covered:
- Timeseries Operation
- Working with NoSQL
- Working with Pandas

### :books: Description:

You are given with a dataset of bicycle rental company in the form of a JSON file. The end goal of the project is to create an API interface that will provide the following: 
1.  Get the user who has the highest trip duration.
2. Get the user who has used the service the most.
3. Generate Excel Report based on `bike id`, `station name` and `start date`
Sample Document:
```json
{
 "tripduration":  889,
 "start station id":  268,
 "start station name":  "Howard St & Centre St",
 "end station id":  3002,
 "end station name":  "South End Ave & Liberty St",
 "bikeid":  22794,
 "usertype":  "Subscriber",
 "birth year":  1961,
 "start station location":  {
 "type":  "Point",
 "coordinates":  [
 -73.99973337,
 40.71910537
 ]
 },
 "end station location":  {
 "type":  "Point",
 "coordinates":  [
 -74.015756,
 40.711512
 ]
 },
 "start time":  {
 "$date":  "2016-01-01T00:01:06.000Z"
 },
 "stop time":  {
 "$date":  "2016-01-01T00:15:56.000Z"
 }
}
```
Bonus Points: Use Mongo Aggregate framework

### :wrench: Tools to use: 
1. Pycharm / VSCode
2. Robo3T / Studio3T / MongoDB Compass
3. PyMongo


### :mag: References:
* [Querying Documents on Mongo](https://www.mongodb.com/docs/manual/tutorial/query-documents/)
* [Quick Summary on Mongo Aggregation Stages](https://www.mongodb.com/docs/manual/reference/operator/aggregation-pipeline/)
* [Generating Excel Sheets from a Pandas Dataframe](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html)
* [How to return files on FastAPI response](https://fastapi.tiangolo.com/advanced/custom-response/#fileresponse)
* [PyMongo Official Documentation](https://pymongo.readthedocs.io/en/stable/)



_________________________________

## :pushpin: Task 2: Working with Timeseries


### :golf: Areas covered:
- Timeseries Operation
- Working with Timeseries
- Working with Pandas

### :books: Description:
You are given with a dataset of weather in the form of a CSV file. The end goal of the project is to create an API interface that will provide the following: 

1. Get daily, monthly, weekly and monthly aggregate (min, max, and average) of the data and generate report in Excel format.

Sample Document:


|Formatted Date               |Summary      |Precip Type|Temperature C|Apparent Temperature C|Humidity|Wind Speed (km/h)|Wind Bearing (degrees)|Visibility (km)|Loud Cover|Pressure (millibars)|
|-----------------------------|-------------|-----------|---------------|------------------------|--------|-----------------|----------------------|---------------|----------|--------------------|
|2006-04-01 00:00:00.000 +0200|Partly Cloudy|rain       |9.472222222    |7.388888889             |0.89    |14.1197          |251                   |15.8263        |0         |1015.13             |


### :wrench: Tools to use: 
1. Pycharm / VSCode
2. Pandas 
3. Kairos

### :mag: References:
* [How to query Kairos DB using Metrics](https://kairosdb.github.io/docs/restapi/QueryMetrics.html)


------------------------------------------------------

## :pushpin: Task 3: Working with MQTT & REDIS

### :golf: Areas covered:
- MQTT Protocol
- Caching using Redis DB

### :books: Description


In a theatre, Tickets are to be given to people in queue, There are 2 counters for Gold  and Silver class.

Gold Class has 10 rows with 30 seats per row, Silver class has 5 rows with 25 seats per row
	
Store the information of booking, Maximum 5 tickets allowed in a booking.
	

- Publish message to a MQTT topic

- Subscribe message, Assign seats per requirement and store information to Redis db.

sample format: 
```json
  {
	"mobile_no" : "9876543210",
	"preferred_class" :"Gold/Silver",
	"no_of_tickets" : 5
	}
```
	   
Use Redis for caching/storing information.

Create consumer's which consumes data from these topics and store to a Redis db.

### :wrench: Tools to use: 
1. Pycharm / VSCode
2. MQTT - (PIP package: `paho-mqtt`)
3. REDIS - (PIP package: `redis`)

### :mag: References:
* [Using MQTT in Python](https://www.emqx.com/en/blog/how-to-use-mqtt-in-python)
* [Connection to Redis in Python](https://docs.redis.com/latest/rs/references/client_references/client_python/)



