Voice of the DBA

Tag: python

Should You Learn R or Python?

I’ve been working on various skills over the last few years, trying to solve some simple problems in Python and PowerShell, in addition to T-SQL, to continue to improve my skills. It’s an interesting challenge at times, especially when I need to use new features or functions to which I haven’t had exposure in the past.

I also work with R lightly, as I need to build some Questions of the Day for SQLServerCentral and I try to alternate Python and R questions every week. This has caused me to dig in and try to learn more about the language and how to manipulate data.

Recently I was reading an essay from a consultant that works with clients using both R and Python. The piece talks about the differences and how these work to solve business problems. If you don’t want to read the entire thing, the comparison starts with the simple “you need both”, though there is more to the story.

The most interesting part of this for me was that the author notes that while these are good languages in different ways for data analysis, they aren’t great for data preparation and SQL is still required. Either a database like SQL Server or a platform like Apache Spark. Part of the reason is that R and Python aren’t very efficient, and as we work with more data and larger workloads, efficiency matters.

The other part of the piece I liked was the note that we need to collaborate and our work needs to be reproducible for others. I love having git for moving code around and keeping configuration files in a repository of some sort. It has certainly helped me take advantage of bits that others have written and easily reproduce their work on my system.

While some of us work with just SQL, I expect that we will get involved with other parts of projects and may need to help troubleshoot or improve code. I find both of these languages interesting and a nice complement to each other. I’ve also learned there are places where I much prefer one over the other, especially with some of the Advent of Code problems. Some are simple in SQL, but others are much more suited to Python. I haven’t tried them in R, but I bet some of them would be well suited to that environment.

If you have tried either, or have a preference, let us know. What are the advantages or disadvantages of each when you are working in a business?

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

May 21, 2021
2020 Advent of Code–Day 4
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 4. I’m going to do this one in Python here, though I did solve it in other languages in my repo.

Part 1

We have another string parsing operation. We get a series of lines that represent a passport. Passports are separated by blank lines. Therefore, we can get 1-x number of lines representing a passport.

Ugh.

Python seems like a good place to start here. I loaded the file and then started to concatenate rows of data until I found a blank one.
```
file_handle = open('2020\day4\day4_data.txt', 'r')
passports = file_handle.readlines()
part1 = 0
currpassport = ""
for row in passports:
if row not in ['\n','\r\n']:
        currpassport += row.replace('\n',' ')
#print(currpassport.split(" "))else:
```
At this point, I have a passport I can look at, with all the various sections. I used another split, this time into a dictionary to get each item separate.
```
currdict = dict(x.split(":") for x in currpassport.split(" ") if x)
```
Now, I can count these. If there are 8, or if there are 7 and CID is one of them, I have a valid passport. Adding these up gets me the answer.

Part 2

This is very similar, but each part now needs validation. So, I take the same structure, but once I have passports, I assume they are valid and start to check each section. It’s really a series of IF statements for me.
```
            valid = 1
if ((int(currdict["iyr"]) < 2010) or (int(currdict["iyr"]) > 2020)):
                valid = 0
if int(currdict["byr"]) < 1920 or int(currdict["byr"]) > 2002 :
                valid = 0
if int(currdict["eyr"]) < 2020 or int(currdict["iyr"]) > 2030:
                valid = 0
```
These each could be functions, and I’d refactor that way, but I couldn’t come up with an easier way to do this. After checking if I have enough valid items, I tally another passport (or not).

Overall, this felt like busy work, not hard, but just a grind through each set of validation.
April 28, 2021
2020 Advent of Code – Day 4
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 4.

Part 1

This is an interesting data set. It’s ugly, inconsistent, and spans across lines. In fact, to determine what any particular “row” is, you need to read line by line and process the data until you find a blank line. An SSIS or ADF exercise indeed.

However, I decided to try this with Python first. I thought the loading and splitting of items on consecutive lines would be easier. I opened the file and then scanned it for values like this:
```
for row in passports:
if row not in ['\n','\r\n']:
        currpassport += row.replace('\n',' ')
```
This let me look for a blank row. If I didn’t find one, I added the row to my current passport value. If I did have a blank value, I split the row into a dictionary:
```
currdict = dict(x.split(":") for x in currpassport.split(" ") if x)
```
From here, I could check the length being either 8 entries, or 7 entries if the cid was not present. I counted all these up to get to the answer.

Part II

This was annoying. Validating each one of the entries based on years or a set of values. I knew I could build a number of validation functions, which is the better way. I ended up just using a series of IF statements to check values and set a validation variable. A sample of them is here:
```
if currdict["hgt"][-2:]=="cm" and ( int(currdict["hgt"][:-2]) < 150 or int(currdict["hgt"][:-2]) > 193):
                valid = 0
if currdict["hgt"][-2:]=="in" and ( int(currdict["hgt"][:-2]) < 59 or int(currdict["hgt"][:-2]) > 76):
                valid = 0
if currdict["ecl"] not in ["amb", "blu", "brn", "gry", "grn", "hzl", "oth"]:
                valid = 0
if currdict["hcl"][0] != "#" or len(currdict["hcl"]) != 7:                valid = 0
```
This let me tally up the valid passports.

I still need to work on these in PoSh and SQL, but life has gotten in the way of things outside of work.
April 16, 2021
T-SQL Tuesday #137–Notebook Uses

It’s actually my month to host T-SQL Tuesday, and I came up with the notebook idea last year. Aaron Nelson (b | t) sent me a link to a talk he did at a Meetup. It contained some interesting things, showing how you can use notebooks. Worth a watch.

For me, I don’t use notebooks a lot in my work. I had thought about using them to show clients and customers how to use our products, but I hadn’t moved far in that direction. I think too many customers are still using SSMS primarily, and haven’t moved to ADS.

The one place I’ve found them interesting is with Python. When I am sometimes trying to work through an issue, I think it’s easier to run a cell of code and get some results to work through an issue. If I use the REPL, I lose things. In VS Code, which I prefer, I don’t like the split code/terminal. I find that slightly annoying. Notebooks make things easier.

I was doing this recently, as I tried to work on the Advent of Code. I tried different parts of an algorithm in different cells, just to see what the results were. This helped me to work out some logic.

That’s not a great algorithm, and I got better over time, but this was really a good way for me to think about the sections of the problem, putting each on in a separate area.

It’s a little of “teaching myself” within the construct of a notebook. After all, that’s what a lot of data scientists are doing when they start to go through different code items in a notebook. They can share the notebook with others, which helps to teach.

For me, it’s teaching myself.

April 13, 2021