Shantanu's Blog

Here is 7 steps process to load data from any csv file into Amazon DynamoDB.

1) Create the pandas dataframe from the source data
2) Clean-up the data, change column types to strings to be on safer side :)
3) Convert dataframe to list of dictionaries (JSON) that can be consumed by any no-sql database
4) Connect to DynamoDB using boto
5) Connect to the DynamoDB table
6) Load the JSON object created in the step 3 using put_item method
7) Test

# Create the pandas dataframe from the source data

import pandas as pd
import boto3

df=pd.read_excel('http://www.tvmmumbai.in/Alumini%20Std.X-2013-2014.xls')

df.columns=["srno", "seat_no", "surname", "name", "father_name", "mother_name", "english", "marathi", "hindi", "sanskrit", "maths", "science","ss","best_of_5", "percent_best_of_5" , "total_out_of_6", "percent_of_600"]

# Clean-up the data, change column types to strings to be on safer side :)

df=df.replace({'-': '0'}, regex=True)
df=df.fillna(0)

for i in df.columns:
    df[i] = df[i].astype(str)

# Convert dataframe to list of dictionaries (JSON) that can be consumed by any no-sql database

myl=df.T.to_dict().values()

# Connect to DynamoDB using boto

MY_ACCESS_KEY_ID = 'XXX'
MY_SECRET_ACCESS_KEY = 'XXX'

resource = boto3.resource('dynamodb', aws_access_key_id=MY_ACCESS_KEY_ID, aws_secret_access_key=MY_SECRET_ACCESS_KEY, region_name='us-east-1')

# Connect to the DynamoDB table

table = resource.Table('marks1')

# Load the JSON object created in the step 3 using put_item method

for student in myl:
    table.put_item(Item=student)

# Test
response = table.get_item(Key={'seat_no': 'A 314216'})
response

#### dataframe metadata
# show create table df
df.dtypes

# select count(*) from df
df.shape

#### queries
# select * from df where senderid = 'CANB'
df[df['senderid'] == 'CANB']

# select * from df where deliver_date < '2015-07-02 00:00:00'
df=df[df['deliver_date'] < '2015-07-02 00:00:00']

# select deliver_date, count(*) from df group by deliver_date
df.deliver_date.value_counts()

# select messageid, deliver_date from df group by messageid, deliver_date
df=df.drop_duplicates(['messageid', 'deliver_date'])

#### indexing in dataframe
# select date(deliver_date), count(*) from df group by date(deliver_date)
# create an index on deliver_date and then resample on "Date"
df.index = df.deliver_date
df.resample("D", how='count')

# Remove index completely assuming deliver_date column is there in dataframe
myj=df.reset_index(drop=True)

# index column can also be used for query
# select * from df where deliver_date = '2015-07-02 00:00:00'
df[df.index == '2015-07-02 00:00:00']

#### merge 2 text columns into date column

df.senddate = pd.to_datetime(df.senddate)

df['sdate'] = pd.to_timedelta(pd.to_datetime(df.senddate1, format='%Y%m%d%H%M%S', errors='ignore'), unit='ns') + pd.to_datetime(df.senddate)

df.drop(['senddate', 'senddate1' ], axis=1, inplace=True)

# http://stackoverflow.com/questions/42003177/merge-text-into-datetime-column

1) Create a table in dynamoDB with the name "ssc" and use field "seat_no" as a string primary key.
2) Add the records for the subjects by creating fields "English", "Maths" and adding marks as string "54", "32". The primary key can be something like "B54MH".

3) Create python function in Lambda as shown below:

import boto3
import json

client = boto3.resource('dynamodb')
table = client.Table('ssc')

def lambda_handler(event, context):
    item = {'seat_no': (event['seatno'])}
    r=table.get_item(Key=item)  
    return json.dumps(r)

4) Create an API
Link the above lambda function name for e.g. "ssc" to the API gateway.
It is important to correctly specify the mapping for API get method execution request:

{"seatno": "$input.params('seatno')"}
_____

Once deployed, the URL will look something like this...

https://9xd7zbdqjg.execute-api.us-east-1.amazonaws.com/S1?seatno=B54MH

Note we are using secure server https to connect. This makes it possible to consume the API results directly into an application like slack.

It is also possible to include cutom authorizer using the steps outlined here...

http://docs.aws.amazon.com/apigateway/latest/developerguide/use-custom-authorizer.html