1

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

jyotirmay
  • 37
  • 4
  • Index() with match() works in Excel. – Solar Mike Aug 21 '21 at 10:53
  • see [pandas-merging-101](https://stackoverflow.com/questions/53645882/pandas-merging-101) but for thar you need to load your excel file in pandas or filter out current dataframe on the basis of values of Symbol in your excel sheet – Anurag Dabas Aug 21 '21 at 11:12

1 Answers1

0

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
Allan
  • 321
  • 1
  • 8