neo33 neo33 - 3 months ago 9
Python Question

How to build the following tables?

Hello I have process a excel file and to take some parameters of it to create many tables the structure of the tables is the following:

"AWK|USL|R|SVKDIKG_tVstiKg|S|[PARAMETER1]~BURAGO~[PARAMETER2]~WVDG~333" "AFUSLR~USLSSHS~Farm~~%ERD_ARGV=MR4567.%VRSD%.%23WF%.333.%RVB%.tRt"
"AWK|USL|R|Bimbo|S|[PARAMETER3]~K~999" "USLo99941VRR.VxV"
"AWK|USL|R|Bimbo|S|[PARAMETER3]~Q~999" "USLo99941VRR.VxV"
"AWK|USL|R|Ford|S|[PARAMETER3]~K~999" "[PARAMETER3]~K"
"AWK|USL|R|Ford|S|[PARAMETER3]~Q~999" "[PARAMETER3]~K"


The parameters that I need to use to create the tables are contained in a excel file and they looks as follows:

123123,RIBICOM,FACTIBLE
050944,TELCOM,423423
.
.
.
42342,CORPS,233243


The idea is to take the "," as a column separator, where the fist column would be the "PARAMETER1", second column "PARAMETER2" and finally "PARAMETER3" the third column raw by raw, for every raw or this archive I need to produce one table filling the place holders of my template as follows:

"AWK|USL|R|SVKDIKG_tVstiKg|S|123123~BURAGO~RIBICOM~WVDG~333" "AFUSLR~USLSSHS~Farm~~%ERD_ARGV=MR4567.%VRSD%.%23WF%.333.%RVB%.tRt"
"AWK|USL|R|Bimbo|S|FACTIBLE~K~999" "USLo99941VRR.VxV"
"AWK|USL|R|Bimbo|S|FACTIBLE~Q~999" "USLo99941VRR.VxV"
"AWK|USL|R|Ford|S|FACTIBLE~K~999" "FACTIBLE~K"
"AWK|USL|R|Ford|S|FACTIBLE~Q~999" "FACTIBLE~K"


to be more clear the place holders of the template are the followings:

[PARAMETER1]
[PARAMETER2]
[PARAMETER3]


those are the things that I need to fill,

The example of above would be the desired output for the first row, I need to produce a txt file with all the tables concatenated, in order to achieve this I tried:

import pandas as pd
# -*- coding: utf-8 -*-
xl = pd.ExcelFile("Book1.xlsx")
#to clean from duplicates
df = xl.parse("Sheet1")
df=df.drop_duplicates()
#these are the values that I am concatenating below
Parameter1=df[u'Header1 ']
Parameter2=df[u'Header2 ']
Parameter3=df[u'Header3 ']

#This is the dataframe with the corresponding columns
important_Parameters=df[u'Header1 '].astype(str)+","+df[u'Header2 '].astype(str)+","+df[u'Header3 '].astype(str)

#to write my dataframe on disk.
important_Parameters.to_csv("important33.txt", index=False)


I am not sure of what would be the best approach to proceed since I used to do that kind of things in bash using "sed" and "awk" but this time I would like to try using pandas and python I really appreciate any suggestion to proceed with this specific task.

Answer

you try this

import pandas as pd
# -*- coding: utf-8 -*-
df = pd.read_csv("param.csv")
print df
df=df.drop_duplicates()
filename='sample.txt'

print "\n\nReplace with new values"
for index, row in df.iterrows():
    print "New Values \n\n"
    print row 
    f=open(filename)
    filedata = f.read()
    filedata=filedata.replace("[PARAMETER1]",row[0])
    filedata=filedata.replace('[PARAMETER2]',row[1])
    filedata=filedata.replace('[PARAMETER3]',row[2])
    print filedata

output

      Parameter1 Parameter2 Parameter3
    0    123123A    RIBICOM   FACTIBLE
    1   050944BS     TELCOM     423423


    Replace with new values
    New Values


    Parameter1     123123A
    Parameter2     RIBICOM
    Parameter3    FACTIBLE
    Name: 0, dtype: object
    AWK|USL|R|SVKDIKG_tVstiKg|S|123123A~BURAGO~RIBICOM~WVDG~333 AFUSLR~USLSSHS~Farm~
    ~%ERD_ARGV=MR4567.%VRSD%.%23WF%.333.%RVB%.tRt
    AWK|USL|R|Bimbo|S|FACTIBLE~K~999 USLo99941VRR.VxV
    AWK|USL|R|Bimbo|S|FACTIBLE~Q~999 USLo99941VRR.VxV
    AWK|USL|R|Ford|S|FACTIBLE~K~999 FACTIBLE~K
    AWK|USL|R|Ford|S|FACTIBLE~Q~999 FACTIBLE~K
    New Values


    Parameter1    050944BS
    Parameter2      TELCOM
    Parameter3      423423
    Name: 1, dtype: object
    AWK|USL|R|SVKDIKG_tVstiKg|S|050944BS~BURAGO~TELCOM~WVDG~333 AFUSLR~USLSSHS~Farm~
    ~%ERD_ARGV=MR4567.%VRSD%.%23WF%.333.%RVB%.tRt
    AWK|USL|R|Bimbo|S|423423~K~999 USLo99941VRR.VxV
    AWK|USL|R|Bimbo|S|423423~Q~999 USLo99941VRR.VxV
    AWK|USL|R|Ford|S|423423~K~999 423423~K
    AWK|USL|R|Ford|S|423423~Q~999 423423~K

Sample.txt

"AWK|USL|R|SVKDIKG_tVstiKg|S|[PARAMETER1]~BURAGO~[PARAMETER2]~WVDG~333" "AFUSLR~USLSSHS~Farm~~%ERD_ARGV=MR4567.%VRSD%.%23WF%.333.%RVB%.tRt"
"AWK|USL|R|Bimbo|S|[PARAMETER3]~K~999" "USLo99941VRR.VxV"
"AWK|USL|R|Bimbo|S|[PARAMETER3]~Q~999" "USLo99941VRR.VxV"
"AWK|USL|R|Ford|S|[PARAMETER3]~K~999" "[PARAMETER3]~K"
"AWK|USL|R|Ford|S|[PARAMETER3]~Q~999" "[PARAMETER3]~K"
Comments