Python: How to assign value in each column with a given total in each row-CodePudding

I want to assign row-specific values to each row over 30 new columns. I have a column called totalnumber(Int) and I want to create 30 new columns and assign a value of 1 into each new column repeat until the sum of the 30 columns equal to the value of the totalnumber. Like this

Total Number col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 so on col30
9            1    1    1    1    1    1    1    1    1    0     0     0           0
30           1    1    1    1    1    1    1    1    1    1     1     1           1
35           2    2    2    2    2    1    1    1    1    1     1     1           1

I am new to python, I think I need a while and for loops but now sure how to go about to do this. Can anyone help?

I can only to think of divide and assign the partial into the first column using the below code but this is not what I want...

df = baseline.loc[baseline.Pathway == "Referred", grouping_cols   ["TotalNumbers"]] 
for col in list(range(1, 31)): #Iterate through the 30 columns
    referred[col] = np.floor(df["TotalNumbers"] / 30) 
df[1] = df[1]   (df["TotalNumbers"] % 30)

CodePudding user response：



import pandas as pd

df = pd.DataFrame({"Total Number": [9, 30, 35]})



# define which columns need to be created
# this will be the range between 1 and the maximum of the Total Number column
columns_to_fill = ["col"   str(i) for i in range(1, 31)]
# columns_to_fill = [ col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, .... , col35 ]


# now, go through each row of your dataframe
for indx, row in df.iterrows():
    # and for each column in the new columns to be filled
    # check if the number is smaller or equal than the row's Total Number
    # if it is smaller, fill the column with 1
    # else fill the column with 0
    
    for number, column in enumerate(columns_to_fill):
        if number   1 <= row["Total Number"]:
            df.loc[indx, column] = 1
        else:
            df.loc[indx, column] = 0
    

    # now check if there is a remainder
    remainder = row["Total Number"] - 30
    
    # while remainder is greater than 0
    # we need to continue adding  1 to the columns
    while remainder > 0:
        for number, column in enumerate(columns_to_fill):
            if number   1 <= remainder:
                df.loc[indx, column]  = 1
            else:
                continue
        # update remainder
        remainder = remainder - 30


print(df)

CodePudding user response：

In pandas, you can use two-dimensional slicing (as in NumPy), to select a part of the dataframe. This is quite convenient here, because it allows you to assign 1 to a slice of one row at once.

First I would save the number of existing columns in the dataframe, so that you have an easy way to count upwards from that. Then assign the new columns as all zero.

After these preparations, you can iterate over the rows and assign 1 to a slice of each row, whose length is given by the Total Number:

import pandas as pd

df = pd.DataFrame({'Total Number': [9, 30, 35]})
n_columns = len(df.columns)

for newcol in range(1, 31):
    df['col'   str(newcol)] = 0
    
for row in range(len(df)):
    total = df['Total Number'][row]
    df.iloc[row, n_columns : n_columns   total] = 1

To get the 2s in those lines where the total was more than 30, you could just repeat the process after reducing each total by 30:

remainder = df['Total Number'] - 30

for row in range(len(df)):
    if remainder[row] > 0:
        df.iloc[row, n_columns : n_columns   remainder[row]]  = 1

If the totals can be greater than 60, you may want to write an extra loop for repeating this process.