CODEWITHSUNDEEP
pythoncsvpandas
user3378649
user3378649

Reputation: 5354

Add column to a specific CSV row using Python pandas

I want to merge rows in csv files by matching the id with a given dictionary.

I have a dictionary: l= {2.80215: [376570], 0.79577: [378053], 22667183: [269499]}

I have a csv file. 

          A        B        C        D      
2000-01-03 -0.59885 -0.18141 -0.68828 -0.77572
2000-01-04  0.83935  0.15993  0.95911 -1.12959
2000-01-05  2.80215 -0.10858 -1.62114 -0.20170
2000-01-06  0.71670 -0.26707  1.36029  1.74254
2000-01-07 -0.45749  0.22750  0.46291 -0.58431
2000-01-10 -0.78702  0.44006 -0.36881 -0.13884
2000-01-11  0.79577 -0.09198  0.14119  0.02668
2000-01-12 -0.32297  0.62332  1.93595  0.78024
2000-01-13  1.74683 -1.57738 -0.02134  0.11596

The output should be :

     A        B        C        D          E      F     
2000-01-03 -0.59885 -0.18141 -0.68828 -0.77572    0
2000-01-04  0.83935  0.15993  0.95911 -1.12959    0
2000-01-05  2.80215 -0.10858 -1.62114 -0.20170    376570 
2000-01-06  0.71670 -0.26707  1.36029  1.74254    0
2000-01-07 -0.45749  0.22750  0.46291 -0.58431    0
2000-01-10 -0.78702  0.44006 -0.36881 -0.13884    0
2000-01-11  0.79577 -0.09198  0.14119  0.02668    378053
2000-01-12 -0.32297  0.62332  1.93595  0.78024    0
2000-01-13  1.74683 -1.57738 -0.02134  0.11596    0

I tried to do it this way:

import pandas
data = panda.read_csv("thisfiel.csv")

data["F"] = data["B"].apply(lambda x: l[x])

But I couldn't get the aimed results.

Upvotes: 2

Views: 1083

Answers (2)

Andy Hayden
Andy Hayden

Reputation: 375535

If you l were a DataFrame you could do a merge:

In [11]: l_df = pd.DataFrame.from_dict(l, orient='index')

In [12]: l_df.columns = ['F']

In [13]: l_df
Out[13]: 
                     F
2.80215         376570
0.79577         378053
22667183.00000  269499

Merge on column A and the index of l_df:

In [14]: merged = df.merge(l_df, left_on='A', right_index=True, how='left')

In [15]: merged
Out[15]: 
                  A        B        C        D       F
2000-01-03 -0.59885 -0.18141 -0.68828 -0.77572     NaN
2000-01-04  0.83935  0.15993  0.95911 -1.12959     NaN
2000-01-05  2.80215 -0.10858 -1.62114 -0.20170  376570
2000-01-06  0.71670 -0.26707  1.36029  1.74254     NaN
2000-01-07 -0.45749  0.22750  0.46291 -0.58431     NaN
2000-01-10 -0.78702  0.44006 -0.36881 -0.13884     NaN
2000-01-11  0.79577 -0.09198  0.14119  0.02668  378053
2000-01-12 -0.32297  0.62332  1.93595  0.78024     NaN
2000-01-13  1.74683 -1.57738 -0.02134  0.11596     NaN

Note: atm NaN mean missing, you can fill them in with fillna:

In [16]: merged['F'].fillna(0, inplace=True)

Upvotes: 2

EdChum
EdChum

Reputation: 394051

Do this:

def getVal(x):
    try:
        return l[x][0]

    except KeyError:
        return 0

data['F'] = data['B'].map(getVal)

Upvotes: 1

Related Questions