If I have data that I'd like to add as new columns in an already built CSV.

I have a dictionary with the new data to add for each dictionary key (corresponds to the person's ID in the CSV).

import pandas as pd

#New values to add for each ID
#'ID': Area, Age, Temperature
myData = {
    'A00147': (189, 40, 39),
    'A00198': (142, 28, 28),
    'A00360': (202, 40, 39),
    'A00367': (168, 33, 33),
    'BA9330': (142, 29, 29),
}

I have this CSV:

ID,Sex,Height,Weight,Hair,Eyes
A00147,Male,5 ft. 07 in.,185 lbs.,Brown,Blue
A00198,Male,6 ft. 02 in.,190 lbs.,Red or Auburn,Brown
A00360,Male,5 ft. 09 in.,167 lbs.,Gray or Partially Gray,Green
A00367,Male,6 ft. 00 in.,245 lbs.,Black,Brown

I would like it to become:

ID,Sex,Height,Weight,Hair,Eyes,Area,Age,Temperature
A00147,Male,5 ft. 07 in.,185 lbs.,Brown,Blue,189,40,39
A00198,Male,6 ft. 02 in.,190 lbs.,Red or Auburn,Brown,142,28,28
A00360,Male,5 ft. 09 in.,167 lbs.,Gray or Partially Gray,Green,202,40,39
A00367,Male,6 ft. 00 in.,245 lbs.,Black,Brown,142,29,29

So I would be adding the new columns with their values to the corresponding ID.

So the first iteration of the loop would look into the dictionary myData, look at the first key ('A00147'), check if it exists in the CSV's ID column. If it does, it'll create the 3 new columns and add their respective values. Then it would look for 'A00198', if it's there, add the 3 new values in those previously created new columns. If it finds a key that doesn't exist in the CSV ID (ex: 'BA9330'), it would just skip over it.

I'm thinking of using Pandas for this task, but not entirely sure how to approach it.

CodePudding user response：

Create a dataframe from myData then merge it with your actual dataset:

import pandas as pd

df = pd.read_csv('data.csv')

cols = ['Area', 'Age', 'Temperature']
df1 = (pd.DataFrame.from_dict(myData, orient='index', columns=cols)
         .rename_axis('ID').reset_index())

out = df.merge(df1, on='ID', how='left')
out.to_csv('data.csv', index=False)

Output: