Missing data, insert rows in Pandas and fill with NAN

Question

I'm new to Python and Pandas so there might be a simple solution which I don't see.

I have a number of discontinuous datasets which look like this:

ind A    B  C   0   0.0  1  3   1   0.5  4  2   2   1.0  6  1   3   3.5  2  0   4   4.0  4  5   5   4.5  3  3   

I now look for a solution to get the following:

ind 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   

The problem is,that the gap in A varies from dataset to dataset in position and length...

Answer 1

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