pandas 使用 groupby 和另一列值添加列条件

mmm 发表于 Dev

MMM

我已经pandas.DataFrame调用companysubset如下，但实际数据要长得多。

        conm                       fyear    dvpayout    industry    firmycount  ipodate
46078   CAESARS ENTERTAINMENT CORP  2003    0.226813    Services    22  19891213.0
46079   CAESARS ENTERTAINMENT CORP  2004    0.226813    Services    22  19891213.0
46080   CAESARS ENTERTAINMENT CORP  2005    0.226813    Services    22  19891213.0
46091   CAESARS ENTERTAINMENT CORP  2016    0.226813    Services    22  19891213.0
114620  CAESARSTONE LTD 2010    0.487543    Manufacturing   10  20120322.0
114621  CAESARSTONE LTD 2011    0.487543    Manufacturing   10  20120322.0
114622  CAESARSTONE LTD 2012    0.487543    Manufacturing   10  20120322.0
114623  CAESARSTONE LTD 2013    0.487543    Manufacturing   10  20120322.0
114624  CAESARSTONE LTD 2014    0.487543    Manufacturing   10  20120322.0
114625  CAESARSTONE LTD 2015    0.487543    Manufacturing   10  20120322.0
114626  CAESARSTONE LTD 2016    0.487543    Manufacturing   10  20120322.0
132524  CAFEPRESS INC   2010    0.000000    Retail Trade    7   20120329.0
132525  CAFEPRESS INC   2011    0.000000    Retail Trade    7   20120329.0
132526  CAFEPRESS INC   2012    -0.000000   Retail Trade    7   20120329.0
132527  CAFEPRESS INC   2013    -0.000000   Retail Trade    7   20120329.0
132528  CAFEPRESS INC   2014    -0.000000   Retail Trade    7   20120329.0
132529  CAFEPRESS INC   2015    -0.000000   Retail Trade    7   20120329.0
132530  CAFEPRESS INC   2016    -0.000000   Retail Trade    7   20120329.0
120049  CAI INTERNATIONAL INC   2005    0.000000    Services    12  20070516.0
120050  CAI INTERNATIONAL INC   2006    0.000000    Services    12  20070516.0
3896    CALAMP CORP 1999    -0.000000   Manufacturing   23  NaN
3897    CALAMP CORP 2000    0.000000    Manufacturing   23  NaN
3898    CALAMP CORP 2001    0.000000    Manufacturing   23  NaN
3899    CALAMP CORP 2002    0.000000    Manufacturing   23  NaN
21120   CALATLANTIC GROUP INC   1995    -0.133648   Construction    22  NaN
21121   CALATLANTIC GROUP INC   1996    -0.133648   Construction    22  NaN
21122   CALATLANTIC GROUP INC   1997    -0.133648   Construction    22  NaN
21123   CALATLANTIC GROUP INC   1998    -0.133648   Construction    22  NaN
21124   CALATLANTIC GROUP INC   1999    -0.133648   Construction    22  NaN
21125   CALATLANTIC GROUP INC   2000    -0.133648   Construction    22  NaN
21126   CALATLANTIC GROUP INC   2001    -0.133648   Construction    22  NaN
21127   CALATLANTIC GROUP INC   2002    -0.133648   Construction    22  NaN
21128   CALATLANTIC GROUP INC   2003    -0.133648   Construction    22  NaN

1）我想dvpayout按行业计算公司的四分位数，并添加名为的列dv并表明它在Q1、Q2、Q3或中Q4。

我想出了这个代码，但它不起作用。

pd.cut(companysubset['dvpayout'].mean(), bins=[0,25,75,100], labels=False)

2）age如果有ipodate. 该值将是fyear - ipodate一年中最大的。（例如，2016 - 1989对于CAESARS ENTERTAINMENT COR）

我想看到的结果数据框如下所示。

        conm            fyear    dvpayout   industry    firmycount  ipodate  dv   age
