1

I have a csv file(or dataframe) like below :

Text    Location    State
A   Florida, USA    Florida
B   NY              New York
C       
D   abc

And a dictionary with key value pair as :

stat_map = {
        'FL': 'Florida',
        'NY': 'New York',
        'AR': 'Arkansas',
}

How may I delete row 3rd and 4th i.e. row with Text C & D so that my dataframe contains only those rows for which i have value in dictionary. All rows for which state is either blank or has some value which is not in dictionary value should be deleted. The final output should look like :

Text    Location    State
    A   Florida, USA    Florida
    B   NY              New York

Please help.

Alex
  • 81
  • 4
  • 10
  • Why aren't you removing Florida? It isn't a key in your dict after all. – cs95 Oct 17 '17 at 07:45
  • It's not in dict key, but it is in dictionary value. The dataframe 'State' column contains many such values which does not exist in dictionary value. I need to retain all those dataframe rows for which value exists in dictionary value. And delete only those for which value does not match to dictionary values. – Alex Oct 17 '17 at 07:53
  • Possible duplicate of [Deleting DataFrame row in Pandas based on column value](https://stackoverflow.com/questions/18172851/deleting-dataframe-row-in-pandas-based-on-column-value) – Alessandro Da Rugna Oct 17 '17 at 08:08

1 Answers1

1

Use extract + replace, last remove rows by dropna:

stat_map = {
        'FL': 'Florida',
        'NY': 'New York',
        'AR': 'Arkansas',
}

#get list from all values from keys and values of dict
L = list(stat_map.keys()) + list(stat_map.values())
print (L)
['NY', 'FL', 'AR', 'New York', 'Florida', 'Arkansas']


df['State1'] = df['Location'].str.extract('(' + '|'.join(L) + ')', expand=False)
                             .replace(stat_map)
df = df.dropna(subset=['State1'])
print (df)
  Text      Location     State    State1
0    A  Florida, USA   Florida   Florida
1    B            NY  New York  New York
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252