Iterate through columns and divide column value and creating new column with result in column

Question

I'm working with census data, and the columns provide age groups instead of discrete ages. For the sake of working with the data on a generational basis (millennials, baby boomers, etc), I need to convert these to to single years. (i.e. One column for Ages 5-9 instead of one column for Age 5, one column for Age 6, etc.)

As I'm just trying to identity trends, I'm fine with just splitting each age category equally to get a discrete value for each year.

I'd like to iterate through each column, and create 5 new columns, each with the original column value divided by 5 (as there are 5 years in each group.

I've tried setting variables at zero for both the new column names and the index of the column I want to divide equally.

I've then written a for loop to iterate through each column in the dataframe. Within that I have a nested for loop so it performs the operation 5 times on each column. I'm then incrementing the n value for the new column name each time in the inner for loop, and incrementing the s value for the index of the column being divided in the outer for loop.

df = pd.DataFrame([[6.8, 6.5], [5.2, 8.9], [6.4, 7.6]], columns= ['Under 5 years', '5 to 9 years'])

## Set up variables. 'n' is for the new column name. 's' is the index of the column to be divided.

n= 0 
s = 0

## For loop to iterate through each column in the dataframe and perform the operation on each column 5 times before moving onto the next column:

for s in df.iteritems():
    for i in range(5):
        df['{}'.format(n)].iloc = df[s].iloc/5
        n+=1 
     s+=1

I keep getting a Type Error: Under 5 years, dtype: float64)' is an invalid key

I can't figure out how to resolve this error, or if the rest of the code will even work properly.

Desired Output would be the following dataframe:

df = pd.DataFrame([[6.8, 6.5, 1.36, 1.36, 1.36, 1.36, 1.36, 1.3, 1.3, 1.3, 1.3, 1.3], [5.2, 8.9, 1.04, 1.04, 1.04, 1.04, 1.04, 1.78, 1.78, 1.78, 1.78, 1.78], [6.4, 7.6, 1.28, 1.28, 1.28, 1.28, 1.28, 1.52, 1.52, 1.52, 1.52, 1.52]], columns=['Under 5 years', '5 to 9 years', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9'])

Please provide a [mcve] for your issue. See this link on creating [good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) so we know how to help you better — G. Anderson, Jun 24 '19 at 17:21
What is your desired output given the provided input? That's not entirely clear from your question — G. Anderson, Jun 24 '19 at 17:32
@harvpan Updated question. df_numbers was a typo from my actual code. — 7lana7, Jun 24 '19 at 17:59

score 2 · Accepted Answer · answered Jun 24 '19 at 18:15

This line df['{}'.format(n)].iloc = df[s].iloc/5 is plain wrong:

s is not a column name but is a pair (column_name, column) so it cannot be used to index a dataframe.
iloc is wrong too, and is anyway useless
it is extremely dangerous to change something you are iterating, so in a for s in df.iteritems(): loop, you should never add columns to df

According to the expected result, what you want is:

cols = df.columns.tolist()
for j, s in enumerate(cols):  # ok cols is a plain list
    for i in range(5):
        df[str(i + 5*j)] = df[s]/5

score 0 · Answer 2 · answered Jun 24 '19 at 18:25

Why not just use cut

In [12]: df = pd.DataFrame({'age':[5,8,10,25,33,22,38]})

In [13]: df
Out[13]:
   age
0    5
1    8
2   10
3   25
4   33
5   22
6   38

In [14]: df['age_group'] = pd.cut(df.age,bins=[5,10,15,20,25,30,35,40])

In [15]: df
Out[15]:
   age age_group
0    5       NaN
1    8   (5, 10]
2   10   (5, 10]
3   25  (20, 25]
4   33  (30, 35]
5   22  (20, 25]
6   38  (35, 40]

In [16]: df.groupby(['age_group']).count()
Out[16]:
           age
age_group
(5, 10]      2
(10, 15]     0
(15, 20]     0
(20, 25]     2
(25, 30]     0
(30, 35]     1
(35, 40]     1

Iterate through columns and divide column value and creating new column with result in column

2 Answers2