Repository: pandas-dev/pandas
Pull request: GH:11670: possible bug when calculating mean of DataFrame?
- [x] closes #11670
- [x] tests added / passed
- [x] passes `black pandas`
- [x] added test test_mean_mixed_string_float

Problem statement:
possible bug when calculating mean of DataFrame?
I'm trying to calculate the mean of all the columns of a DataFrame but it looks like having a value in the B column of row 6 prevents from calculating the mean on the C column. Possible bug? (pandas: 0.17.1)

``` python
import pandas as pd
from decimal import Decimal
d = [
    {'A': 2, 'B': None, 'C': Decimal('628.00')},
    {'A': 1, 'B': None, 'C': Decimal('383.00')},
    {'A': 3, 'B': None, 'C': Decimal('651.00')},
    {'A': 2, 'B': None, 'C': Decimal('575.00')},
    {'A': 4, 'B': None, 'C': Decimal('1114.00')},
    {'A': 1, 'B': 'TEST', 'C': Decimal('241.00')},
    {'A': 2, 'B': None, 'C': Decimal('572.00')},
    {'A': 4, 'B': None, 'C': Decimal('609.00')},
    {'A': 3, 'B': None, 'C': Decimal('820.00')},
    {'A': 5, 'B': None, 'C': Decimal('1223.00')}
]

df = pd.DataFrame(d)

In : df
Out:
   A     B        C
0  2  None   628.00
1  1  None   383.00
2  3  None   651.00
3  2  None   575.00
4  4  None  1114.00
5  1  TEST   241.00
6  2  None   572.00
7  4  None   609.00
8  3  None   820.00
9  5  None  1223.00
```

dtypes are equivalent:

```
In : df.dtypes
Out:
A     int64
B    object
C    object
dtype: object

In : df.head(5).dtypes
Out:
A     int64
B    object
C    object
dtype: object
```

But calling mean on the dataframe does not work when row 6 is present:

``` python
# no mean for C column: row 6 is present
In : df.mean()
Out:
A    2.7
dtype: float64

# mean for C column when row 6 is left out of the DF
In : df.head(5).mean()
Out:
A      2.4
B      NaN
C    670.2
dtype: float64

# no mean for C column when row 6 is part of the DF
In : df.head(6).mean()
Out:
A    2.166667
dtype: float64
```

Also, it works when I explicitely leave out column B for calculating the mean:

``` python
In : df[['A','B','C']].mean()
Out:
A    2.7
dtype: float64

In : df[['A','C']].mean()
Out:
A      2.7
C    681.6
dtype: float64
```

Hints from issue discussion:
we would have to jump thru lots of hoops for this to work I think. In general `object` dtype mean is really for strings. putting `Decimal` which is 'float-like' is just asking for trouble. So what is happening is that when the mean is tried on `B-C` block it errors, so that is excluded.

I'll mark it, but if you really want it fixed, then pls submit a pull-request.

I receive the data with Decimal objects. That's not my choice.
So should Decimal not be used with pandas and I should manually do a conversion for each column that has Decimal objects first?
`df['C'] = df['C'].astype(float)`

I think it gets confusing when `df[['A','C']].mean()` works and `df[['A','B','C']].mean()` does not.
If Decimal objects are not supported, I'd expect them not to work no matter what I do and the behavior would especially not change based on the values of another column.

Why isn't the `mean` function applied per column? Why is it applied on the `B-C` block and not on each column separately?

Thanks

Columns are internally consolidated into blocks, one per dtype (usually). The object dtypes are all lumped together into one block here. When the `.mean()` for that block fails all the columns in it ('B' & 'C') are treated as nuisance columns and excluded from the result.

You are better of sticking to numpy dtypes + the ones pandas has added on (datetimes, categoricals) _if your application allows it_. You might not want to cast to floats without checking whether that will mess up the people your giving the data back to :)

edit: as for why it's done at the block level vs once per column, I think the biggest reason is performance. We do have an issue about not consolidating blocks automatically, but that's not really meant to be exposed to a user and used in this kind of scenario (I think).

edit2: and if you really need to support this, you could try

``` python
def func(x):
    try:
        return np.nanmean(x)
    except:  # catch the actual error
         pass

df.apply(func)
```

but this will be slower

Thanks for the explanation for what happens under the hood.
I guess it will require some development to make `mean` work per column instead of per dtype block.

So are you suggesting to cast to a numpy float type for now?
`df['C'] = df['C'].astype(numpy.float64)`

yes, I would _never_ use `Decimal`, it is completely non-performant. I understand there are people who need a fixed decimal type, but to be honest, that is just for display purposes.

Trying to reproduce OP's behaviour in 0.23.4 and failing to do so

```
In [12]: df.dtypes                                                                                                               
Out[12]: 
A     int64
B    object
C    object
dtype: object

In [13]: df.mean()                                                                                                               
Out[13]: 
A      2.7
C    681.6
dtype: float64
```

Has this block treatment of object columns changed? Because it now works as expected (well, almost, the mean itself is a float, but I guess that's fine).

(cc @jreback @TomAugspurger)
Yes this looks to work on master. Could use a test:

```
In [51]: df.mean()
Out[51]:
A      2.7
C    681.6
dtype: float64

In [54]: pd.__version__
Out[54]: '0.26.0.dev0+593.g9d45934af'
```

Commit message: improved with black

Patch to review:
diff --git a/pandas/tests/frame/test_analytics.py b/pandas/tests/frame/test_analytics.py
index e99208ac78e15..7cbe3ec8a5171 100644
--- a/pandas/tests/frame/test_analytics.py
+++ b/pandas/tests/frame/test_analytics.py
@@ -1075,6 +1075,29 @@ def test_mean_excludeds_datetimes(self, tz):
         expected = pd.Series()
         tm.assert_series_equal(result, expected)
 
+    def test_mean_mixed_string_float(self):
+        # GH 11670
+        # possible bug when calculating mean of DataFrame?
+
+        d = [
+            {"A": 2, "B": None, "C": 628.00},
+            {"A": 1, "B": None, "C": 383.00},
+            {"A": 3, "B": None, "C": 651.00},
+            {"A": 2, "B": None, "C": 575.00},
+            {"A": 4, "B": None, "C": 1114.00},
+            {"A": 1, "B": "TEST", "C": 241.00},
+            {"A": 2, "B": None, "C": 572.00},
+            {"A": 4, "B": None, "C": 609.00},
+            {"A": 3, "B": None, "C": 820.00},
+            {"A": 5, "B": None, "C": 1223.00},
+        ]
+
+        df = pd.DataFrame(d)
+
+        result = df.mean()
+        expected = pd.Series([2.7, 681.6], index=["A", "C"])
+        tm.assert_series_equal(result, expected)
+
     def test_var_std(self, datetime_frame):
         result = datetime_frame.std(ddof=4)
         expected = datetime_frame.apply(lambda x: x.std(ddof=4))


Code review comments:
diff_hunk: @@ -1075,6 +1075,29 @@ def test_mean_excludeds_datetimes(self, tz):
         expected = pd.Series()
         tm.assert_series_equal(result, expected)
 
+    def test_mean_mixed_string_float(self):
+        # GH 11670
+        # possible bug when calculating mean of DataFrame?
+
+        d = [
+            {"A": 2, "B": None, "C": 628.00},
path: pandas/tests/frame/test_analytics.py
text: @user1:
The `C` values need to be `Decimal` objects as the issue described. Could you also update the test name to reflect that?