I had a some issue about defining multiple seperator when reading a file. It is originally solved in my previous post reading-files-with-multiple-delimiter-in-column-headers-and-skipping-some-rows thanks to @piRsquared
When I looked in detail to my real data I realized that some columns have .cd or .dvd extensions and when I applied the solution above they are also separated as a new column and the solution above started not to work!
b.txt
skip1
A1| A2 |A3 |A4# A5# A6 A7| A8 , A9
1,2,3,4,5.cd,6,7,8.dvd,9
1,2,3,4,5.cd,6,7,8.dvd,9
1,2,3,4,5.cd,6,7,8.dvd,9
END123
Some other data starts from here
and read this b.txt file using the solution above
txt = open('b.txt').read().split('\nEND')[0]
pd.read_csv(
pd.io.common.StringIO(txt),
sep=r'\W+',
skiprows=1,index_col=False, engine='python')
A1 A2 A3 A4 A5 A6 A7 A8 A9
0 1 2 3 4 5 cd 6 7 8
1 1 2 3 4 5 cd 6 7 8
2 1 2 3 4 5 cd 6 7 8
A5 column should have the rows
5.cd
5.cd
5.cd
and same for the A9 column
8.dvd
8.dvd
8.dvd
and we should have A9 column but seems that it disappears due to this conflict.
EDIT:
I put almost similar identity to my real data
skip rows
A1| A2| A3|A4# A5# | A6 | A7 , A8, A9 | A10 |
1 | 2 | 3 |4 # 5 # | 6.cd|7.dvd, , | 10 |
1 | 2 | 3 |4 # 5 # | 6.cd| , , 9 | 10 |
1 | 2 | 3 |4 # 5 # | |7.dvd, , | 10 |
END123
Some other data starts from here
and tried
txt = open('real_dat.txt').read().split('\nEND')[0]
_, h, txt = txt.split('\n', 2)
pat = r'[\|, ,#,\,]+'
names = re.split(pat, h.strip())
df=pd.read_csv(
pd.io.common.StringIO(txt),
names=names,skiprows=1,index_col=False,
engine='python')
and got this output!
