python-3.x - 如何将多行 pandas 数据框组合成另外两个行值 python3 之间的一个？

Question

我有一个 pandas 数据框，只有一列包含姓名、地址和电话信息，由空白或 na 行分隔，如下所示:

   data
0  Business name one
1  1234 address ln
2  Town, ST 55655
3  (555) 555-5555
4  nan
5  Business name two
6  5678 address dr
7  New Town, ST 55677
8  nan
9  Business name three
10 nan

等等……

我想要的是:

   Name                 Addr1             Addr2               Phone
0  Business name one    1234 address ln   Town, ST 55655      (555) 555-5555
1  Business name two    5678 address dr   New Town, ST 55677
2  Business name three  

我正在使用 python 3 并且卡住了，非常感谢任何帮助!

Answer 1

您可以使用:

• 使用 isnull 为每一行创建组和 cumsum

• 要与非 NaN 行对齐，请添加 reindex

• 通过 dropna 删除 NaN , set_index使用 cumcount 到 MultiIndex

• reshape unstack

---

a = df['data'].isnull().cumsum().reindex(df.dropna().index)
print (a)
0    0
1    0
2    0
3    0
5    1
6    1
7    1
9    2
Name: data, dtype: int32

---

df = df.dropna().set_index([a, a.groupby(a).cumcount()])['data'].unstack()
df.columns = ['Name','Addr1','Addr2','Phone']
print (df)
                     Name            Addr1               Addr2           Phone
data                                                                          
0       Business name one  1234 address ln      Town, ST 55655  (555) 555-5555
1       Business name two  5678 address dr  New Town, ST 55677            None
2     Business name three             None                None            None

如果有多个 address 可以动态创建列:

df.columns = ['Name'] + 
             ['Addr{}'.format(x+1) for x in range(len(df.columns) - 2)] +
             ['Phone']