I'm having problems to load a simple .csv file with pd.read_csv(). The problem is the last header which groups multiple 'columns'.

The .csv looks like this:

Machine; Process; Date; Statistic; Data_Series
A; P22; 08.12.2015; X ; 0; 0,5; 1; 2; 4; 6
A; P22; 08.12.2015; Y; 12; 15; 25; 33; 23
A; PX67; 09.12.2015; X ; 0; 0,3; 2; 3; 7; 8; 8,5; 9,5
A; PX67; 09.12.2015; Y; 22; 45; 35; 48; 55; 60; 62; 58

The fifth columns looks like this:

Data_Series
0; 0,5; 1; 2; 4; 6
12; 15; 25; 33; 23
0; 0,3; 2; 3; 7; 8; 8,5; 9,5
22; 45; 35; 48; 55; 60; 62; 58

Expecting result should be a DataFrame with 5 columns
( with the Statistic column being pivoted [X, Y] to contain the Data_Series values ).

csv file and expected result:

Any idea how to approach this? Thanks

CodePudding user response：

Using csv.reader and pandas pivot explode

text = """Machine;Process;Date;Statistic;Data_Series
A;P22;08.12.2015;X;0;0,5;1;2;4;6
A;P22;08.12.2015;Y;12;15;25;33;23;45
A;PX67;09.12.2015;X;0;0,3;2;3;7;8;8,5;9,5
A;PX67;09.12.2015;Y;22;45;35;48;55;60;62;58"""

import csv
headers = None
data = []
reader = csv.reader(text.splitlines(), delimiter=';', quotechar='"')
for row in reader:
    if headers is None:
        headers = row
        continue
    std_columns = row[:len(headers) - 1]
    series = [float(x.replace(",", ".")) for x in row[len(headers):]]
    data.append([*std_columns, series])

df = pd.DataFrame(data, columns=headers)
df = df.pivot(index=["Machine", "Process", "Date"], columns="Statistic", values="Data_Series")
df = df.explode(["X", "Y"]).reset_index()

CodePudding user response：

import pandas as pd

df_list = []

with open('data_in.csv', 'r') as f:
    headers = False
    for line in f:
        if not headers:
            headers = line.strip().split('; ')
            continue
        
        # Machine; Process; Date; Statistic; Data_Series -> 5 columnss -> split 4 times
        unparsed = line.strip().split('; ', len(headers)-1)
        
        # split the last column
        parsed = [float(val.replace(',', '.')) for val in unparsed[-1].split('; ')]
        
        # add data to list after removeing any spaces on "Statistic"
        df_list.append(unparsed[:-2]   [unparsed[-2].strip()]   [parsed])

df = pd.DataFrame(df_list, columns=headers)

# pivot > explode > reset_index
df_new = df.pivot(index=["Machine", "Process", "Date"], columns="Statistic", values="Data_Series").explode(list('XY')).reset_index()

# remove columns names
df_new.columns.name = None