How to get correspondent value in excel from a pandas dataframe

Question

In Python from a pandas dataframe json = pd.read_json('https://tikdata.com/master.json'), how can I find the corresponding Token number for the values in the Symbol column of the excel sheet and print the correspondent token number in the Token column of the Excel sheet?

Pandas data frame output:

          Token      Symbol
 0         w            d
 1         v            b
 2         x            a
 3         y            e
 4         z            c
 

Excel Sheet

Answer 1

It can be easily done in Excel with index and match functions. But if you want to do it in python/pandas, I'd suggest you to

• import your excel sheet,
• merge the dataframes and then, (if you wish),
• export the new dataframe to excel:

import pandas as pd

excel = pd.read_excel("/your pathway/your excel file name.xlsx", header=0, usecols="A")

excel = pd.merge(
    excel,
    json,
    on=["Symbol"], 
    how="left"
)

excel.to_excel("/your pathway/new file name.xlsx") 

Just to test this solution, see the code below:

# Creating your Json dataframe
json_dict = {'Token': {0: 'w', 1: 'v', 2: 'x', 3: 'y', 4: 'z'},
 'Symbol': {0: 'd', 1: 'b',
  2: 'a', 3: 'e', 4: 'c'}}

json = pd.DataFrame(json_dict)

# Simulating your excel dataframe
excel_dict = {
 'Symbol': {0: 'a', 1: 'b',
  2: 'c'}}

excel = pd.DataFrame(excel_dict)

# Merge the dataframes
excel = pd.merge(
    excel,
    json,
    on=["Symbol"], 
    how="left"
)

Output:

    Symbol  Token
0   a       x
1   b       v
2   c       z