CODEWITHSUNDEEP
pythoncsvdictionary
kingmakerking
kingmakerking

Reputation: 2089

Updating a pandas dataframe or csv with dictionary

A function in my script returns a dictionary for John as follows:

{ "Apple": 10, "Mango": 20, "Banana":30}

The keys and values are not necessarily the same every time I call the function. For example, it can also yield a dictionary for Jen such as

{ "Apple": 10, "Banana":30, "Watermelon": 5}

I want to update the values to preferably a csv (or to a pandas dataframe and then to csv) to store it for later analysis. The desired output of the csv is:

Name | Apple | Banana | Mango | Watermelon |
------------------------------------------
John | 10    | 30     | 20    |            
Jen  | 10    | 30     |       | 5

So, the puedocode is as follows:

if dictionary-keys == csv_or_df_header:
   add value to corresponding columns by matching keys with column headers
else:
   add the new key as a column header
   add value to corresponding columns by matching keys with column headers

Upvotes: 3

Views: 9463

Answers (3)

Eric Ed Lohmar
Eric Ed Lohmar

Reputation: 1922

The pandas append function takes care of most of this work for you. This code:

import pandas as pd

df = pd.DataFrame({'Apple': 10, "Mango": 20, "Banana": 30}, index=['John'])

jen = pd.Series({"Apple": 10, "Banana": 30, "Watermelon": 5}, name='Jen')
df = df.append(jen)

print(df)

yields this result:

      Apple  Banana  Mango  Watermelon
John   10.0    30.0   20.0         NaN
Jen    10.0    30.0    NaN         5.0

If you want to move it to csv from there you can tack df.to_csv(csv_filepath) on the end of the program and it'll export it to the filepath you specified.

Upvotes: 7

zipa
zipa

Reputation: 27869

So, if your data looks like this:

data = {'John': { "Apple": 10, "Mango": 20, "Banana":30},
        'Jen': { "Apple": 10, "Banana":30, "Watermelon": 5}}

You can create dataframe from it:

df = pd.DataFrame.from_dict(data, orient='index')

And to update it, you can revert to dict:

df = df.to_dict(orient='index')

Update the dict:

update = {'Jack': { "Apple": 10, "Mango": 20, "Kiwi":60}}
df.update(update)

And push it back to dataframe:

df = pd.DataFrame.from_dict(df, orient='index')

Furthermore, if you want to replace NaN just use fillna() at the end:

df = pd.DataFrame.from_dict(df, orient='index').fillna('').astype(int,raise_on_error=False)

Result:

     Mango Watermelon  Apple Banana Kiwi
Jack    20                10          60
Jen                 5     10     30     
John    20                10     30

Upvotes: 3

SparkAndShine
SparkAndShine

Reputation: 18007

Use the dictionary operation get(key[, default]), 

# Let's say you return  a name and a dict from your scirpt
name = 'John'
d = { "Apple": 10, "Mango": 20, "Banana":30}

# Add the record to a list of lists
lists = list()
lists.append([name, d.get('Apple', ''), d.get('Banana', ''), d.get('Mango', ''), d.get('Watermelon', '')])

# Write to a csv file
fieldnames = ['Name', 'Apple', 'Banana', 'Mango', 'Watermelon']
with open('result.csv', 'w') as f:
    writer = csv.writer(f)
    writer.writerow(fieldnames) # write the header 
    writer.writerows(lists)     # write a list of lists

Upvotes: 0

Related Questions