Missing data, insert rows in Pandas and fill with NAN

by

set_index and reset_index are your friends. 

df = DataFrame({"A":[0,0.5,1.0,3.5,4.0,4.5], "B":[1,4,6,2,4,3], "C":[3,2,1,0,5,3]})

First move column A to the index:

In [64]: df.set_index("A")
Out[64]: 
     B  C
 A        
0.0  1  3
0.5  4  2
1.0  6  1
3.5  2  0
4.0  4  5
4.5  3  3

Then reindex with a new index, here the missing data is filled in with nans. We use the Index object since we can name it; this will be used in the next step.

In [66]: new_index = Index(arange(0,5,0.5), name="A")
In [67]: df.set_index("A").reindex(new_index)
Out[67]: 
      B   C
0.0   1   3
0.5   4   2
1.0   6   1
1.5 NaN NaN
2.0 NaN NaN
2.5 NaN NaN
3.0 NaN NaN
3.5   2   0
4.0   4   5
4.5   3   3

Finally move the index back to the columns with reset_index. Since we named the index, it all works magically:

In [69]: df.set_index("A").reindex(new_index).reset_index()
Out[69]: 
       A   B   C
0    0.0   1   3
1    0.5   4   2
2    1.0   6   1
3    1.5 NaN NaN
4    2.0 NaN NaN
5    2.5 NaN NaN
6    3.0 NaN NaN
7    3.5   2   0
8    4.0   4   5
9    4.5   3   3

More Related Contents:

  1. Pandas conditional creation of a series/dataframe column
  2. Converting between datetime, Timestamp and datetime64
  3. Pandas read_csv low_memory and dtype options
  4. Fast Haversine Approximation (Python/Pandas)
  5. replace() method not working on Pandas DataFrame
  6. Pandas Timedelta in Days
  7. Conditionally fill column values based on another columns value in pandas
  8. Calculate weighted average using a pandas/dataframe
  9. How to remove blanks/NA’s from dataframe and shift the values up
  10. Comparing previous row values in Pandas DataFrame
  11. Take multiple lists into dataframe
  12. Ambiguity in Pandas Dataframe / Numpy Array “axis” definition
  13. Populate a Pandas SparseDataFrame from a SciPy Sparse Matrix
  14. Using numpy.genfromtxt to read a csv file with strings containing commas
  15. Problem in combining bar plot and line plot (python)
  16. how do I calculate a rolling idxmax
  17. binning a dataframe in pandas in Python [duplicate]
  18. why does pandas rolling use single dimension ndarray
  19. PIP Install Numpy throws an error “ascii codec can’t decode byte 0xe2”
  20. vectorize conditional assignment in pandas dataframe
  21. Pandas: How to create a datetime object from Week and Year?
  22. Binning a column with pandas
  23. Convert numpy type to python
  24. Numpy isnan() fails on an array of floats (from pandas dataframe apply)
  25. plotting a histogram on a Log scale with Matplotlib
  26. A faster alternative to Pandas `isin` function
  27. Split Python sequence (time series/array) into subsequences with overlap
  28. Add density curve on the histogram
  29. Bin values based on ranges with pandas [duplicate]
  30. Numpy.dtype has the wrong size, try recompiling

Leave a Comment