Scott Robinson

Federico Trotta

Data Integrity: How OrderedDict Preserves Key Order in Python

Jakub Dąbkowski

Running Airflow Locally with Docker: A Technical Guide

Shri Varsheni

Guide to Fine-Tuning Open Source LLM Models on Custom Data

<h2 id="theroleofgpusindeeplearning">The Role of GPUs in Deep Learning</h2>
<p>GPUs, or Graphics Processing Units, are important pieces of hardware originally designed for rendering computer graphics, primarily for games and movies. However, in recent years, GPUs have gained recognition for significantly enhancing the speed of computational processes involving neural networks.</p>
<p>GPUs now play a pivotal role in the artificial intelligence revolution, predominantly driving rapid advancements in deep learning, computer vision, and large language models, among others.</p>
<p>In this article, we will delve into the utilization of GPUs to expedite neural network training using PyTorch, one of the most widely used deep learning libraries.</p>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note</strong>: An NVIDIA GPU-equipped machine is required to follow the instructions in this article.</p>

                    </div>
                </div>
            </div>
            <h2 id="inroductiontogpuswithpytorch">Inroduction to GPUs with PyTorch</h2>
<p>PyTorch is an open-source, simple, and powerful machine-learning framework based on Python. It is used to develop and train neural networks by performing tensor computations like automatic differentiation using the Graphics Processing Units.</p>
<p>PyTorch employs the CUDA library to configure and leverage NVIDIA GPUs. <code>CUDA</code> is a GPU computing toolkit developed by Nvidia, designed to expedite compute-intensive operations by parallelizing them across multiple GPUs. PyTorch offers support for CUDA through the <code>torch.cuda</code> library.</p>
<h2 id="utilisinggpusintorchviathecudapackage">Utilising GPUs in Torch via the CUDA Package</h2>
<p>The CUDA library in PyTorch is instrumental in detecting, activating, and harnessing the power of GPUs. Let's delve into some functionalities using PyTorch.</p>
<h3 id="verifyinggpuavailability">Verifying GPU Availability</h3>
<p>Before using the GPUs, we can check if they are configured and ready to use. The following code returns a boolean indicating whether GPU is configured and available for use on the machine.</p>
<pre><code class="hljs"><span class="hljs-keyword">import</span> torch
<span class="hljs-built_in">print</span>(torch.cuda.is_available())
</code></pre>
<pre><code class="hljs"><span class="hljs-literal">True</span>
</code></pre>
<p>The number of GPUs present on the machine and the device in use can be identified as follows:</p>
<pre><code class="hljs"><span class="hljs-built_in">print</span>(torch.cuda.device_count())
<span class="hljs-built_in">print</span>(torch.cuda.current_device())
</code></pre>
<pre><code class="hljs">1
0
</code></pre>
<p>This output indicates that there is a single GPU available, and it is identified by the device number 0.</p>
<h3 id="initializethedevice">Initialize the Device</h3>
<p>The active device can be initialized and stored in a variable for future use, such as loading models and tensors into it. This step is necessary if GPUs are available because CPUs are automatically detected and configured by PyTorch.</p>
<p>The <code>torch.device</code> function can be used to select the device.</p>
<pre><code class="hljs"><span class="hljs-meta">&gt;&gt;&gt; </span>device = torch.device(<span class="hljs-string">&quot;cuda&quot;</span> <span class="hljs-keyword">if</span> torch.cuda.is_available() <span class="hljs-keyword">else</span> <span class="hljs-string">&quot;cpu&quot;</span>)
<span class="hljs-meta">&gt;&gt;&gt; </span>device
</code></pre>
<pre><code class="hljs">device(type=&#x27;cuda&#x27;)
</code></pre>
<p>With the device variable, we can now create and move tensors into it.</p>
<h3 id="creatingandmovingtensorstothegpu">Creating and Moving tensors to the GPU</h3>
<p>The models and datasets are represented as PyTorch tensors, which must be initialized on, or transferred to, the GPU prior to training the model. This can be accomplished in several ways, as outlined below:</p>
<ol>
<li><strong>Creating Tensors Directly on the GPU</strong></li>
</ol>
<p>Tensors can be directly created on the desired device, such as the GPU, by specifying the <code>device</code> parameter. By default, tensors are created on the CPU. You can determine the device where the tensor is stored by accessing the <code>device</code> parameter of the tensor.</p>
<pre><code class="hljs">x = torch.tensor([<span class="hljs-number">1</span>, <span class="hljs-number">2</span>, <span class="hljs-number">3</span>])
<span class="hljs-built_in">print</span>(x)
<span class="hljs-built_in">print</span>(<span class="hljs-string">&quot;Device: &quot;</span>, x.device)
</code></pre>
<pre><code class="hljs">tensor([1, 2, 3]) 
Device: cpu
</code></pre>
<p>Now, let's generate the tensors directly on the device.</p>
<pre><code class="hljs">y = torch.tensor([<span class="hljs-number">4</span>, <span class="hljs-number">5</span>, <span class="hljs-number">6</span>], device=device)
<span class="hljs-built_in">print</span>(y)
<span class="hljs-built_in">print</span>(<span class="hljs-string">&quot;Device: &quot;</span>, y.device)
</code></pre>
<pre><code class="hljs">tensor([4, 5, 6], device=&#x27;cuda:0&#x27;) 
Device: cuda:0
</code></pre>
<p>Lastly, the device number where the tensors are stored can be retrieved using the <code>get_device()</code> method.</p>
<pre><code class="hljs"><span class="hljs-built_in">print</span>(x.get_device())
<span class="hljs-built_in">print</span>(y.get_device())
</code></pre>
<pre><code class="hljs">-1
0
</code></pre>
<p>In the output above, <code>-1</code> represents the CPU, while <code>0</code> represents GPU number 0.</p>
<ol start="3">
<li><strong>Transferring Tensors Using the <code>to()</code> Method</strong></li>
</ol>
<p>Tensors can be transferred from the CPU to the device using the <code>to()</code> method, which is supported by PyTorch tensors.</p>
<pre><code class="hljs">x = torch.tensor([<span class="hljs-number">1</span>, <span class="hljs-number">2</span>, <span class="hljs-number">3</span>])
x = x.to(device)
<span class="hljs-built_in">print</span>(<span class="hljs-string">&quot;Device: &quot;</span>, x.device)
<span class="hljs-built_in">print</span>(x.get_device())
</code></pre>
<pre><code class="hljs">Device: cuda:0
0
</code></pre>
<p>When multiple GPUs are available, tensors can be transferred to specific GPUs by passing the device number as a parameter.</p>
<p>For instance, <code>cuda:0</code> is for the first GPU, <code>cuda:1</code> for the second GPU, and so on.</p>
<pre><code class="hljs"><span class="hljs-comment"># Transfer to the first GPU</span>
x = torch.tensor([<span class="hljs-number">8</span>, <span class="hljs-number">9</span>, <span class="hljs-number">10</span>])
x = x.to(<span class="hljs-string">&quot;cuda:0&quot;</span>)
<span class="hljs-built_in">print</span>(x.device)
</code></pre>
<pre><code class="hljs">cuda:0
</code></pre>
<p>Attempting to transfer to a GPU that is not available or to an incorrect GPU number will result in a <code>CUDA error</code>.</p>
<ol start="4">
<li><strong>Transferring Tensors Using the <code>cuda()</code> Method</strong></li>
</ol>
<p>Below is an example of creating a sample tensor and transferring it to the GPU using the <code>cuda()</code> method, which is supported by PyTorch tensors.</p>
<pre><code class="hljs"><span class="hljs-comment"># Create a random tensor of shape (100, 30)</span>
tensor = torch.rand((<span class="hljs-number">100</span>, <span class="hljs-number">30</span>)) 
tensor = tensor.cuda()
<span class="hljs-built_in">print</span>(tensor.device)
</code></pre>
<pre><code class="hljs">device(type=&#x27;cuda&#x27;, index=0)
</code></pre>
<p>Now let's explore techniques to load the tensors into multiple GPUs through parallelisation i.e. one of the most important features responsible for high computational speeds in GPUs.</p>
<h2 id="multigpudistributedtraining">Multi-GPU Distributed Training</h2>
<p>Distributed training involves deploying both the model and the dataset across multiple GPUs, thereby dramatically accelerating the training process via the capability of parallelization. We will cover some of the distributed training classes offered by PyTorch in the following sections.</p>
<p><img src="https://s3.stackabuse.com/media/articles/how-to-use-the-gpu-with-pytorch-1.png" alt="CPUs vs GPUs for intense computational activities"><br>
<small>Source: <a href="https://blogs.nvidia.com/blog/2009/12/16/whats-the-difference-between-a-cpu-and-a-gpu/" target="_blank" rel="nofollow noopener noreferrer">NVIDIA</a></small></p>
<h3 id="dataparallel"><em>DataParallel</em></h3>
<p><code>DataParallel</code> is an effective way for conducting multi-GPU training of models on a single machine. It achieves data parallelization at the module level by dividing the input across the designated devices via chunking, and then propagating it through the model by replicating the inputs on all devices.</p>
<p>Let's create and initialise a basic <code>LinearRegression</code> model class prior to wrapping it within the <code>DataParallel</code> class.</p>
<pre><code class="hljs"><span class="hljs-keyword">import</span> torch.nn <span class="hljs-keyword">as</span> nn

<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">LinearRegression</span>(<span class="hljs-params">nn.Module</span>):</span>
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">__init__</span>(<span class="hljs-params">self, input_size, output_size</span>):</span>
        <span class="hljs-built_in">super</span>(LinearRegression, self).__init__()
        self.linear = nn.Linear(input_size, output_size)

    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">forward</span>(<span class="hljs-params">self, x</span>):</span>
        <span class="hljs-keyword">return</span> self.linear(x)

<span class="hljs-comment"># Initialize the model</span>
model = LinearRegression(<span class="hljs-number">2</span>, <span class="hljs-number">5</span>)
<span class="hljs-built_in">print</span>(model)
</code></pre>
<pre><code class="hljs">LinearRegression( 
    (linear): Linear(in_features=2, out_features=5, bias=True) 
)
</code></pre>
<p>Now, let's wrap the model to execute data parallelization across multiple GPUs. This can be achieved by utilizing the <code>nn.DataParallel</code> class and passing the model along with the device list as parameters.</p>
<pre><code class="hljs">model = nn.DataParallel(model, device_ids=[<span class="hljs-number">0</span>])
<span class="hljs-built_in">print</span>(model)
</code></pre>
<pre><code class="hljs">DataParallel( 
    (module): LinearRegression( 
        (linear): Linear(in_features=2, out_features=5, bias=True) 
    ) 
)
</code></pre>
<p>In the above code, we have passed the model along with the list of device ids as parameters. Now we can proceed by directly loading the model on to device and perform model training as required.</p>
<pre><code class="hljs"><span class="hljs-comment"># Move the model and inputs to GPU</span>
model = model.to(device) 
input_data = input_data.to(device)

<span class="hljs-comment"># Continue with Training Loop</span>
<span class="hljs-comment"># ...</span>
</code></pre>
<h3 id="distributeddataparallelddp">DistributedDataParallel (DDP)</h3>
<p>The <code>DistributedDataParallel</code> class from PyTorch supports training across multiple GPU training on multiple machines. The <code>DistributedDataParallel</code> class is recommended over the <code>DataParallel</code> class, as it manages single machine scenarios by default and exhibits superior speed compared to the <code>DataParallel</code> wrapper.</p>
<p>The <code>DistributedDataParallel</code> module operates on the principle of data parallelism. Here, the model and data are duplicated across multiple processes, and each process conducts training on a data subset.</p>
<p>Setting up the <code>DistributedDataParallel</code> class entails initializing the distributed environment and subsequently wrapping the model with the DDP object.</p>
<pre><code class="hljs"><span class="hljs-comment"># Imports</span>
<span class="hljs-keyword">import</span> torch
<span class="hljs-keyword">import</span> torch.nn <span class="hljs-keyword">as</span> nn
</code></pre>
<pre><code class="hljs"><span class="hljs-comment"># Define the Linear Regression model</span>
<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">LinearRegression</span>(<span class="hljs-params">nn.Module</span>):</span>
    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">__init__</span>(<span class="hljs-params">self, input_size, output_size</span>):</span>
        <span class="hljs-built_in">super</span>(LinearRegression, self).__init__()
        self.linear = nn.Linear(input_size, output_size)

    <span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">forward</span>(<span class="hljs-params">self, x</span>):</span>
        <span class="hljs-keyword">return</span> self.linear(x)

<span class="hljs-comment"># Initialize the model</span>
model = LinearRegression(<span class="hljs-number">2</span>, <span class="hljs-number">5</span>)
</code></pre>
<pre><code class="hljs"><span class="hljs-comment"># Initialize the distributed environment.</span>
torch.distributed.init_process_group(backend=<span class="hljs-string">&#x27;nccl&#x27;</span>)

<span class="hljs-comment"># Wrap the model with DDP</span>
model = nn.parallel.DistributedDataParallelCPU(model)

<span class="hljs-comment"># Proceed to load and train the model</span>
<span class="hljs-comment"># ...</span>
</code></pre>
<p>This provides a basic wrapper to load the model for multi-GPU training across multiple nodes.</p>
<h2 id="conclusion">Conclusion</h2>
<p>In this article, we've explored various methods to leverage NVIDIA GPUs using the <code>CUDA</code> library in the PyTorch ML library. These strategies help us harness the power of robust GPUs, accelerating the model training process by a factor of ten compared to traditional CPUs in deep learning applications. This significant reduction in training time expedites a broad array of compute-intensive tasks.</p>


Guest Contributor

How to use GPUs with PyTorch

<h2 id="introduction">Introduction</h2>
<p>Removing empty lines from files is a common task in Linux Bash scripting. Empty lines can clutter up files and make them harder to read or process. Thankfully, the versatile <code>sed</code> command comes to the rescue. With its powerful text manipulation capabilities, <code>sed</code> provides an efficient way to delete empty lines from files.</p>
<blockquote>
<p>In this article, we'll explore how to use <code>sed</code> to eliminate those pesky empty lines, making your scripts and text files cleaner and more manageable. This concise guide will equip you with the knowledge to streamline your scripting tasks. We'll start with the basics of the <code>sed</code> command and the regular expressions used to identify empty lines. Then, we'll combine that knowledge to help you remove all types of empty lines.</p>
</blockquote>
<h2 id="whatisthesedcommandaquickrecall">What is the <em>sed</em> Command - A Quick Recall</h2>
<p>The <code>sed</code> command, short for &quot;stream editor,&quot; is a powerful utility in Linux Bash scripting that allows you to perform text transformations on input streams or files. It operates by sequentially reading the input line by line, applying specified commands, and then outputting the modified text.</p>
<p>In the context of deleting empty lines, <code>sed</code> becomes an invaluable tool. It supports regular expressions, which enable us to define patterns for matching and manipulating text. By leveraging <code>sed</code>'s regex capabilities, we can easily identify and remove empty lines from files.</p>
<p>With its intuitive syntax and extensive functionality, <code>sed</code> is widely used for a variety of text processing tasks. Its efficiency and versatility make it an excellent choice for automating operations on large-scale projects or within simple one-liner scripts.</p>
<h2 id="howtoidentifyemptylines">How to Identify Empty Lines</h2>
<p>Before we can delete empty lines using <code>sed</code>, we need to <em>be able to identify them</em>. Empty lines usually refer to lines that <em>contain no visible characters</em>, including spaces or tabs. However, we'll define an empty line as a line without any characters in it (besides the line termination character <code>\n</code>), just to illustrate the basic concepts without much overhead. Later, we'll introduce a more comprehensive definition and show examples of how to deal with lines without visible characters.</p>
<p>Thankfully, <code>sed</code> allows us to use regular expressions to define patterns for matching such lines. To identify empty lines, we can use the caret (<code>^</code>) symbol to represent the <em>start of a line</em> and the dollar sign (<code>$</code>) symbol to represent the <em>end of a line</em>. By combining these symbols, we can create a regex pattern that matches lines with no content:</p>
<pre><code class="hljs">/^$/
</code></pre>
<p>As discussed before, <code>^</code> indicates the start of a line and <code>$</code> indicates the end of a line. Therefore, <code>^$</code> represents a <strong>line with no characters between the start and end</strong>.</p>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong> Again, this regular expression matches a line with no characters, not necessarily lines with no visible characters, keep that in mind.</p>

                    </div>
                </div>
            </div>
            <blockquote>
<p>In addition to that, we can use the <code>d</code> command at the end of the pattern <em>to delete</em> the matching lines - <code>/^$/d</code>.</p>
</blockquote>
<p>In the next section, we'll explore how to use <code>sed</code> with this regex pattern to delete empty lines from files.</p>
<h2 id="usingsedtodeleteemptylines">Using <em>sed</em> to Delete Empty Lines</h2>
<p>Once we have identified the regex pattern to match empty lines (<code>/^$/</code>), we can leverage the power of <code>sed</code> to delete those lines from files. The <code>sed</code> command allows us to perform <em>in-place</em> editing of files, modifying them directly without the need for creating temporary files.</p>
<p>To delete empty lines using <code>sed</code>, we use the following syntax:</p>
<pre><code class="hljs">sed -i <span class="hljs-string">&#x27;/^$/d&#x27;</span> &lt;filename&gt;
</code></pre>
<p>Here, <code>-i</code> is the option used to perform <em>in-place editing of the file</em>. <code>'/^$/d'</code> is the regex pattern we discussed earlier, enclosed in single quotes. It matches the empty lines and the <code>d</code> command deletes them. Finally, <code>&lt;filename&gt;</code> is the name of the file on which we want to perform the operation.</p>
<p>By executing this <code>sed</code> command, the empty lines in the specified file will be deleted, resulting in a modified version of the file without the empty lines.</p>
<p>Imagine you have a file named <code>users.txt</code> in the current directory and it has the following content:</p>
<pre><code class="hljs">01; john_doe; john_doe@example.com; New York, USA
02; jane_smith; jane_smith@example.com; London, UK

03; bob_marshall; bob_marshall@example.com;  Sydney, Australia

04; alice_johnson; alice_johnson@example.com; Toronto, Canada
</code></pre>
<p>As you can see, there are two empty lines in this file. Suppose they are empty in the sense that they contain no characters (besides the newline character <code>\n</code>).</p>
<p>Now, we can run the previously discussed command on the <code>users.txt</code> file:</p>
<pre><code class="hljs">sed -i <span class="hljs-string">&#x27;/^$/d&#x27;</span> users.txt
</code></pre>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong> If the file is outside of the directory you're currently positioned in, you should pass the path to the file instead of the file name as the argument of the <code>sed</code> command.</p>

                    </div>
                </div>
            </div>
            <p>This will change the original <code>users.txt</code> file so it now contains no empty lines:</p>
<pre><code class="hljs">01; john_doe; john_doe@example.com; New York, USA
02; jane_smith; jane_smith@example.com; London, UK
03; bob_marshall; bob_marshall@example.com;  Sydney, Australia
04; alice_johnson; alice_johnson@example.com; Toronto, Canada
</code></pre>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong> As you saw in the example above, the <code>-i</code> option modifies the file directly, so it's always a good practice to make a backup of your file before applying any <code>sed</code> commands.</p>

                    </div>
                </div>
            </div>
            <h2 id="deletinglinescontainingjustwhitespacecharacters">Deleting Lines Containing Just Whitespace Characters</h2>
<p>But what if the lines look like they are empty, but, in fact, contain only invisible characters (whitespace characters, such as spaces or tabs)? To handle such lines and ensure they are deleted along with empty lines, we can modify our <code>sed</code> command slightly. We can utilize the <code>\s</code> regex pattern to match whitespace characters:</p>
<pre><code class="hljs">sed -i <span class="hljs-string">&#x27;/^\s*$/d&#x27;</span> &lt;filename&gt;
</code></pre>
<p>The<code>/^\s*$/d</code> is the updated regex pattern enclosed in single quotes, where<code>^\s*$</code> represents a line that starts with zero or more whitespace characters and ends with zero or more whitespace characters. By including <code>\s*</code> in the pattern, we ensure that lines with only whitespace characters are also matched and deleted.</p>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong> There are several other ways to represent zero or more whitespace characters using regex in the <code>sed</code> command. One of the most notable ones is the <code>'/^[[:space:\]]*$/d'</code> regular expression, which you can use interchangeably with the <code>'/^\s*$/d'</code>.</p>

                    </div>
                </div>
            </div>
            <h2 id="conclusion">Conclusion</h2>
<p>In this article, we explored how to use the <code>sed</code> command in Linux Bash scripting to efficiently delete empty lines from files. By harnessing the power of regular expressions, we were able to identify and remove empty lines, as well as lines containing only whitespace characters. That can be pretty useful for a variety of scenarios, such as data preprocessing, text file cleanup, and much more.</p>
<p>We began by understanding the <code>sed</code> command's capabilities and its role as a stream editor for text manipulation tasks. We then delved into identifying empty lines using regex patterns, specifically targeting lines with no visible content.</p>
<p>Using the <code>sed</code> command with the regex pattern <code>/^$/d</code>, we demonstrated how to delete empty lines in a file, performing in-place editing directly without the need for temporary files.</p>
<p>We expanded our knowledge by addressing lines that contain only whitespace characters. By modifying the regex pattern to <code>/^\s*$/d</code>, we ensured the removal of lines with whitespace-only content.</p>
<p>All-in-all, <code>sed</code> provides a powerful and efficient solution for deleting empty lines in Linux Bash scripting.</p>


Dimitrije Stamenic

Bash: How to Delete Empty Lines Using Sed

<h2 id="introduction">Introduction</h2>
<p>Efficient data manipulation is a critical skill for any data scientist or analyst. Among the many tools available, the Pandas library in Python stands out for its versatility and power. However, one often overlooked aspect of data manipulation is data type conversion - the practice of changing the data type of your data series or DataFrame.</p>
<p>Data type conversion in Pandas is not just about transforming data from one format to another. It's also about enhancing computational efficiency, saving memory, and ensuring your data aligns with the requirements of specific operations. Whether it's converting a string to a datetime or transforming an object to a categorical variable, efficient type conversion can lead to cleaner code and faster computation times.</p>
<blockquote>
<p>In this article, we'll delve into the various techniques of converting data types in Pandas, helping you unlock the further potential of your data manipulation capabilities. We'll discover some key functions and techniques in Pandas for effective data type conversion, including <code>astype()</code>, <code>to_numeric()</code>, <code>to_datetime()</code>, <code>apply()</code>, and <code>applymap()</code>. We'll also highlight the crucial best practices to bear in mind while undertaking these conversions.</p>
</blockquote>
<h2 id="masteringtheastypefunctioninpandas">Mastering the <em>astype()</em> Function in Pandas</h2>
<p>The <code>astype()</code> function in Pandas is one of the simplest yet most powerful tools for data type conversion. It allows us to change the data type of a single column or even multiple columns in a DataFrame.</p>
<p>Imagine you have a DataFrame where a column of numbers has been read as strings (object data type). This is quite a common scenario, especially when importing data from various sources like CSV files. You could use the <code>astype()</code> function to convert this column from object to numeric.</p>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong> Before attempting any conversions, you should always explore your data and understand its current state. Use the <code>info()</code> and <code>dtypes</code> attribute to understand the current data types of your DataFrame.</p>

                    </div>
                </div>
            </div>
            <p>Suppose we have a DataFrame named <code>df</code> with a column <code>age</code> that is currently stored as string (object). Let's take a look at how we can convert it to integers:</p>
<pre><code class="hljs">df[<span class="hljs-string">&#x27;age&#x27;</span>] = df[<span class="hljs-string">&#x27;age&#x27;</span>].astype(<span class="hljs-string">&#x27;int&#x27;</span>)
</code></pre>
<p>With a single line of code, we've changed the data type of the entire <code>age</code> column to integers.</p>
<p>But what if we have <em>multiple columns that need conversion</em>? The <code>astype()</code> function can handle that too. Assume we have two columns, <code>age</code> and <code>income</code>, both stored as strings. We can convert them to integer and float respectively as follows:</p>
<pre><code class="hljs">df[[<span class="hljs-string">&#x27;age&#x27;</span>, <span class="hljs-string">&#x27;income&#x27;</span>]] = df[[<span class="hljs-string">&#x27;age&#x27;</span>, <span class="hljs-string">&#x27;income&#x27;</span>]].astype({<span class="hljs-string">&#x27;age&#x27;</span>: <span class="hljs-string">&#x27;int&#x27;</span>, <span class="hljs-string">&#x27;income&#x27;</span>: <span class="hljs-string">&#x27;float&#x27;</span>})
</code></pre>
<p>Here, we provide a dictionary to the <code>astype()</code> function, where the keys are the column names and the values are the new data types.</p>
<p>The <code>astype()</code> function in Pandas is truly versatile. However, it's important to <em>ensure that the conversion you're trying to make is valid</em>. For instance, if the <code>age</code> column contains any non-numeric characters, the conversion to integers would fail. In such cases, you may need to use more specialized conversion functions, which we will cover in the next section.</p>
<h2 id="pandasconversionfunctionsto_numericandto_datetime">Pandas Conversion Functions - <em>to_numeric()</em> and <em>to_datetime()</em></h2>
<p>Beyond the general <code>astype()</code> function, Pandas also provides specialized functions for converting data types - <code>to_numeric()</code> and <code>to_datetime()</code>. These functions come with additional parameters that provide <em>more control during conversion</em>, especially when dealing with ill-formatted data.</p>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong> Convert data types to the most appropriate type for your use case. For instance, if your numeric data doesn't contain any decimal values, it's more memory-efficient to store it as integers rather than floats.</p>

                    </div>
                </div>
            </div>
            <h3 id="to_numeric"><em>to_numeric()</em></h3>
<p>The <code>to_numeric()</code> function is designed to <em>convert numeric data stored as strings into numeric data types</em>. One of its key features is the <code>errors</code> parameter which allows you to handle non-numeric values in a robust manner.</p>
<p>For example, if you want to convert a string column to a float but it contains some non-numeric values, you can use <code>to_numeric()</code> with the <code>errors='coerce'</code> argument. This will convert all non-numeric values to <code>NaN</code>:</p>
<pre><code class="hljs">df[<span class="hljs-string">&#x27;column_name&#x27;</span>] = pd.to_numeric(df[<span class="hljs-string">&#x27;column_name&#x27;</span>], errors=<span class="hljs-string">&#x27;coerce&#x27;</span>)
</code></pre>
<h3 id="to_datetime"><em>to_datetime()</em></h3>
<p>When dealing with dates and time, the <code>to_datetime()</code> function is a lifesaver. It can convert a wide variety of date formats into a standard datetime format that can be used for further date and time manipulation or analysis.</p>
<pre><code class="hljs">df[<span class="hljs-string">&#x27;date_column&#x27;</span>] = pd.to_datetime(df[<span class="hljs-string">&#x27;date_column&#x27;</span>])
</code></pre>
<p>The <code>to_datetime()</code> function is very powerful and can handle a lot of date and time formats. However, if your data is in an unusual format, you might need to specify a format string.</p>
<pre><code class="hljs">df[<span class="hljs-string">&#x27;date_column&#x27;</span>] = pd.to_datetime(df[<span class="hljs-string">&#x27;date_column&#x27;</span>], <span class="hljs-built_in">format</span>=<span class="hljs-string">&#x27;%d-%m-%Y&#x27;</span>)
</code></pre>
<p>Now that we have an understanding of these specialized conversion functions, we can talk about the efficiency of converting data types to 'category' using <code>astype()</code>.</p>
<h2 id="boostingefficiencywithcategorydatatype">Boosting Efficiency with Category Data Type</h2>
<p>The <code>category</code> data type in Pandas is here to help us deal with text data that falls into a limited number of categories. A categorical variable typically takes a limited, and usually fixed, number of possible values. Examples are gender, social class, blood types, country affiliations, observation time, and so on.</p>
<p>When you have a string variable that only takes a few different values, converting it to a categorical variable can save a lot of memory. Furthermore, operations like sorting or comparisons can be <em>significantly faster</em> with categorized data.</p>
<p>Here's how you can convert a DataFrame column to the <code>category</code> data type:</p>
<pre><code class="hljs">df[<span class="hljs-string">&#x27;column_name&#x27;</span>] = df[<span class="hljs-string">&#x27;column_name&#x27;</span>].astype(<span class="hljs-string">&#x27;category&#x27;</span>)
</code></pre>
<p>This command changes the data type of <code>column_name</code> to <code>category</code>. After the conversion, the data is no longer stored as a string but as a reference to an internal array of categories.</p>
<p>For instance, if you have a DataFrame <code>df</code> with a column <code>color</code> containing the values <code>Red</code>, <code>Blue</code>, <code>Green</code>, converting it to <code>category</code> would result in significant memory savings, especially for larger datasets. This happens because</p>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong>  The <code>category</code> data type is ideal for nominal variables - variables where the order of values doesn't matter. However, for ordinal variables (where the order does matter), you might want to pass an ordered list of categories to the <code>CategoricalDtype</code> function.</p>

                    </div>
                </div>
            </div>
            <p>In the next section, we will look at applying custom conversion functions to our DataFrame for more complex conversions with <code>apply()</code> and <code>applymap()</code>.</p>
<h2 id="usingapplyandapplymapforcomplexdatatypeconversions">Using <em>apply()</em> and <em>applymap()</em> for Complex Data Type Conversions</h2>
<p>When dealing with complex data type conversions that cannot be handled directly by <code>astype()</code>, <code>to_numeric()</code>, or <code>to_datetime()</code>, Pandas provides two functions, <code>apply()</code> and <code>applymap()</code>, which can be highly effective. These functions allow you to <em>apply a custom function to a DataFrame or Series</em>, enabling you to perform more sophisticated data transformations.</p>
<h3 id="theapplyfunction">The <em>apply()</em> Function</h3>
<p>The <code>apply()</code> function can be used on a DataFrame or a Series. When used on a DataFrame, it applies a function along an axis - either columns or rows.</p>
<p>Here's an example of using <code>apply()</code> to convert a column of stringified numbers into integers:</p>
<pre><code class="hljs"><span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">convert_to_int</span>(<span class="hljs-params">x</span>):</span>
    <span class="hljs-keyword">return</span> <span class="hljs-built_in">int</span>(x)

df[<span class="hljs-string">&#x27;column_name&#x27;</span>] = df[<span class="hljs-string">&#x27;column_name&#x27;</span>].apply(convert_to_int)
</code></pre>
<p>In this case, the <code>convert_to_int()</code> function is applied to each element in <code>column_name</code>.</p>
<h3 id="theapplymapfunction">The <em>applymap()</em> Function</h3>
<p>While <code>apply()</code> works on a row or column basis, <code>applymap()</code> works element-wise on an entire DataFrame. This means that the function you pass to <code>applymap()</code> is applied to every single element in the DataFrame:</p>
<pre><code class="hljs"><span class="hljs-comment"># Convert all the stringified numbers in a DataFrame to integers</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">convert_to_int</span>(<span class="hljs-params">x</span>):</span>
    <span class="hljs-keyword">return</span> <span class="hljs-built_in">int</span>(x)

df = df.applymap(convert_to_int)
</code></pre>
<p>The <code>convert_to_int()</code> function is applied to <em>every single element</em> in the DataFrame.</p>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong> Bear in mind that complex conversions can be computationally expensive, so use these tools judiciously.</p>

                    </div>
                </div>
            </div>
            <h2 id="conclusion">Conclusion</h2>
<p>The right data type for your data can play a critical role in boosting computational efficiency and ensuring the correctness of your results. In this article, we have gone through the fundamental techniques of converting data types in Pandas, including the use of the <code>astype()</code>, <code>to_numeric()</code>, and <code>to_datetime()</code> functions, and delved into the power of applying custom functions using <code>apply()</code> and <code>applymap()</code> for more complex transformations.</p>
<p>Remember, the key to efficient data type conversion is understanding your data and the requirements of your analysis, and then applying the most appropriate conversion technique. By employing these techniques effectively, you can harness the full power of Pandas to perform your data manipulation tasks more efficiently.</p>
<p>The journey of mastering data manipulation in Pandas doesn't end here. The field is vast and ever-evolving. But with the fundamental knowledge of data type conversions that you've gained through this article, you're now well-equipped to handle a broader range of data manipulation challenges. So, as always, keep exploring and learning!</p>


How to Efficiently Convert Data Types in Pandas

<h2 id="introduction">Introduction</h2>
<p>SQL is the predominant language for database queries, and proficiency in SQL is important for accurate data querying. This requires a comprehensive understanding of the sequence in which SQL executes its clauses. Debugging your SQL script effectively and creating precise queries necessitates knowledge of how a database interprets and executes your SQL query.</p>
<p>In this article, we will discuss the specific sequence in which the clauses of an SQL query execute. However, if your query includes sub-queries or Common Table Expressions (CTE), bear in mind that these will always be executed first before any action takes place on the main query. Nonetheless, the execution order of clauses within a CTE or subquery remains unchanged.</p>
<p>We will be referencing the following two tables:</p>
<p><strong>Customers</strong></p>
<table class="table table-striped">
    <thead>
        <tr>
            <th>customer_id</th>
            <th>customer</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>1</td>
<td>Bruce Wayne</td>
</tr>
<tr>
    <td>2</td>
    <td>Clark Kent</td>
</tr>
<tr>
    <td>3</td>
    <td>Tony Stark</td>
</tr>
<tr>
    <td>4</td>
    <td>Bruce Banner</td>
</tr>
<tr>
    <td>5</td>
    <td>Peter Parker</td>
</tr>
</tbody>
</table>
<p><strong>Purchases</strong></p>
<table class="table table-striped">
    <thead>
        <tr>
            <th>purchase_id</th>
            <th>item</th>
            <th>price</th>
            <th>customer_id</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>1</td>
            <td>Red Cape</td>
            <td>3.75</td>
            <td>2</td>
        </tr>
        <tr>
            <td>2</td>
            <td>Web Shooter</td>
            <td>9.26</td>
            <td>5</td>
        </tr>
        <tr>
            <td>3</td>
            <td>Batarang</td>
            <td>23.24</td>
            <td>1</td>
</tr>
</tr>
<tr>
    <td>4</td>
    <td>Smoke Pellet</td>
    <td>2.99</td>
    <td>1</td>
</tr>
<tr>
    <td>5</td>
    <td>Red Boots</td>
    <td>17.41</td>
    <td>2</td>
</tr>
<tr>
    <td>6</td>
    <td>Sunglasses</td>
    <td>299.99</td>
    <td>3</td>
</tr>
<tr>
    <td>7</td>
    <td>Lab Coat</td>
    <td>74.23</td>
    <td>4</td>
</tr>
</tbody>
</table>
<p>Here is our SQL query, which identifies the two customers who have spent the most money, excluding purchases exceeding $200 and customers whose total purchases are less than $10:</p>
<pre><code class="hljs"><span class="hljs-keyword">SELECT</span> 
   customers.customer_id,
   customers.customer,
   <span class="hljs-built_in">SUM</span>(price) <span class="hljs-keyword">as</span> 
   total_money_spent
<span class="hljs-keyword">FROM</span> customers 

<span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span>
    purchases
<span class="hljs-keyword">on</span> customers.customer_id <span class="hljs-operator">=</span> purchases.customer_id

<span class="hljs-keyword">WHERE</span>
    price <span class="hljs-operator">&lt;</span> <span class="hljs-number">200</span>

<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> 
   customers.customer_id,
   customers.customer

<span class="hljs-keyword">HAVING</span>
   total_money_spent <span class="hljs-operator">&gt;</span> <span class="hljs-number">10</span>

<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span>
   total_money_spent <span class="hljs-keyword">desc</span>

LIMIT
   <span class="hljs-number">2</span>
</code></pre>
<p>Here is the sequence of execution, breaking down what occurs at each stage:</p>
<ul>
<li><code>FROM</code> (including joins: <code>INNER JOIN</code>, <code>LEFT JOIN</code>, <code>RIGHT JOIN</code>, <code>OUTER JOIN</code>, <code>CROSS JOIN</code>, etc.)</li>
<li><code>WHERE</code></li>
<li><code>GROUP BY</code></li>
<li><code>HAVING</code></li>
<li><code>SELECT</code></li>
<li><code>ORDER BY</code></li>
<li><code>LIMIT</code></li>
</ul>
<h2 id="step1fromandjoins">Step 1: <em>FROM</em> and <em>JOINS</em></h2>
<pre><code class="hljs"><span class="hljs-keyword">FROM</span> customers 

<span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span>
    purchases
<span class="hljs-keyword">on</span> customers.customer_id <span class="hljs-operator">=</span> purchases.customer_id
</code></pre>
<p>The complete <code>customers</code> table is invoked and combined with the <code>purchases</code> table based on the <code>customer_id</code>, resulting in a new primary table that includes the matches from both tables. Therefore, after our query execution, the database assembles this primary table:</p>
<table class="table table-striped">
    <thead>
        <tr>
            <th>customer_id</th>
            <th>customer</th>
            <th>purchase_id</th>
            <th>purchase</th>
            <th>price</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>1</td>
            <td>Bruce Wayne</td>
            <td>3</td>
            <td>Batarang</td>
<td>23.24</td>
</tr>
<tr>
<td>1</td>
<td>Bruce Wayne</td>
<td>4</td>
<td>Smoke Pellet</td>
<td>2.99</td>
</tr>
<tr>
<td>2</td>
<td>Clark Kent</td>
<td>1</td>
<td>Red Cape</td>
<td>3.75</td>
</tr>
<tr>
<td>2</td>
<td>Clark Kent</td>
<td>5</td>
<td>Red Boots</td>
<td>17.41</td>
</tr>
<tr>
<td>3</td>
<td>Tony Stark</td>
<td>6</td>
<td>Sunglasses</td>
<td>299.99</td>
</tr>
<tr>
<td>4</td>
<td>Bruce Banner</td>
<td>7</td>
<td>Lab Coat</td>
<td>74.23</td>
</tr>
<tr>
<td>5</td>
<td>Peter Parker</td>
<td>2</td>
<td>Web Shooter</td>
<td>9.26</td>
</tr>
</tbody>
</table>
<h2 id="step2thewhereclause">Step 2: The <em>WHERE</em> Clause</h2>
<pre><code class="hljs"><span class="hljs-keyword">WHERE</span>
    price <span class="hljs-operator">&lt;</span> <span class="hljs-number">200</span>
</code></pre>
<p>The <code>WHERE</code> clause serves as our filter, enabling us to omit undesired data from the main table and retain the data we wish to view. In this scenario, we are retaining all purchases below $200, thereby excluding the sunglasses purchase valued at $299.99.</p>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong> It's important to bear in mind that you can't utilize a <code>WHERE</code> clause on any columns that are undergoing aggregation (sum, avg, etc) in the statement. For this purpose, you'll need to use the <code>HAVING</code> clause, which we'll discuss later. If you are aggregating, the <code>WHERE</code> clause will omit rows <code>BEFORE</code> the aggregation commences. Therefore, in the context of our table, we will NOT be excluding total purchases exceeding $200.</p>

                    </div>
                </div>
            </div>
            <table class="table table-striped">
    <thead>
        <tr>
            <th>customer_id</th>
            <th>customer</th>
            <th>purchase_id</th>
            <th>purchase</th>
            <th>price</th>
        </tr>
    </thead>
<tbody>
    <tr>
        <td>1</td>
        <td>Bruce Wayne</td>
        <td>3</td>
        <td>Batarang</td>
        <td>23.24</td>
    </tr>
    <tr>
        <td>1</td>
        <td>Bruce Wayne</td>
        <td>4</td>
        <td>Smoke Pellet</td>
        <td>2.99</td>
    </tr>
    <tr>
        <td>2</td>
        <td>Clark Kent</td>
        <td>1</td>
        <td>Red Cape</td>
        <td>3.75</td>
    </tr>
    <tr>
        <td>2</td>
        <td>Clark Kent</td>
        <td>5</td>
        <td>Red Boots</td>
        <td>17.41</td>
    </tr>
    <tr>
        <td>4</td>
        <td>Bruce Banner</td>
        <td>7</td>
        <td>Lab Coat</td>
        <td>74.23</td>
    </tr>
    <tr>
        <td>5</td>
        <td>Peter Parker</td>
        <td>2</td>
        <td>Web Shooter</td>
        <td>9.26</td>
    </tr>
</tbody>
</table>
<h2 id="step3groupcolumnswithgroupby">Step 3: Group Columns with <em>GROUP BY</em></h2>
<pre><code class="hljs"><span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> 
   customers.customer_id,
   customers.customer
</code></pre>
<p>As there is an aggregation (<code>SUM</code>) in our query, <code>GROUP BY</code> will execute, aggregating the price by the two non-aggregated columns (<code>customer_id</code> and <code>customer</code>).</p>

            <div class="alert alert-note">
                <div class="flex">
                    
                        <div class="flex-shrink-0 mr-3">
                            <img src="/assets/images/icon-information-circle-solid.svg" class="icon" aria-hidden="true" />
                        </div>
                        
                    <div class="w-full">
            <p><strong>Note:</strong> When there is an aggregation in your statement, it's imperative to group by all non-aggregated columns that you are incorporating into your query. In this instance, since we are including both <code>customer_id</code> and <code>customer</code> in our <code>SELECT</code> clause, we must <code>GROUP BY</code> both of these columns. The sequence here is deliberate as it ensures that the query executes <code>GROUP BY</code> with their distinct ID first and <em>then</em> their name.</p>

                    </div>
                </div>
            </div>
            <table class="table table-striped">
    <thead>
        <tr>
            <th>customer_id</th>
            <th>customer</th>
            <th>price</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>1</td>
            <td>Bruce Wayne</td>
            <td>26.23</td>
        </tr>
        <tr>
<td>21.16</td>
        </tr>
        <tr>
            <td>4</td>
            <td>Bruce Banner</td>
            <td>74.23</td>
        </tr>
    </tbody>
</table>
<h2 id="step4thehavingclause">Step 4: The <em>HAVING</em> Clause</h2>
<pre><code class="hljs"><span class="hljs-keyword">HAVING</span>
   total_money_spent <span class="hljs-operator">&gt;</span> <span class="hljs-number">10</span>
</code></pre>
<p>This clause enables us to filter by our aggregation as this occurs post-<code>GROUP BY</code>, when the data has been aggregated. It can't be used in place of a <code>WHERE</code> clause. In this scenario, we're excluding total purchases under $10 (poor Peter Parker).</p>
<table class="table table-striped">
    <thead>
        <tr>
            <th>customer_id</th>
            <th>customer</th>
            <th>price</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>1</td>
            <td>Bruce Wayne</td>
            <td>26.23</td>
        </tr>
        <tr>
            <td>2</td>
            <td>Clark Kent</td>
<td>Peter Parker</td>
<td>21.16</td>
</tr>
<tr>
<td>4</td>
<td>Bruce Banner</td>
<td>74.23</td>
</tr>
</tbody>
</table>
<h2 id="step5selectstatement">Step 5: <em>SELECT</em> Statement</h2>
<pre><code class="hljs"><span class="hljs-keyword">SELECT</span> 
   customers.customer_id,
   customers.customer,
   <span class="hljs-built_in">SUM</span>(price) <span class="hljs-keyword">as</span> 
   total_money_spent
</code></pre>
<p>This clause specifies the particular columns we wish to extract from the primary table we've assembled. In this instance, since we've used the <code>GROUP BY</code> clause and performed aggregation, we've already narrowed down to these columns. However, outside of aggregation, this step is pivotal to ensure that we're only extracting the desired data and assigning a suitable alias to each column, if required.</p>
<table class="table table-striped">
    <thead>
        <tr>
            <th>customer_id</th>
            <th>customer</th>
            <th>total_money_spent</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>1</td>
            <td>Bruce Wayne</td>
            <td>26.23</td>
        </tr>
        <tr>
            <td>2</td>
<td>Clark Kent</td>
<td>21.16</td>
</tr>
<tr>
<td>4</td>
<td>Bruce Banner</td>
<td>74.23</td>
</tr>
</tbody>
</table>
<h2 id="step6utilizingorderby">Step 6: Utilizing <em>ORDER BY</em></h2>
<pre><code class="hljs"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span>
   total_money_spent <span class="hljs-keyword">DESC</span>
</code></pre>
<p>This clause facilitates the sorting of the table. We can <code>ORDER BY</code> column <code>ASC</code> (Ascending, which is the default) or <code>ORDER BY</code> column <code>DESC</code> (descending). Additionally, you can <code>ORDER BY</code> multiple columns. In this instance, we are sorting by our aggregated column in descending order (note that <code>ORDER BY</code> is one of the few instances where the aggregated column's alias can be used because it occurs <em>after</em> the <code>SELECT</code> clause)</p>
<table class="table table-striped">
    <thead>
        <tr>
            <th>customer_id</th>
            <th>customer</th>
            <th>total_money_spent</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>4</td>
            <td>Bruce Banner</td>
            <td>74.23</td>
</tr>
<tr>
    <td>1</td>
    <td>Bruce Wayne</td>
    <td>26.23</td>
</tr>
<tr>
    <td>2</td>
    <td>Clark Kent</td>
    <td>21.16</td>
</tr>
</tbody>
</table>
<h2 id="step7settinglimit">Step 7: Setting <em>LIMIT</em></h2>
<pre><code class="hljs">LIMIT
   <span class="hljs-number">2</span>
</code></pre>
<p>This clause restricts the number of data rows we wish to retrieve. For instance, in our scenario, we are interested in only the top two customers. Therefore, setting a <code>LIMIT</code> of 2 confines our results to Bruce Banner and Bruce Wayne.</p>
<table class="table table-striped">
    <thead>
        <tr>
            <th>customer_id</th>
            <th>customer</th>
            <th>total_money_spent</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>4</td>
            <td>Bruce Banner</td>
            <td>74.23</td>
        </tr>
        <tr>
            <td>1</td>
            <td>Bruce Wayne</td>
            <td>26.23</td>
        </tr>
    </tbody>
</table>
<p>When you're merging multiple SQL queries vertically using Union or Union All, it's important to remember that both <code>ORDER BY</code> and <code>LIMIT</code> indicate the conclusion of the clause. Here's a brief example:</p>
<pre><code class="hljs"><span class="hljs-keyword">SELECT</span>
    customer_id,
    customer
<span class="hljs-keyword">FROM</span> customers

<span class="hljs-keyword">WHERE</span>
    customers.customer <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;Clark Kent&#x27;</span>

<span class="hljs-keyword">UNION</span> 

<span class="hljs-keyword">SELECT</span>
    customer_id,
    customer
<span class="hljs-keyword">FROM</span> customers

<span class="hljs-keyword">WHERE</span>
    customer <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;Bruce Wayne&#x27;</span>

<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span>
    customer_id

LIMIT
    <span class="hljs-number">2</span>
</code></pre>
<p>While the above query won't result in any errors, the <code>ORDER BY</code> and <code>LIMIT</code> won't execute until after the queries have been merged into a single table, instead of just on the second query. Consider the following example as well:</p>
<pre><code class="hljs"><span class="hljs-keyword">SELECT</span>
    customer_id,
    customer
<span class="hljs-keyword">FROM</span> customers

<span class="hljs-keyword">WHERE</span>
    customers.customer <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;Clark Kent&#x27;</span>

<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span>
    customer_id

<span class="hljs-keyword">UNION</span> 

<span class="hljs-keyword">SELECT</span>
    customer_id,
    customer
<span class="hljs-keyword">FROM</span> customers

<span class="hljs-keyword">WHERE</span>
    customer <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;Bruce Wayne&#x27;</span>
</code></pre>
<p>This example will generate an error because <code>ORDER BY</code> indicates the termination of the query. The same applies to <code>LIMIT</code>, so it is impossible to execute the second SQL query after <code>UNION</code> at the bottom. If you wish to include more than one <code>ORDER BY</code> and/or <code>LIMIT</code> in a <code>UNION</code>/<code>UNION ALL</code> statement, you must use parentheses to enclose the queries:</p>
<pre><code class="hljs">(<span class="hljs-keyword">SELECT</span>
    customer_id,
    customer
<span class="hljs-keyword">FROM</span> customers

<span class="hljs-keyword">WHERE</span>
    customers.customer <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;Clark Kent&#x27;</span>

<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span>
    customer_id) 

<span class="hljs-keyword">UNION</span> 

(<span class="hljs-keyword">SELECT</span>
    customer_id,
    customer
<span class="hljs-keyword">FROM</span> customers

<span class="hljs-keyword">WHERE</span>
    customer <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;Bruce Wayne&#x27;</span>

<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span>
    customer_id)
</code></pre>
<h2 id="conclusion">Conclusion</h2>
<p>Understanding the order in which SQL clauses execute can enhance your ability to write dynamic, accurate, and efficient queries that extract the precise data required for your projects. This understanding will also aid in troubleshooting your SQL queries when data retrieval is erroneous, by facilitating careful tracking of your steps in the sequence of execution until the problem is identified. Happy querying!</p>


Order of Execution of SQL Clauses 

Guide to Interfaces in Python

<h2 id="introduction">Introduction</h2>
<p>The <code>apply()</code> function is a powerful tool in Python for data analysis and manipulation. It is a valuable instrument for any analyst's toolkit, as it can be seamlessly integrated with other Pandas functions and custom functions to perform complex data transformations.</p>
<p>In this article, we will learn how to leverage the <code>apply()</code> function in Pandas for efficient and flexible data manipulation. We will examine code examples and understand the advantages and disadvantages of this function in various scenarios.</p>
<h2 id="overviewoftheapplyfunction">Overview of the Apply() Function</h2>
<p>The <code>apply()</code> function allows you to implement a range of transformations on your data. You can define your own function for specific tasks, such as string manipulations, complex operations, or custom calculations. Once defined, you can <code>apply()</code> these to your DataFrame without the need to code them again each time you need to use them. Similarly, you can define a <a href="https://docs.python.org/3/tutorial/controlflow.html?highlight=lambda#lambda-expressions" target="_blank" rel="noopener noreferrer nofolow" target="_blank">lambda function</a>, which is an anonymous and quick operation created on the fly. You can pass it to <code>apply()</code> to instantly use it on your data. The <code>apply()</code> function can also be used with built-in functions that come pre-packaged with Python. <a href="https://stackabuse.com/beginners-tutorial-on-the-pandas-python-library/" target="_blank">Pandas Series and DataFrames</a> are both compatible with the use of <code>apply()</code>.</p>
<h3 id="usingapplyonaseries">Using Apply() on a Series</h3>
<p>Let's define a series of average monthly temperatures (in Celsius) for a city in a year by importing the Pandas package and using the <code>Series()</code> class:</p>
<pre><code class="hljs"><span class="hljs-keyword">import</span> pandas <span class="hljs-keyword">as</span> pd

city_temps = pd.Series([<span class="hljs-number">1</span>, <span class="hljs-number">4</span>, <span class="hljs-number">8</span>, <span class="hljs-number">9</span>, <span class="hljs-number">14</span>, <span class="hljs-number">25</span>, <span class="hljs-number">31</span>, <span class="hljs-number">35</span>, <span class="hljs-number">32</span>, <span class="hljs-number">25</span>, <span class="hljs-number">11</span>, <span class="hljs-number">2</span>])
</code></pre>
<p>We can create a custom function to convert these temperatures to the Fahrenheit scale:</p>
<pre><code class="hljs"><span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">celsius_to_fahrenheit</span>(<span class="hljs-params">celsius</span>):</span>
    fahrenheit = (celsius * <span class="hljs-number">9</span>/<span class="hljs-number">5</span>) + <span class="hljs-number">32</span>
    <span class="hljs-keyword">return</span> fahrenheit
</code></pre>
<p>The function above takes a numeric temperature value in Celsius and converts it to Fahrenheit. Now, we can transform each element of our Series:</p>
<pre><code class="hljs">temp_fahrenheit = city_temps.apply(celsius_to_fahrenheit)
<span class="hljs-built_in">print</span>(temp_fahrenheit)
</code></pre>
<p>The output is a Series containing the transformed values of the city temperatures:</p>
<pre><code class="hljs">0     33.8
1     39.2
2     46.4
3     48.2
4     57.2
5     77.0
6     87.8
7     95.0
8     89.6
9     77.0
10    51.8
11    35.6
dtype: float64
</code></pre>
<h2 id="applyingapplytoadataframe">Applying <code>apply()</code> to a DataFrame</h2>
<p>Using <code>apply()</code> provides flexibility for adding or manipulating columns in Pandas DataFrames. Consider a DataFrame with mean monthly temperatures during a year for two cities:</p>
<pre><code class="hljs">data = {<span class="hljs-string">&#x27;City 1&#x27;</span>: [<span class="hljs-number">1</span>, <span class="hljs-number">4</span>, <span class="hljs-number">8</span>, <span class="hljs-number">9</span>, <span class="hljs-number">14</span>, <span class="hljs-number">25</span>, <span class="hljs-number">31</span>, <span class="hljs-number">35</span>, <span class="hljs-number">32</span>, <span class="hljs-number">25</span>, <span class="hljs-number">11</span>, <span class="hljs-number">2</span>],
        <span class="hljs-string">&#x27;City 2&#x27;</span>: [<span class="hljs-number">19</span>, <span class="hljs-number">23</span>, <span class="hljs-number">30</span>, <span class="hljs-number">31</span>, <span class="hljs-number">35</span>, <span class="hljs-number">40</span>, <span class="hljs-number">45</span>, <span class="hljs-number">39</span>, <span class="hljs-number">30</span>, <span class="hljs-number">25</span>, <span class="hljs-number">15</span>, <span class="hljs-number">10</span>]
       }
<span class="hljs-comment"># Create the DataFrame</span>
df = pd.DataFrame(data)
<span class="hljs-built_in">print</span>(df)
</code></pre>
<p>The DataFrame appears as follows:</p>
<pre><code class="hljs">    City 1  City 2
0        1      19
1        4      23
2        8      30
3        9      31
4       14      35
5       25      40
6       31      45
7       35      39
8       32      30
9       25      25
10      11      15
11       2      10
</code></pre>
<p>We can <code>apply()</code> our function on the <code>df</code> DataFrame and create a new column <code>City 1 Fahrenheit</code>:</p>
<pre><code class="hljs">df[<span class="hljs-string">&#x27;City 1 Fahrenheit&#x27;</span>] = df[<span class="hljs-string">&#x27;City 1&#x27;</span>].apply(celsius_to_fahrenheit)

<span class="hljs-comment"># Print the DataFrame</span>
<span class="hljs-built_in">print</span>(df)
</code></pre>
<p>The transformation was successful:</p>
<pre><code class="hljs">    City 1  City 2  City 1 Fahrenheit
0        1      19               33.8
1        4      23               39.2
2        8      30               46.4
3        9      31               48.2
4       14      35               57.2
5       25      40               77.0
6       31      45               87.8
7       35      39               95.0
8       32      30               89.6
9       25      25               77.0
10      11      15               51.8
11       2      10               35.6
</code></pre>
<p>An important note here is the use of the <code>axis</code> parameter within the <code>apply()</code> function, which indicates whether we want to perform the operation on the rows or the columns of the DataFrame. Specifying <code>axis=0</code> applies the operation to each column, while <code>axis=1</code> applies it to each row. In our previous example, the default <code>axis=0</code> was used since we didn't explicitly pass a value for <code>axis</code>. This applied our function to each column and returned a value for every row.</p>
<p>Let's explicitly pass <code>axis=1</code> to <code>apply()</code> our custom function to all columns in the DataFrame. We will create two new columns by applying the transformation to each row.</p>
<pre><code class="hljs">data = {<span class="hljs-string">&#x27;City 1&#x27;</span>: [<span class="hljs-number">1</span>, <span class="hljs-number">4</span>, <span class="hljs-number">8</span>, <span class="hljs-number">9</span>, <span class="hljs-number">14</span>, <span class="hljs-number">25</span>, <span class="hljs-number">31</span>, <span class="hljs-number">35</span>, <span class="hljs-number">32</span>, <span class="hljs-number">25</span>, <span class="hljs-number">11</span>, <span class="hljs-number">2</span>],
        <span class="hljs-string">&#x27;City 2&#x27;</span>: [<span class="hljs-number">19</span>, <span class="hljs-number">23</span>, <span class="hljs-number">30</span>, <span class="hljs-number">31</span>, <span class="hljs-number">35</span>, <span class="hljs-number">40</span>, <span class="hljs-number">45</span>, <span class="hljs-number">39</span>, <span class="hljs-number">30</span>, <span class="hljs-number">25</span>, <span class="hljs-number">15</span>, <span class="hljs-number">10</span>]
       }

df = pd.DataFrame(data)

df[[<span class="hljs-string">&#x27;City 1 Fahrenheit&#x27;</span>, <span class="hljs-string">&#x27;City 2 Fahrenheit&#x27;</span>]] = df.apply(
    <span class="hljs-keyword">lambda</span> row: pd.Series(
        [celsius_to_fahrenheit(row[<span class="hljs-string">&#x27;City 1&#x27;</span>]),
         celsius_to_fahrenheit(row[<span class="hljs-string">&#x27;City 2&#x27;</span>])]),
         axis=<span class="hljs-number">1</span>
)

<span class="hljs-built_in">print</span>(df)
</code></pre>
<p>We used a lambda expression here to take each row as an input using <code>axis=1</code> and returned a Series object with the Fahrenheit values. This resulting series was assigned to the 'City 1 Fahrenheit' and 'City 2 Fahrenheit' columns of the DataFrame.</p>
<h3 id="determinetheapplyfunctionsreturntype">Determine the Apply() Function's Return Type</h3>
<p>You may have already noticed that <code>apply()</code> always returned a Series in our previous examples. Generally, the result of the applied function is a Series object that maintains the indexing structure of the original data. However, we can utilize the <code>result_type</code> parameter and set its value to &quot;expand&quot;, which instructs it to return a DataFrame. This is particularly helpful when we need to reshape the data structure or apply functions that generate multiple values.</p>
<p>Let's modify our previous example to see how we can return a DataFrame using the <code>result_type</code> parameter.</p>
<pre><code class="hljs">data = {<span class="hljs-string">&#x27;City 1&#x27;</span>: [<span class="hljs-number">1</span>, <span class="hljs-number">4</span>, <span class="hljs-number">8</span>, <span class="hljs-number">9</span>, <span class="hljs-number">14</span>, <span class="hljs-number">25</span>, <span class="hljs-number">31</span>, <span class="hljs-number">35</span>, <span class="hljs-number">32</span>, <span class="hljs-number">25</span>, <span class="hljs-number">11</span>, <span class="hljs-number">2</span>],
        <span class="hljs-string">&#x27;City 2&#x27;</span>: [<span class="hljs-number">19</span>, <span class="hljs-number">23</span>, <span class="hljs-number">30</span>, <span class="hljs-number">31</span>, <span class="hljs-number">35</span>, <span class="hljs-number">40</span>, <span class="hljs-number">45</span>, <span class="hljs-number">39</span>, <span class="hljs-number">30</span>, <span class="hljs-number">25</span>, <span class="hljs-number">15</span>, <span class="hljs-number">10</span>]
       }

df = pd.DataFrame(data)

df[[<span class="hljs-string">&#x27;City 1 (Fahrenheit)&#x27;</span>, <span class="hljs-string">&#x27;City 2 (Fahrenheit)&#x27;</span>]] = df.apply(<span class="hljs-keyword">lambda</span> row: celsius_to_fahrenheit(row), axis=<span class="hljs-number">1</span>, result_type=<span class="hljs-string">&#x27;expand&#x27;</span>)

<span class="hljs-built_in">print</span>(df)
</code></pre>
<p>The output is the same as we obtained earlier:</p>
<pre><code class="hljs">    City 1  City 2  City 1 (Fahrenheit)  City 2 (Fahrenheit)
0        1      19                 33.8                 66.2
1        4      23                 39.2                 73.4
2        8      30                 46.4                 86.0
3        9      31                 48.2                 87.8
4       14      35                 57.2                 95.0
5       25      40                 77.0                104.0
6       31      45                 87.8                113.0
7       35      39                 95.0                102.2
8       32      30                 89.6                 86.0
9       25      25                 77.0                 77.0
10      11      15                 51.8                 59.0
11       2      10                 35.6                 50.0
</code></pre>
<p>It is clear that <code>result_type</code> enables us to greatly streamline the transformation of new columns, as we no longer need to explicitly manipulate the output to convert it to a DataFrame structure.</p>
<h2 id="performanceconsiderations">Performance Considerations</h2>
<p>Although the <code>apply()</code> function appears to be a powerful tool for simplifying data transformations, it is better suited for specific types of tasks. In addition to custom data transformations or row and column-wise operations, as mentioned earlier, it can also be employed to combine columns for data cleaning or feature engineering. Furthermore, it is useful for handling missing values, as we can define and apply custom imputation methods.</p>
<p>It is worth considering alternative approaches to <code>apply()</code> if you need optimal performance for large datasets or tasks that are computationally intensive. You should use built-in vectorized operations wherever possible. For instance, you could use the <code>sum()</code> method instead of <code>apply(sum)</code>. You can also leverage built-in string operations like <code>str.replace()</code> and conditional operations like <code>where()</code> for efficient aggregations and group-wise operations. Additionally, consider Python packages such as Swifter or Dask, which use parallel processing to efficiently work with larger datasets. Depending on your dataset size and available computational resources, these alternatives can significantly enhance the performance of data manipulation tasks compared to using <code>apply()</code> alone.</p>
<h2 id="finalthoughts">Final Thoughts</h2>
<p>In summary, the <code>apply()</code> function serves as a valuable resource for data manipulation, especially in routine tasks that require repetitive code. This function allows for seamless integration of custom or built-in functions with Pandas Series and DataFrames. Identifying opportunities to utilize the <code>apply()</code> function can significantly improve your productivity in daily tasks.</p>
