当前位置: 首页 > 面试题库 >

pandas:从Excel中解析合并的标题列

孙德本
2023-03-14
问题内容

Excel工作表中的数据存储如下:

   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

产品名称是给定月份的1000个左右区域中每个区域的两行“ no no sales”和“ sales
value”两行的合并。同样,最近5年的每个月都有单独的文件。此外,新产品已在不同月份中添加和删除。因此,其他月份的文件可能类似于:

   Area     |          Product1     |      Product4        |      Product3

论坛可以建议使用熊猫读取此数据的最佳方法吗?我不能使用索引,因为产品列每月不同

理想情况下,我想将上面的初始格式转换为:

 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

我想将其转换为Auto loan.No of accountAuto loan.Portfolio Outstanding作为标题。


问题答案:

假设您的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

然后,将前两行中的NaN向前填充(例如,传播“自动借贷”)。

df.iloc[0:2] = df.iloc[0:2].fillna(method='ffill', axis=1)

接下来,用空字符串填充其余的NaN:

df.iloc[0:2] = df.iloc[0:2].fillna('')

现在,将这两行与结合在一起,.并将其分配为列级值:

df.columns = df.iloc[0:2].apply(lambda x: '.'.join([y for y in x if y]), axis=0)

最后,删除前两行:

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列而不是创建一个扁平列索引:

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:

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'Branch Name', u'No of accounts', u'Portfolio Outstanding', u'Region']

然后,您可以通过指定两个级别的值来访问列:

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,Auto loans可以使用:

In [279]: df.loc[:, 'Auto loan']
Out[279]: 
  No of accounts Portfolio Outstanding
2              0                     0
3              0                     0

有关从MultiIndex中选择行和列的更多信息,请参见使用切片器进行MultiIndexing。



 类似资料:
  • 问题内容: 可以说我有一个DataFrame如下: 我想创建一个新的DataFrame像这样: 可能的代码是什么? 问题答案: 1.使用Python 3.6+更新,使用带有列表理解功能的f字符串格式: 2.使用和: 3.如果您的列具有数字数据类型,请使用和: 输出:

  • 本文向大家介绍Python pandas实现excel工作表合并功能详解,包括了Python pandas实现excel工作表合并功能详解的使用技巧和注意事项,需要的朋友参考一下 获取工作表2内容 合并文件包含三个工作表:完成任务,下周计划,本周工时(包含了全年的数据,所以需要抽取)。 合并后得文件,如图: 以上就是关于Python_pandas实现excel工作表合并功能的全部实例内容,感谢大家

  • 问题内容: 我有一个带有4列的(example-)数据框: 我现在想将B,C和D列合并/合并到新的E列,如本例所示: 我在这里发现了一个非常类似的问题,但这在A列的末尾添加了合并的列B,C和D: 感谢帮助。 问题答案: 选项1 使用和 选项2 使用分配和 选项3 最近,我喜欢第3个选项。 使用

  • 问题内容: 我有包含多个工作表的Excel文件,每个工作表看起来都像这样(但更长): 第一列实际上是四个垂直合并的单元格。 当我使用pandas.read_excel阅读此内容时,我得到一个看起来像这样的DataFrame: 如何让Pandas理解合并的单元格,或者快速方便地删除NaN并按适当的值分组?(一种方法是重置索引,逐步查找值并将NaN替换为值,传入天数列表,然后将索引设置为该列。但是似乎

  • 问题内容: 如何执行与pandas(LEFT| RIGHT| FULL)(INNER| OUTER)的联接? 合并后如何为缺失的行添加NaN? 合并后如何去除NaN? 我可以合并索引吗? 与pandas交会吗? 如何合并多个DataFrame? merge?join?concat?update?Who? What? Why?! … 和更多。我已经看到这些重复出现的问题,询问有关熊猫合并功能的各个方

  • 我试图结合2个不同的Excel文件。(多亏了post导入多个excel文件到python pandas中,并将它们连接到一个dataframe中) 到目前为止,我得出的结论是: 下面是他们的样子。 null