I’ve been working on an assignment for the past week in which I’ve needed to import datasets and analyze them with pandas. This also coincided with a large task at work. The assignment required a report to be written critically evaluating my results and visualizing them with MatPlotLib. The timing could not have been better…

Thankfully I started a week in advance but this still wasn’t sufficient as there were loads and loads of things to do. I've put together a list of things that I find useful to look through when I'm analyzing data-sets. it's a bit of a summary of what Series and Datasets are and some useful ways of interacting with them.

First off, let's talk about Series structures.

Series

These are structures that hold lists, basically akin to python lists except that you can associate an index with them which by default starts at 0 if it's not provided at initialization.

1. The Series type is an object:

s = Series()

2. You set the series' values/content when you instantiate it - this is like a normal python list so not very difficult to understand

s = Series([1,2,3,4,5]) # which sets the value of the series to that of a numbered list 

3. You can set the series' value to any list, that is it can hold numbers, strings, tuples etc.

s = Series(['string1', 'string2']) 

3. A series has an implicit index, corresponding to each value item in the series, starting from 0 for example 0='string1' 1='string2'
4. You can get access to the values of a series by indexing it using an index value, which is the same way you do it in python with its inbuilt lists. 

 s[0] 

5. However where it changes a bit is that the list's index can be more than just positional numbers, It can be strings and the numbers don't have to be in order. You can thus define what index refers to which value in the Series. This start making a series look more like a dictionary in python, where traditional dictionary keys refer to items.

 s = Series(['Stuart,'Mathews',12], index=['name', 'Surname', 'age']) 

6. You can fetch several values in a series by referring to several index values, you can't do this in python. You can only specify one positional index in a normal python list. So this is a big win over normal python lists.

s[['age','Surname']] returns values [12,'Mathews']

7. You can get the Series' current index

s.index 

8. By using the dictionary instead as a traditional list syntax as an initializer, you can specify each value and each index for that value during instantiation by 

s = Series({'index1':value, 'index2': value2}) 

9. You can filter a series' values, so this will return all values in the series where the value is greater than 2

s[ s > 2] 

10. You can join/append series and these operations will consult each other's matching index values.

series2+series3 # will add values where both series' index values are the same 

11. Those add/join operations that don’t match each others index values can ignore the mismatch using fill_value=0

s.add(s1, fill_value=0)

Data frames

These structures are where the big work gets done and they are composed of Series' such that they are akin to a dictionary where each key, is a column and each value of the column is the vertical series' values.

So where a Series is an upgraded python list which does useful things with additional indexing capabilities(among other things), a data frame can be seen as a composition of multiple series. Data frames give you a 2-D view of data, rows and columns.

Data frames always return a new data frame if you operate on an existing data frame. This is one of the tenants of functional programming.

You can lookup by key, in addition to indexing using an index
There an implicit key starting at 0 for each element in a series but the key is used to set data frame values

• You can set the values of a data frame at instantiation, you see we're basically looking at an initialization of a dictionary of lists, which will be turned into series' for this data frame.

df = DataFrame({'column1':[value1,value2,value3], 'column2': [val1,val2,val3]})

• You look up a data frame by its key, which is a view of the series at that key (if you treat a data frame as a dictionary)

# gives you the series of values for the key /column
df['key']

• You can also initialise a data frame by initializing using lists of tuples. Each tuple is a row, and the column parameter labels those rows positionally according to the position in the provided columns

df = DataFrame([(1,2,3),(4,5,6)], columns=['col1', 'col2', 'col3'])

• You can also refer to the value by key lookup but using attributes by the key value, such that df['col'] is the same as df.col

df.col1 # same as df['col']

• You can get multiple data frame values(series) by specifying multiple lookup key values, this is similar to the capability that series' have over traditional python lists

df[['column1', 'column2']] 

• You can reorder the key values(which are series') by creating a new data frame. Note how an operation creates a new Data frame instead of operating in-place on the existing data frame.

df = DataFrame(df, columns=['column2', 'column1']) 

• You can set specify a series by referring to its key when setting it via scalar assignment, which puts all its sequence to the provided value:

df['column1'] = 'unknown' 

• You can set a data frames series' by referring to its key when setting it, this is like the above but instead of using a scalar value, you specify a series.

df.column1 = Series([1,2,3,4,5,6]) 

• You can set the index for the data frame and this is where things get interesting... because now you have a cross-sectional way to refer to values in a data frame. This means you can hone in on a value by row and column. See the following key points:

df.set_index('key') # this adds a cross-section lookup along with a key to refer to a specific value/row/col combo 

• The data frame's traditional index is still the same, in as much as you'd index a dictionary. If you now set the index explicitly, it refers to the row and it can be used it look up data along with the data frame's dictionary key.

df['key]['index'] 

• You can filter out rows in a data frame (row selection) 

df[df.column1 > 2]  

• Get unique values in a series that represents a data frame's column/key

df['column1'].unique() or df['column1'].unique().tolist() 

Here are some useful operations when working with data frames and Series: 

• Each of the keys/columns can be associated with a datatype like in relational tables.

#shows all types by key
df.dtypes 

• Scalar assign a column to fill the value series with nan 

ss_df.num.fillna(0)) 

• Remove any rows with nan

ss_df.dropna() 

• Remove rows where specified column is nan

ss_df.dropna(subset=['key'])

•  Changing the data type of a column in a data frame

#Modifying a Series's datatype
coursedata_df.level = coursedata_df.level.astype(int)

# Modifying a list of series within the data frame to be a specific datatype
coursedata_df[ ['level', 'points'] ] = coursedata_df[ ['level', 'points'] ].astype(float)

• Renaming a series/column

splitaddresses_df.rename(columns = {0:'Country',1:'State',2:'City'}, inplace=True)

• Replacing all values in a column and using regex pattern matching

messynumbers_df['cleanvals'] = messynumbers_df.messyvals.str.replace(',', '')

messynumbers_df.replace({'cleanvals' : "^[^\d]*([\d]*)[^\d]*$"}, {'cleanvals' : r'\1'}, regex=True)

• Filter rows and then show only two columns

result_df = course_df[course_df['points']==30][['courseCode','level']]

• SELECT ROWS where column = c7

result_df = ABCD_df[ABCD_df['C']=='c7']

• Show first two rows

course_df.head(2)
print(course_df[0:2])

• Sort values by key

result_df = course_df.sort_values(by=['courseCode'])
result_df = course_df.sort_values(by=['courseCode'], ascending=False)
result_df = course_df.sort_values(by=['points', 'level'], ascending=[True, False]) # multiple sorts on key

Here are a  few advanced moves which I'll go into more detail in another article:

• Vertical joins/union
• union aligns same columns in union
  

  concat_diffcolumns_df = pd.concat([sample1_df, sample4_df]) 
  concat_inner_df = pd.concat([sample1_df, sample4_df], join='inner')​

• Inner join - horizontal join
  

  simplemerge_df = pd.merge(bldgSample_df, lettingsSample_df, on=['http://opendatacommunities.org/def/ontology/geography/refArea','Reference area'])​

• Left outer join - bring back all left columns
  

  pd.merge(bldgSample_df, lettingsSample_long_df, on=['http://opendatacommunities.org/def/ontology/geography/refArea','Reference area'], how='left')​

• Right outer join
  

  pd.merge(bldgSample_df, lettingsSample_long_df, on=['http://opendatacommunities.org/def/ontology/geography/refArea','Reference area'], how='right')​

• Full outer join
  

  pd.merge(bldgSample_df, lettingsSample_long_df, on=['http://opendatacommunities.org/def/ontology/geography/refArea','Reference area'], how='outer')​

• Expand Directorate to all its possible values in its column, the sum of each unique value in Capital or Revenue value for each directorate
  

  pd.crosstab(df['Capital or Revenue'], df['Directorate'])​

• Group by the values of Directorate, and sum the values in their groups
  

  df.pivot_table(index=['Directorate'], aggfunc=np.sum)​

So as you can see there is a lot going on in the world of data analysis in Python.

I’ve had a pretty busy couple of weeks recently and haven’t had much time to do some things that I’d wanted to continue with. However this weekend I had some time to think a little bit more about my investment service that I’ve been writing.

Previously, I’d successfully deployed my .Net Core 2 Web API into Amazon ECS using Travis-CI. It automatically builds the Docker container, and then create the AWS service, a Task Definition and finally the ECS cluster. What I still need to do is create a Load balancer to manage this.

What I wanted to do is rationalise some of the ideas/concepts that I'd previously taken for granted in the persuit of just-getting-it-done. It also gave me a bit of time to understand more about ECS’s infrastructure and how it works, as I'll be doing more work in this area in the coming weeks. 

My initial thought was to effectively describe what a task definition was as this concept is a bit murky and to do this, I need to show you how it fits into the broader ecosystem.

Here is a diagram I drew to explain much of the fundamental concepts of ECS:

Basically when managing an application that deployed as a docker container, you need to understand how your container is organised by ECS.

Fundamentally, managing a Docker image starts with creating a ‘Service’ that is responsible for managing the infrastructure for your Docker image. Each Service will obviously then be associated with your Docker Image. To make this association, you define a Task Definition which represents which Docker image to use and which repository its hosted/located in - so it knows where to fetch it from. Then, and this is the important part, your task definition can be realised or instantiated (as a Task) onto running EC2 AMI container host instances thereby effectively running your image in EC2 host containers (EC2 instance in diagram).

The part I’ve left off, is that you also define a ECS cluster which is just how many EC2 container hosts are available for tasks to run on. These are ECS optimised AMIs provided by Amazon.

The Service will automatically run the tasks on available hosts in the cluster. Normally when you initially create the service, you specify how many tasks must be always running at the same time and the service will ensure that that many tasks are created across all the EC2 instances associated with that service(through the association the service has with a cluster)

What I still need to do is deploy my Angular front end app in the same way. This is what I’m going to be doing in the days that follow.

To give you a bit of an idea how this is programatically achieved in Travis-CI, this is what setups the AWS stuff prior to deploying this to ECS:

Firstly this is my docker file:

#Image(build) that is used to compile/publish ASP.NET Core applications inside the container. 
FROM microsoft/aspnetcore-build:2.0 AS build-env
WORKDIR /app

#Copy BUILD_DIR\*csproj and restore as distinct layers
COPY *.csproj ./
RUN dotnet restore

# Copy everything else and build
COPY . ./
RUN dotnet publish -c Release -o out

# Build runtime image by adding the compiled output above to a runtime image(aspnetcore)

FROM microsoft/aspnetcore:2.0
WORKDIR /app
COPY --from=build-env /app/out .

# Expose port 5000 on container to the world outside (container host)
EXPOSE 5000/tcp

# Ask Kestral to listen on 5000
ENV ASPNETCORE_URLS http://*:5000
ENTRYPOINT ["dotnet", "CoreInvestmentTracker.dll"]

And this is how it gets deployed by Travis-CI:

First setup some environment variables:

#!/bin/bash

# set environment variables used in deploy.sh and AWS task-definition.json:
export IMAGE_NAME=coreinvestmenttracker
export IMAGE_VERSION=latest

export AWS_DEFAULT_REGION=eu-west-2
export AWS_ECS_CLUSTER_NAME=default


# set any sensitive information in travis-ci encrypted project settings:
# required: AWS_ACCOUNT_NUMBER, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY
# optional: SERVICESTACK_LICENSE

First, build the docker file etc.

#!/bin/bash
source ../deploy-envs.sh

#AWS_ACCOUNT_NUMBER={} set in private variable
export AWS_ECS_REPO_DOMAIN=\(AWS_ACCOUNT_NUMBER.dkr.ecr.\)AWS_DEFAULT_REGION.amazonaws.com

# Build process
docker build -t \(IMAGE_NAME ../
docker tag \)IMAGE_NAME \(AWS_ECS_REPO_DOMAIN/\)IMAGE_NAME:\(IMAGE_VERSION

and finally setup the AWS ECS infrastructure:

#!/bin/bash
source ../deploy-envs.sh

export AWS_ECS_REPO_DOMAIN=\)AWS_ACCOUNT_NUMBER.dkr.ecr.\(AWS_DEFAULT_REGION.amazonaws.com
export ECS_SERVICE=\)IMAGE_NAME-service
export ECS_TASK=\(IMAGE_NAME-task

# install dependencies
sudo apt-get install jq -y #install jq for json parsing
sudo apt-get install gettext -y 
pip install --user awscli # install aws cli w/o sudo
export PATH=\)PATH:\(HOME/.local/bin # put aws in the path

# replace environment variables in task-definition
envsubst < task-definition.json > new-task-definition.json

eval \)(aws ecr get-login --region \(AWS_DEFAULT_REGION --no-include-email | sed 's|https://||') #needs AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY envvars

## Check to see if the repository already existsm otherwise create it
if [ \)(aws ecr describe-repositories | jq --arg x \(IMAGE_NAME '[.repositories[] | .repositoryName == \)x] | any') == "true" ]; then
    echo "Found ECS Repository \(IMAGE_NAME"
else
    echo "ECS Repository doesn't exist, Creating \)IMAGE_NAME ..."
    aws ecr create-repository --repository-name \(IMAGE_NAME
fi

# Push the image to the repository
docker push \)AWS_ECS_REPO_DOMAIN/\(IMAGE_NAME:\)IMAGE_VERSION

 # Create a new task revision
aws ecs register-task-definition --cli-input-json file://new-task-definition.json --region \(AWS_DEFAULT_REGION > /dev/null
 #get latest revision
TASK_REVISION=\)(aws ecs describe-task-definition --task-definition \(ECS_TASK --region \)AWS_DEFAULT_REGION | jq '.taskDefinition.revision')
SERVICE_ARN="arn:aws:ecs:\(AWS_DEFAULT_REGION:\)AWS_ACCOUNT_NUMBER:service/\(ECS_SERVICE"
ECS_SERVICE_EXISTS=\)(aws ecs list-services --region \(AWS_DEFAULT_REGION --cluster \)AWS_ECS_CLUSTER_NAME | jq '.serviceArns' | jq 'contains(["'"\(SERVICE_ARN"'"])')
if [ "\)ECS_SERVICE_EXISTS" == "true" ]; then
    echo "ECS Service already exists, Updating \(ECS_SERVICE ..."
    aws ecs update-service --cluster \)AWS_ECS_CLUSTER_NAME --service \(ECS_SERVICE --task-definition "\)ECS_TASK:\(TASK_REVISION" --desired-count 1 --region \)AWS_DEFAULT_REGION > /dev/null #update service with latest task revision
else
    echo "Creating ECS Service \(ECS_SERVICE ..."
    aws ecs create-service --cluster \)AWS_ECS_CLUSTER_NAME --service-name \(ECS_SERVICE --task-definition "\)ECS_TASK:\(TASK_REVISION" --desired-count 1 --region \)AWS_DEFAULT_REGION > /dev/null #create service
