python - 如何将类似的列名称组合到 Pandas 中的单独行中

Question

如果我有以下数据并读入，我会得到类似列的列名称带有 .1 或 .2 的列。数据如下:

import io
dfff=io.StringIO("""address,phone,name,website,type,address,phone,name,website,type,address,phone,name,type
123 APPLE STREET,555-5555,APPLE STORE,APPLE.COM,BUSINESS,456 peach ave,777-7777,PEACH STORE,PEACH.COM,BUSINESS,789 banana rd,999-9999,banana store,BUSINESS""")

dfff=io.StringIO("""address,phone,name,website,type,address,phone,name,website,type,address,phone,name,type 
123 APPLE STREET,555-5555,APPLE STORE,APPLE.COM,BUSINESS,456 peach ave,777-7777,PEACH STORE,PEACH.COM,BUSINESS,789 banana rd,999-9999,banana store,BUSINESS""") 
dfff.seek(0)
newdf2=pd.read_csv(dfff)

这是输出，pandas 将类似的列名称重命名为 .1 或 .2。

newdf2
#            address     phone         name    website      type      address.1   phone.1       name.1  website.1    type.1      address.2   phone.2        name.2    type.2
#0  123 APPLE STREET  555-5555  APPLE STORE  APPLE.COM  BUSINESS  456 peach ave  777-7777  PEACH STORE  PEACH.COM  BUSINESS  789 banana rd  999-9999  banana store  BUSINESS

如何将类似的地址行组合成单独的行，以获得此输出(因为没有 website.2，它将是 NaN 或 0 或空白):

#            address     phone         name    website      type      
#0  123 APPLE STREET  555-5555  APPLE STORE  APPLE.COM  BUSINESS
#1     456 peach ave  777-7777  PEACH STORE  PEACH.COM  BUSINESS
#2     789 banana rd  999-9999  banana store       NaN  BUSINESS

现在，我真的不知道从哪里开始，但我尝试堆叠数据，这按预期工作，但取消堆叠只会返回原始数据:

newdf2.stack().to_frame()
#                            0
#0 address    123 APPLE STREET
#  phone              555-5555
#  name            APPLE STORE
#  website           APPLE.COM
#  type               BUSINESS
#  address.1     456 peach ave
#  phone.1            777-7777
#  name.1          PEACH STORE
#  website.1         PEACH.COM
#  type.1             BUSINESS
#  address.2     789 banana rd
#  phone.2            999-9999
#  name.2         banana store
#  type.2             BUSINESS

我想一定有一种方法可以堆叠，从列中删除 .，然后将其拆分为我想要的格式？或者也许还有其他方法？

Answer 1

您可以使用wide_to_long。

df.columns = [f'{x}.0' if '.' not in x else x for x in df.columns]
df['id'] = df.index

df = pd.wide_to_long(df, stubnames=['address', 'phone', 'name', 'website', 'type'], i='id', j='row', sep='.')

df.reset_index(drop=True)

Out[1]: 
            address     phone          name    website      type
0  123 APPLE STREET  555-5555   APPLE STORE  APPLE.COM  BUSINESS
1     456 peach ave  777-7777   PEACH STORE  PEACH.COM  BUSINESS
2     789 banana rd  999-9999  banana store        NaN  BUSINESS