{"id":25758,"date":"2019-12-16T09:00:04","date_gmt":"2019-12-16T16:00:04","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/dotnet\/?p=25758"},"modified":"2020-01-23T12:22:36","modified_gmt":"2020-01-23T19:22:36","slug":"an-introduction-to-dataframe","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/dotnet\/an-introduction-to-dataframe\/","title":{"rendered":"An Introduction to DataFrame"},"content":{"rendered":"

Last month, we announced .NET support for Jupyter notebooks, and showed how to use them to work with .NET for Apache Spark and ML.NET. Today, we’re announcing the preview of a DataFrame<\/a> type for .NET to make data exploration easy. If you’ve used Python to manipulate data in notebooks, you’ll already be familiar with the concept of a DataFrame. At a high level, it is an in-memory representation of structured data. In this blog post, I’m going to give an overview of this new type and how you can use it from Jupyter notebooks. To play along, fire up a .NET Jupyter Notebook in a browser<\/a>.<\/p>\n

How to use DataFrame?<\/h2>\n

DataFrame<\/code> stores data as a collection of columns. Let’s populate a DataFrame<\/code> with some sample data and go over the major features. The full sample can be found on Github(C#<\/a> and F#<\/a>). To follow along in your browser, click here<\/a> and navigate to csharp\/Samples\/DataFrame-Getting Started.ipynb<\/em>(or fsharp\/Samples\/DataFrame-Getting Started.ipynb<\/em>). To get started, let’s import the Microsoft.Data.Analysis<\/a> package and namespace into our .NET Jupyter Notebook (make sure you’re using the C# or F# kernel):<\/p>\n

\"Microsoft.Data.Analysis<\/p>\n

Let’s make three columns to hold values of types DateTime<\/code>, int<\/code> and string<\/code>.<\/p>\n

PrimitiveDataFrameColumn<DateTime> dateTimes = new PrimitiveDataFrameColumn<DateTime>(\"DateTimes\"); \/\/ Default length is 0.\nPrimitiveDataFrameColumn<int> ints = new PrimitiveDataFrameColumn<int>(\"Ints\", 3); \/\/ Makes a column of length 3. Filled with nulls initially\nStringDataFrameColumn strings = new StringDataFrameColumn(\"Strings\", 3); \/\/ Makes a column of length 3. Filled with nulls initially\n<\/code><\/pre>\n
PrimitiveDataFrameColumn<\/code> is a generic column that can hold primitive types such as int<\/code>, float<\/code>, decimal<\/code> etc. A StringDataFrameColumn<\/code> is a specialized column that holds string<\/code> values. Both the column types can take a length<\/code> parameter in their contructors and are filled with null<\/code> values initially. Before we can add these columns to a DataFrame<\/code> though, we need to append three values to our dateTimes<\/code> column. This is because the DataFrame<\/code> constructor expects all its columns to have the same length.<\/p>\n
\/\/ Append 3 values to dateTimes\ndateTimes.Append(DateTime.Parse(\"2019\/01\/01\"));\ndateTimes.Append(DateTime.Parse(\"2019\/01\/01\"));\ndateTimes.Append(DateTime.Parse(\"2019\/01\/02\"));\n<\/code><\/pre>\n
Now we’re ready to create a DataFrame<\/code> with three columns.<\/p>\n
DataFrame df = new DataFrame(dateTimes, ints, strings); \/\/ This will throw if the columns are of different lengths\n<\/code><\/pre>\n
One of the benefits of using a notebook for data exploration is the interactive REPL. We can enter df<\/code> into a new cell and run it to see what data it contains. For the rest of this post, we’ll work in a .NET Jupyter environment. All the sample code will work in a regular console app as well though.<\/p>\n
\"Array<\/p>\n
We immediately see that the formatting of the output can be improved. Each column is printed as an array of values and we don’t see the names of the columns. If df<\/code> had more rows and columns, the output would be hard to read. Fortunately, in a Jupyter environment, we can write custom formatters for types. Let’s write a formatter for DataFrame<\/code>.<\/p>\n
using Microsoft.AspNetCore.Html;\nFormatter<DataFrame>.Register((df, writer) =>\n{\n    var headers = new List<IHtmlContent>();\n    headers.Add(th(i(\"index\")));\n    headers.AddRange(df.Columns.Select(c => (IHtmlContent) th(c.Name)));\n    var rows = new List<List<IHtmlContent>>();\n    var take = 20;\n    for (var i = 0; i < Math.Min(take, df.Rows.Count); i++)\n    {\n        var cells = new List<IHtmlContent>();\n        cells.Add(td(i));\n        foreach (var obj in df.Rows[i])\n        {\n            cells.Add(td(obj));\n        }\n        rows.Add(cells);\n    }\n\n    var t = table(\n        thead(\n            headers),\n        tbody(\n            rows.Select(\n                r => tr(r))));\n\n    writer.Write(t);\n}, \"text\/html\");\n<\/code><\/pre>\n
This snippet of code register a new DataFrame<\/code> formatter. All subsequent evaluations of df<\/code> in a notebook will now output the first 20 rows of a DataFrame<\/code> along with the column names. In the future, the DataFrame<\/code> type and other libraries that target Jupyter as one of their environments will be able to ship with their formatters.<\/p>\n
\"Print<\/p>\n
Sure enough, when we re-evaluate df<\/code>, we see that it contains the three columns we created previously. The formatting makes it much easier to inspect our values. There’s also a helpful index<\/code> column in the output to quickly see which row we’re looking at. Let’s modify our data by indexing into df<\/code>:<\/p>\n
df[0, 1] = 10; \/\/ 0 is the rowIndex, and 1 is the columnIndex. This sets the 0th value in the Ints columns to 10\n<\/code><\/pre>\n
\"DataFrameIndexing\"<\/p>\n
We can also modify the values in the columns through indexers defined on PrimitiveDataFrameColumn<\/code> and StringDataFrameColumn<\/code>:<\/p>\n
\/\/ Modify ints and strings columns by indexing\nints[1] = 100;\nstrings[1] = \"Foo!\";\n<\/code><\/pre>\n
\"ColumnIndexers\"<\/p>\n
One caveat to keep in mind here is the data type of the value passed in to the indexers. We passed in the right data types to the column indexers in our sample: an integer value of 100<\/code> to ints[1]<\/code> and a string \"Foo!\"<\/code> to string[1]<\/code>. If the data types don’t match, an exception will be thrown. For cases where the type of data in the columns is not obvious, there is a handy DataType<\/code> property defined on each column. The Info<\/code> method displays the DataType<\/code> and Length<\/code> properties of each column:<\/p>\n
\"Info\"<\/p>\n
The DataFrame<\/code> and DataFrameColumn<\/code> classes expose a number of useful APIs: binary operations, computations, joins, merges, handling missing values and more. Let’s look at some of them:<\/p>\n
\/\/ Add 5 to Ints through the DataFrame\ndf[\"Ints\"].Add(5, inPlace: true);\n<\/code><\/pre>\n
\"Add\"<\/p>\n
\/\/ We can also use binary operators. Binary operators produce a copy, so assign it back to our Ints column \ndf[\"Ints\"] = (ints \/ 5) * 100;\n<\/code><\/pre>\n
\"BinaryOperations\"<\/p>\n
All binary operators are backed by functions that produces a copy by default. The +<\/code> operator, for example, calls the Add<\/code> method and passes in false<\/code> for the inPlace<\/code> parameter. This lets us elegantly manipulate data using operators without worrying about modifying our existing values. For when in place semantics are desired, we can set the inPlace<\/code> parameter to true<\/code> in the binary functions.<\/p>\n
In our sample, df<\/code> has null<\/code> values in its columns. DataFrame<\/code> and DataFrameColumn<\/code> offer an API to fill nulls<\/code> with values.<\/p>\n
df[\"Ints\"].FillNulls(-1, inPlace: true);\ndf[\"Strings\"].FillNulls(\"Bar\", inPlace: true);\n<\/code><\/pre>\n
\"Fill<\/p>\n
DataFrame<\/code> exposes a Columns<\/code> property that we can enumerate over to access our columns and a Rows<\/code> property to access our rows. We can index Rows<\/code> to access each row. Here’s an example that accesses the first row:<\/p>\n
DataFrameRow row0 = df.Rows[0];\n<\/code><\/pre>\n
\"Access<\/p>\n
To inspect our values better, let’s write a formatter for DataFrameRow<\/code> that displays values in a single line.<\/p>\n
using Microsoft.AspNetCore.Html;\nFormatter<DataFrameRow>.Register((dataFrameRow, writer) =>\n{\n    var cells = new List<IHtmlContent>();\n    cells.Add(td(i));\n    foreach (var obj in dataFrameRow)\n    {\n        cells.Add(td(obj));\n    }\n\n    var t = table(\n        tbody(\n            cells));\n\n    writer.Write(t);\n}, \"text\/html\");\n<\/code><\/pre>\n
\"Access<\/p>\n
To enumerate over all the rows in a DataFrame<\/code>, we can write a simple for loop. DataFrame.Rows.Count<\/code> returns the number of rows in a DataFrame<\/code> and we can use the loop index to access each row.<\/p>\n
for (long i = 0; i < df.Rows.Count; i++)\n{\n       DataFrameRow row = df.Rows[i];\n}\n<\/code><\/pre>\n
Note that each row is a view of the values in the DataFrame<\/code>. Modifying the values in the row<\/code> object modifies the values in the DataFrame<\/code>. We do however lose type information on the returned row<\/code> object. This is a consequence of DataFrame<\/code> being a loosely typed data structure.<\/p>\n
Let’s wrap up our DataFrame<\/code> API tour by looking at the Filter<\/code>, Sort<\/code>, GroupBy<\/code> methods:<\/p>\n
\/\/ Filter rows based on equality\nPrimitiveDataFrameColumn<bool> boolFilter = df[\"Strings\"].ElementwiseEquals(\"Bar\");\nDataFrame filtered = df.Filter(boolFilter);\n<\/code><\/pre>\n
\"DataFrame<\/p>\n
ElementwiseEquals<\/code> returns a PrimitiveDataFrameColumn<bool><\/code> filled with a true<\/code> for every row that equals \"Bar\"<\/code> in the Strings<\/code> column, and a false<\/code> when it doesn’t equal \"Bar\"<\/code>. In the df.Filter<\/code> call, each row corresponding to a true<\/code> value in boolFilter<\/code> selects a row out of df<\/code>. The resulting DataFrame<\/code> contains only these rows.<\/p>\n
\/\/ Sort our dataframe using the Ints column\nDataFrame sorted = df.Sort(\"Ints\");\n\/\/ GroupBy \nGroupBy groupBy = df.GroupBy(\"DateTimes\");\n<\/code><\/pre>\n
\"Sort<\/p>\n
The GroupBy<\/code> method takes in the name of a column and creates groups based on unique values in the column. In our sample, the DateTimes<\/code> column has two unique values, so we expect one group to be created for 2019-01-01 00:00:00Z<\/code> and one for 2019-01-02 00:00:00Z<\/code>.<\/p>\n
\/\/ Count of values in each group\nDataFrame groupCounts = groupBy.Count();\n\/\/ Alternatively find the sum of the values in each group in Ints\nDataFrame intGroupSum = groupBy.Sum(\"Ints\");\n<\/code><\/pre>\n
\"GroupBy<\/p>\n
The GroupBy<\/code> object exposes a set of methods that can called on each group. Some examples are Max()<\/code>, Min()<\/code>, Count()<\/code> etc. The Count()<\/code> method counts the number of values in each group and return them in a new DataFrame<\/code>. The Sum(\"Ints\")<\/code> method sums up the values in each group.<\/p>\n
Finally, when we want to work with existing datasets, DataFrame<\/code> exposes a LoadCsv<\/code> method.<\/p>\n
DataFrame csvDataFrame = DataFrame.LoadCsv(\"path\/to\/file.csv\");\n<\/code><\/pre>\n
Charting<\/h2>\n
Another cool feature of using a DataFrame<\/code> in a .NET Jupyter environment is charting. XPlot.Plotly<\/a> is one option to render charts. We can import the XPlot.Plotly<\/code> namespace into our notebook and create interactive visualizations of the data in our DataFrame<\/code>. Let’s populate a PrimitiveDataFrameColumn<double><\/code> with a normal distribution and plot a histogram of the samples:<\/p>\n
#r \"nuget:MathNet.Numerics,4.9.0\"\nusing XPlot.Plotly;\nusing System.Linq;\nusing MathNet.Numerics.Distributions;\n\ndouble mean = 0;\ndouble stdDev = 0.1;\nMathNet.Numerics.Distributions.Normal normalDist = new Normal(mean, stdDev);\n\nPrimitiveDataFrameColumn<double> doubles = new PrimitiveDataFrameColumn<double>(\"Normal Distribution\", normalDist.Samples().Take(1000));\ndisplay(Chart.Plot(\n    new Graph.Histogram()\n    {\n        x = doubles,\n        nbinsx = 30\n    }\n));\n<\/code><\/pre>\n
\"Chart\"<\/p>\n
We first create a PrimitiveDataFrameColumn<double><\/code> by drawing 1000 samples from a normal distribution and then plot a histogram with 30 bins. The resulting chart is interactive! Hovering over the chart reveals the underlying data and lets us inspect each value precisely.<\/p>\n
Summary<\/h2>\n
We’ve only explored a subset of the features that DataFrame<\/code> exposes. Append<\/code>, Join<\/code>, Merge<\/code>, and Aggregations<\/code> are supported. Each column also implements IEnumerable<T?><\/code>, so users can write LINQ queries on columns. The custom DataFrame<\/code> formatting code we wrote has a simple example. The complete source code(and documentation) for Microsoft.Data.Analysis<\/code> lives on GitHub<\/a>. In a follow up post, I’ll go over how to use DataFrame<\/code> with ML.NET and .NET for Spark. The decision to use column major backing stores (the Arrow format in particular) allows for zero-copy in .NET for Spark User Defined Functions (UDFs)!<\/p>\n
We always welcome the community’s feedback! In fact, please feel free to contribute to the source code<\/a>. We’ve made it easy for users to create new column types that derive from DataFrameColumn<\/code> to add new functionality. Support for structs such as DateTime<\/code> and user defined structs is also not as complete as primitive types such as int<\/code>, float<\/code> etc. We believe this preview package allows the community to do data analysis in .NET. Try out DataFrame<\/a> in a .NET Jupyter Notebook<\/a> and let us know what you think!<\/p>\n","protected":false},"excerpt":{"rendered":"
Last month, we announced .NET support for Jupyter notebooks, and showed how to use them to work with .NET for Apache Spark and ML.NET. Today, we’re announcing the preview of a DataFrame type for .NET to make data exploration easy. If you’ve used Python to manipulate data in notebooks, you’ll already be familiar with the […]<\/p>\n","protected":false},"author":11778,"featured_media":58792,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[685,196,756,636,688,691],"tags":[2855,30,43,437,107,117],"class_list":["post-25758","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dotnet","category-dotnet-core","category-csharp","category-fsharp","category-machine-learning","category-ml-dotnet","tag-analytics","tag-announcement","tag-bcl","tag-data","tag-open-source","tag-releases"],"acf":[],"blog_post_summary":"
Last month, we announced .NET support for Jupyter notebooks, and showed how to use them to work with .NET for Apache Spark and ML.NET. Today, we’re announcing the preview of a DataFrame type for .NET to make data exploration easy. If you’ve used Python to manipulate data in notebooks, you’ll already be familiar with the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/25758","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/users\/11778"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/comments?post=25758"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/25758\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media\/58792"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media?parent=25758"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/categories?post=25758"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/tags?post=25758"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}