{"id":77388,"date":"2018-02-28T09:11:34","date_gmt":"2018-02-28T09:11:34","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=77388"},"modified":"2026-04-15T19:19:11","modified_gmt":"2026-04-15T19:19:11","slug":"sql-server-machine-learning-services-part-6-merging-data-frames-python","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sql-server-machine-learning-services-part-6-merging-data-frames-python\/","title":{"rendered":"Merge SQL Server Data with CSV Files in Python Using pandas (MLS Part 6)"},"content":{"rendered":"\n

Executive Summary<\/h2>\n\n\n\n

This article demonstrates merging data from two sources inside SQL Server Machine Learning Services using Python. The first DataFrame is created from SQL Server data using sp_execute_external_script. The second DataFrame is built from CSV files read with pandas read_csv(). The two DataFrames are merged using pandas merge() – equivalent to a SQL JOIN – on a common key column. The merged result is then used to create a bar chart with matplotlib, showing annual sales per sales representative. Requires SQL Server 2017+ with Machine Learning Services and Python enabled.<\/strong><\/p>\n\n\n

The series so far:<\/h4>\n
    \n
  1. SQL Server Machine Learning Services \u2013 Part 1: Python Basics<\/a><\/li>\n
  2. SQL Server Machine Learning Services \u2013 Part 2: Python Data Frames<\/a><\/li>\n
  3. SQL Server Machine Learning Services \u2013 Part 3: Plotting Data with Python<\/a><\/li>\n
  4. SQL Server Machine Learning Services \u2013 Part 4: Finding Data Anomalies with Python<\/a><\/li>\n
  5. SQL Server Machine Learning Services \u2013 Part 5: Generating multiple plots in Python<\/a><\/li>\n
  6. SQL Server Machine Learning Services \u2013 Part 6: Merging Data Frames in Python<\/a><\/li>\n<\/ol>\n\n\n\n\n

    SQL Server Machine Learning Services (MLS), along with the Python language, offer a wide range of options for analyzing and visualizing data. I covered a number of these options in the previous articles in this series and provided examples of how to use them in your Python scripts. This article builds on those discussions by demonstrating how to merge two data frames, one created from SQL Server data and the other from multiple .csv files. The final output is a third data frame that provides the foundation for plotting the data in order to generate a visualization.<\/p>\n\n\n\n

    The examples in this article are based on data from the AdventureWorks2017<\/strong> sample database and a small set of .csv files (download at the bottom of the article) that contain data mapping back to the SQL Server data. The goal is to create a single data frame that merges the two original data sets. The final data frame will contain six years of annual sales data for a group of sales reps. The data will then be used to generate a bar chart that visualizes each individual\u2019s sales. I created the examples in SQL Server Management Studio (SSMS), connecting to a local instance of SQL Server 2017.<\/p>\n\n\n\n

    Creating a data frame based on SQL Server data<\/h2>\n\n\n\n

    As you\u2019ve seen in the previous articles, the key to running a Python (or R) script within the context of a SQL Server database is to call the sp_execute_external_script<\/strong> stored procedure, passing in the Python script as a parameter value. If you have any questions about how this works, refer back to the first article in this series. In this article, we focus primarily on the Python script.<\/p>\n\n\n\n

    If you plan to try out these examples for yourself, you\u2019ll ultimately create a merged data frame and use it to generate a visualization. To get to that point, you need to take four steps:<\/p>\n\n\n

    \n
      \n
    1. Create the first data frame based on SQL Server data.<\/li>\n\n\n\n
    2. Create the second data frame based on data from a set of .csv files.<\/li>\n\n\n\n
    3. Create the final data frame by merging the first two data frames.<\/li>\n\n\n\n
    4. Create a bar chart based on the data in the final data frame.<\/li>\n<\/ol>\n<\/div>\n\n\n

      The following example carries out the first step:<\/p>\n\n\n\n

      Use AdventureWorks2017;\nGO\n-- define Python script\nDECLARE @pscript NVARCHAR(MAX);\nSET @pscript = N'\n# assign SQL Server dataset to the df1 variable\ndf1 = InputDataSet\ndf1[\"2012\"] = df1[\"2012\"].astype(int)\nprint(df1)';\n-- define T-SQL query\nDECLARE @sqlscript NVARCHAR(MAX);\nSET @sqlscript = N'\nSELECT TOP(6) BusinessEntityID AS RepID, \n  LastName + '', '' + FirstName AS FullName,\n  CAST(SalesLastYear AS FLOAT) AS [2012]\nFROM Sales.vSalesPerson\nWHERE JobTitle = ''Sales Representative''\nORDER BY SalesLastYear DESC;';\n \n-- run procedure, using Python script and T-SQL query\nEXEC sp_execute_external_script\n  @language = N'Python',\n  @script = @pscript,\n  @input_data_1 = @sqlscript;\nGO<\/pre>\n\n\n\n
      The example uses the sp_execute_external_script<\/strong> stored procedure to retrieve the SQL Server data and create the df1<\/strong> data frame. As you\u2019ve seen in the previous articles, you start by declaring the @pscript<\/strong> variable and assigning the Python script as its value. Next, you declare the @sqlscript<\/strong> variable and assign the T-SQL statement as its value. The T-SQL statement returns sales information from the vSalesPerson<\/strong> view. However, the statement returns only the top six rows, based on values in the SalesLastYear<\/strong> column, sorted in descending order. I\u2019ve renamed the column to 2012<\/strong> to indicate the year of sales. (I picked this year just for demonstration purposes. It is not meant to reflect the dates associated with the underlying data.)<\/p>\n\n\n\n
      With the @pscript<\/strong> and @sqlscript<\/strong> variables defined, you can then pass them in as parameter values when calling the sp_execute_external_script<\/strong> stored procedure. Again, refer to the first article in this series if you have questions about how any of this works.<\/p>\n\n\n\n
      Returning now to the Python script, you can see that it includes only a few statements. The first one assigns the data from the InputDataSet<\/strong> variable to the df1<\/strong> variable:<\/p>\n\n\n\n
      df1 = InputDataSet<\/pre>\n\n\n\n
      The InputDataSet<\/strong> variable contains the data returned by the T-SQL statement, which is passed into the df1<\/strong> variable. The df1<\/strong> variable is created as a DataFrame<\/strong> object. You can make changes to the data frame by calling the variable. For example, the next Python statement converts the 2012<\/strong> column to the int<\/strong> data type:<\/p>\n\n\n\n
      df1[\"2012\"] = df1[\"2012\"].astype(int)<\/pre>\n\n\n\n
      This is the first time we\u2019ve used the astype<\/strong> function in this series. You call the function by tagging it onto the column reference (using a period to separate the two) and then specifying the data type. Be aware, however, when using the astype<\/strong> function to convert a column from float<\/strong> to int<\/strong>, Python merely truncates the values, without rounding them up or now. In this case, it\u2019s not a big deal, but if you need more precision, you should use the round<\/strong> function. You can also round the values within the T-SQL statement.<\/p>\n\n\n\n
      That\u2019s all you need to do to set up the first data frame. You can verify that the data frame has been created the way you want it by calling the print<\/strong> function and passing in df1<\/strong> as the parameter value:<\/p>\n\n\n\n
      print(df1)<\/pre>\n\n\n\n
      The following figure shows the data returned by the print<\/strong> statement, as it appears in the SSMS console on my system. Your results should look similar to this.<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      <\/p>\n\n\n\n
      The results include the six rows returned by the T-SQL statement, with the 2012<\/strong> values truncated to integers. Notice that Python automatically generates a 0-based index for the data frame, which is separate from the RepID<\/strong> column.<\/p>\n\n\n\n
      Creating a data frame based on data from .csv files<\/h2>\n\n\n\n
      With the first data frame in place, you\u2019re ready to create the second one. For this, you\u2019ll need to create six.csv files similar to those shown in the following figure. You can download the files from the link at the bottom of the article.<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      <\/p>\n\n\n\n
      Each file includes the sales rep ID in the file name. This is the same ID returned by the RepID<\/strong> column of the df1<\/strong> data set. Each file also contains the rep\u2019s annual sales for five years (2013 through 2017), with some files containing additional information:<\/p>\n\n\n
      \n