About Carlos Ferreira

How to install Keras and Theano in Anaconda Python on Windows

Install Anaconda

Is the leading open data science platform and is the recommended tool for use with Theano.  It is a free, easy to install python distribution and package manager that has a collection of over 720 open source package.  You can find a cheat sheet at the following location: Anaconda cheat sheet

  • Install Anaconda x64.
  • Open the Anaconda prompt

Conda is the Anaconda command line for managing packages.  We will be using this to install all the python related packages required. Before doing this we must ensure we have the most up to date version of Conda.  To do this type the following in the Anaconda prompt:

Next, we update Anaconda and all installed packages. To do this type the following in the Anaconda prompt:

Next, we install the packages required for Theano. To do this type the following in the Anaconda prompt:

Install Theano

Theano is a Python library that allows you to define, optimize, and evaluate mathematical expressions involving multi-dimensional arrays efficiently. It can use GPUs and perform efficient symbolic differentiation.  To install the latest version of Theano type in the following in the Anaconda prompt:

Install Keras

Keras is a high-level deep learning library written in Python which runs on top of either  TensorFlowCNTK, or Theano.  It was developed so that developers can easily create convolutional and/or recurrent networks.  To install the latest version of Keras, type in the following in the Anaconda prompt:

 

Equivalent of Oracle PL/SQL TRIM function in SQL Server (T-SQL)

In PL/SQL, when the TRIM function is called with just the data parameter it will strip the leading and trailing spaces.   You can accomplish the same functionality in t-sql by using two t-sql functions:  LTRIM (strips leading spaces) and RTRIM (strips trailing spaces).  An example of accomplishing this would be the following:

One thing to note is that TRIM with the LEADING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the LTRIM function in t-sql;  TRIM with the TRAILING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the RTRIM function in t-sql.

You can also create a user defined function for TRIM in your database.  The following listing below will do that for you.

After running the above script you could test it by running the following:

References

 PL/SQL TRIM

TSQL LTRIM

TSQL RTRIM

Writing CSV files using CSVHelper package (C#, IEnumerable)

This is a follow-up on my post on how to read a CSV file using Josh Close’s CsvHelper.  CsvHelper is a fast and flexible .NET library for reading and writing CSV files.

Below I will now show how to write CSV files using CsvHelper.  The example will actually be reading a CSV file and then writing the contents of that CSV into another but doing it three times.  Each time we do this we will use another method (WriteRecords for all records at once, WriteRecord for writing one record, and WriteField for writing one field).

Our sample input CSV file will look like the following.

CommonName,FormalName,TelephoneCode,CountryCode

Argentina,Argentine Republic,54,ARG
Armenia,Republic of Armenia,374,ARM
Australia,Commonwealth of Australia,61,AUS
Austria,Republic of Austria,43,AUT
“Bahamas, The”,Commonwealth of The Bahamas,-241,BHS
Bangladesh,People’s Republic of Bangladesh,880,BGD

Note this CSV file is a simpler file based on the file which is located here.

The first thing to do is to Install CsvHelper.  To do this run the following command in the Package Manager Console:

Now add CsvHelper to the program by adding:

The next step is to create a class which has properties with the same name of the column headings found in the csv file.  Below you will find an example of a class which does this:

Finally create an instance of CSVReader and CSVWriter  and invoke the GetRecords method using the DataRecord class to read the CSV file. 

Once we have the completed we are now ready to write the file out.  We first will write the entire file out by using the WriteRecords method.  Note this method will also normally write the header out automatically.

We then write the records out again but this time we will do so by iterating thru the records collection and writing the entire record using the WriteRecord method and also by writing the fields of the record using the WriteField method.

One point to note that if you were writing a file using the WriteRecord or WriteField methods, you will not get a header record in the file.   If you wish to have a header record us the WriteHeader method (example is commented out in final code).

A completed example can be found below:

 

You can download this sample code with CsvHelper and CSV file here:

Insert data into new (or existing table) from another (T-SQL, PL/SQL, SQL Server, Oracle)

Often times we wish to copy data from one table into another.  The methods of doing this where the new table does not exist will be slightly different from the case in which it does exist.  Also I will show display the method of doing this in T-SQL and PL/SQL (just because I get confused all the time when I switch databases Smile ).

SQL copy tables to new table

When performing this command, a table will created using the same data types as the columns which as selected in the select statement.  This is usually very useful when making a copy of the existing table before making any alterations to the data.

T-SQL:

PL/SQL:

 

SQL copy tables to existing table

These following when executed will insert data into already existing table.  It will match fields based on name if you choose to list the fields in the select clause.  This normally useful for when you need to reinsert data (don’t ask why… Smile)

T-SQL:

PL/SQL:

How to combine values from multiple rows of a single column (T-SQL, Microsoft SQL Server, FOR XML PATH, CSV ).

The Problem:

I need a way to roll-up multiple rows into one row and one column.  In essence I needed to convert something that looked like this:

Original SubCategories Lisitng

Original SubCategories Listing



Into something that looks like this:

SubCategories formatted data

SubCategories formatted data

The Solution:

The solution I will show will make use of the FOR XML PATH clause and STUFF commands in T-SQL.

FOR XML PATH Clause

Will will make use of the XML functionality of SQL Server to amalgamate the series of rows into one row.  In order to create a XML string you must append the FOR XML command after any regular query.   There are four options to append to the FOR XML PATH command which will alter what the result will look like.  These options are either RAW, AUTO, EXPLICIT, or PATH.  For this solution we will use the PATH option (will explain these four option in more detail in a later post).

The following query shows a regular query which retrieves the subcategory name in an XML string

The results look like the following:

XMLResult

 You will notice that you get a formatted XML string without a root node.  To convert this list to a CSV string we just need to append a comma (“,”)  to the name field.

The following sql query demonstrates the XML string being converted to a CSV string:

CSV Result

CSV Result

This is getting a bit closer to the results we want, but we if you haven’t noticed the string begins with a comma which we should remove.  In order to do this we will use the t-sql STUFF function

STUFF() Function

The syntax of the t-sql STUFF function looks like the following:

To remove the first character of the string all we need to so is do something like the following:

This says, starting with the first 1 character place, replace 1 characters with nothing ''.

What does the final solution look like?

SubCategories formatted data
SubCategories formatted data

 References

 

Run Transact-SQL Script Files Using sqlcmd–SQL Server T-SQL

Problem:

I recently received a script which creates a database and loads data.  I was unfortunately too large for SQL Server Management Studio to process.

Solution:

You can use the sqlcmd utility to run the script file. The example below shows the execution of a script file called script.sql which is located on the C drive and a connection is made to the SQL Server using integrated security.

To run the script file

  1. Open a windows command prompt window.
  2. In the command prompt window, type: sqlcmd –S SQLServerName-i C:\DatabaseLoad.sql
  3. Press ENTER.

How to delete duplicate records from your SQL Server tables – T-SQL

Below you will find two methods that can be used to remove duplicate records from you Microsoft SQL Server tables.

Method 1:

This method will only work in SQL Server 2005 and later; if you need to remove duplicates from an earlier version please upgrade you SQL Server version.  Just kidding!  Use the technique in method 2.  Actually you really should upgrade your version of SQL Server…

This method uses the Common Table Expression functionality and row_number function which was introduced in Microsoft SQL Server 2005.   The key strategy to this method is to generate a number next to the  records which denotes the row number.

So in essence we want to transform this:

Duplicated Records

Duplicated Records

To this:

DuplicatedResultWithNumber

Duplicated Results with Numbers

And remove all instances of those rows which the row number column do not correspond to 1.

Below is a script which can be used to demonstrate this.   Also you can use this link in SQL Fiddle.

Method 2:

This method should work on all versions for SQL Server.  It basically works by copying the unique rows out of the original table into a temporary table, and then deleting all records from the original table;  the unique rows from the temporary table are then copied into the original table.

Link to SQL Fiddle is here.

— Create Table

How to create a blocking transaction in SQL Server – T-SQL (exclusive lock)

Excuse the odd topic.   I often like to experiment with SQL Server and create odd situations which we normally should be avoiding.  One of those things is creating SQL Statement which get blocked and creating a timeout.  One of the simplest ways to run a query against a table which is locked in another in another session.  To do this do the following:

1) Create a lock on a table.  

