I have some data in the following csv format:

And want to put it in the following data frame format with pandas:

         Variable1    Variable2    Variable3    
Time1           12     [1,2,3]             3
Time2           32    [2,45,5]             4
Time3            4    [4,2,54]             5

How would I even go about this? I know the format is awful, don't ask me why it's like that but I'm kinda stuck with it. I really don't even know where to begin with this one. TIA

CodePudding user response：

updated code as per comments

initial file read is based on code from here:

import pandas as pd
import numpy as np

file = r'C:\Test\TIMBER-1100-10M.csv'
# Loop the data lines
with open(file, 'r') as temp_f:
    # get No of columns in each line
    col_count = [ len(l.split(",")) for l in temp_f.readlines() ]

# Generate column names  (names will be 0, 1, 2, ..., maximum columns - 1)
column_names = [i for i in range(0, max(col_count))]
df = pd.read_csv(file, header=None, delimiter=",", names=column_names)

# preparing dataframe for pivot
df['Variable'] = np.where(df[0].str.contains('VARIABLE:'), df[0], np.nan)
df['Variable'].ffill(inplace=True)
df[1].dropna(inplace=True)
drop_values = ['Timestamp','VARIABLE:']
df2 = df[~df[0].str.contains('|'.join(drop_values))].astype({col: str for col in df.columns[2:]})

conc_col = df2.columns.to_list()
conc_col.remove(0)
conc_col.remove('Variable')

df2['Value'] = df2[conc_col].apply(lambda x: ','.join(x.dropna()), axis=1).str.strip(',nan')
df2.rename(columns={ df.columns[0]: "Time" }, inplace = True)

# creating the pivot as final dataframe
pivot = df2.pivot_table(index=['Time'],
                        columns=['Variable'],
                        values='Value',
                        aggfunc='sum')\
                        .rename_axis(None, axis=1)\
                        .reset_index()
pivot.to_excel(r'C:\Test\temp1.xlsx')