BUG: Group-by on an empty data object dtype loses the index name (cython aggregation is ok)

[carterk]

If pd.read_sql is used to load a data frame with the results of an SQL query that returns no results, the columns in the data frame will be of type 'object'. That type cannot be aggregated, so a subsequent group-by operation on that empty data frame will drop all the columns. So instead of 'profit' in the below example being an empty series, an attribute error is thrown because the columns 'revenue' and 'expenses' cannot be found in the data frame.

Two things I can think of that could fix this:

1. Have pd.read_sql populate the data frame with empty columns of the correct type even if the SQL query returns no results. Then the group-by would not drop the columns because they are of a type that can be aggregated.
2. Have an option in groupby to not drop columns of types that cannot be aggregated: maybe a drop_non_agg flag. I think not dropping columns of types that cannot be aggregated should be the default behaviour. Columns with data that cannot be aggregated can just be populated with null after a group-by.

I think 1) probably should be implemented, and 2) is kind of a design decision.

You can run this code to reproduce the issue.

import pandas as pd
import sqlite3 as lite
import sys

finance = (
    (2, 132, 65),
    (6, 142, 86),
    (3, 183, 34),
    (3, 147, 46)
)

con = lite.connect('test.db')
cur = con.cursor()

cur.execute("DROP TABLE IF EXISTS finance")
cur.execute("CREATE TABLE finance(day_of_week INT, revenue FLOAT, expenses FLOAT)")
cur.executemany("INSERT INTO finance VALUES(?, ?, ?)", finance)

# remove the 'WHERE' clause, and the error won't be thrown
my_query = '''
    SELECT *
    FROM finance
    WHERE day_of_week = 5
    '''

df = pd.read_sql(my_query, con)

df_gb = df[df.day_of_week == 5].groupby('day_of_week').sum().reset_index()

profit = df_gb.revenue - df_gb.expenses # AttributeError thrown here

[jorisvandenbossche]

The problem is that pandas, when reading a query, does not know anything about the table structure itself. It constructs the resulting frame only from the returned values from the query. And if there are no values, it cannot determine the dtype.

[jorisvandenbossche]

By the way, if I run this with pandas 0.14.1, I don't get a AttributeError. df has 'object' dtypes, but after the groupby, df_gb has columns of 'float' dtype.

[carterk]

I'm running this on pandas 0.13.1

df.info() gives:

<class 'pandas.core.frame.DataFrame'>
Index: 0 entries
Data columns (total 3 columns):
day_of_week    0 non-null object
revenue        0 non-null object
expenses       0 non-null object
dtypes: object(3)None

df_gb.info() gives:

<class 'pandas.core.frame.DataFrame'>
Int64Index: 0 entries
Data columns (total 1 columns):
index    0 non-null object
dtypes: object(1)None

Is it different for you?

[jorisvandenbossche]

yes:

In [23]: df.info()
<class 'pandas.core.frame.DataFrame'>
Index: 0 entries
Data columns (total 3 columns):
day_of_week    0 non-null object
revenue        0 non-null object
expenses       0 non-null object
dtypes: object(3)

In [24]: df_gb.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 0 entries
Data columns (total 3 columns):
index       0 non-null int64
revenue     0 non-null float64
expenses    0 non-null float64
dtypes: float64(2), int64(1)

But I don't know which of both is correct. As converting the object column to float is also strange I think.

[carterk]

Any idea why those 'object' columns are 'float64' after the group-by? Where does the 'day_of_week' column go? This still isn't what I would expect/want from the group-by operation.

[jorisvandenbossche]

@jreback object columns that get converted to float, is that OK?

You can keep the 'day_of_week' column by providing as_index=False: df.groupby('day_of_week', as_index=False).sum() (although I would think it should also work with reset_index, but for some reason it loses the index name, this seems a bug to me)

[jreback]

@carterk yes, that is by definition, the index of a returned groupby uses the grouper.

[carterk]

@jreback Yeah, I thought reset_index handled that. Maybe not. Either way I'm still interested to know why the 'object' columns turned into 'float' columns. Maybe in 0.14.1 groupby now treats the 'object' type as aggregatable and converts it to 'float'? Which is kind of unexpected behaviour, but would also be a decent solution to the issue.

[jorisvandenbossche]

@jreback there are two things I don't understand/are a bit strange:

• (empty) object columns are converted to float -> expected? (changed from 0.13.1 to 0.14.1)
• groupby looses the index name (the name of the column it sets as idnex)

[jreback]

neither of those are true

the result of the input array deteomes the dtype - they r not coerced

name of the groupby column is preserved

I suspect the input to the frame creation is not exactly right - save that and u will see

[jorisvandenbossche]

What do you exactly mean with 'input to frame creation is not exactly right'?

In [66]: df = pd.DataFrame(columns=list('ABC'))

In [67]: df
Out[67]: 
Empty DataFrame
Columns: [A, B, C]
Index: []

In [68]: df.info()
<class 'pandas.core.frame.DataFrame'>
Index: 0 entries
Data columns (total 3 columns):
A    0 non-null object
B    0 non-null object
C    0 non-null object
dtypes: object(3)

In [69]: grouped = df.groupby('A').sum()

In [70]: grouped
Out[70]: 
Empty DataFrame
Columns: [B, C]
Index: []

In [71]: grouped.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 0 entries
Data columns (total 2 columns):
B    0 non-null float64
C    0 non-null float64
dtypes: float64(2)

In [72]: grouped.index.name

[jreback]

In [119]: df = pd.DataFrame(columns=list('ABC'),dtype='float64')

In [120]: df.groupby('A').sum().index.name
Out[120]: 'A'

In [121]: df = pd.DataFrame(columns=list('ABC'))

In [122]: df.groupby('A').sum().index.name

I think that the object dtype causes a python aggregation (while the float is a cython aggregation). somewhere the name is getting lost. call this a bug.