How to create a text file based on the unique values of a dataframe column?

Question

I have an excel table with 2 columns called ('NE' and 'Interface'), and what I want to do is: to edit a .txt-file template (which I already have, I show it below) with each Interface value. Then concatenate the txt-files which belongs to the same group of 'NE'.

This is my excel:

this is my txt-file template, i want to change "Interface" with the interface value of the excel:

conf t
**$interface**   
no service-policy input QOS-IN_ACCESS
end
conf t
no policy-map QOS-IN_ACCESS 
policy-map QOS-IN_ACCESS 
class DSCP_VOIX_SIG 
set mpls experimental imposition 5 
set qos-group 5 
end
conf t
**$interface**   
service-policy input QOS-IN_ACCESS
end

and this is my code: ( i already concatenate the files, what i need to do is to put them in a group of NE)

from string import Template
import pandas as pd

df3 = pd.read_excel(r"C:\Users\audit_policymap.xlsx")
with open(r"C:\Users\audit_policymap.txt") as fp:
    template = Template(fp.read())

content2 = ''
content3 = ''
for i in range(len(df3)):
    file_name = df.loc[i, "NE"] + '_output.txt'
    with open(file_name, 'w') as fp:
        content = template.substitute(interface=df.loc[i, "Interface"]) 
        if  df.loc[i, "NE"] == df.loc[i+1, "NE"]:
            content2 = str(content2)+'\n'+str(content)+'\n'
            content3 = str(content2)+'\n'
            fp.write(content2)
        else:  
            content2 = ''
            content3 = str(content3)+'\n'+str(content)+'\n'
            fp.write(content3)

Summarizing: I want to have one txt-file per 'NE' edited with all the interfaces according to their corresponding 'NE'

Answer 1

• Use pandas.DataFrame.groupby on the NE column.
  • This returns a DataFrameGroupBy object, where i is the unique groupby value from NE, and g is the associated group.
  • The for-loop will iterate through each unique value in NE
• Use an f-string to specify a unique file name (e.g. f'{i}_output.txt')
  • '250002-PEFTTS-2_output.txt'
• Because all values in g belong only to one of the unique values in NE, there's no need to check if NE matches for each row, as you've done in the question.
• [str(template.substitute(interface=row)) for row in g.Interface] is a list-comprehension which, for each row in g.Interface, adds str(template.substitute(interface=row)) to a list.
  • '\n'.join() joins each item in the list as a string separated by a newline.

for i, g in df.groupby('NE'):  # iterate through unique values in NE
    file_name = f'{i}_output.txt'  # create the empty content string
    with open(file_name, 'w') as fp:  # open the file
        content = '\n'.join([str(template.substitute(interface=row)) for row in g.Interface])
        fp.write(content)  # write content to file