pandas ：从Excel解析合并标题列 [英] Pandas: parse merged header columns from Excel
问题描述
Excel表格中的数据存储如下：
The data in excel sheets is stored as follows:
Area | Product1 | Product2 | Product3
| sales|sales.Value| sales |sales.Value | sales |sales.Value
Location1 | 20 | 20000 | 25 | 10000 | 200 | 100
Location2 | 30 | 30000 | 3 | 12300 | 213 | 10
产品名称是两行2个单元格的合并销售数量和销售额价值为一个月的1000个左右的区域。同样地，过去5年里每个月都有不同的文件。此外，新产品在不同月份被添加和删除。因此，不同的月份文件可能如下所示：
the product name is a merge of 2 cells of two rows "no of sales" and "sales value" for each of 1000 or so areas for a given month. Similarly there are separate files for each month for the last 5 years. Further, new products have been added and removed in different months. So a different month file might look like:
Area | Product1 | Product4 | Product3
论坛可以建议使用熊猫阅读此数据的最佳方式吗？
我不能使用索引，因为产品列每个月不同
Can the forum suggest the best way to read this data using pandas? I can't use index since the product columns are different each month
理想情况下，我想将上面的初始格式转换为：
Ideally, I would like to convert the initial format above to:
Area | Product1.sales|Product1.sales.Value| Product2.sales |Product2.sales.Value |
Location1 | 20 | 20000 | 25 | 10000 |
Location2 | 30 | 30000 | 3 | 12300 |
import pandas as pd
xl_file = read_excel("file path", skiprow=2, sheetname=0)
/* since the first two rows are always blank */
0 1 2 3 4
0 NaN NaN NaN Auto loan NaN
1 Branch Code Branch Name Region No of accounts Portfolio Outstanding
2 3000 Name1 Central 0 0
3 3001 Name2 Central 0 0
我想将其转换为汽车贷款。 ，自动贷款。组合优秀作为标题。
I want to convert it to Auto loan.No of account, Auto loan.Portfolio Outstanding as the headers.
推荐答案
假设你的DataFrame是 df ：
import numpy as np
import pandas as pd
nan = np.nan
df = pd.DataFrame([
(nan, nan, nan, 'Auto loan', nan)
, ('Branch Code', 'Branch Name', 'Region', 'No of accounts'
, 'Portfolio Outstanding')
, (3000, 'Name1', 'Central', 0, 0)
, (3001, 'Name2', 'Central', 0, 0)
])
所以它看起来像这样：
0 1 2 3 4
0 NaN NaN NaN Auto loan NaN
1 Branch Code Branch Name Region No of accounts Portfolio Outstanding
2 3000 Name1 Central 0 0
3 3001 Name2 Central 0 0
然后首先向前填充NaNs前两行（例如，传播Auto
贷款）。
Then first forward fill the NaNs in the first two rows (thus propagating 'Auto loan', for example).
df.iloc[0:2] = df.iloc[0:2].fillna(method='ffill', axis=1)
接下来用空字符串填充剩余的NaN：
Next fill in the remaining NaNs with empty strings:
df.iloc[0:2] = df.iloc[0:2].fillna('')
现在将两行连同。并将其分配为列级别值：
Now join the two rows together with . and assign that as the column level values:
df.columns = df.iloc[0:2].apply(lambda x: '.'.join([y for y in x if y]), axis=0)
最后，删除第一个两行：
And finally, remove the first two rows:
df = df.iloc[2:]
这样产生
Branch Code Branch Name Region Auto loan.No of accounts \
2 3000 Name1 Central 0
3 3001 Name2 Central 0
Auto loan.Portfolio Outstanding
2 0
3 0
，您可以创建一个MultiIndex列，而不是创建一个平面列索引：
Alternatively, you could create a MultiIndex column instead of creating a flat column index:
import numpy as np
import pandas as pd
nan = np.nan
df = pd.DataFrame([
(nan, nan, nan, 'Auto loan', nan)
, ('Branch Code', 'Branch Name', 'Region', 'No of accounts'
, 'Portfolio Outstanding')
, (3000, 'Name1', 'Central', 0, 0)
, (3001, 'Name2', 'Central', 0, 0)
])
df.iloc[0:2] = df.iloc[0:2].fillna(method='ffill', axis=1)
df.iloc[0:2] = df.iloc[0:2].fillna('Area')
df.columns = pd.MultiIndex.from_tuples(
zip(*df.iloc[0:2].to_records(index=False).tolist()))
df = df.iloc[2:]
现在 df 如下所示：
Area Auto loan
Branch Code Branch Name Region No of accounts Portfolio Outstanding
2 3000 Name1 Central 0 0
3 3001 Name2 Central 0 0
列是一个MultiIndex：
the column is a MultiIndex:
In [275]: df.columns
Out[275]:
MultiIndex(levels=[[u'Area', u'Auto loan'], [u'Branch Code', u'Branch Name', u'No of accounts', u'Portfolio Outstanding', u'Region']],
labels=[[0, 0, 0, 1, 1], [0, 1, 4, 2, 3]])
列有两个级别。第一级有价值 [u'Area'，u'Auto loan'] ，第二级有价值 [u'Branch Code'，u'分支机构名称'，u'No of accounts'，u'Portfolio Outstanding'，u'Region'] 。
The column has two levels. The first level has values [u'Area', u'Auto loan'], the second has values [u'Branch Code', u'Branch Name', u'No of accounts', u'Portfolio Outstanding', u'Region'].
然后您可以访问列指定两个级别的值：
You can then access a column by specifing the value from both levels:
print(df.loc[:, ('Area', 'Branch Name')])
# 2 Name1
# 3 Name2
# Name: (Area, Branch Name), dtype: object
print(df.loc[:, ('Auto loan', 'No of accounts')])
# 2 0
# 3 0
# Name: (Auto loan, No of accounts), dtype: object
使用MultiIndex的一个优点是可以轻松选择所有具有一定级别值的列。例如，要选择与自动贷款有关的子DataFrame，您可以使用：
One advantage of using a MultiIndex is that you can easily select all columns which have a certain level value. For instance, to select the sub-DataFrame having to do with Auto loans you could use:
In [279]: df.loc[:, 'Auto loan']
Out[279]:
No of accounts Portfolio Outstanding
2 0 0
3 0 0
有关从MultiIndex中选择行和列的更多信息，请参阅使用切片器进行MultiIndexing 。
For more on selecting rows and columns from a MultiIndex, see MultiIndexing Using Slicers.
这篇关于 pandas ：从Excel解析合并标题列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