The example below demonstrates a lock on the Person table in the AdventureWorks2012 sample table.    The key points which make this work is to create a transaction, and then issuing a query using the TABLOCKX and HOLDLOCK table lock hints.  This will force an exclusive lock on the table.  To keep the lock for significant amount of time we specify that the transaction stays alive for a minute by introducing the WAITFOR DELAY ’00:01:00′ command, which will prevent any further execution of statements until a minute has passed.

2)  Now open up another session in SQL Server (new query from Microsoft SQL Server Management Studio), and execute the following query:

You will notice that after this query is excuted, the query seems to run forever.  It will actually never  execute until the query in step 1 is finished.

3) Check for blocked transaction.  You can find this by opening up an new query in Microsoft SQL Server Management Studio and executing the following query:

I will explain in more detail how this blocking query works in a later post.

If you have any other tips on how to create a blocked transaction please share them in the comments below.

About Me

Reading CSV files using CSVHelper package (C#, IEnumerable)

I have come across many times the need to incorporate the reading of CSV files in an application.  Below I will show how to use Josh Close’s CsvHelper to  import a CSV file which looks like the following (file will be included below).

CommonName,FormalName,TelephoneCode,CountryCode
Argentina,Argentine Republic,54,ARG
Armenia,Republic of Armenia,374,ARM
Australia,Commonwealth of Australia,61,AUS
Austria,Republic of Austria,43,AUT
“Bahamas, The”,Commonwealth of The Bahamas,-241,BHS
Bangladesh,People’s Republic of Bangladesh,880,BGD

Note this CSV file is a simpler file based on the file which is located here.

The first thing to do is to Install CsvHelper.  To do this run the following command in the Package Manager Console:

Now add CsvHelper to the program by adding:

The next step is to create a class which has properties with the same name of the column headings found in the csv file.  Below you will find an example of a class which does this:

Finally create an instance of CSVReader and invoke the GetRecords method using the DataRecord class.  Below you will find an example of this:

An completed example can be found below:

You can download this sample code with  CsvHelper and CSV file here: CVSHelperReadSample

%d bloggers like this: