CODEWITHSUNDEEP
pythonpandasdataframe
JavierSando
JavierSando

Reputation: 339

Replacing string by float in a dataframe according to their value in a different dataframe

I have the following dataframes:

df.head()
Out[65]: 
            PU     IN     PR
NUTS_ID                     
BE       110.4  129.3  136.4
BG        72.1   74.4   73.2
CZ        68.3   75.9   89.1
DK        94.8  125.1  135.4
DE        77.4  101.1  113.5

df2.head()
Out[66]: 
        category
NUTS_ID         
BE111         PR
BE112         PR
BE113         PU
BE121         PR
BE122         IN

I would like to replace the strings in the column 'category' of the second dataframe by their corresponding float value in the first dataframe according to their code. As an example, in the second dataframe, search for all the rows starting by 'BE' in the first dataframe, and then replace their string value by their corresponding float from the first dataframe.

The result should be:

          category
NUTS_ID
BE111       136.4
BE112       136.4
BE113       110.4
BE121       136.4
BE122       129.3
...

I'd appreciate any help you can provide. Thanks in advance!

Upvotes: 2

Views: 364

Answers (2)

Celius Stingher
Celius Stingher

Reputation: 18367

Since we are using the first row only, we can use map:

output = df2['category'].map(df.iloc[0,:].to_dict())

Output:

         category
NUTS_ID          
BE111       136.4
BE112       136.4
BE113       110.4
BE121       136.4
BE122       129.3

Upvotes: 2

Scott Boston
Scott Boston

Reputation: 153460

Use pd.DataFrame.lookup with some string manipulation:

df2['category'] = df.lookup(df2.index.str[:2], df2['category'])

Output:

         category
NUTS_ID          
BE111       136.4
BE112       136.4
BE113       110.4
BE121       136.4
BE122       129.3

Upvotes: 2

Related Questions