fi
if [ "\((aws ecs list-tasks --service-name \)ECS_SERVICE --region \(AWS_DEFAULT_REGION | jq '.taskArns' | jq 'length')" -gt "0" ]; then
    TEMP_ARN=\)(aws ecs list-tasks --service-name \(ECS_SERVICE --region \)AWS_DEFAULT_REGION | jq '.taskArns[0]') # Get current running task ARN
    TASK_ARN="\({TEMP_ARN%\"}" # strip double quotes
    TASK_ARN="\){TASK_ARN#\"}" # strip double quotes
    aws ecs stop-task --task \(TASK_ARN --region \)AWS_DEFAULT_REGION > /dev/null # Stop current task to force start of new task revision with new image
fi

 

 

One very important thing about a Task definition, other than defining which docker image to use, is that you can define the environment variables that the docker image will see when its running(as a Task!). This is very important for me because I define the RDS connection string information in here, which includes passwords etc. This task definition although I define it in the source code, does not have passwords in it, but I update it by updating it's revision and then apply the new task definition to the service and that is then applied. Then then runs this runs tasks using this new revision.

Hopefully I'll be able to get the system setup in such a way I can setup and tear down the system quickly to avoid long running costs while developing. I've read alittle about AWS data pipelines as a way to achieve this so I'll look into that later. In the mean time, its slowly coming together.