{"id":76391,"date":"2017-12-18T10:44:42","date_gmt":"2017-12-18T10:44:42","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=76391"},"modified":"2022-04-27T21:23:37","modified_gmt":"2022-04-27T21:23:37","slug":"sql-server-machine-learning-services-part-2-python-data-frames","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sql-server-machine-learning-services-part-2-python-data-frames\/","title":{"rendered":"SQL Server Machine Learning Services \u2013 Part 2: Python Data Frames"},"content":{"rendered":"

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 If you\u2019ve spent any time with the R language in SQL Server R Services or Machine Learning Services (MLS), you\u2019re no doubt aware of the important role that data frames can play in your scripts, whether working with data that comes from a SQL Server database or from another source. The same is true for Python. You use data frames when passing data sets into and out of a script as well as when manipulating and analyzing data within that script.\n<\/p>\n

    \n This article focuses on using data frames in Python. It is the second article in a series about MLS and Python. The first article introduced you briefly to data frames. This article continues that discussion, describing how to work with data frame objects and the data within those objects.\n<\/p>\n

    \n Data frames and the functions they support are available to MLS and Python through the pandas library. The library is available as a Python module that provides tools for analyzing and manipulating data, including the ability to generate data frame objects and work with data frame data. The pandas library is included by default in MLS, so the functions and data structures available to pandas are ready to use, without having to manually install pandas in the MLS library.\n<\/p>\n

    \n This article includes a number of examples that leverage the pandas library to demonstrate various data frame concepts, using data from the AdventureWorks2017<\/strong> sample database. As you\u2019ll recall from the first article, you can specify a T-SQL query when calling the sp_execute_external_script<\/strong> stored procedure and then incorporate the data returned by that query into your Python script. The examples that follow all use the same T-SQL query, allowing us to focus specifically on the Python scripts and data frame concepts.\n<\/p>\n

    Introducing the data frame<\/h2>\n

    \n The pandas library supports a variety of data structures. The two most commonly used for analytics are Series<\/strong> and DataFrame<\/strong>. The Series<\/strong> data structure is an indexed, one-dimensional array whose values can be any Python data type, including strings, integers, floating point numbers, and other Python objects. The DataFrame<\/strong> data structure is an indexed, two-dimensional array that includes one or more columns, similar to an Excel spreadsheet or SQL Server table. All columns in a data frame are Series<\/strong> objects of the same length.\n<\/p>\n

    \n To better understand the Series<\/strong> and DataFrame<\/strong> data structures, take a look at the following example, which calls the sp_execute_external_script<\/strong> stored procedure, passing in a Python script and T-SQL query:\n<\/p>\n

    \r\nUse AdventureWorks2017;\r\nGO\r\n\r\n-- define Python script\r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], as_index=False).sum()\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return aggregated data set\r\nprint(df2)\r\nOutputDataSet = df2';\r\n\r\n-- define T-SQL query\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\n-- run procedure, using Python script and T-SQL query\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS(\r\n    (Territories NVARCHAR(100), OrderMonths NCHAR(3), TotalSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  If you reviewed the first article in this series, most of the elements should look familiar to you. The example first defines the Python script, saving it to the @pscript<\/strong> variable, and then defines the T-SQL query, saving it to the @sqlscript<\/strong> variable. Both variables are passed in as parameter values when calling the sp_execute_external_script<\/strong> stored procedure. The procedure call also specifies the Python language and the name and type of the columns in the output data set.\n<\/p>\n
    \n  Refer to the first article in this series if you have questions about working with the sp_execute_external_script<\/strong> stored procedure.\n<\/p>\n
    \n  With that in mind, let\u2019s return to the example above, starting with the first statement in the Python script, which imports the calendar<\/strong> module and assigns the cl<\/strong> alias to the module:\n<\/p>\n
    \r\n  import calendar as cl\r\n<\/pre>\n
    \n  The calendar<\/strong> module provides tools for working with date values. When you import a module in this way, you can use its data structures and functions in your Python script by referencing the alias.\n<\/p>\n
    \n  After importing the module, the Python script assigns the data in the InputDataSet<\/strong> variable to the df1<\/strong> variable:\n<\/p>\n
    \r\n  df1 = InputDataSet\r\n<\/pre>\n
    \n  As you\u2019ll recall from the first article, the InputDataSet<\/strong> variable provides access to the SQL Server data returned by the T-SQL query specified when calling the sp_execute_external_script<\/strong> stored procedure. By default, the variable is defined as a DataFrame<\/strong> object, which means that the df1<\/strong> variable is also created as a DataFrame<\/strong> object, as is the df2<\/strong> variable.\n<\/p>\n
    \n  After assigning the data to the df1<\/strong> variable, the script groups and aggregates the data in the df1<\/strong> data set and then assigns the data set to the df2<\/strong> variable:\n<\/p>\n
    \r\n  df2 = df1.groupby([\"Territories\", \"OrderMonths\"], as_index=False).sum()\r\n<\/pre>\n
    \n  A DataFrame<\/strong> object such as df1<\/strong> or df2<\/strong> provides a number of functions for working with the data frame and its data. One of these functions is groupby<\/strong>, which you call by adding a period after the DataFrame<\/strong> object (in this case, df1<\/strong>) and then specifying the function name.\n<\/p>\n
    \n  The first argument you pass to the function is the column or columns on which to base the grouping. When specifying multiple columns, you must enclose the column names in brackets and separate them with a comma. In this case, the data will be grouped first by the Territories<\/strong> column and then by the OrderMonths<\/strong> column.\n<\/p>\n
    \n  The second argument you pass to the groupby<\/strong> function is as_index<\/strong>, which is set to False<\/strong>. This argument is related to how data frames are indexed and how Python returns data frame values. We\u2019ll get into these topics more in a bit. Just know you must set this option to False<\/strong> to ensure that the values from the Territories<\/strong> and OrderMonths<\/strong> columns are returned to the calling application along with the aggregated sales data. The setting also ensures that the data is returned in \u2018SQL-style,\u2019 according to the pandas document pandas.DataFrame.groupby<\/a>.\n<\/p>\n
    \n  After specifying the groupby<\/strong> function, you must then tag on an aggregate function that specifies how the data should be summarized. In this case, the sum<\/strong> function is used to find the sales totals for each month for each territory.\n<\/p>\n
    \n  One other note about the working with DataFrame<\/strong> functions: If calling a function outside of the context of a DataFrame<\/strong> object, you must first import the pandas<\/strong> module, using a statement such as the following:\n<\/p>\n
    \r\n  import pandas as pd\r\n<\/pre>\n
    \n  You can then use the module\u2019s alias when calling the specific function. For example, if you want to create a series, you can use the Series<\/strong> function:\n<\/p>\n
    \r\n  data = [\"one\", 2, \"three\", 4.3456]\r\n ds = pd.Series(data)\r\n<\/pre>\n
    \n  The function generates a Series<\/strong> object based on the data<\/strong> variable. The object is then assigned to the ds<\/strong> variable. Python will also automatically generate an integer-based index for the Series<\/strong> object, starting with 0.\n<\/p>\n
    \n  As noted earlier, each column in the data frame is a Series<\/strong> object. You can test this out by including a statement similar to the following in your script:\n<\/p>\n
    \r\n  print(type(df2[\"Territories\"]))\r\n<\/pre>\n
    \n  The type<\/strong> function returns the data type of the Territories<\/strong> column and the print<\/strong> function returns the data type to the console, confirming that the variable\u2019s type is pandas.core.series.Series<\/strong>.\n<\/p>\n
    \n  After aggregating the data, the Python script converts the OrderMonths<\/strong> values to a more readable format. If you run the T-SQL query in the above example separately from the stored procedure, you\u2019ll see that the OrderMonths<\/strong> column returns numerical values to represent the months (the integers 1 through 12). To make the output more readable, the Python script creates a lambda function to convert these values into abbreviated month names:\n<\/p>\n
    \r\n  df2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n<\/pre>\n
    \n  A lambda function is simply an anonymous, unnamed function. To use a lambda function to convert values in a column, you first specify the column by providing the data frame name followed by the column name, enclosed in brackets. Next, you tag on the pandas apply<\/strong> function, which tells Python to run the lambda function against every value in the specified column.\n<\/p>\n
    \n  The lambda function is passed as an argument to the apply<\/strong> function. The function definition begins with the keyword lambda<\/strong>, followed by the x<\/strong> variable and a colon. The function definition then calls the month_abbr<\/strong> function from the calendar<\/strong> module (using the cl<\/strong> alias) and passes the x<\/strong> variable in as an argument, enclosed in brackets. In this way, the month_abbr<\/strong> function will be applied to each of the column\u2019s integers, changing them to month abbreviations.\n<\/p>\n
    \n  The final part of the Python script includes two statements that return the final data frame. The first statement uses the print<\/strong> function to return the data frame to the console:\n<\/p>\n
    \r\n  print(df2)\r\n<\/pre>\n
    \n  The following figure shows how the returned data looks in SQL Server Management Studio (SSMS).\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig01b.png\"\n<\/p>\n
    \n  The first column, which is unnamed, is the data frame\u2019s index. As with Series<\/strong> objects, Python generates the index automatically, starting with 0; however, you can change the index, reset it, or designate one of the columns as the index. The important point to remember is that a data frame, by default, is automatically assigned an index, which can be a factor when manipulating and analyzing data.\n<\/p>\n
    \n  The final statement in the Python script assigns the df2<\/strong> variable to the OutputDataSet<\/strong> variable:\n<\/p>\n
    \r\n  OutputDataSet = df2\r\n<\/pre>\n
    \n  The OutputDataSet<\/strong> variable is the default variable used to return the script\u2019s data to a calling application. Like the InputDataSet<\/strong> variable, the OutputDataSet<\/strong> variable is defined as a DataFrame<\/strong> object. The implication of this is that, when working with Python in SQL Server, you must pass your data to the OutputDataSet<\/strong> variable as a DataFrame<\/strong> object in order to return that data to a calling application. The following figure shows part of the data set returned by the OutputDataSet<\/strong> variable.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig01a.png\"\n<\/p>\n
    \n  The integers shown in the first column are the row number labels used by SSMS. These values are not the index. The index is not included in the results. To include the index in the OutputDataSet<\/strong> results, you should use the reset_index<\/strong> function to turn that index into a column. For example, you can include the following statement in your script:\n<\/p>\n
    \r\n  df2.reset_index(inplace=True)\r\n<\/pre>\n
    \n  The reset_index<\/strong> function turns the existing index into a column and generates a new index. For this example, the function takes only the inplace<\/strong> argument, which is available to a number of functions. When set to True<\/strong>, the argument tells Python to update the data frame in place, without needing to reassign the data frame to itself or assign it to a new variable.\n<\/p>\n
    \n  Also note that the results shown in the above figure include the Territories<\/strong> and OrderMonths<\/strong> columns. If you had set the as_index<\/strong> argument to True<\/strong> or had not included the argument when calling the groupby<\/strong> function, the results would include only the TotalSales<\/strong> column.\n<\/p>\n
    \n  You can retrieve information about a data frame by calling its properties. For example, the following Python script retrieves the column names and dimensions of the df2<\/strong> data frame:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], as_index=False).sum()\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return information about data df2 data frame\r\nprint(list(df2.columns.values))\r\nprint(df2.shape)\r\nprint(len(df2.index))';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript;     \r\nGO\r\n<\/pre>\n
    \n  The first print<\/strong> statement uses the list<\/strong> function and columns.values<\/strong> property to return a list of the columns in the df2<\/strong> data frame. The shape<\/strong> property returns the data frame\u2019s dimensions (number of rows and columns). The index<\/strong> property returns the data frame\u2019s index. If you use the index<\/strong> property in conjunction with the len<\/strong> function, you\u2019ll get the number of values in the index. (The len<\/strong> function returns the number of items in an object.) The Python scripts returns the following results:\n<\/p>\n
    \r\n['Territories', 'OrderMonths', 'Sales']\r\n(120, 3)\r\n120\r\n<\/pre>\n
    \n  The DataFrame<\/strong> class supports a wide range of properties (attributes) and functions (methods), making it possible to work with data in a variety of ways. For more information about the DataFrame<\/strong> class, see the topic pandas.DataFrame<\/a> in the pandas documentation.\n<\/p>\n
    Adding columns to a data frame<\/h2>\n
    \n  Whether your data frames are based on SQL Server data or come from another source (or are a combination of both), there might be times when you\u2019ll want to add columns. One way to do this is to aggregate the same source column in two different ways when calling the groupby<\/strong> function, as shown in the following example:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return df2 data set\r\nOutputDataSet = df2';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), \r\n    OrderMonths NCHAR(3), AvgSales MONEY, TotalSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  You should call the groupby<\/strong> function just like you did in the previous examples, but rather than tagging on the sum<\/strong> aggregate function at the end of the groupby<\/strong> function call, you specify the agg<\/strong> function, passing in a dictionary (dict<\/strong>) value as an argument. The dictionary value specifies the Sales<\/strong> column and two aggregate functions, mean<\/strong> and sum<\/strong>. The following figure shows part of the results that the Python script returns.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig03.png\"\n<\/p>\n
    \n  The data set now includes the AvgSales<\/strong> and TotalSales<\/strong> column, which are both based on the Sales<\/strong> column in the df1<\/strong> data frame.\n<\/p>\n
    \n  You can also insert columns into a data frame more directly, without having to define another aggregation. For example, the following Python script generates TargetSales<\/strong> sales, a computed column that adds 18% to the TotalSales<\/strong> values:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# assign column names and add computed column\r\ndf2.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf2[\"TargetSales\"] = df2[\"TotalSales\"] * 1.18\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return df2 data set\r\nOutputDataSet = df2';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), OrderMonths NCHAR(3),\r\n    AvgSales MONEY, TotalSales MONEY, TargetSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  Before adding the column, I assigned names to the columns in the df2<\/strong> data frame. I took this step to make it easier to reference those columns later in the script, which is especially beneficial when working with aggregated columns. (The pandas library also provides methods for naming aggregated and non-aggregated columns individually, but we\u2019ll save that topic for a separate discussion.)\n<\/p>\n
    \n  Instead of naming columns, you can reference columns by their internal index, but that can sometimes add more complexity than necessary. Personally, I prefer the readability that comes with using actual names.\n<\/p>\n
    \n  To add column labels, first call the columns<\/strong> property on the df2<\/strong> data frame and then assign the appropriate values to the property, providing each column name in the order the columns appears. You should enclose the column names in brackets and separate them with commas.\n<\/p>\n
    \n  After renaming the columns, you can add the new column. Start by specifying the df2<\/strong> data frame, followed by the new column name in brackets. Then set its value to an expression that calculates the values for the new column. In the example above, the expression simply multiples the TotalSales<\/strong> values by 1.18. The following figures shows part of the results now returned by the Python script.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig04.png\"\n<\/p>\n
    \n  Whenever you change your data frame in a way that will impact the columns retuned by the Python script, be sure to update the WITH<\/strong> RESULT<\/strong> SETS<\/strong> clause as necessary when calling the sp_execute_external_script<\/strong> stored procedure.\n<\/p>\n
    Filtering data in a data frame<\/h2>\n
    \n  The pandas library provides a variety of methods for filtering your data frame. For example, the following Python script uses the head<\/strong> function to return the first five rows from the data set:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# assign column names and add computed column\r\ndf2.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf2[\"TargetSales\"] = df2[\"TotalSales\"] * 1.18\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return top five rows\r\nOutputDataSet = df2.head(5)';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), OrderMonths NCHAR(3),\r\n    AvgSales MONEY, TotalSales MONEY, TargetSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  To call the head<\/strong> function, you need only tag it onto the DataFrame<\/strong> object, optionally specifying the number of rows to return. If you don\u2019t specify a number, Python will return five rows. The following figures shows the results returned by the Python script.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig05.png\"\n<\/p>\n
    \n  The pandas library also includes the tail<\/strong> function, which works the same way as the head<\/strong> function, except that it filters out all but the last rows in the data frame, instead of the first.\n<\/p>\n
    \n  You can also specify which columns to retain in a data set:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# assign column names and add computed column\r\ndf2.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf2[\"TargetSales\"] = df2[\"TotalSales\"] * 1.18\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return specific columns\r\nOutputDataSet = df2[[\"Territories\", \"OrderMonths\", \"TotalSales\"]]';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), OrderMonths NCHAR(3), TotalSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  When including multiple columns, enclose them in a second set of brackets and separate the column names with commas. Python will then filter out all but the specified columns. The following figure shows part of the script\u2019s results.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig06.png\"\n<\/p>\n
    \n  You can also add the head<\/strong> or tail<\/strong> function after specifying other filter criteria:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# assign column names and add computed column\r\ndf2.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf2[\"TargetSales\"] = df2[\"TotalSales\"] * 1.18\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return specific columns, last five rows\r\nOutputDataSet = df2[[\"Territories\", \"OrderMonths\", \"TotalSales\"]].tail(5)';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), OrderMonths NCHAR(3), TotalSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  Now the returned data set includes only three columns and last five rows, as shown in the following figure.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig07.png\"\n<\/p>\n
    \n  In addition to columns, you can filter rows out of a data frame, based on conditional expressions you specify when calling the columns. For example, the following statement filters out all rows except those with a Territories<\/strong> value of France<\/strong> and TotalSales<\/strong> value greater than 500000:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# assign column names and add computed column\r\ndf2.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf2[\"TargetSales\"] = df2[\"TotalSales\"] * 1.18\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return specific rows\r\nOutputDataSet = df2[(df2[\"Territories\"] == \"France\") & (df2[\"TotalSales\"] > 500000)]';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), OrderMonths NCHAR(3),\r\n    AvgSales MONEY, TotalSales MONEY, TargetSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  The first expression compares the Territories<\/strong> values to France<\/strong>, using the equals (==<\/strong>) comparison operator. The second expression compares the TotalSales<\/strong> values to 500000, using the greater than (><\/strong>) comparison operator. Each expression is enclosed in parentheses and separated with the and (&<\/strong>) conditional operator, which indicates that both expressions must evaluate to true for a row to be included in the results, as shown in the following figure.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig08.png\"\n<\/p>\n
    \n  As an alternative to what we\u2019ve seen so far, the pandas library provides a set of functions that also make it possible to filter data in a data frame. The functions include at<\/strong>, iat<\/strong>, loc<\/strong>, and iloc<\/strong>. The pandas documentation recommends using these functions when possible because they are optimized for data access. For information about these functions, see the pandas document Indexing and Selecting Data<\/a>.\n<\/p>\n
    \n  One of the most valuable of these functions is loc<\/strong>, which lets you filter out columns, rows, or both. The following syntax provides a simple overview of how to use the function:\n<\/p>\n
    \r\n  <DataFrame>.loc[<rows>, <columns>]\r\n<\/pre>\n
    \n  You specify row information as the first argument and column information as the second argument, separating the two by a comma. If you specify column information without row information, you need only include a colon for the first argument:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# assign column names and add computed column\r\ndf2.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf2[\"TargetSales\"] = df2[\"TotalSales\"] * 1.18\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return specific columns\r\nOutputDataSet = df2.loc[:,[\"Territories\", \"OrderMonths\", \"TotalSales\"]]';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), OrderMonths NCHAR(3), TotalSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  The statement filters out all but the Territories<\/strong>, OrderMonths<\/strong>, and TotalSales<\/strong> columns. The following figure shows part of the results returned by the Python script.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig09.png\"\n<\/p>\n
    \n  If you want to filter out only rows, you can use the colon placeholder for the second argument or leave it off altogether. The following example includes the placeholder:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# assign column names and add computed column\r\ndf2.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf2[\"TargetSales\"] = df2[\"TotalSales\"] * 1.18\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return specific rows\r\nOutputDataSet = df2.loc[(df2[\"Territories\"] == \"France\") &\r\n  (df2[\"TotalSales\"] > 500000),:]';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), OrderMonths NCHAR(3),\r\n    AvgSales MONEY, TotalSales MONEY, TargetSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  Now the script returns all columns, but only few rows, as shown in the following figure.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig10.png\"\n<\/p>\n
    \n  To filter out both rows and columns, you can include both sets of criteria in one statement when using loc<\/strong>:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# assign column names and add computed column\r\ndf2.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf2[\"TargetSales\"] = df2[\"TotalSales\"] * 1.18\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# return specific columns and rows\r\nOutputDataSet = df2.loc[(df2[\"Territories\"] == \"France\") & (df2[\"TotalSales\"] > 500000),\r\n  [\"Territories\", \"OrderMonths\", \"TotalSales\"]]';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), OrderMonths NCHAR(3), TotalSales MONEY));     \r\nGO\r\n<\/pre>\n
    \n  As the following figure shows, the results are now leaner still.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig11.png\"\n<\/p>\n
    \n  Be sure to review the pandas documentation for more information about the different ways you can filter data in a data set. Consider what we\u2019ve covered here as only the beginning.\n<\/p>\n
    Adding rows to a data frame<\/h2>\n
    \n  Earlier in this article, you saw how to add columns to your data frame. With pandas, you can also add rows. In the following example, the Python script generates a new data frame based on filtered data and then adds a row to the data frame:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# calculate sales averages and totals for each territory and month\r\ndf1 = InputDataSet\r\ndf2 = df1.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\n\r\n# assign column names and add computed column\r\ndf2.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf2[\"TargetSales\"] = df2[\"TotalSales\"] * 1.18\r\n\r\n# change month numbers to abbreviated month names\r\ndf2[\"OrderMonths\"] = df2[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\n\r\n# define the df3 data frame and add row\r\ndf3 = df2.loc[(df2[\"OrderMonths\"] == \"Jun\") & (df2[\"TotalSales\"] > 500000),\r\n  [\"Territories\", \"AvgSales\", \"TotalSales\"]]\r\nrow = [\"Australia\", 8257.1674, 830092.7536]\r\ndf3.loc[len(df3)] = row\r\ndf3 = df3.sort_values(by=\"Territories\")\r\n\r\n# return the df3 data set\r\nOutputDataSet = df3';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), AvgSales MONEY, TotalSales MONEY));\r\nGO\r\n<\/pre>\n
    \n  To create the new data frame, the script uses the loc<\/strong> function to filter out all rows except those with an OrderMonths<\/strong> value that equals Jun<\/strong> and a TotalSales<\/strong> value greater than 500000<\/strong>. The function also filters out all but the Territories<\/strong>, AvgSales<\/strong>, and TotalSales<\/strong> columns. The filtered results are then assigned to the df3<\/strong> variable to create the new data frame.\n<\/p>\n
    \n  After defining the df3<\/strong> data frame, the script specifies the values for the new row and saves them to the row<\/strong> variable. The script then uses the loc<\/strong> and len<\/strong> functions to set the value of the new row. The len<\/strong> function returns the number of rows in the data frame, and the loc<\/strong> function interprets that number as an index value for the new row.\n<\/p>\n
    \n  This works because the index starts with 0, so the highest index number before the row is added is 3. The new row is added with an index value of 4 because the index size is four. As a result, the new row is inserted after all the other rows, which means that the territory names are no longer in alphabetical order. To fix this, you can call the sort_values<\/strong> function on the df3<\/strong> data frame, specifying the name of the Territories<\/strong> column. The Python script now returns that data set shown in the following figure.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig12.png\"\n<\/p>\n
    \n  As you can see, the script returns a small data set that includes only five rows and three columns, just what we were after, with the values nicely sorted by territory name. The new row for Australia is included.\n<\/p>\n
    Removing a column from a data frame<\/h2>\n
    \n  You might also run into situations when you want to remove a column from a data frame. One way to do this is to use the loc<\/strong> function to filter out the column and then reassign the data frame to itself or a different variable. Another approach is to use the drop<\/strong> function available to the DataFrame<\/strong> class, as shown in the following example:\n<\/p>\n
    \r\nDECLARE @pscript NVARCHAR(MAX);\r\nSET @pscript = N'\r\nimport calendar as cl\r\n\r\n# define the df1 data frame\r\ndf1 = InputDataSet.groupby([\"Territories\", \"OrderMonths\"], \r\n  as_index=False).agg({\"Sales\": [\"mean\", \"sum\"]})\r\ndf1.columns = [\"Territories\", \"OrderMonths\", \"AvgSales\", \"TotalSales\"]\r\ndf1[\"TargetSales\"] = df1[\"TotalSales\"] * 1.18\r\ndf1[\"OrderMonths\"] = df1[\"OrderMonths\"].apply(lambda x: cl.month_abbr[x])\r\ndf1 = df1.loc[df1[\"TotalSales\"] > 800000]\r\ndf1 = df1.sort_values(by=\"TotalSales\", ascending=False)\r\n\r\n# remove AvgSales column\r\ndf1.drop(\"AvgSales\", axis=1, inplace=True)\r\n\r\n# return df1 data set\r\nOutputDataSet = df1';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT t.Name AS Territories, MONTH(h.OrderDate) AS OrderMonths,\r\n    CAST(h.Subtotal AS FLOAT) AS Sales\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID\r\n  WHERE YEAR(h.OrderDate) = 2013;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'Python',\r\n  @script = @pscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS((Territories NVARCHAR(100), OrderMonths NCHAR(3),\r\n    TotalSales MONEY, TargetSales MONEY));   \r\nGO\r\n<\/pre>\n
    \n  This time round, I consolidated some of the script, although at this point, you should be well familiar with most of these elements. The only new construct is the statement that uses the drop<\/strong> function to remove the AvgSales<\/strong> column.\n<\/p>\n
    \n  When calling the function, first specify the name of the column that should be dropped. Next, add the axis<\/strong> argument, setting its value to 1. This indicates that you\u2019re dropping a column, not the index (which would be 0). The inplace<\/strong> argument comes next. As you saw earlier, it tells Python to update the data frame in place. The Python script returns the data set shown in the following figure.\n<\/p>\n
    \n  \"%20screenshots2\/stPython2_fig13.png\"\n<\/p>\n
    \n  As the example demonstrates, the drop<\/strong> function provides yet another way to remove a column from a data frame. When working with pandas and Python, you\u2019ll find that there are often a number of different ways to carry out a task. You might have to do some digging to figure out the best approach, but sometimes it\u2019s just a matter of preference. The better you understand pandas and Python, the better you\u2019ll understand the options you have available to work with the data.\n<\/p>\n
    Making the most of the data frame<\/h2>\n
    \n  There is, of course, a great deal more to data frames and the pandas library than what we\u2019ve covered here, but this should provide you with a foundation for getting started so you can strike out on your own. The best way to acquaint yourself with Python, pandas, and data frames is to play around with data sets in your Python scripts and experiment with the various ways you can manipulate and analyze data.\n<\/p>\n
    \n  As we progress through this series, we\u2019ll dig more into the Python language and pandas library and some of ways you can work with data. Your understanding of data frames will be instrumental in your ability to move forward with MLS and Python and to perform advanced data analytics.<\/p>\n","protected":false},"excerpt":{"rendered":"
    SQL Server Machine Learning Services provides the ability to run Python scripts directly against data in SQL Server. To do so, you must understand how to work with the data frame object. Robert Sheldon explains how to get started using the data frame object, how to pass data from SQL Server to it, and how to manipulate it with Python and pandas commands. …<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528,146042],"tags":[5134],"coauthors":[6779],"class_list":["post-76391","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","category-python","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/76391","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=76391"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/76391\/revisions"}],"predecessor-version":[{"id":76409,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/76391\/revisions\/76409"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=76391"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=76391"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=76391"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=76391"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}