Pandas Excel: Import some excel data skipping first twenty rows into a Pandas dataframe

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

Pandas: Excel Exercise-6 with Solution

Write a Pandas program to import some excel data (coalpublic2013.xlsx ) skipping first twenty rows into a Pandas dataframe. Go to Excel data

Sample Solution:

Python Code :

import pandas as pd
import numpy as np
df = pd.read_excel('E:\coalpublic2013.xlsx', skiprows = 20)
df

Sample Output:

    2013   102976   Piney Woods Preparation Plant        0    23193
0   2013   103380                          Calera        0    12621
1   2013   103380                          Calera        0     1402
2   2013   103422                 Clark No 1 Mine   122727   140250
3   2013   103467             Helena Surface Mine    59664    30539
4   2013   101247                       No 4 Mine  2622528  1551141
5   2013   101401                       No 7 Mine  5405412  2464719
6   2013   103172  Searles Mine No. 2, 3, 4, 5, 6   258078   119542
7   2013   103179             Fleetwood Mine No 1    75937    63745
8   2013   103303                    Shannon Mine   317491   164388
9   2013   103323                   Deerlick Mine   133452    46381
10  2013   103364           Brc Alabama No. 7 Llc        0    14324
11  2013   103436                Swann's Crossing   137511    77190
12  2013   100347                    Choctaw Mine   537429   215295
13  2013   101362                 Manchester Mine   219457   116914
14  2013   102996                  Jap Creek Mine   375715   164093
15  2013   103155              Corinth Prep Plant        0    27996
16  2013   103155              Corinth Prep Plant        0    51994
17  2013   103195     Mccollum/Sparks Branch Mine    71910    17411
18  2013   103342             Reese's Branch Mine   263888   115123
19  2013   103370             Cresent Valley Mine     2860      621
20  2013   103372                 Cane Creek Mine    66258    32401
21  2013   103376                      Town Creek   299167   176499
22  2013   103389                Carbon Hill Mine    76241    84966
23  2013   103410                Coal Valley Mine   407841   158591
24  2013   103423                Dutton Hill Mine    37275     9162
25  2013  1519322                         Ghm #25    25054     3108
26  2013   103321                  Poplar Springs   189370    76366
27  2013   103358                       Old Union   284563   161805
28  2013  5000030                        Usibelli  1631584   286079
29  2013   201195                    Kayenta Mine  7602722  1015333

Excel Data:

coalpublic2013.xlsx:

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

Previous: Write a Pandas program to add a column named "column1" in the sixth position of the said excel sheet and fill it with NaN values.
Next: Write a Pandas program to add sum to a row of the given excel file.

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