We have covered how to use csv modules in the previous two posts. In this post, we will try to make the job easier using the python pandas library to read write csv with python for Network Automation. In my opinion, pandas is the one-stop-shop for all needs when you are dealing with tabular data like CSV sheets or excel sheets.
Below two posts cover Part1 and Part2 of this series. Please make sure to read these as the code examples may be related from the older posts.
import pandas as pd
df = pd.read_csv("cisco_always_on_devnet_sanbox_lab.csv")
print(df)
╰─ python3 script2a.py ─╯
HOST device_type SSH PORT NETCONF PORT RESTCONF PORT USERNAME PASSWORD SECRET
0 sandbox-iosxe-latest-1.cisco.com cisco_xe 22 830 443.0 developer C1sco12345 C1sco12345
1 sandbox-iosxr-1.cisco.com cisco_xr 22 830 NaN admin C1sco12345 C1sco12345
2 sandbox-nxos-1.cisco.com cisco_nxos 22 830 443.0 admin Admin_1234! Admin_1234!Convert the dataframe into a list of dictionaries with each row as a dictionary. Once you have a list of dictionaries or list of lists, pandas can readily use that data structure into creating a dataframe for you and then finally put that dataframe to your desired output format like CSV or EXCEL. There are a lot of formats that pandas support while reading and writing to a file. You can find more information here.
import pandas as pd
df = pd.read_csv("cisco_always_on_devnet_sanbox_lab.csv")
print(df.to_dict(orient='records'))
[{'HOST': 'sandbox-iosxe-latest-1.cisco.com', 'device_type': 'cisco_xe', 'SSH PORT': 22, 'NETCONF PORT': 830, 'RESTCONF PORT': 443.0, 'USERNAME': 'developer', 'PASSWORD': 'C1sco12345', 'SECRET': 'C1sco12345'}, {'HOST': 'sandbox-iosxr-1.cisco.com', 'device_type': 'cisco_xr', 'SSH PORT': 22, 'NETCONF PORT': 830, 'RESTCONF PORT': nan, 'USERNAME': 'admin', 'PASSWORD': 'C1sco12345', 'SECRET': 'C1sco12345'}, {'HOST': 'sandbox-nxos-1.cisco.com', 'device_type': 'cisco_nxos', 'SSH PORT': 22, 'NETCONF PORT': 830, 'RESTCONF PORT': 443.0, 'USERNAME': 'admin', 'PASSWORD': 'Admin_1234!', 'SECRET': 'Admin_1234!'}]HOW TO WRITE CSV WITH PYTHON PANDAS
In this previous post below, we discussed how to read/write from an excel file with pandas. Write to CSV is no different for pandas like read from csv. All you need to change from the post below is to use df.to_csv( ) instead of df.to_excel( ) and that’s it, everything else remains the same. That’s how versatile python’s pandas module is.
If you would like to know more about how to analyze data using pandas, please read this post
