3 ms·

> NULL indicates a missing value, and NaN is Not a Number. That’s actually less true than it sounds. One of the primary functions of NaN is to be the result of

> NULL indicates a missing value, and NaN is Not a Number. That’s actually less true than it sounds. One of the primary functions of NaN is to be the result of 0/0, so there it means that there could be a value but we don’t know what it is because we didn’t take the limit properly. One of the primary functions of NULL is to say that a tuple satisfies a predicate except we don’t know what this one position is—it’s certainly is something out in the real world, we just don’t know what. These ideas are what motivates the comparison shenanigans both NaN and NULL are known for. There’s certainly an argument to be made that the actual implementation of both of these ideas is half-baked in the respective standards, and that they are half-baked differently so we shouldn’t confuse them. But I don’t think it’s fair to say that they are just completely unrelated. If anything, it’s Python’s None that’s doesn’t belong.

cpcloud 2y ago
100% this. We'd love to have a docs contribution that lays this out in detail if you'd be up for it! Disclaimer: (I lead the project and work on it full time).
aeonik 2y ago
Why don't we build a portable numeric system that just has these mathematical constants and definitions built in, in a portable and performance manner?
- verandaguy 2y ago
  We just don’t have the technology for that, else there’d be libraries floating around that let you do that.
miohtama 2y ago
Related to this, below someone posted a link to the blog post of Wes McKinney where he discussed Pandas limitations and how PyArrow works around these > 4. Doing missing data right > All missing data in Arrow is represented as a packed bit array, separate from the rest of the data. This makes missing data handling simple and consistent across all data types. You can also do analytics on the null bits (AND-ing bitmaps, or counting set bits) using fast bit-wise built-in hardware operators and SIMD. > The null count in an array is also explicitly stored in its metadata, so if data does not have nulls, we can choose faster code paths that skip null checking. With pandas, we cannot assume that arrays do not have null sentinel values and so most analytics has extra null checking which hurts performance. If you have no nulls, you don’t even need to allocate the bit array. > Because missing data is not natively supported in NumPy, over time we have had to implement our own null-friendly versions of most key performance-critical algorithms. It would be better to have null-handling built into all algorithms and memory management from the ground up. https://wesmckinney.com/blog/apache-arrow-pandas-internals/ https://wesmckinney.com/blog/apache-arrow-pandas-internals/
- jononor 2y ago
  Note that the post is from 2017, and pandas now has (optional) support for PyArrow backed dataframes. So there is movement away from the critiques that were presented there.
  - miohtama 2y ago
    Yes I think that early post discussed about upcoming Pandas 2.0 which now realised 7 years later.
