What i want to achieve:
I want to create a (Pandas) data frame from a text file with variable-width formatted lines. For example the text file looks like
Time_stamp:0.0, Column_0:1.0, Column_1:2.0
Time_stamp:1.0, Column_2:3.0, Column_3:4.0, Column_4:5.0
Time_stamp:2.0, Column_5:6.0
Time_stamp:3.0, Column_2:3.0, Column_3:4.0, Column_4:5.0
Time_stamp:4.0, Column_0:1.0, Column_1:2.0
Time_stamp:5.0, Column_2:3.0, Column_3:4.0, Column_4:5.0
...
and the file size can be up to several GBs (> 1 million lines). At the end, i want to convert the data from the text file to a data frame with a similiar structure to
| Time_stamp | Column_0 | Column_1 | Column_2 | Column_3 | Column_4 | Column_5 |
|---|---|---|---|---|---|---|
| 0.0 | 1.0 | 2.0 | NULL | NULL | NULL | NULL |
| 1.0 | NULL | NULL | 3.0 | 4.0 | 5.0 | NULL |
| 2.0 | NULL | NULL | NULL | NULL | NULL | 6.0 |
| 3.0 | NULL | NULL | 3.0 | 4.0 | 5.0 | NULL |
| 4.0 | 1.0 | 2.0 | NULL | NULL | NULL | NULL |
| 5.0 | NULL | NULL | 3.0 | 4.0 | 5.0 | NULL |
The final data frame will have around 140 columns.
What i have tried:
To create a Pandas data frame from the text file, I use the following Python function that creates a Python dictionary for each line in the text file with the column name as the key. After a certain number of lines, all dictionaries are converted to a Pandas data frame with sparse data types to avoid high memory consumption. This step is repeated until all lines in the text file are processed. At the end all pandas data frames are concatenated to one data frame.
def generate_data_frame(data: TextIO, delimiter: list, save_point: int) -> pd.DataFrame:
"""generate_data_frame takes a text file pointer and builds a Pandas data frame out of it
Args:
data (TextIO): a python file pointer with lines containing data
separated by the delimiters
delimiter (list): char separating the data in a line
save_point (int): number of lines from which the data is transformed to
a Pandas data frame. Regulates the memory usage
Returns:
pandas.DataFrame: containing data in tabular form with columns
casted as sparse data type
"""
# saves the dictionaries for each line of the data
line_dictionaries = []
# after a specified number of iterations (save_point) the dictionaries of
# the lines are converted to a Pandas data frame with columns
# casted as sparse data type. These Pandas data frames are saved in
# list_of_sub_data_frames and will be concatenated in a later step
list_of_sub_data_frames = []
for i, line in enumerate(data):
# each line is saved as dictionary with the column names as keys
line_dict = {}
# the data in each line can separated by first delimiter, default ","
columns = line.split(delimiter[0])
for column in columns:
column_parts = column.split(delimiter[1])
# first element of the column_parts is the column name
# second element of the column_parts is the column value in this line
try:
value = column_parts[1].rstrip("\n")
except IndexError as e:
# checks for empty lines in the file
raise IndexError(
f"In line number {i} the data can not be separated in "
"column name and value"
) from e
line_dict[column_parts[0]] = value
line_dictionaries.append(line_dict)
if len(line_dictionaries) >= save_point:
logging.info("save data")
# creates Pandas data frame with sparse data type to reduce the memory
# usage
sub_data_frame = pd.DataFrame.from_dict(
line_dictionaries, dtype=pd.SparseDtype(object, np.nan)
)
list_of_sub_data_frames.append(sub_data_frame)
line_dictionaries = []
# save last data in data frame
sub_data_frame = pd.DataFrame.from_dict(
line_dictionaries, dtype=pd.SparseDtype(object, np.nan)
)
list_of_sub_data_frames.append(sub_data_frame)
line_dictionaries = []
# concatenate all sub data frames to one data frame
data_frame = pd.concat(list_of_sub_data_frames)
# the column names contain a unit specification at the end
# cast columns with units to float
columns_with_unit = [
column for column in data_frame.columns if not "None" in column
]
data_frame[columns_with_unit] = data_frame[columns_with_unit].astype(
pd.SparseDtype(np.float64, np.nan)
)
list_of_sub_data_frames = []
return data_frame
What is the problem:
The above code works, but it is very slow and therefore I am looking for a faster and memory efficient way to genereate a data frame. The data does not have to be structured to a data frame with pandas. In case there is a better package to do the job, I am open to everything.