-2

I have a dataframe that looks like:

df = pd.DataFrame({'ISIN': ['A1kT23', '4523', 'B333', '49O33'], 'Name': ['Example A', 'Name Xy', 'Example B', 'Test123'], 'Sector': ['Energy', 'Industrials', 'Utilities', 'Real Estate'],  'Country': ['UK', 'USA', 'Germany', 'China']})

I would like to translate the column Sector into German by using the dataframe Sector_EN_DE

Sector_EN_DE = pd.DataFrame({'Sector_EN': ['Energy', 'Industrials', 'Utilities', 'Real Estate', 'Materials'], 'Sector_DE': ['Energie', 'Industrie', 'Versorger', 'Immobilien', 'Materialien']})

so that I get as result the dataframe

df = pd.DataFrame({'ISIN': ['A1kT23', '4523', 'B333', '49O33'], 'Name': ['Example A', 'Name Xy', 'Example B', 'Test123'], 'Sector': ['Energie', 'Industrie', 'Versorger', 'Immobilien'],  'Country': ['UK', 'USA', 'Germany', 'China']})

What would be the apropriate code line?

Economist Learning Python
  • 306
  • 1
  • 3
  • 17
  • 1
    You need to merge two dataframes https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html – nithish08 Jul 06 '21 at 23:50
  • Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) – Amit Gupta Jul 07 '21 at 04:50

2 Answers2

0

This line will do the merge and DataFrame cleanup:

df.merge(Sector_EN_DE, left_on='Sector', right_on='Sector_EN').drop(['Sector', 'Sector_EN'], axis=1).rename(columns={'Sector_DE': 'Sector'})

Explanation:

  • The merge function do the join between both DataFrames.
  • The drop function drops the English version of Sector, with axis=1 because you're dropping columns (you can also use that function to drop rows).
  • The rename function renames the Sector_DE column.
bruno-uy
  • 1,647
  • 12
  • 20
0

Another way via map():

df['Sector']=df['Sector'].map(dict(Sector_EN_DE[['Sector_EN', 'Sector_DE']].values))

OR

via replace():

df['Sector']=df['Sector'].replace(dict(Sector_EN_DE[['Sector_EN', 'Sector_DE']].values))
Anurag Dabas
  • 23,866
  • 9
  • 21
  • 41