Python Forum

1.I need to convert the following .txt file into csv format using Python. Basically, we need to reshape the wide txt data to long data format, but there doesn't seem to be a straightforward way to do this as my output data needs sourcefile, rownumber, columnnumber and value.

2.Input .txt file look likes this. See attached image

3.Output .csv files looks like the below attached image. In my output csv file, I need SourceFile(name of the file),RowNumber(which row, Value field is coming),ColumnNumber(which column, value field is coming),and Value(actual data).

Here is the code:

for fn in txt_files:
    df2 = pd.read_csv(fn,sep="\t",header=None) #Reading file
    df2 = all_dfs.dropna(axis=1, how='all') #Drop the columns where all columns NaN
    df2 = all_dfs.dropna(axis=0, how='all') #Drop the rows where all columns are NaN
    rs = pd.DataFrame(columns= ['SourceFile','RowNumber','ColNumber','HeaderName','Value'])
    for i,c in enumerate(df2.columns):
        rs = rs.append(pd.DataFrame({'SourceFile': Path(fn.name),'RowNumber': range(1, len(df2) + 1),'ColNumber': i + 1,'HeaderName': c,'Value': df2[[str(c)]].values[:, 0],                                 }))                                                         
 rs['Value'].replace('', np.nan, inplace=True)
 rs.dropna(subset=['Value'], inplace=True)
 rs.to_csv('Data.csv', index=False)

Input File Attached
Any help????