问题描述

我有大约 90 个文本文件要转换为 90 个 excel 文件。所有文本文件的格式如下，

HF8 90 156 HYDE,CAROLYN 001306604 28/11/16 2,637.96 QAMAR,ATIF 004379749 17/10/16 3,202.42 ============= 部门 156 总计 - 87,077.64 00270/23 6300/70 维特斯,630 莫尼奎16 2,213.13 TANEGA,MICHELLE 002984474 25/01/16 2,003.56 HF8 90 861 ISTEL,KARIN 008794117 08/02/16 1,422.36 AGUILERA,MARIA 008794455 08/02/16 1,752.

我想将它们转换为以下格式的 Excel，

|---|--|---|----------------|---------|--------|---------|
|SL |NO|DEP|NAME            |ID       |DATE    |SALES    |
|---|--|---|----------------|---------|--------|---------|
|HF8|90|156|HYDE,CAROLYN    |001306604|28/11/16|2,637.96 |
|   |  |   |QAMAR,ATIF      |004379749|17/10/16|3,202.42 |
|   |  |   |                |         |        |=========|
|   |  |   |                |         |TOTALS  |87,077.64|
|   |  |747|WEITHERS,MONIQUE|002790236|30/05/16|2,213.13 |
|   |  |   |TANEGA,MICHELLE |002984474|25/01/16|2,003.56 |
|HF8|90|861|ISTEL,KARIN     |008794117|08/02/16|1,422.36 |
|   |  |   |AGUILERA,MARIA  |008794455|08/02/16|1,752.16 |
|---|--|---|----------------|---------|--------|---------|

标签： python-3.xpandas

您的任务可以完成，尽管它需要非常流利的正则表达式。把这个任务分成以下几个步骤会更方便。

第一步：读取输入文件

将输入文件作为固定宽度的文件读入单列：

df = pd.read_fwf('Input.txt', widths=[100], names=['line'])

对于您的数据，结果是：

                                                line
0  HF8 90 156 HYDE,CAROLYN 001306604 28/11/16 2,6...
1             QAMAR,ATIF 004379749 17/10/16 3,202.42
2                                          =========
3                  DEPARTMENT 156 TOTALS - 87,077.64
4   747 WEITHERS,MONIQUE 002790236 30/05/16 2,213.13
5        TANEGA,MICHELLE 002984474 25/01/16 2,003.56
6  HF8 90 861 ISTEL,KARIN 008794117 08/02/16 1,42...
7         AGUILERA,MARIA 008794455 08/02/16 1,752.16

不要担心“缩短”的线条，这只是 Pandas的呈现细节。每一行的实际内容都是应有的。

第 2 步：提取ID、DATE和SALES列

跑：

pat1 = '''
(?P<Init>.*?)
(?P<ID>\d+)?\s?
(?:DEPARTMENT\s\d+\s)?(?P<DATE>\d{2}/\d{2}/\d{2}|TOTALS)?(?:\s-)?\s*
(?P<SALES>[\d,]+\.\d{2}|=+)'''
df1 = df.line.str.extract(pat1, flags=re.X).fillna('')

命名捕获组提供正确的列名。为了代码可读性，我使用了带有X（扩展）正则表达式标志的正则表达式，因此您必须导入 re。

结果是我们得到 3 个“最后”列和Init列 - 每行的“初始”部分：

                       Init         ID      DATE      SALES
0  HF8 90 156 HYDE,CAROLYN   001306604  28/11/16   2,637.96
1               QAMAR,ATIF   004379749  17/10/16   3,202.42
2                                                 =========
3                                         TOTALS  87,077.64
4     747 WEITHERS,MONIQUE   002790236  30/05/16   2,213.13
5          TANEGA,MICHELLE   002984474  25/01/16   2,003.56
6   HF8 90 861 ISTEL,KARIN   008794117  08/02/16   1,422.36
7           AGUILERA,MARIA   008794455  08/02/16   1,752.16

第 3 步：提取 4 个“初始”列

跑：

pat2 = '''
(?P<SL>[A-Z]+\d+)?\s?
(?P<NO>\d{2})?\s?
(?P<DEP>\d{3})?\s?
(?P<NAME>[A-Z,]+)'''
df2 = df1.Init.str.extract(pat2, flags=re.X).fillna('')

结果是：

    SL  NO  DEP              NAME
0  HF8  90  156      HYDE,CAROLYN
1                      QAMAR,ATIF
2                                
3                                
4           747  WEITHERS,MONIQUE
5                 TANEGA,MICHELLE
6  HF8  90  861       ISTEL,KARIN
7                  AGUILERA,MARIA

第 4 步：连接第 2 步和第 3 步的结果

跑：

pd.concat([df2, df1.iloc[:, 1:]], axis=1)

（最终）结果是：

    SL  NO  DEP              NAME         ID      DATE      SALES
0  HF8  90  156      HYDE,CAROLYN  001306604  28/11/16   2,637.96
1                      QAMAR,ATIF  004379749  17/10/16   3,202.42
2                                                       =========
3                                               TOTALS  87,077.64
4           747  WEITHERS,MONIQUE  002790236  30/05/16   2,213.13
5                 TANEGA,MICHELLE  002984474  25/01/16   2,003.56
6  HF8  90  861       ISTEL,KARIN  008794117  08/02/16   1,422.36
7                  AGUILERA,MARIA  008794455  08/02/16   1,752.16

最后的评论

请注意，DATE和SALES列都存储为字符串。理论上它们应该转换为日期和数字，但问题是某些行的数据格式不同。这是在某些列中插入演示详细信息的副作用。例如TOTAL绝不是任何Date。这同样适用于“=”序列而不是SALES值。

python-3.x - 将文本文件转换为数据框，将不同的值映射到不同的列