How to use a column in dataframe as dictionary key value?-CodePudding

Suppose there is a dataset. Let's say that the dataset has a column A which contains city names like "New York", "California", or "Florida" now we have a dictionary like

my_dict = {"New York":1, "California":2, "Florida":3}

So I need to generate a column B such that if column A has a row value "New York", then column B has the value 1 as in the dictionary.

I used the lambda function and it worked but is it possible without the use of the lambda function?

CodePudding user response：

This seems rather contrived but works:

df = pd.DataFrame([my_dict]).stack().reset_index()
df.drop(df.columns[[0]], axis=1, inplace=True)
df.columns = ['A', 'B']

and gives

            A  B
0    New York  1
1  California  2
2     Florida  3

CodePudding user response：

Method 1

import pandas as pd

my_dict = {"New York":1, "California":2, "Florida":3}

# creating dataframe from dictionary itself, for reproducing the scenario
existing_df = pd.DataFrame({"reference_column" : my_dict.keys()})

# duplicate the reference column (city column)
existing_df["value_column"] = existing_df["reference_column"]

# replace the values in duplicate column with corresponding values from dictionary
existing_df.replace({"value_column" : my_dict}, inplace = True)

Explanation :

df.replace({'column' : replacement_dictionary}) is a find and replace technique. find compares the values of column with keys of replacement_dictionary. If the key matches, its value is used to replace existing value of column

Method 2

import pandas as pd

my_dict = {"New York":1, "California":2, "Florida":3}

# reproducing original dataframe with reference city column
existing_df = pd.DataFrame({"reference_column" : my_dict.keys()})

# dictionary coverted into dataframe
replacement_df = pd.DataFrame({"reference_column" : my_dict.keys(), "value_column" : my_dict.values()})

# left join both on city column with original df as left table
merge_df = existing_df.merge(replacement_df, on = ["reference_column"], how = "left")

Explanation :

Dictionary can be converted into dataframe (replacement_df) with keys as one column and values as another column. This converted dataframe can be merged with existing dataframe on the condition that city names in existing dataframe should match with city names in replacement_df

Output of both methods :

Resultant Dataframe