Find and Add Missing Column Values Based on Index Increment Python Pandas Dataframe

Question

Good Afternoon!

I have a pandas dataframe with an index and a count.

dictionary = {1:5,2:10,4:3,5:2}
df = pd.DataFrame.from_dict(dictionary , orient = 'index' , columns = ['count'])

What I want to do is check from df.index.min() to df.index.max() that the index increment is 1. If a value is missing like in my case the 3 is missing then I want to add 3 to the index with a 0 in the count.

The output will look like the below df2 but done in a programmatic fashion so I can use it on a much bigger dataframe.

RESULTS EXAMPLE DF:

dictionary2 = {1:5,2:10,3:0,4:3,5:2}
df2 = pd.DataFrame.from_dict(dictionary2 , orient = 'index' , columns = ['count'])

Thank you much!!!

sammywemmy · Accepted Answer · 2021-02-02 22:37:50Z

1

Ensure the index is sorted:

df = df.sort_index()

Create an array that starts from the minimum index to the maximum index

   complete_array = np.arange(df.index.min(), df.index.max() + 1)

Reindex, fill the null value with 0, and optionally change the dtype to Pandas Int:

df.reindex(complete_array, fill_value=0).astype("Int16")

    count
1   5
2   10
3   0
4   3
5   2

answered Feb 2, 2021 at 22:37

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find and Add Missing Column Values Based on Index Increment Python Pandas Dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related