46078   CAESARS ...     2003    0.226813    Services    22  19891213.0   Q2  27
46079   CAESARS ...     2004    0.226813    Services    22  19891213.0   Q2  27
46080   CAESARS ...     2005    0.226813    Services    22  19891213.0   Q2  27
46091   CAESARS ...     2016    0.226813    Services    22  19891213.0   Q2  27
114620  CAESARSTONE LTD 2010    0.487543    Manufacturing   10  20120322.0  Q3  4
114621  CAESARSTONE LTD 2011    0.487543    Manufacturing   10  20120322.0  Q3  4
114622  CAESARSTONE LTD 2012    0.487543    Manufacturing   10  20120322.0  Q3  4
114623  CAESARSTONE LTD 2013    0.487543    Manufacturing   10  20120322.0  Q3  4
114624  CAESARSTONE LTD 2014    0.487543    Manufacturing   10  20120322.0  Q3  4
114625  CAESARSTONE LTD 2015    0.487543    Manufacturing   10  20120322.0  Q3  4
114626  CAESARSTONE LTD 2016    0.487543    Manufacturing   10  20120322.0  Q3  4
132524  CAFEPRESS INC   2010    0.000000    Retail Trade    7   20120329.0  Q1  4
132525  CAFEPRESS INC   2011    0.000000    Retail Trade    7   20120329.0  Q1  4
132526  CAFEPRESS INC   2012    -0.000000   Retail Trade    7   20120329.0  Q1  4
132527  CAFEPRESS INC   2013    -0.000000   Retail Trade    7   20120329.0  Q1  4
132528  CAFEPRESS INC   2014    -0.000000   Retail Trade    7   20120329.0  Q1  4
132529  CAFEPRESS INC   2015    -0.000000   Retail Trade    7   20120329.0  Q1  4
132530  CAFEPRESS INC   2016    -0.000000   Retail Trade    7   20120329.0  Q1  4
120049  CAI INTERNATIONAL INC   2006    0.000000    Services    12  20070516.0 Q1  0
120050  CAI INTERNATIONAL INC   2007    0.000000    Services    12  20070516.0 Q1  0
3896    CALAMP CORP 1999    -0.000000   Manufacturing   23  NaN   Q1  Nan  
3897    CALAMP CORP 2000    0.000000    Manufacturing   23  NaN   Q1  Nan
3898    CALAMP CORP 2001    0.000000    Manufacturing   23  NaN   Q1  Nan
3899    CALAMP CORP 2002    0.000000    Manufacturing   23  NaN   Q1  Nan
21120   CALATLANTIC GROUP INC   1995    -0.133648   Construction    22  NaN   Q1  Nan
21121   CALATLANTIC GROUP INC   1996    -0.133648   Construction    22  NaN   Q1  Nan
21122   CALATLANTIC GROUP INC   1997    -0.133648   Construction    22  NaN   Q1  Nan
21123   CALATLANTIC GROUP INC   1998    -0.133648   Construction    22  NaN   Q1  Nan
21124   CALATLANTIC GROUP INC   1999    -0.133648   Construction    22  NaN   Q1  Nan
21125   CALATLANTIC GROUP INC   2000    -0.133648   Construction    22  NaN   Q1  Nan
21126   CALATLANTIC GROUP INC   2001    -0.133648   Construction    22  NaN   Q1  Nan
21127   CALATLANTIC GROUP INC   2002    -0.133648   Construction    22  NaN  Q1  Nan
21128   CALATLANTIC GROUP INC   2003    -0.133648   Construction    22  NaN  Q1  Nan

提前致谢！！！！

斯蒂芬·劳赫

年龄列可以通过以下方式生成：

代码

df.set_index(['conm'], inplace=True)
df['age'] = df.groupby(level=0).apply(
    lambda x: max(x.fyear) - round(x.ipodate.iloc[0]/10000-0.5))

测试代码：

df = pd.read_fwf(StringIO(
    u"""
        ID      conm                  fyear   ipodate
        46078   CAESARS ENTERTAINMENT 2003    19891213.0
        46079   CAESARS ENTERTAINMENT 2004    19891213.0
        46080   CAESARS ENTERTAINMENT 2005    19891213.0
        46091   CAESARS ENTERTAINMENT 2016    19891213.0
        114620  CAESARSTONE LTD       2010    20120322.0
        114621  CAESARSTONE LTD       2011    20120322.0
        114622  CAESARSTONE LTD       2012    20120322.0
        114623  CAESARSTONE LTD       2013    20120322.0
        114624  CAESARSTONE LTD       2014    20120322.0
        114625  CAESARSTONE LTD       2015    20120322.0
        114626  CAESARSTONE LTD       2016    20120322.0
        132524  CAFEPRESS INC         2010    20120329.0
        132525  CAFEPRESS INC         2011    20120329.0
        132526  CAFEPRESS INC         2012    20120329.0
        132527  CAFEPRESS INC         2013    20120329.0
        132528  CAFEPRESS INC         2014    20120329.0
        132529  CAFEPRESS INC         2015    20120329.0
        132530  CAFEPRESS INC         2016    20120329.0
        120049  CAI INTERNATIONAL INC 2005    20070516.0
        120050  CAI INTERNATIONAL INC 2006    20070516.0
        3897    CALAMP CORP           2000    NaN
        3898    CALAMP CORP           2001    NaN
        3896    CALAMP CORP           1999    NaN
        3899    CALAMP CORP           2002    NaN
        21120   CALATLANTIC GROUP INC 1995    NaN
        21121   CALATLANTIC GROUP INC 1996    NaN
        21122   CALATLANTIC GROUP INC 1997    NaN
        21123   CALATLANTIC GROUP INC 1998    NaN
        21124   CALATLANTIC GROUP INC 1999    NaN
        21125   CALATLANTIC GROUP INC 2000    NaN
        21126   CALATLANTIC GROUP INC 2001    NaN
        21127   CALATLANTIC GROUP INC 2002    NaN
        21128   CALATLANTIC GROUP INC 2003    NaN"""),
    header=1)

