Pandas groupby indices behaving diferrently with 2 and 3 rows

user_8040498 0
Viewed 129

I have 2 dataframes df1 and df2. Their format is the same with the only difference that the first has 3 rows and the second 2.

df1 = pd.DataFrame({
    'Company': ['Foo Inc.', 'Foo Inc.', 'Foo Inc.'],
    'ID': ['123456', '123456', '123456'],
    'Employee': ['John Doe', 'Richard Roe', 'Jane Doe'],
    'Position': ['Executive Director', 'Director', 'Company Secretary']
})

df2 = pd.DataFrame({
    'Company': ['Bar Inc.', 'Bar Inc.'],
    'ID': ['56789', '56789'],
    'Employee': ['Mark Moe', 'Larry Loe'],
    'Position': ['Tax Consultant', 'Company Secretary']
})

print(df1)
    Company     Employee      ID            Position
0  Foo Inc.     John Doe  123456  Executive Director
1  Foo Inc.  Richard Roe  123456            Director
2  Foo Inc.     Jane Doe  123456   Company Secretary

print(df2)
    Company   Employee     ID           Position
0  Bar Inc.   Mark Moe  56789     Tax Consultant
1  Bar Inc.  Larry Loe  56789  Company Secretary

when I try to do the following it works for the first but not for the second

gb1 = df1.set_index(['Company', 'ID', 'Employee']).groupby(['Company', 'ID'])
gb2 = df2.set_index(['Company', 'ID', 'Employee']).groupby(['Company', 'ID'])

for (name, id), new_df in gb1:
    print(name)
    print(id)

for (name, id), new_df in gb2:
    print(name)
    print(id)

Foo Inc.
123456

      3     print(id)
      4
----> 5 for (name, id), new_df in gb2:
      6     print(name)
      7     print(id)

ValueError: too many values to unpack (expected 2)

this is because their indices are different

gb1.indices
>>> {('Foo Inc.', '123456'): array([0, 1, 2], dtype=int64)}

gb2.indices
>>> {'Company': array([0], dtype=int64), 'ID': array([1], dtype=int64)}

am I missing something? is this a bug?

pandas-groupby pandas python
1 Answers

When you set_index,you are groupby the index not the column anymore , so you can add level=, groupby index without using level will have wired thing happen . 

gb2 = df2.set_index(['Company', 'ID', 'Employee']).groupby(level=['Company', 'ID'])
for (name, id), new_df in gb2:
    print(name)
    print(id)

Bar Inc.
56789
user_79645270
Related