数据帧中字符串上的行合并？

Question

我有一个电话目录，存储部门，标题，电子邮件和分机在不同的行，共同的东西是名字和姓氏。我已经将名字和姓氏合并为一个键，并希望将这些行合并到一个单独的行，其中包含姓名、职务、部门、电子邮件和分机名。你知道吗

我曾尝试为每个键创建一个字典，但在实际的合并中没有任何运气。这就是我在编代码的地方。我必须先清理数据才能得到合适的列。你知道吗

该表如下所示：

LastName  FirstName  Department Title   Extension Email           Key
Doe       Jane       HR         Officer 0000                      Jane Doe
Doe       Jane       HR         Officer           jdoe@email.com  Jane Doe

df = pd.read_excel("Directory.xlsx")
df = df.drop(columns = ["group_name","editable","id","contact_type","id2","account_id","server_uuid","picture",
             "dial_prefix","name","label","id3","transfer_name","value","key","primary","label4","id5",
             "type","display","group_name6"])

df = df.rename(index = str, columns = {"last_name":"Last Name","first_name":"First Name","location":"Department",
               "title":"Title","dial":"Extension","address":"Email"})

df["Key"] = df["First Name"].map(str) + " " + df["Last Name"].map(str)

LastName FirstName Department Title   Extension Email          Key  
Doe      Jane      HR         Officer 0000      jdoe@email.com Jane Doe

Answer 1

首先，我们使用^{}将空格替换为NaN。然后使用^{}并将^{}与方法backfill和forwardfill一起应用来填充空白。最后，我们可以使用^{}获得所需的单行。你知道吗

df['Key'] = df['FirstName'] + ' ' + df['LastName']
df.replace('', np.NaN, inplace=True)
df = df.groupby('Key').apply(lambda x: x.fillna(method='ffill').fillna(method='bfill')).drop_duplicates()

print(df)
  LastName FirstName Department    Title Extension           Email       Key
0      Doe      Jane         HR  Officer      0000  jdoe@email.com  Jane Doe