df.set_index(['conm'], inplace=True)
df['age'] = df.groupby(level=0).apply(
    lambda x: max(x.fyear) - round(x.ipodate.iloc[0]/10000-0.5))
print(df)

结果：

                           ID  fyear     ipodate   age
conm                                                  
CAESARS ENTERTAINMENT   46078   2003  19891213.0  27.0
CAESARS ENTERTAINMENT   46079   2004  19891213.0  27.0
CAESARS ENTERTAINMENT   46080   2005  19891213.0  27.0
CAESARS ENTERTAINMENT   46091   2016  19891213.0  27.0
CAESARSTONE LTD        114620   2010  20120322.0   4.0
CAESARSTONE LTD        114621   2011  20120322.0   4.0
CAESARSTONE LTD        114622   2012  20120322.0   4.0
CAESARSTONE LTD        114623   2013  20120322.0   4.0
CAESARSTONE LTD        114624   2014  20120322.0   4.0
CAESARSTONE LTD        114625   2015  20120322.0   4.0
CAESARSTONE LTD        114626   2016  20120322.0   4.0
CAFEPRESS INC          132524   2010  20120329.0   4.0
CAFEPRESS INC          132525   2011  20120329.0   4.0
CAFEPRESS INC          132526   2012  20120329.0   4.0
CAFEPRESS INC          132527   2013  20120329.0   4.0
CAFEPRESS INC          132528   2014  20120329.0   4.0
CAFEPRESS INC          132529   2015  20120329.0   4.0
CAFEPRESS INC          132530   2016  20120329.0   4.0
CAI INTERNATIONAL INC  120049   2005  20070516.0  -1.0
CAI INTERNATIONAL INC  120050   2006  20070516.0  -1.0
CALAMP CORP              3897   2000         NaN   NaN
CALAMP CORP              3898   2001         NaN   NaN
CALAMP CORP              3896   1999         NaN   NaN
CALAMP CORP              3899   2002         NaN   NaN
CALATLANTIC GROUP INC   21120   1995         NaN   NaN
CALATLANTIC GROUP INC   21121   1996         NaN   NaN
CALATLANTIC GROUP INC   21122   1997         NaN   NaN
CALATLANTIC GROUP INC   21123   1998         NaN   NaN
CALATLANTIC GROUP INC   21124   1999         NaN   NaN
CALATLANTIC GROUP INC   21125   2000         NaN   NaN
CALATLANTIC GROUP INC   21126   2001         NaN   NaN
CALATLANTIC GROUP INC   21127   2002         NaN   NaN
CALATLANTIC GROUP INC   21128   2003         NaN   NaN

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2021-06-7

我来说两句

0 条评论

登录后参与评论

pandas 使用 groupby 和另一列值添加列条件

pandas 使用 groupby 和另一列值添加列条件

代码

测试代码：

结果：

构建类似于Jarvis的本地语言应用程序

在 Avalonia 中是否有带有柱子的 TreeView 或类似的东西？

Qt Creator Windows 10 - “使用 jom 而不是 nmake”不起作用

SQL Server中的非确定性数据类型

使用next.js时出现服务器错误，错误：找不到react-redux上下文值；请确保组件包装在<Provider>中

Swift 2.1-对单个单元格使用UITableView

Hashchange事件侦听器在将事件处理程序附加到事件之前进行侦听

HttpClient中的角度变化检测

如何了解DFT结果

错误：找不到存根。请确保已调用spring-cloud-contract：convert

Embers js中的更改侦听器上的组合框

在Wagtail管理员中，如何禁用图像和文档的摘要项？

如何避免每次重新编译所有文件？

Java中的循环开关案例

ng升级性能注意事项

Swift中的指针替代品？

如何使用geoChoroplethChart和dc.js在Mapchart的路径上添加标签或自定义值？

使用分隔符将成对相邻的数组元素相互连接

在同一Pushwoosh应用程序上Pushwoosh多个捆绑ID

ggplot：对齐多个分面图-所有大小不同的分面

完全禁用暂停（在内核级别？-必须与使用的DE和登录状态无关！）