size = df['Symbol'].map({'circle':25, 'triangle':20})

This is what i want to do

Excel file number one:

So here i am mapping from the column symbol to column size. for example wherever is the symbol circle in the symbol column the mapping is pasting 25 in the size column. But because there is a lot of symbols and i have them in excel number two, how can i do it without type in them one by one . I want to do the same thing as above but throw the excel sheets

Excel file number two

CodePudding user response：

So let's understand the issue quickly. You have a pandas DataFrame df that has a column named 'Symbol', right?

Let's say df['Symbol'] is something like:

>>> df["Symbol"]
0      circle
1    triangle
2      circle
3      circle
4    triangle
5      square

and you have an Excel sheet (let's say named mapping.xlsx) that has the size info for each symbol for example:

and you want to map the sizes from mapping.xlsx to the symbols in your DataFrame df.

What you need to do simply is:

# read your excel sheet as a DataFrame: (make sure you install openpyxl)
mapping = pd.read_excel('mapping.xlsx', engine='openpyxl', index_col=0)
# you can treat "mapping" as a dict
size = df['Symbol'].map(mapping['size'].get)
# and if you want to add a size column to your DataFrame:
df['size'] = df['Symbol'].map(mapping['size'].get)

CodePudding user response：

From what I understand from the query, you can import the excel sheet as a .csv file and then convert it into a key-value dictionary before passing it to the map function.

I assume your excel file looks something like this:

(credit for image:Mina Melek)

Having a column for symbol and a column for size. You now gotta import it:

import pandas as pd
symbols_sizes = pd.read_csv('path')

Now as map function can't be applied to a dataframe, you need to convert it to a dictionary first.

dictionary = {}
for symbol,size in zip(symbols_sizes[:,0],symbols_sizes[:,1]):
    dictionary[symbol] = size

And wallah!! now you have a dictionary made of all the entries in your excel file.

(This should also take care of all the duplicates in the original sheet because the dictionary doesn't allow duplication)