I'm still working on a reproducible example, but unfortunately, I'm an absolute beginner at pandas, and I'm lost as to what I'm doing wrong. The behavior seems like a bug in Pandas 1.2.4.
I have two dataframes, files_df which gives the type and complexity of source code files in several repos, and proj_df which gives the projects and the last time they were updated.
I am trying to pick the top two projects for each language by complexity, and it looks like this:
def largest(x):
return (
# Pick the top two rows in each language (from the groupby) by scc_complexity
x.nlargest(2, 'scc_complexity')
# Combine the proj_repo values (we don't actually care about scc_complexity at this point)
.agg({'proj_repo': lambda y: '<br>'.join(y.values)})
)
max_proj = (
# Pick the language and complexity columns
files_df[['scc_lang', 'scc_complexity', 'proj_repo']]
# Sum scc_complexity by language and repo
.groupby(['scc_lang', 'proj_repo'], observed=True).sum()
.reset_index()
)
max_proj = (
max_proj
# Select only repos updated in the last two years
.loc[max_proj['proj_repo'].isin(proj_df.loc[proj_df['last_author_time'] > '2019-05-01', 'proj_repo'])]
# Now pick the top repos by language complexity
.groupby('scc_lang').apply(largest)
)
This should produce a frame with scc_lang as its index and proj_repo as its only column, but instead it produces this shape:
<class 'pandas.core.frame.DataFrame'>
MultiIndex: 151 entries, ('ASP', 'proj_repo') to ('XML Schema (min)', 'proj_repo')
Data columns (total 2 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 0 151 non-null object
1 proj_repo 0 non-null object
dtypes: object(2)
memory usage: 8.7+ KB
MultiIndex([( 'ASP', 'proj_repo'),
( 'Autoconf', 'proj_repo'),
( 'BASH', 'proj_repo'),
( 'Batch', 'proj_repo'),
...
That is, there's now a multi-index, and the proj_info column is now a column named 0, and the proj_info column is now empty.
Now for the really strange part. This code produces the correct shape if you remove the .loc[] selection from max_proj. For the life of me, I can't figure out why making a selection changes the shape of the results. I haven't been able to reproduce the results with a small complete example.
I will keep experimenting, and if I learn anything interesting, I'll report here.
Slight update: I've discovered that removing observed=True also seems to produce the correct shape—so maybe this is related to missing keys?
Bigger update: I managed to reproduce it, and yes, it does somehow relate to missing keys, though I'm not sure how:
tdf = pd.DataFrame([['lang1', 1, 'ab'],
['lang2', 2, 'cd'],
['lang2', 4, 'cd'],
['lang2', 3, 'ef'],
['lang2', 4, 'gh'],
['lang3', 2, 'ef']
],
columns=['lang', 'size', 'name']).astype({'lang': 'category'}, copy=False).set_index('lang')
d = tdf.groupby(['lang', 'name'], observed=True).sum().reset_index()
d = d.loc[d['name'].isin(pd.Series(['ab','cd','gh']))].groupby('lang').apply(lambda x: x.agg({'name': lambda y: ' '.join(y.values)}))
display(d)
d.info()
| 0 | name | ||
|---|---|---|---|
| lang | |||
| lang1 | name | ab | NaN |
| lang2 | name | cd gh | NaN |
<class 'pandas.core.frame.DataFrame'>
MultiIndex: 2 entries, ('lang1', 'name') to ('lang2', 'name')
Data columns (total 2 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 0 2 non-null object
1 name 0 non-null object
dtypes: object(2)
memory usage: 356.0+ bytes
Removing either the 'ab' or 'ef' names triggers this, meaning it has to do with a missing category. Indeed, changing the column type back to object also fixes this issue. So it seems I've misunderstood categories...
Another update: I've managed to reduce it down to these two lines:
tdf2 = pd.DataFrame([['a', 'boof']], columns=['lang', 'name']).astype({'lang': pd.CategoricalDtype(['a', 'b'])})
display(tdf2.groupby('lang').apply(lambda x: x.agg({'name': lambda y: y.values})))
We're pretty minimal now. Removing almost any bit of the above code causes the resulting data frame to snap to the correct shape.
What we have here is an agg being applied to an empty dataframe produced by .groupby('lang'), which resurrects a category that has been removed from the data. There seems to be strangeness around the result being rejoined inside apply().
One new finding here is that returning y.values from the apply() lambda is part of it: if I return y (the Series) instead, the strange structure doesn't happen.