Skip to main content
 首页 » 编程设计

python之Groupby 和仅选定的列

2024年12月31日7wuhuacong

我在这里读取文件“userdata.xlsx”:

ID  Debt    Email   Age User 
1   7.5 john@email.com  16  John 
2   15  john@email.com  15  John 
3   22  john@email.com  15  John 
4   30  david@email.com 22  David 
5   33  david@email.com 22  David 
6   51  fred@email.com  61  Fred 
7   11  fred@email.com  25  Fred 
8   24  eric@email.com  19  Eric 
9   68  terry@email.com 55  Terry 
10  335 terry@email.com 55  Terry 

这里我按用户分组并为每个用户创建一个电子表格并将其输出为自己的 .xlsx 文件,如下所示:

ID  Debt    Email   Age User 
1   7.5 john@email.com  16  John 
2   15  john@email.com  15  John 

完整代码如下:

    #!/usr/bin/env python3 
 
    import pandas as pd 
    import numpy as np 
    import matplotlib.pyplot as plt 
    import xlrd 
 
    df = pd.read_excel('userdata.xlsx') 
    grp = df.groupby('User') 
 
    for group in grp.groups: 
        grouptofile = (grp.get_group(group)) 
        print(grouptofile) 
        print(group) 
        grouptofile.to_excel('%s.xlsx' % group , sheet_name='sheet1', index=False) 

现在我只想为每个用户保存选定的列。假设我只想选择“ID”和“Email”列。我学会了如何像这样只选择某些列:

selected = df[['ID','Email']] 

我现在认为在此处添加 ID 和电子邮件是有意义的。

grp = df.groupby('User') 

添加了“ID”和“Email”

grp = df[['ID', 'Email']].groupby('User') 

是否可以合并 groupby 和 select 列?

#!/usr/bin/env python3 
 
    import pandas as pd 
    import numpy as np 
    import matplotlib.pyplot as plt 
    import xlrd 
 
    df = pd.read_excel('userdata.xlsx') 
    grp = df[['ID', 'Email']].groupby('User') 
 
    for group in grp.groups: 
        grouptofile = (grp.get_group(group)) 
        print(grouptofile) 
        print(group) 
        grouptofile.to_excel('%s.xlsx' % group , sheet_name='sheet1', index=False) 

这是我得到的错误:

Traceback (most recent call last): 
  File "/Users/Barry/Documents/Python/Pandas/exelscript.py", line 22, in <module> 
    grp = df[['ID', 'Email']].groupby('User') 
  File "/usr/local/lib/python3.7/site-packages/pandas/core/generic.py", line 6665, in groupby 
    observed=observed, **kwargs) 
  File "/usr/local/lib/python3.7/site-packages/pandas/core/groupby/groupby.py", line 2152, in groupby 
    return klass(obj, by, **kwds) 
  File "/usr/local/lib/python3.7/site-packages/pandas/core/groupby/groupby.py", line 599, in __init__ 
    mutated=self.mutated) 
  File "/usr/local/lib/python3.7/site-packages/pandas/core/groupby/groupby.py", line 3291, in _get_grouper 
    raise KeyError(gpr) 
KeyError: 'User' 

请您参考如下方法:

我认为您需要在子集中指定列:

cols = ['ID', 'Email'] 
for i, group in df.groupby('User'): 
    group[cols].to_excel('{}.xlsx'.format(i), sheet_name='sheet1', index=False) 

如果得到 KeyError: 'User' 这意味着你想要选择不存在的列。

因此,如果选择列 ID 和 Email,则链接的 groupby 无法找到 User 列并引发错误:

print (df[['ID', 'Email']]) 
   ID            Email 
0   1   john@email.com 
1   2   john@email.com 
2   3   john@email.com 
3   4  david@email.com 
4   5  david@email.com 
5   6   fred@email.com 
6   7   fred@email.com 
7   8   eric@email.com 
8   9  terry@email.com 
9  10  terry@email.com 

因此有必要选择列也用于 groupby:

for i, group in df[['ID', 'Email', 'User']].groupby('User'): 
    group.to_excel('{}.xlsx'.format(i), sheet_name='sheet1', index=False) 

或者像第一个解决方案一样在写入文件之前选择列。

for i, group in df[['ID', 'Email', 'User']].groupby('User'): 
    group[cols].to_excel('{}.xlsx'.format(i), sheet_name='sheet1', index=False)