Add 1 incremental based in a value of a column-CodePudding

I'm having a hard time trying to code a algorithm that add 1 to a "Card Number" based in the amount of the column "Card Amount".

[An Exemple of what i need]

I'll post what i've already tried, and the error that is happening.

The ValueError thats is happening is the

"The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()."

cod_1 = pd.Series({'Card Number':'1000','Card Amount': 4})
cod_2 = pd.Series({'Card Number':'2000','Card Amount': 5})
cod_3 = pd.Series({'Card Number':'3000','Card Amount': 3})

df = pd.DataFrame([cod_1, cod_2, cod_3])
df = df.loc[df.index.repeat(df['Card Amount'])]

for amt in df['Card Amount'].unique():
    number = 0
    for index,row in df.iterrows():
        if df.loc[index,'Card Amount'] == amt:
            df.loc[index,'Card Number'] = df.loc[index,'Card Number']   number
            number  = 1

Image - ValueError

CodePudding user response：

alright IIUC,you just need one line of code :

df['Card Number'] = df['Card Number'].astype(int)   df.groupby('Card Number').cumcount()

output :

    Card Number  Card Amount
0          1000            4
1          1001            4
2          1002            4
3          1003            4
4          2000            5
5          2001            5
6          2002            5
7          2003            5
8          2004            5
9          3000            3
10         3001            3
11         3002            3

CodePudding user response：

The issue is that you have repeated indexes in your df:

  Card Number  Card Amount
0        1000            4
0        1000            4
0        1000            4
0        1000            4
1        2000            5
1        2000            5
1        2000            5
1        2000            5
1        2000            5
2        3000            3
2        3000            3
2        3000            3

So when you call df.loc[index,'Card Amount'], this returns all the rows with an 0 index:

0    4
0    4
0    4
0    4
Name: Card Amount, dtype: int64

This is a series, so comparing it against the integer amt causes your error. I would suggest building your dataframe without using index.repeat, or using df.reset_index() before running.

You will then get an issue with the line after the if statement, as your Card Number column is stored as strings and the number variable is int. You can convert the card number to an integer to add the number to it:

df.loc[index,'Card Number'] = int(df.loc[index,'Card Number'])   number