Convert one row of a pandas dataframe into multiple rows

Question

I want to turn this:

   age  id  val
0   99   1  0.3
1   99   2  0.5
2   99   3  0.1

Into this:

   age  id  val
0   25   1  0.3
1   50   1  0.3
2   75   1  0.3
3   25   2  0.5
4   50   2  0.5
5   75   2  0.5
6   25   3  0.1
7   50   3  0.1
8   75   3  0.1

Context: I have data stored with one value coded for all ages (age = 99). However, the application I am developing for needs the value explicitly stated for every id-age pair (id =1, age = 25,50, and 75). There are simple solutions to this: iterate over id's and append a bunch of dataframes, but I'm looking for something elegant. I'd like to do a many:one merge from my original dataframe to a template containing all the ages, but I would still have to loop over id's to create the template.

roman · Accepted Answer · 2013-11-19 05:57:50Z

2

Don't know, may be there's more elegant approach, but you can do something like cross join (or cartesian product):

>>> df = pd.DataFrame({'age':[99,99,99],'id':[1,2,3],'val':[0.3,0.5,0.1]})
>>> df
   age  id  val
0   99   1  0.3
1   99   2  0.5
2   99   3  0.1
>>> df2 = pd.DataFrame({'age':[99,99,99],'new_age':[25,50,75]})
>>> df2 = pd.merge(df, df2, on='age')
>>> del df2['age']
>>> df2 = df2.rename(columns={'new_age':'age'})
>>> df2
   id  val      age
0   1  0.3       25
1   1  0.3       50
2   1  0.3       75
3   2  0.5       25
4   2  0.5       50
5   2  0.5       75
6   3  0.1       25
7   3  0.1       50
8   3  0.1       75

edited Nov 19, 2013 at 5:57

answered Nov 19, 2013 at 5:49

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kyle Heuton Over a year ago

Thanks! This is exactly what I was looking for, and I guess I even said the words many to one in my question, but I didn't understand that you could merge like that

roman Over a year ago

@Snoozer I think code could be cleaned a bit, but you've got overall idea

Kenneth · Accepted Answer · 2022-01-21 18:23:12Z

0

If Pandas >= 1.2 version

import pandas as pd 

pd.__version__
# '1.2.0'

left = pd.DataFrame({'col1': [1, 2], 'col2': [3, 4]})
right = pd.DataFrame({'col3': [5, 6]}) 

left.merge(right, how='cross')

   col1  col2  col3
0     1     3     5
1     1     3     6
2     2     4     5
3     2     4     6

If Pandas < 1.2

df1.assign(key=1).merge(df2.assign(key=1), on="key").drop("key", axis=1)

answered Jan 21, 2022 at 18:23

Kenneth

313 bronze badges

Collectives™ on Stack Overflow

Convert one row of a pandas dataframe into multiple rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related