I am trying to write a code to parse some data from network output with a loop on set of devices and append the results in a dictionary and then write each dictionary keys and values into an excel sheet

The problem I am facing at the moment that the key values are printed as column headers every time the loop is executed

dictionary = {"key1":[],"key2":[],"key3":[]}
dictionary["key1"].append(parse_value1)
dictionary["key2"].append(parse_value2)
dictionary_to_df = pd.DataFrame(dictionary)
dictionary_to_df("csv path,mode = "a",index = False, header = True)

output is something like that:

however I would like to get the output as below

CodePudding user response：

use simple code (pd.DataFrame.from_dict):

dictionary = {"key1":[],"key2":[]}
parse_value1=["value1","value2","value3"]
parse_value2=["value4","value5","value6"]
dictionary["key1"].extend(parse_value1)
dictionary["key2"].extend(parse_value2)
dictionary_to_df  =pd.DataFrame.from_dict(dictionary)
dictionary_to_df.to_csv("test.csv",mode = "a",index = False, header = True)

CodePudding user response：

You can try concating all the dataframes generating from the loop to one bigger dataframe

dfs = []

for loop
    dictionary = {"key1":[],"key2":[],"key3":[]}
    dictionary["key1"].append(parse_value1)
    dictionary["key2"].append(parse_value2)
    dictionary_to_df = pd.DataFrame(dictionary)
    dfs.append(dictionary_to_df)

df = pd.concat([dfs])
df.to_csv("csv path", mode = "a",index = False, header = True)

Or make the dictionary is global for the for-loop

dictionary = {"key1":[],"key2":[],"key3":[]}

for loop
    dictionary["key1"].append(parse_value1)
    dictionary["key2"].append(parse_value2)

dictionary_to_df = pd.DataFrame(dictionary)
dictionary_to_df.to_csv("csv path", mode = "a",index = False, header = True)

Or check the file header existence

for loop
    dictionary = {"key1":[],"key2":[],"key3":[]}
    dictionary["key1"].append(parse_value1)
    dictionary["key2"].append(parse_value2)
    dictionary_to_df = pd.DataFrame(dictionary)
    with open("csv path", 'a') as f:
        dictionary_to_df.to_csv(f,mode = "a",index=False, header=not f.tell())