Take values of a column in a Pandas data frame, add numbers to value and save as new data frame

Question

So I basically have a Pandas data frame df that looks like this:

      0     1
  0  123   234
  1  534   42
  2  213   687
  3  425   123
 ...
 20

What I want to do is take the values of column 1, add all numbers from 00 to 20 and save that in a new data frame newdf with the corresponding value from column 0. The output is supposed to look like this:

      0     1
  0  123   23400
  1  123   23401
  2  123   23402
  3  123   23403   
 ... 
 20  123   23420
 21  534   4200
 ...

I understand that I have to use loops, but I am completely lost in how to achieve what I want. This is what I have come up with so far:

newdf = pd.DataFrame()
for x in df[1]:
    for y in range(len(df)):
        for one in range(0,9):
            newdf.append(df.iloc[y,0], df.iloc[y,1])
        for two in range(10,20):
            newdf.append(df.iloc[y,0], df.iloc[y,1])

This is missing the part where it adds the numbers from 00 to 20 to the value from the second column, because I can't even get that to work.

I hope this question is understandable. If I did anything wrong in this post please let me know!

score 1 · Accepted Answer · answered Feb 07 '19 at 21:28

You can do this by first performing a CROSS JOIN (Cartesian Product) on the DataFrame and suffixes.

u = pd.DataFrame(dict(suf=range(20), key=1))
v = df.assign(key=1).merge(u, on='key').drop('key', 1)

v.iloc[:,1] = v.iloc[:,1].astype(str).add(
    v.pop('suf').astype(str).str.zfill(2)).astype(int)

v.head()

     0      1
0  123  23400
1  123  23401
2  123  23402
3  123  23403
4  123  23404

Take values of a column in a Pandas data frame, add numbers to value and save as new data frame

1 Answers1