w3resource

Pandas Excel: Insert a column in specified position in a excel sheet and fill it with NaN values

Pandas: Excel Exercise-5 with Solution

Write a Pandas program to insert a column in the sixth position of the said excel sheet and fill it with NaN values. Go to Excel data

Sample Solution:

Python Code :

import pandas as pd
import numpy as np
df = pd.read_excel('E:\coalpublic2013.xlsx')
df.insert(3, "column1", np.nan)
print(df.head) 

Sample Output:

    Year  MSHA ID                       Mine_Name  column1  Production  \
0   2013   103381            Tacoa Highwall Miner      NaN       56004  ..... 
1   2013   103404                Reid School Mine      NaN       28807  ..... 
2   2013   100759  North River #1 Underground Min      NaN     1440115  ..... 
3   2013   103246                      Bear Creek      NaN       87587  ..... 
4   2013   103451                     Knight Mine      NaN      147499  ..... 
5   2013   103433              Crane Central Mine      NaN       69339  ..... 
6   2013   100329                    Concord Mine      NaN           0  ..... 
7   2013   100851                  Oak Grove Mine      NaN     2269014  ..... 
8   2013   102901                Shoal Creek Mine      NaN           0  ..... 
9   2013   102901                Shoal Creek Mine      NaN     1453024  ..... 
10  2013   103180             Sloan Mountain Mine      NaN      327780  .....
11  2013   103182                        Fishtrap      NaN      175058  ..... 
12  2013   103285                     Narley Mine      NaN      154861  ..... 
13  2013   103332                   Powhatan Mine      NaN      140521  ..... 
14  2013   103375                    Johnson Mine      NaN         580  ..... 
15  2013   103419               Maxine-Pratt Mine      NaN      125824  ..... 
16  2013   103432                   Skelton Creek      NaN        8252  ..... 
17  2013   103437         Black Warrior Mine No 1      NaN      145924  ..... 
18  2013   102976   Piney Woods Preparation Plant      NaN           0  ..... 
19  2013   102976   Piney Woods Preparation Plant      NaN           0  ..... 
20  2013   103380                          Calera      NaN           0  ..... 
21  2013   103380                          Calera      NaN           0  ..... 
22  2013   103422                 Clark No 1 Mine      NaN      122727  ..... 
23  2013   103467             Helena Surface Mine      NaN       59664  ..... 
24  2013   101247                       No 4 Mine      NaN     2622528  ..... 
25  2013   101401                       No 7 Mine      NaN     5405412  ..... 
26  2013   103172  Searles Mine No. 2, 3, 4, 5, 6      NaN      258078  ..... 
27  2013   103179             Fleetwood Mine No 1      NaN       75937  ..... 
28  2013   103303                    Shannon Mine      NaN      317491  ..... 
29  2013   103323                   Deerlick Mine      NaN      133452  ..... 
30  2013   103364           Brc Alabama No. 7 Llc      NaN           0  ..... 
31  2013   103436                Swann's Crossing      NaN      137511  ..... 
32  2013   100347                    Choctaw Mine      NaN      537429  ..... 
33  2013   101362                 Manchester Mine      NaN      219457  ..... 
34  2013   102996                  Jap Creek Mine      NaN      375715  ..... 
35  2013   103155              Corinth Prep Plant      NaN           0  ..... 
36  2013   103155              Corinth Prep Plant      NaN           0  ..... 
37  2013   103195     Mccollum/Sparks Branch Mine      NaN       71910  ..... 
38  2013   103342             Reese's Branch Mine      NaN      263888  ..... 
39  2013   103370             Cresent Valley Mine      NaN        2860  ..... 
40  2013   103372                 Cane Creek Mine      NaN       66258  ..... 
41  2013   103376                      Town Creek      NaN      299167  ..... 
42  2013   103389                Carbon Hill Mine      NaN       76241  ..... 
43  2013   103410                Coal Valley Mine      NaN      407841  ..... 
44  2013   103423                Dutton Hill Mine      NaN       37275  ..... 
45  2013  1519322                         Ghm #25      NaN       25054  ..... 
46  2013   103321                  Poplar Springs      NaN      189370  ..... 
47  2013   103358                       Old Union      NaN      284563  ..... 
48  2013  5000030                        Usibelli      NaN     1631584  ..... 
49  2013   201195                    Kayenta Mine      NaN     7602722  ..... 	                                       

Excel Data:

coalpublic2013.xlsx:


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

Previous: Write a Pandas program to find the sum, mean, max, min value of 'Production (short tons)' column of coalpublic2013.xlsx file.
Next: Write a Pandas program to import given excel data (coalpublic2013.xlsx ) skipping first twenty rows into a Pandas dataframe.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

Python: Tips of the Day

Split a String:

>>> sentence = 'this is, a python, tutorial, about, idioms.'
>>> sentence.split(', ')
['this is', 'a python', 'tutorial', 'about', 'idioms.']
>>> sentence.split(', ', 2)
['this is', 'a python', 'tutorial, about, idioms.']
>>> sentence.rsplit(', ')
['this is', 'a python', 'tutorial', 'about', 'idioms.']
>>> sentence.rsplit(', ', 2)
['this is, a python, tutorial', 'about', 'idioms.']