file.txt
///// A
13 32 12 13
4 22 34 42
///// B
3 1 34 11
0 NaN 21 1
44 32 33 32
///// C
5 32 11 21
43 23 NaN 3
我正在尝试读取此文件,以便根据/////
之后的字母对值进行分组。期望输出:
0 1 2 3 Group
13 32 12 13 A
4 22 34 42 A
3 1 34 11 B
0 NaN 21 1 B
44 32 33 32 B
5 32 11 21 C
43 23 NaN 3 C
我尝试了pd.read_table
中的大多数选项,但我不知道如何处理分组,因为只有忽略/////
行df = pd.read_table('file.txt', sep=' ', header=None, comment='/')
,我才能读取文件
您可以使用正则表达式读取组标头并计算行数,然后读取文件,将中间标头视为注释并添加组:
with open('file.txt') as f:
groups = re.findall(r'/////s*(w+)|^', f.read(), flags=re.M)
s = pd.Series(groups)
m = s.eq('')
df = pd.read_table('file.txt', sep='s+', header=None, comment='/')
df['group'] = s.mask(m).ffill()[m].values
输出:
0 1 2 3 group
0 13 32.0 12.0 13 A
1 4 22.0 34.0 42 A
2 3 1.0 34.0 11 B
3 0 NaN 21.0 1 B
4 44 32.0 33.0 32 B
5 5 32.0 11.0 21 C
6 43 23.0 NaN 3 C
试试这个:
import numpy as np
import pandas as pd
df_list = []
def converter(x):
try:
return int(x)
except:
return np.nan
with open('file.txt', 'r') as f:
for line in f:
line = line.strip()
if line.startswith('/////'):
group = line[-1]
else:
values = map(converter, line.split())
df_list.append([*values, group])
df = pd.DataFrame(df_list, columns=[*[i for i in range(4)], 'Group'])
这里我用read_csv读取txt文件,并将一列拆分为两个Alpha和num。最后用空格拆分num并创建新列。
代码:
df = pd.read_csv("t1.txt", header=None, skip_blank_lines=False)
df.insert(0, 'Group', df[0].where(df[0].str.startswith('/')).ffill())
df = df[df['Group'].ne(df[0])].reset_index(drop=True).rename(columns={0:'NUM'})
df = df.join(df['NUM'].str.split(' ',3, expand=True).rename(columns={0:'A', 1:'B', 2:'C', 3:'D'}))
清洁:
df['Group'] = df['Group'].apply(lambda x: x.split(' ')[1])
df.drop('NUM', axis=1)
输出:
Group A B C D
0 A 13 32 12 13
1 A 4 22 34 42
2 B 3 1 34 11
3 B 0 NaN 21 1
4 B 44 32 33 32
5 C 5 32 11 21
6 C 43 23 NaN 3