CODEWITHSUNDEEP
pythonpython-3.xdataframe
question.it
question.it

Reputation: 2978

Fill Data Column Value with Incremental Number, Python 3.6

I have Dataframe contains columns + "Order" column which has integer unique numbers and some rows are zero.

I need to update zero with incremental number from maximum value of "Order" value.

For ex:

Max Value of df['Order'] = 4 and there are 3 records df['Order'] == 0 then those 3 rows with 0 values need to fill with 5, 6, 7.

I tried below script:

Dimension_Items = {'Col1':['A', 'B', 'C', 'D', 'E', 'F'], 'Order':[0,2,3,4,0,0]}
Dimension_Items = pd.DataFrame.from_dict(Dimension_Items)

Max_Order = Dimension_Items['Order'].max()
Dimension_Items.loc[Dimension_Items['Order'] == 0, 'Order'] = range(Max_Order+1, len(Dimension_Items)+1)

Error:

Traceback (most recent call last):
    Dimension_Items.loc[Dimension_Items['Order'] == 0, 'Order'] = range(Max_Order+1, len(Dimension_Items)+1)
  File "C:\Python36\lib\site-packages\pandas\core\indexing.py", line 189, in __setitem__
    self._setitem_with_indexer(indexer, value)
  File "C:\Python36\lib\site-packages\pandas\core\indexing.py", line 606, in _setitem_with_indexer
    raise ValueError('Must have equal len keys and value '
ValueError: Must have equal len keys and value when setting with an iterable

Giveing Error, Please help on this

Upvotes: 0

Views: 73

Answers (2)

gold_cy
gold_cy

Reputation: 14226

So we can isolate the zero's and then fill them with values from a list if I understand correctly.

l = range(df.Order.max() + 1, df.Order.max() + df.loc[df.Order == 0, 'Order'].size + 1)

df.loc[df.Order == 0, 'Order'] = l

  Col1  Order
0    A      5
1    B      2
2    C      3
3    D      4
4    E      6
5    F      7

Upvotes: 1

Vinícius Alcântara
Vinícius Alcântara

Reputation: 191

SPy.

You can iterate through the DataFrame to find items with Order = 0 and then update each Order with max_order + 1. Try this:

import pandas as pd

data = {

    'Col1': ['A', 'B', 'C', 'D', 'E', 'F'], 
    'Order': [0, 2, 3, 4, 0, 0]

}

df = pd.DataFrame(data)

max_order = df['Order'].max()

for i in range(len(df)):

    if df.loc[i, 'Order'] == 0:
        df.loc[i, 'Order'] = max_order + 1
        max_order += 1

df.head()

Output:

    Order   Value
0      A        5
1      B        2
2      C        3
3      D        4
4      E        6

Hope this helps.

Upvotes: 1

Related Questions