CODEWITHSUNDEEP
pythonpandasnumpyopenpyxl
Lynn
Lynn

Reputation: 4408

Extract values from two different excel files using openpyxl and create a new dataframe using Python

I have two excel workbooks, df1 and df2, where I would like to extract values from to create a new dataframe using openpyxl.

df1

      2021    2021
      q1      q2
ID    5       10
ID2   1       1

enter image description here

df2

   name    rank
   hello   1000

enter image description here

Desired

    #Create a new dataframe using both excel values inputs from both df1 and df2

    name   year   quarter  ID  rank     ID2
 
    hello  2021   q1       1   1000     5

Doing

   from  openpyxl import load_workbook
   import pandas as pd
   wb1 = load_workbook('df1.xlsx')
   wb2 = load_workbook('df2.xlsx')


   df1 = pd.DataFrame(wb1.values)
   df2 = pd.DataFrame(wb2.values)


   #Grabbing all the required values from wb1

   a = wb1['B1']
   b = wb1['B2']
   c = wb1['B3']
   d = wb1['B4']
   e = wb1['A3']
   f = wb1['A4']


   #Grabbing all the required values from wb2

   g = wb2['A1']
   h = wb2['A2']
   i = wb2['B1']
   j = wb3['B2']

I know how to extract values using openpyxl, but a little unclear on to how to then create a new dataframe with these extracted values. Any suggestion is appreciated.

Upvotes: 0

Views: 144

Answers (1)

Sırrı Kırımlıoğlu
Sırrı Kırımlıoğlu

Reputation: 607

You can do something like this:

data = {'Name':[h],
    'Year':[a],
    'Quarter': [b],
    'ID': [c],
    'Rank': [j],
    'ID2': [d]
}

df = pd.DataFrame(data)

Upvotes: 1

Related Questions