Is there a more efficient way to create a data frame (Pandas) from semi structured data?

Viewed 58

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.

1 Answers

From the looks of it, you can split on whitespace into one massive row of data, then stack it, split it again on the : to separate the key/value pairs.

Then you can flag each group incrementally by checking if the value is Time_stamp. From this point it's a pivot.

import pandas as pd
import io   

data = io.StringIO("""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""")

df = pd.read_csv(data, header=None, delim_whitespace=True)
df = df.stack().str.replace(',','').str.split(':', expand=True)
df.columns = ['key','value']

df['group'] = df['key'].eq('Time_stamp').cumsum()

df = df.pivot_table(index='group', columns='key', values='value').reset_index(drop=True).rename_axis(None, axis=1)


print(df)

Output

   Column_0  Column_1  Column_2  Column_3  Column_4  Column_5  Time_stamp
0       1.0       2.0       NaN       NaN       NaN       NaN         0.0
1       NaN       NaN       3.0       4.0       5.0       NaN         1.0
2       NaN       NaN       NaN       NaN       NaN       6.0         2.0
3       NaN       NaN       3.0       4.0       5.0       NaN         3.0
4       1.0       2.0       NaN       NaN       NaN       NaN         4.0
5       NaN       NaN       3.0       4.0       5.0       NaN         5.0
Related