Pandas在每一行中删除包含部分完成数据的重复项并合并d

Question

我有一个具有重复ID的数据帧，但数据在多个区域中部分完成。在

df = pd.DataFrame([[1234, 'Customer A', '123 Street', np.nan, np.nan],
               [1234, 'Customer A', np.nan, '333 Street', np.nan],
               [1234, 'Customer A', '12345 Street', np.nan, np.nan],
               [1234, 'Customer A', np.nan, np.nan, np.nan],
               [1233, 'Customer B', '444 Street', '3335 Street', np.nan],
               [1233, 'Customer B', '555 Street', '666 Street', np.nan],
               [1233, 'Customer B', '553 Street', '666 Street', 'abc@email.com'],
               [1235, 'Customer C', '1553 Street', '644 Street', 'abc@email.com'],
               [1235, 'Customer C', '2553 Street', '644 Street', 'abc@email.com']],     
               columns=['ID', 'Customer', 'Billing Address', 'Shipping Address', 'Contact'])


df
        ID  Customer    Billing Address Shipping Address    Contact
0   1234    Customer A  123 Street      NaN                 NaN
1   1234    Customer A  NaN             333 Street          NaN
2   1234    Customer A  12345 Street    NaN                 NaN
3   1234    Customer A  NaN             NaN                 NaN
4   1233    Customer B  444 Street      3335 Street         NaN
5   1233    Customer B  555 Street      666 Street          NaN
6   1233    Customer B  553 Street      666 Street          abc@email.com
7   1235    Customer C  1553 Street     644 Street          abc@email.com
8   1235    Customer C  2553 Street     644 Street          abc@email.com

我希望保留所有数据，以便在数据存在时创建新列，以便看起来像下面的数据框：

我尝试了以下操作，但它删除了我要保留的数据。在

^{pr2}$

编辑：我添加了更多的数据，因为从最初的帖子中不清楚id可以有多行。在

Answer 1

这里有一种使用apply并创建新列的方法，使用dict创建pd.Series

In [1057]: cols = ['Billing Address', 'Shipping Address']

In [1058]: (df.groupby(['ID', 'Customer'])
              .apply(lambda g: pd.Series({'%s %s' % (x, i+1): v[x] 
                     for i, v in enumerate(g[cols].to_dict('r'))
                     for x in v})))
Out[1058]:
                Billing Address 1 Billing Address 2 Shipping Address 1  \
ID   Customer
1233 Customer B        444 Street        555 Street         333 Street
1234 Customer A        123 Street               NaN                NaN

                Shipping Address 2
ID   Customer
1233 Customer B         666 Street
1234 Customer A         333 Street