我有一个dataframe(df)(最初来自一个excel文件),前9行如下:

      Control      Recd_Date/Due_Date                Action        Signature/Requester
0     2000-1703   2000-01-31 00:00:00           OC/OER/OPA/PMS/                 M WEBB
1           NaN   2000-02-29 00:00:00                       NaN              DATA CORP
2     2000-1776   2000-01-02 00:00:00            OC/ORA/OE/DCP/                  G KAN
3           NaN   2000-01-03 00:00:00           OC/ORA/ORO/PNC/              PALM POST
4           NaN                   NaN  FDA/OGROP/ORA/SE-FO/FLA-                    NaN
5           NaN                   NaN                DO/FLA-CB/                    NaN
6     2000-1983   2000-02-02 00:00:00  FDA/OGROP/ORA/CE-FO/CHI-                 M EGAN
7           NaN   2000-02-03 00:00:00                DO/CHI-CB/   BERNSTEIN LIEBHARD &
8           NaN                   NaN                       NaN             LONDON LLP

类型(df['Control'][1])=浮点;
类型(df['Recd_Date/Due_Date'][1])=日期时间.datetime;
类型(df['Action_Office'][1])=浮动;
类型(df['Signature/Requester'][1])=unicode
我要将此数据帧(例如前9行)转换为:
      Control            Recd_Date/Due_Date                           Action                                                            Signature/Requester
0     2000-1703   2000-01-31 00:00:00,2000-02-29 00:00:00           OC/OER/OPA/PMS/                                                      M WEBB,DATA CORP
1     2000-1776   2000-01-02 00:00:00,2000-01-03 00:00:00           OC/ORA/OE/DCP/OC/ORA/ORO/PNC/FDA/OGROP/ORA/SE-FO/FLA-DO/FLA-CB/      G KAN,PALM POST
2     2000-1983   2000-02-02 00:00:00,2000-02-03 00:00:00           FDA/OGROP/ORA/CE-FO/CHI-DO/CHI-CB/                                   M EGAN,BERNSTEIN LIEBHARD & LONDON LLP

所以基本上:
每当pd.is null(row['Control'])(这应该是唯一的if条件)为true时,将此行与前一行(其'Control'值不为空)合并。
对于“Recd_Date/Due_Date”和“Signature/Requester”,在两个值(来自两个合并行)之间添加“,”(或“/”)(例如“2000-01-31 00:00:002000-02-29 00:00:00”和“g KAN,PALM POST”)
对于“Action”,只需合并它们而不添加任何标点符号(例如FDA/OGROP/ORA/CE-FO/CHI-DO/CHI-CB/)
有人能帮我吗?这是我想让它工作的代码:
for i, row in df.iterrows():
    if pd.isnull(df.ix[i]['Control_#']):
       df.ix[i-1]['Recd_Date/Due_Date'] = str(df.ix[i-1]['Recd_Date/Due_Date'])+'/'+str(df.ix[i]['Recd_Date/Due_Date'])
       df.ix[i-1]['Subject'] = str(df.ix[i-1]['Subject'])+' '+str(df.ix[i]['Subject'])
       if str(df.ix[i-1]['Action_Office'])[-1] == '-':
           df.ix[i-1]['Action_Office'] = str(df.ix[i-1]['Action_Office'])+str(df.ix[i]['Action_Office'])
       else:
           df.ix[i-1]['Action_Office'] = str(df.ix[i-1]['Action_Office'])+','+str(df.ix[i]['Action_Office'])
       if pd.isnull(df.ix[i-1]['Signature/Requester']):
           df.ix[i-1]['Signature/Requester'] = str(df.ix[i-1]['Signature/Requester'])+str(df.ix[i]['Signature/Requester'])
       elif str(df.ix[i-1]['Signature/Requester'])[-1] == '&':
           df.ix[i-1]['Signature/Requester'] = str(df.ix[i-1]['Signature/Requester'])+' '+str(df.ix[i]['Signature/Requester'])
       else:
           df.ix[i-1]['Signature/Requester'] = str(df.ix[i-1]['Signature/Requester'])+','+str(df.ix[i]['Signature/Requester'])
       df.drop(df.index[i])

