CODEWITHSUNDEEP
pythonpython-2.7
tulanejosh
tulanejosh

Reputation: 319

Find dictionary key match in CSV and write value to the next column

Using Python 2.7.

I run a function each morning that creates an output dictionary with a key and value. The output dictionary results look like this:

'John': 1
'Jake': 5
'Lucy': 235
'Sarah': 33

I then open an existing csv file that has columns/rows that look like this:

Name 1/1/17 1/2/17
John 5 88
Jake 31 34
Sarah 45 12
Lucy 77 57

Where the column A header is always Name and each consecutive column header is the Date of the data in the column.

I am trying to write a function that will:

open the csv, write to the csv append the date to the next available column header (i.e. row 1) then find the Key in the csv that matches the Key in my dictionary append the Value from the dictionary to the next open column in the csv

Using my example above, then
Name 1/1/17 1/2/17
John 5 88
Jake 31 34
Sarah 45 12
Lucy 77 57

turns into

Name 1/1/17 1/2/17 1/3/17
John 5 88 1
Jake 31 34 5
Sarah 45 12 33
Lucy 77 57 235

I have come very close to what I am trying to do with the following code:

import pandas as pd
import datetime


starter_df = pd.read_csv('keyword_results.csv', names = ['Keyword'])

#dictionary created by function
test_dic = {"John": 12, "Jake": 45, "Sarah": 31, "Lucy": 18}

now = datetime.datetime.now()

str_date = now.strftime("%m %d %Y")

starter_df[str_date] = starter_df["Keyword"].map(test_dic)

starter_df.to_csv('keyword_results.csv')

I am reading the csv into a dataframe, attempting to map the new column into the dataframe and then outputting the dataframe into a csv.

The problem is that this is writing the date to a new column, but no data beneath it.

I'm at a loss, thanks for the help. I'm still new to Python.

Upvotes: 0

Views: 1214

Answers (1)

Shadkhan
Shadkhan

Reputation: 238

You can do this with lambda, it is easy and fast. Also no need to add parameter as "names = ['Keyword']" while reading the csv.

import pandas as pd
import datetime
import os 
os.chdir('D:/mypc/')

starter_df = pd.read_csv('keyword_results.csv')

#dictionary created by function
test_dic = {"John": 12, "Jake": 45, "Sarah": 31, "Lucy": 18}

now = datetime.datetime.now()
str_date = now.strftime("%m/%d/%Y")

starter_df[str_date] = starter_df["Name"].map(lambda x : test_dic[x])

starter_df.to_csv('keyword_results.csv')

Upvotes: 1

Related Questions