pandas - 导入带有用户格式表格的 Excel 电子表格

Question

我正在尝试在 Pandas 中导入一个 excel 电子表格，但由于数据“由用户在视觉上构建”以在视觉上对用户友好并且没有使用正确的数据结构，因此我遇到了一些问题。

结果，当在 Pandas 中作为数据框导入时，我得到了这个:

>>> df                 
   Unnamed: 0  Unnamed: 1  Unnamed: 2 Unnamed: 3 Unnamed: 4     Unnamed: 5 Unnamed: 6 Unnamed: 7     Unnamed: 8 Unnamed: 9 Unnamed: 10    Unnamed: 11 Unnamed: 12
0         NaN         NaN         NaN        NaN        NaN            NaN        NaN        NaN            NaN        NaN         NaN            NaN         NaN
1         NaN         NaN         NaN        NaN        NaN            NaN        NaN        NaN            NaN        NaN         NaN            NaN         NaN
2         NaN         NaN         NaN        NaN        NaN            NaN        NaN        NaN            NaN        NaN         NaN            NaN         NaN
3         NaN         NaN         NaN        NaN   ROUTER 1            NaN        NaN   ROUTER 2            NaN        NaN    ROUTER 3            NaN         NaN
4         NaN         NaN         NaN    SERVERS         IP        NETMASK    GATEWAY         IP        NETMASK    GATEWAY          IP        NETMASK     GATEWAY
5         NaN         NaN         NaN   SERVER 1  10.0.0.10  255.255.255.0   10.0.0.1  10.1.0.10  255.255.255.0   10.0.0.1   10.2.0.10  255.255.255.0    10.0.0.1
6         NaN         NaN         NaN   SERVER 2  10.0.1.10  255.255.255.0   10.0.1.1  10.1.1.10  255.255.255.0   10.0.1.1   10.2.1.10  255.255.255.0    10.0.1.1

我想实现的目标 desired output这是:

>>> result
                           ROUTER 1                              ROUTER 2                              ROUTER 3
 SERVERS         IP        NETMASK    GATEWAY         IP        NETMASK    GATEWAY          IP        NETMASK     GATEWAY
 SERVER 1  10.0.0.10  255.255.255.0   10.0.0.1  10.1.0.10  255.255.255.0   10.0.0.1   10.2.0.10  255.255.255.0    10.0.0.1
 SERVER 2  10.0.1.10  255.255.255.0   10.0.1.1  10.1.1.10  255.255.255.0   10.0.1.1   10.2.1.10  255.255.255.0    10.0.1.1

在那里我得到了正确的列多索引(这正是我在 Excel 中所拥有的……) ((ROUTER 1, [ IP, NETMASK, GATEWAY]), (ROUTER 2, [ IP, NETMASK, GATEWAY]), (ROUTER 3, [ IP, NETMASK, GATEWAY]))到目前为止我的尝试相当糟糕..我被困在这里..

>>> pd.read_excel('Test.xlsx',header=3).dropna(axis=1, how='all').stack().unstack(0).reset_index(drop=True).fillna(method='ffill')                                                                                          
          0        1              2              3
0       NaN  SERVERS       SERVER 1       SERVER 2
1  ROUTER 1       IP      10.0.0.10      10.0.1.10
2  ROUTER 1  NETMASK  255.255.255.0  255.255.255.0
3  ROUTER 1  GATEWAY       10.0.0.1       10.0.1.1
4  ROUTER 2       IP      10.1.0.10      10.1.1.10
5  ROUTER 2  NETMASK  255.255.255.0  255.255.255.0
6  ROUTER 2  GATEWAY       10.0.0.1       10.0.1.1
7  ROUTER 3       IP      10.2.0.10      10.2.1.10
8  ROUTER 3  NETMASK  255.255.255.0  255.255.255.0
9  ROUTER 3  GATEWAY       10.0.0.1       10.0.1.1

非常感谢您提前提供帮助。

Answer 1

将您的尝试与@Mayank 的建议相结合，这是我的 2 美分:

>>> pd.read_excel('Test.xls', skiprows=3, usecols="D:M")
        .stack()
        .unstack(0)
        .reset_index(drop=True)
        .fillna(method='ffill')
        .pivot_table(columns=[0,1] aggfunc='first')

我认为这应该给你你正在寻找的东西。