为什么drop()不起作用?我正在尝试删除当前行(如果它的“['Control##]”为空),以便下一行(其“['Control##]”为空)可以迭代地添加到上一行(其“['Control##]”不为空)。。
非常感谢!!

最佳答案

我认为您需要将行组合在一起,然后将列值连接起来。棘手的部分是找到一种方法,以您想要的方式将行组合在一起。这是我的解决方案。。。
1)将行分组:静态变量
由于组依赖于行中的序列,所以我在方法中使用了一个静态变量来将每一行标记为特定组

def rolling_group(val):
    if pd.notnull(val): rolling_group.group +=1 #pd.notnull is signal to switch group
    return rolling_group.group
rolling_group.group = 0 #static variable

此方法沿控制序列应用,将索引排序到组中,然后使用该方法拆分数据帧,以便合并行
#groups = df.groupby(df['Control'].apply(rolling_group),as_index=False)

这确实是之后唯一棘手的部分,您可以通过对每个组应用一个函数来合并行,该函数为您提供所需的输出
完整解决方案代码
def rolling_group(val):
    if pd.notnull(val): rolling_group.group +=1 #pd.notnull is signal to switch group
    return rolling_group.group
rolling_group.group = 0 #static variable

def joinFunc(g,column):
    col =g[column]
    joiner = "/" if column == "Action" else ","
    s = joiner.join([str(each) for each in col if pd.notnull(each)])
    s = re.sub("(?<=&)"+joiner," ",s) #joiner = " "
    s = re.sub("(?<=-)"+joiner,"",s) #joiner = ""
    s = re.sub(joiner*2,joiner,s)    #fixes double joiner condition
    return s

#在上面编辑-str(each)-以转换为字符串。。。
在正则表达式上方编辑以清除联接字符串联接
if __name__ == "__main__":
    df = """      Control      Recd_Date/Due_Date                Action        Signature/Requester
0     2000-1703   2000-01-31 00:00:00           OC/OER/OPA/PMS/                 M WEBB
1           NaN   2000-02-29 00:00:00                       NaN              DATA CORP
2     2000-1776   2000-01-02 00:00:00            OC/ORA/OE/DCP/                  G KAN
3           NaN   2000-01-03 00:00:00           OC/ORA/ORO/PNC/              PALM POST
4           NaN                   NaN  FDA/OGROP/ORA/SE-FO/FLA-                    NaN
5           NaN                   NaN                DO/FLA-CB/                    NaN
6     2000-1983   2000-02-02 00:00:00  FDA/OGROP/ORA/CE-FO/CHI-                 M EGAN
7           NaN   2000-02-03 00:00:00                DO/CHI-CB/   BERNSTEIN LIEBHARD &
8           NaN                   NaN                       NaN             LONDON LLP"""
    df =  pd.read_csv(StringIO.StringIO(df),sep = "\s\s+",engine='python')

    groups = df.groupby(df['Control'].apply(rolling_group),as_index=False)
    groupFunct = lambda g: pd.Series([joinFunc(g,col) for col in g.columns],index=g.columns)
    print groups.apply(groupFunct)

输出
     Control                       Recd_Date/Due_Date  \
0  2000-1703  2000-01-31 00:00:00,2000-02-29 00:00:00
1  2000-1776  2000-01-02 00:00:00,2000-01-03 00:00:00
2  2000-1983  2000-02-02 00:00:00,2000-02-03 00:00:00

                                              Action  \
0                                    OC/OER/OPA/PMS/
1  OC/ORA/OE/DCP/OC/ORA/ORO/PNC/FDA/OGROP/ORA/SE-...
2                 FDA/OGROP/ORA/CE-FO/CHI-DO/CHI-CB/

                      Signature/Requester
0                        M WEBB,DATA CORP
1                         G KAN,PALM POST
2  M EGAN,BERNSTEIN LIEBHARD & LONDON LLP

08-26 21:09
查看更多