Slicing Pandas DataFrame based on csv

Question

Let's say I have a Pandas DataFrame like following.

df = pd.DataFrame({'Name' : ['A','B','C'],
                'Country'  : ['US','UK','SL']})

  Country Name
0      US    A
1      UK    B
2      SL    C

And I'm having a csv like following.

Name,Extended
A,Jorge
B,Alex
E,Mark
F,Bindu

I need to check whether df['Name'] is in csv and if so get the "Extended". If not I need to just get the "Name". So my Expected output is like following.

 Country  Name  Extended
0      US    A  Jorge
1      UK    B  Alex
2      SL    C  C

Following shows what I tried so far.

f = open('mycsv.csv','r')
lines = f.readlines()

def parse(x):
    for line in lines:
        if x in line.split(',')[0]:
            return line.strip().split(',')[1]

df['Extended'] = df['Name'].apply(parse)

 Name Country Extended
0  A  US Jorge
1  B  UK Alex
2  C  SL None

I can not figure out how to get the "Name" for C at "Extended"(else part in the code)? Any help.

Ioana Veronica Chelu · Accepted Answer · 2015-12-17 11:47:52Z

1

You can use the "fillna" function from pandas like this:

import pandas as pd

df1 = pd.DataFrame({'Name' : ['A','B','C'],
            'Country'  : ['US','UK','SL']})
df2 = pd.DataFrame.from_csv('mycsv.csv', index_col=None)

df_merge = pd.merge(df, f, how="left", on="Name")
df_merge["Extended"].fillna('Name', inplace=True)

answered Dec 17, 2015 at 11:47

Ioana Veronica Chelu

564 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

EdChum · Accepted Answer · 2014-06-15 08:45:47Z

0

You could just load the csv as a df and then assign using where:

df['Name'] = df2['Extended'].where(df2['Name'] != df2['Extended'], df2['Name'])

So here we use the boolean condition to test if 'Name' is not equal to 'Extended' and use that value, otherwise just use 'Name'.

Also is 'Extended' always either different or same as 'Name'? If so why not just assign the value of extended to the dataframe:

df['Name'] = df2['Extended']

This would be a lot simpler.

answered Jun 15, 2014 at 8:45

EdChum

397k204 gold badges836 silver badges583 bronze badges

Collectives™ on Stack Overflow

Slicing Pandas DataFrame based on csv

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related