CODEWITHSUNDEEP
pythonpandasloops
nous
nous

Reputation: 109

Pandas iterate over rows and find the column names

i have a two dataframes as:

df = pd.DataFrame({'America':["Ohio","Utah","New York"],
                   'Italy':["Rome","Milan","Venice"],
                   'Germany':["Berlin","Munich","Jena"]});


df2 = pd.DataFrame({'Cities':["Rome", "New York", "Munich"],
                   'Country':["na","na","na"]})

i want to itirate on df2 "Cities" column to find the cities on my (df) and append the country of the city (df column names) to the df2 country column

Upvotes: 4

Views: 106

Answers (2)

JoergVanAken
JoergVanAken

Reputation: 1286

After melting and renaming the first dataframe:

df1 = df.melt().rename(columns={'variable': 'Country', 'value': 'Cities'})

the solution is a simple merge:

df2 = df2[['Cities']].merge(df1, on='Cities')

Upvotes: 1

jezrael
jezrael

Reputation: 862671

Use melt with map by dictionary:

df1 = df.melt()
print (df1)
  variable     value
0  America      Ohio
1  America      Utah
2  America  New York
3    Italy      Rome
4    Italy     Milan
5    Italy    Venice
6  Germany    Berlin
7  Germany    Munich
8  Germany      Jena

df2['Country'] = df2['Cities'].map(dict(zip(df1['value'], df1['variable'])))
#alternative, thanks @Sandeep Kadapa 
#df2['Country'] = df2['Cities'].map(df1.set_index('value')['variable'])
print (df2)
     Cities  Country
0      Rome    Italy
1  New York  America
2    Munich  Germany

Upvotes: 9

Related Questions