OptionOfT 2y ago
> One of the primary functions of NULL is to say that a tuple satisfies a predicate except we don’t know what this one position is—it’s certainly is something out in the real world, we just don’t know what. I'm not sure I understand this. Can you explain it with an example? I'm thinking something like: let (x, y) = NULL; But then I'm stuck.
- kstrauser 2y ago
  In the broader DB theory context and not strictly Pandas, think of a `users` table with a `middle_name` column. There's a difference between `middle_name is null` (we don't know whether they have a middle name or what it is if they do) and `middle_name = ''` (we know that they don't have a middle name). In that case, `select * from users where middle_name is null` gives us a set of users to prod for missing information next time we talk to them. `...where middle_name = ''` gives us a list of people without a middle name, should we care. Edit: A related aside is that Django's ORM's handling of NULL gives me freaking hives. From https://docs.djangoproject.com/en/5.1/ref/models/fields/#null https://docs.djangoproject.com/en/5.1/ref/models/fields/#nul...: > If a string-based field has null=True, that means it has two possible values for “no data”: NULL, and the empty string. In most cases, it’s redundant to have two possible values for “no data;” the Django convention is to use the empty string, not NULL. I've been around numerous Django devs when they found out that other DBs and ORMs do not remotely consider NULL and empty string to be the same thing.
  - emmelaich 2y ago
    In Oracle, "" is converted to NULL so maybe that's where they get that from.
    - kstrauser 2y ago
      Whoa, you're right.[0] I wouldn't have expected that at all. [0] https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Nulls.html#GUID-B0BA4751-9D88-426A-84AD-BCDBD5584071 https://docs.oracle.com/en/database/oracle/oracle-database/1...
    - 2y ago

cpcloud 2y ago

100% this. We'd love to have a docs contribution that lays this out in detail if you'd be up for it! Disclaimer: (I lead the project and work on it full time).

aeonik 2y ago

Why don't we build a portable numeric system that just has these mathematical constants and definitions built in, in a portable and performance manner?

verandaguy 2y ago
We just don’t have the technology for that, else there’d be libraries floating around that let you do that.

miohtama 2y ago

Related to this, below someone posted a link to the blog post of Wes McKinney where he discussed Pandas limitations and how PyArrow works around these > 4. Doing missing data right > All missing data in Arrow is represented as a packed bit array, separate from the rest of the data. This makes missing data handling simple and consistent across all data types. You can also do analytics on the null bits (AND-ing bitmaps, or counting set bits) using fast bit-wise built-in hardware operators and SIMD. > The null count in an array is also explicitly stored in its metadata, so if data does not have nulls, we can choose faster code paths that skip null checking. With pandas, we cannot assume that arrays do not have null sentinel values and so most analytics has extra null checking which hurts performance. If you have no nulls, you don’t even need to allocate the bit array. > Because missing data is not natively supported in NumPy, over time we have had to implement our own null-friendly versions of most key performance-critical algorithms. It would be better to have null-handling built into all algorithms and memory management from the ground up. https://wesmckinney.com/blog/apache-arrow-pandas-internals/ https://wesmckinney.com/blog/apache-arrow-pandas-internals/

jononor 2y ago
Note that the post is from 2017, and pandas now has (optional) support for PyArrow backed dataframes. So there is movement away from the critiques that were presented there.
- miohtama 2y ago
  Yes I think that early post discussed about upcoming Pandas 2.0 which now realised 7 years later.

OptionOfT 2y ago

> One of the primary functions of NULL is to say that a tuple satisfies a predicate except we don’t know what this one position is—it’s certainly is something out in the real world, we just don’t know what. I'm not sure I understand this. Can you explain it with an example? I'm thinking something like: let (x, y) = NULL; But then I'm stuck.

kstrauser 2y ago
In the broader DB theory context and not strictly Pandas, think of a `users` table with a `middle_name` column. There's a difference between `middle_name is null` (we don't know whether they have a middle name or what it is if they do) and `middle_name = ''` (we know that they don't have a middle name). In that case, `select * from users where middle_name is null` gives us a set of users to prod for missing information next time we talk to them. `...where middle_name = ''` gives us a list of people without a middle name, should we care. Edit: A related aside is that Django's ORM's handling of NULL gives me freaking hives. From https://docs.djangoproject.com/en/5.1/ref/models/fields/#null https://docs.djangoproject.com/en/5.1/ref/models/fields/#nul...: > If a string-based field has null=True, that means it has two possible values for “no data”: NULL, and the empty string. In most cases, it’s redundant to have two possible values for “no data;” the Django convention is to use the empty string, not NULL. I've been around numerous Django devs when they found out that other DBs and ORMs do not remotely consider NULL and empty string to be the same thing.
- emmelaich 2y ago
  In Oracle, "" is converted to NULL so maybe that's where they get that from.
  - kstrauser 2y ago
    Whoa, you're right.[0] I wouldn't have expected that at all. [0] https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Nulls.html#GUID-B0BA4751-9D88-426A-84AD-BCDBD5584071 https://docs.oracle.com/en/database/oracle/oracle-database/1...
  - 2y ago