Pandas Excel: Import three datasheets from a given excel data into a single dataframe

Last update on May 28 2022 13:03:50 (UTC/GMT +8 hours)

Pandas: Excel Exercise-22 with Solution

Write a Pandas program to import three datasheets from a given excel data (coalpublic2013.xlsx ) and combine in to a single dataframe. Go to Excel data

Note: Structure of the three datasheets are same.

Sample Solution:

Python Code :

import pandas as pd
import numpy as np
df1 = pd.read_excel('E:\employee.xlsx',sheet_name=0)
df2 = pd.read_excel('E:\employee.xlsx',sheet_name=1)
df3 = pd.read_excel('E:\employee.xlsx',sheet_name=2)
df = pd.concat([df1, df2, df3])
print(df)

Sample Output:

    emp_id   first_name    last_name  hire_date
0      100       Steven         King 2003-06-17
1      101        Neena      Kochhar 2005-09-21
2      102          Lex      De Haan 2001-01-13
3      103    Alexander       Hunold 2006-01-03
4      104        Bruce        Ernst 2007-05-21
5      105        David       Austin 2005-06-25
6      106        Valli    Pataballa 2006-02-05
7      107        Diana      Lorentz 2007-02-07
8      108        Nancy    Greenberg 2002-08-17
9      109       Daniel       Faviet 2002-08-16
10     110         John         Chen 2005-09-28
11     111       Ismael      Sciarra 2005-09-30
12     112  Jose Manuel        Urman 2006-03-07
13     113         Luis         Popp 2007-12-07
14     114          Den     Raphaely 2002-12-07
15     115    Alexander         Khoo 2003-05-18
16     116       Shelli        Baida 2005-12-24
17     117        Sigal       Tobias 2005-07-24
18     118          Guy       Himuro 2006-11-15
19     119        Karen   Colmenares 2007-08-10
0      120      Matthew        Weiss 2004-07-18
1      121         Adam        Fripp 2005-04-10
2      122        Payam     Kaufling 2003-05-01
3      123       Shanta      Vollman 2005-10-10
4      124        Kevin      Mourgos 2007-11-16
5      125        Julia        Nayer 2005-07-16
6      126        Irene  Mikkilineni 2006-09-28
7      127        James       Landry 2007-01-14
8      128       Steven       Markle 2008-03-08
9      129        Laura       Bissot 2005-08-20
10     130        Mozhe     Atkinson 2005-10-30
11     131        James       Marlow 2005-02-16
12     132           TJ        Olson 2007-04-10
13     133        Jason       Mallin 2004-06-14
14     134      Michael       Rogers 2006-08-26
15     135           Ki          Gee 2007-12-12
16     136        Hazel   Philtanker 2008-02-06
17     137       Renske       Ladwig 2003-07-14
18     138      Stephen       Stiles 2005-10-26
0      141       Trenna         Rajs 2003-10-17
1      142       Curtis       Davies 2005-01-29
2      143      Randall        Matos 2006-03-15
3      144        Peter       Vargas 2006-07-09
4      145         John      Russell 2004-10-01
5      146        Karen     Partners 2005-01-05
6      147      Alberto    Errazuriz 2005-03-10
7      148       Gerald    Cambrault 2007-10-15
8      149        Eleni      Zlotkey 2008-01-29

Excel Data:

coalpublic2013.xlsx:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a Pandas program to import sheet2 data from a given excel data (employee.xlsx ) into a Pandas dataframe.
Next: Write a Pandas program to import given excel data (coalpublic2013.xlsx) into a dataframe and draw a bar plot where each bar will represent one of the top 10 production.

Python: Tips of the Day

Find current directory and file's directory:

To get the full path to the directory a Python file is contained in, write this in that file:

import os dir_path = os.path.dirname(os.path.realpath(__file__))

(Note that the incantation above won't work if you've already used os.chdir() to change your current working directory, since the value of the __file__ constant is relative to the current working directory and is not changed by an os.chdir() call.)

To get the current working directory use

import os cwd = os.getcwd()

Documentation references for the modules, constants and functions used above:

The os and os.path modules.

The __file__ constant

os.path.realpath(path) (returns "the canonical path of the specified filename, eliminating any symbolic links encountered in the path")

os.path.dirname(path) (returns "the directory name of pathname path")

os.getcwd() (returns "a string representing the current working directory")

os.chdir(path) ("change the current working directory to path")

Ref: https://bit.ly/3fy0R6m