如何使用 pandas 读取文本文件的键，值对? [英] How to read text file's key, value pair using pandas?

问题描述

我想解析一个包含以下数据的文本文件.

I want to parse one text file which contains following data.

Input.txt-

1=88|11=1438|15=KKK|45=7.7|45=00|21=66|86=a
4=13|4=1388|49=DDD|8=157.73|67=00|45=08|84=b|45=k
6=84|41=18|56=TTT|67=1.2|4=21|45=78|07=d

在此输入文本文件中，没有固定的列，它可以是10或20或任何其他值.我想使用熊猫解析此文件.输出应包含:

In this input text file no columns are fixed it may be 10 or 20 or anything. I want to parse this file using pandas. Output should contain :

output.txt-

index[0]
1     88
11    1438
15    kkk
45    7.7
45    00
21    66
86    a

index[1]
4     13
4     1388
49    DDD
8     157.73
67    00
45    08
84    b
45    k

关于如何获得这种结果的任何建议吗?

Any suggestions about how I can get this type of result?

推荐答案

您可以首先 read_csv ，分隔符不在数据中，例如;，然后将 split stack 的a>:

You can first read_csv with separator which is not in data e.g. ;, then double split with stack:

import pandas as pd
import numpy as np
import io

temp=u"""1=88|11=1438|15=KKK|45=7.7|45=00|21=66|86=a
4=13|4=1388|49=DDD|8=157.73|67=00|45=08|84=b|45=k
6=84|41=18|56=TTT|67=1.2|4=21|45=78|07=d
"""
#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp), sep=";", index_col=None, names=['text'])

print (df)
                                                text
0        1=88|11=1438|15=KKK|45=7.7|45=00|21=66|86=a
1  4=13|4=1388|49=DDD|8=157.73|67=00|45=08|84=b|45=k
2           6=84|41=18|56=TTT|67=1.2|4=21|45=78|07=d

s = df.text.str.split('|', expand=True).stack().str.split('=', expand=True)
print (s)
      0       1
0 0   1      88
  1  11    1438
  2  15     KKK
  3  45     7.7
  4  45      00
  5  21      66
  6  86       a
1 0   4      13
  1   4    1388
  2  49     DDD
  3   8  157.73
  4  67      00
  5  45      08
  6  84       b
  7  45       k
2 0   6      84
  1  41      18
  2  56     TTT
  3  67     1.2
  4   4      21
  5  45      78
  6  07       d

dfs = [g.set_index(0).rename_axis(None) for i, g in s.groupby(level=0)]
print (dfs[0])
       1
1     88
11  1438
15   KKK
45   7.7
45    00
21    66
86     a

for i, g in s.groupby(level=0):
    print (g.set_index(0).rename_axis(None))
       1
1     88
11  1438
15   KKK
45   7.7
45    00
21    66
86     a
         1
4       13
4     1388
49     DDD
8   157.73
67      00
45      08
84       b
45       k
      1
6    84
41   18
56  TTT
67  1.2
4    21
45   78
07    d

通过评论

如果需要写入文件s，请使用 to_csv :

If need write to file s, use to_csv:

s.to_csv('file.txt', header=None, index=None, sep='\t')  

通过评论

您可以将列名称设置为空字符串，并通过 rename_axis (pandas 0.18.0中的新功能)，但更常见的是将列名设置为某些文本(例如s.columns = ['idx','a']):

You can set column name to empty string and remove index name by rename_axis (new in pandas 0.18.0), but more common is set column name to some text (e.g. s.columns = ['idx','a']):

s = df.text.str.split('|', expand=True).stack().str.split('=', expand=True)
s.columns = ['idx','']
print (s)
    idx        
0 0   1      88
  1  11    1438
  2  15     KKK
  3  45     7.7
  4  45      00
  5  21      66
  6  86       a
1 0   4      13
  1   4    1388
  2  49     DDD
  3   8  157.73
  4  67      00
  5  45      08
  6  84       b
  7  45       k
2 0   6      84
  1  41      18
  2  56     TTT
  3  67     1.2
  4   4      21
  5  45      78
  6  07       d
dfs = [g.set_index('idx').rename_axis(None) for i, g in s.groupby(level=0)]
print (dfs[0])
1     88
11  1438
15   KKK
45   7.7
45    00
21    66
86     a

这篇关于如何使用 pandas 读取文本文件的键，值对?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！