If I were doing something similar in the shell, I would just pipe the
file through sed or something to replace '5 days' with, say '5_days'.
How could I achieve a similar sort of preprocessing in Python, ideally >without having to generate a lot of temporary files?
Hi,
I am using pandas to parse a file with the following structure:
Name fileset type KB quota limit in_doubt grace | files quota limit in_doubt grace
shortname sharedhome USR 14097664 524288000 545259520 0 none | 107110 0 0 0 none
gracedays sharedhome USR 774858944 524288000 775946240 0 5 days | 1115717 0 0 0 none
nametoolong sharedhome USR 27418496 524288000 545259520 0 none | 11581 0 0 0 none
I was initially able to use
df = pandas.read_csv(file_name, delimiter=r"\s+")
because all the values for 'grace' were 'none'. Now, however,
non-"none" values have appeared and this fails.
I can't use
pandas.read_fwf
even with an explicit colspec, because the names in the first column
which are too long for the column will displace the rest of the data to
the right.
The report which produces the file could in fact also generate a
properly delimited CSV file, but I have a lot of historical data in the readable but poorly parsable format above that I need to deal with.
If I were doing something similar in the shell, I would just pipe the
file through sed or something to replace '5 days' with, say '5_days'.
How could I achieve a similar sort of preprocessing in Python, ideally without having to generate a lot of temporary files?
On 11/23/2022 11:00 AM, Loris Bennett wrote:
Hi,
I am using pandas to parse a file with the following structure:
Name fileset type KB quota limit
in_doubt grace | files quota limit in_doubt grace
shortname sharedhome USR 14097664 524288000 545259520 0 none | 107110 0 0 0 none
gracedays sharedhome USR 774858944 524288000 775946240 0 5 days | 1115717 0 0 0 none
nametoolong sharedhome USR 27418496 524288000 545259520 0 none | 11581 0 0 0 none
I was initially able to use
df = pandas.read_csv(file_name, delimiter=r"\s+")
because all the values for 'grace' were 'none'. Now, however,
non-"none" values have appeared and this fails.
I can't use
pandas.read_fwf
even with an explicit colspec, because the names in the first column
which are too long for the column will displace the rest of the data to
the right.
The report which produces the file could in fact also generate a
properly delimited CSV file, but I have a lot of historical data in the
readable but poorly parsable format above that I need to deal with.
If I were doing something similar in the shell, I would just pipe
the
file through sed or something to replace '5 days' with, say '5_days'.
How could I achieve a similar sort of preprocessing in Python, ideally
without having to generate a lot of temporary files?
This is really annoying, isn't it? A space-separated line with spaces
in data entries. If the example you give is typical, I don't think
there is a general solution. If you know there are only certain
values like that, then you could do a search-and-replace for them in
Python just like the example you gave for "5 days".
If you know that the field that might contain entries with spaces is
the same one, e.g., the one just before the "|" marker, you could make
use of that. But it could be tricky.
I don't know how many files like this you will need to process, nor
how many rows they might contain. If I were to do tackle this job, I
would probably do some quality checking first. Using this example
file, figure out how many fields there are supposed to be. First,
split the file into lines:
with open("filename") as f:
lines = f.readlines()
# Check space-separated fields defined in first row:
fields = lines[0].split()
num_fields = len(fields)
print(num_fields) # e.g., 100)
# Find lines that have the wrong number of fields
bad_lines = []
for line in lines:
fields = line.split()
if len(fields) != num_fields:
bad_lines.append(line)
print(len(bad_lines))
# Inspect a sample
for line in bad_lines[:10]:
print(line)
This will give you an idea of how many problems lines there are, and
if they can all be fixed by a simple replacement. If they can and
this is the only file you need to handle, just fix it up and run it.
I would replace the spaces with tabs or commas. Splitting a line on
spaces (split()) takes care of the issue of having a variable number
of spaces, so that's easy enough.
If you will need to handle many files, and you can automate the fixes
- possibly with a regular expression - then you should preprocess each
file before giving it to pandas. Something like this:
def fix_line(line):
"""Test line for field errors and fix errors if any."""
# ....
return fixed
# For each file
with open("filename") as f:
lines = f.readlines()
fixed_lines = []
for line in lines:
fixed = fix_line(line)
fields = fixed.split()
tabified = '\t'.join(fields) # Could be done by fix_line()
fixed_lines.append(tabified)
# Now use an IOString to feed the file to pandas
# From memory, some details may not be right
f = IOString()
f.writelines(fixed_lines)
# Give f to pandas as if it were an external file
# ...
Thomas Passin <list1@tompassin.net> writes:
On 11/23/2022 11:00 AM, Loris Bennett wrote:
Hi,
I am using pandas to parse a file with the following structure:
Name fileset type KB quota limit
in_doubt grace | files quota limit in_doubt grace
shortname sharedhome USR 14097664 524288000 545259520 0 none | 107110 0 0 0 none
gracedays sharedhome USR 774858944 524288000 775946240 0 5 days | 1115717 0 0 0 none
nametoolong sharedhome USR 27418496 524288000 545259520 0 none | 11581 0 0 0 none
I was initially able to use
df = pandas.read_csv(file_name, delimiter=r"\s+")
because all the values for 'grace' were 'none'. Now, however,
non-"none" values have appeared and this fails.
I can't use
pandas.read_fwf
even with an explicit colspec, because the names in the first column
which are too long for the column will displace the rest of the data to
the right.
The report which produces the file could in fact also generate a
properly delimited CSV file, but I have a lot of historical data in the
readable but poorly parsable format above that I need to deal with.
If I were doing something similar in the shell, I would just pipe
the
file through sed or something to replace '5 days' with, say '5_days'.
How could I achieve a similar sort of preprocessing in Python, ideally
without having to generate a lot of temporary files?
This is really annoying, isn't it? A space-separated line with spaces
in data entries. If the example you give is typical, I don't think
there is a general solution. If you know there are only certain
values like that, then you could do a search-and-replace for them in
Python just like the example you gave for "5 days".
If you know that the field that might contain entries with spaces is
the same one, e.g., the one just before the "|" marker, you could make
use of that. But it could be tricky.
I don't know how many files like this you will need to process, nor
how many rows they might contain. If I were to do tackle this job, I
would probably do some quality checking first. Using this example
file, figure out how many fields there are supposed to be. First,
split the file into lines:
with open("filename") as f:
lines = f.readlines()
# Check space-separated fields defined in first row:
fields = lines[0].split()
num_fields = len(fields)
print(num_fields) # e.g., 100)
# Find lines that have the wrong number of fields
bad_lines = []
for line in lines:
fields = line.split()
if len(fields) != num_fields:
bad_lines.append(line)
print(len(bad_lines))
# Inspect a sample
for line in bad_lines[:10]:
print(line)
This will give you an idea of how many problems lines there are, and
if they can all be fixed by a simple replacement. If they can and
this is the only file you need to handle, just fix it up and run it.
I would replace the spaces with tabs or commas. Splitting a line on
spaces (split()) takes care of the issue of having a variable number
of spaces, so that's easy enough.
If you will need to handle many files, and you can automate the fixes
- possibly with a regular expression - then you should preprocess each
file before giving it to pandas. Something like this:
def fix_line(line):
"""Test line for field errors and fix errors if any."""
# ....
return fixed
# For each file
with open("filename") as f:
lines = f.readlines()
fixed_lines = []
for line in lines:
fixed = fix_line(line)
fields = fixed.split()
tabified = '\t'.join(fields) # Could be done by fix_line()
fixed_lines.append(tabified)
# Now use an IOString to feed the file to pandas
# From memory, some details may not be right
f = IOString()
f.writelines(fixed_lines)
# Give f to pandas as if it were an external file
# ...
Thanks to both Gerard and Thomas for the pointer to IOString. I ended up just reading the file line-by-line, using a regex to replace
'<n> <units> |'
with
'<n><units> |'
and writing the new lines to an IOString, which I then passed to pandas.read_csv.
The wrapper approach looks interesting, but it looks like I need to read
up more on contexts before adding that to my own code, otherwise I may
not understand it in a month's time.
On 11/24/2022 9:06 AM, Loris Bennett wrote:
Thomas Passin <list1@tompassin.net> writes:
On 11/23/2022 11:00 AM, Loris Bennett wrote:Thanks to both Gerard and Thomas for the pointer to IOString. I
Hi,
I am using pandas to parse a file with the following structure:
Name fileset type KB quota limit
in_doubt grace | files quota limit in_doubt grace
shortname sharedhome USR 14097664 524288000 545259520 0 none | 107110 0 0 0 none
gracedays sharedhome USR 774858944 524288000 775946240 0 5 days | 1115717 0 0 0 none
nametoolong sharedhome USR 27418496 524288000 545259520 0 none | 11581 0 0 0 none
I was initially able to use
df = pandas.read_csv(file_name, delimiter=r"\s+")
because all the values for 'grace' were 'none'. Now, however,
non-"none" values have appeared and this fails.
I can't use
pandas.read_fwf
even with an explicit colspec, because the names in the first column
which are too long for the column will displace the rest of the data to >>>> the right.
The report which produces the file could in fact also generate a
properly delimited CSV file, but I have a lot of historical data in the >>>> readable but poorly parsable format above that I need to deal with.
If I were doing something similar in the shell, I would just pipe
the
file through sed or something to replace '5 days' with, say '5_days'.
How could I achieve a similar sort of preprocessing in Python, ideally >>>> without having to generate a lot of temporary files?
This is really annoying, isn't it? A space-separated line with spaces
in data entries. If the example you give is typical, I don't think
there is a general solution. If you know there are only certain
values like that, then you could do a search-and-replace for them in
Python just like the example you gave for "5 days".
If you know that the field that might contain entries with spaces is
the same one, e.g., the one just before the "|" marker, you could make
use of that. But it could be tricky.
I don't know how many files like this you will need to process, nor
how many rows they might contain. If I were to do tackle this job, I
would probably do some quality checking first. Using this example
file, figure out how many fields there are supposed to be. First,
split the file into lines:
with open("filename") as f:
lines = f.readlines()
# Check space-separated fields defined in first row:
fields = lines[0].split()
num_fields = len(fields)
print(num_fields) # e.g., 100)
# Find lines that have the wrong number of fields
bad_lines = []
for line in lines:
fields = line.split()
if len(fields) != num_fields:
bad_lines.append(line)
print(len(bad_lines))
# Inspect a sample
for line in bad_lines[:10]:
print(line)
This will give you an idea of how many problems lines there are, and
if they can all be fixed by a simple replacement. If they can and
this is the only file you need to handle, just fix it up and run it.
I would replace the spaces with tabs or commas. Splitting a line on
spaces (split()) takes care of the issue of having a variable number
of spaces, so that's easy enough.
If you will need to handle many files, and you can automate the fixes
- possibly with a regular expression - then you should preprocess each
file before giving it to pandas. Something like this:
def fix_line(line):
"""Test line for field errors and fix errors if any."""
# ....
return fixed
# For each file
with open("filename") as f:
lines = f.readlines()
fixed_lines = []
for line in lines:
fixed = fix_line(line)
fields = fixed.split()
tabified = '\t'.join(fields) # Could be done by fix_line()
fixed_lines.append(tabified)
# Now use an IOString to feed the file to pandas
# From memory, some details may not be right
f = IOString()
f.writelines(fixed_lines)
# Give f to pandas as if it were an external file
# ...
ended up
just reading the file line-by-line, using a regex to replace
'<n> <units> |'
with
'<n><units> |'
and writing the new lines to an IOString, which I then passed to
pandas.read_csv.
The wrapper approach looks interesting, but it looks like I need to
read
up more on contexts before adding that to my own code, otherwise I may
not understand it in a month's time.
Glad that IOString works for you here. I seem to remember that after
writing to the IOString, you have to seek to 0 before reading from
it. Better check that point!
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 300 |
| Nodes: | 16 (2 / 14) |
| Uptime: | 75:43:01 |
| Calls: | 6,716 |
| Calls today: | 4 |
| Files: | 12,247 |
| Messages: | 5,357,403 |