CODEWITHSUNDEEP
pythonpandas
raam
raam

Reputation: 241

How to extract a DataFrame using start and end dates with Pandas

How can we extract the DataFrame using start and end dates and achieve this output?

Input

id  start  end
1   2009   2014
2   2010   2012

Output

id  data
1   2009
1   2010
1   2011
1   2012
1   2013
1   2014
2   2010
2   2011
2   2012

Upvotes: 2

Views: 1157

Answers (3)

BENY
BENY

Reputation: 323226

A little bit hard to understand,I think this should be slightly faster than apply

By using reindex and repeat

df.reindex(df.index.repeat(df['end']-df['start']+1)).assign(year=lambda x : x['start']+x.groupby('id').cumcount())
Out[453]: 
   id  start   end  year
0   1   2009  2014  2009
0   1   2009  2014  2010
0   1   2009  2014  2011
0   1   2009  2014  2012
0   1   2009  2014  2013
0   1   2009  2014  2014
1   2   2010  2012  2010
1   2   2010  2012  2011
1   2   2010  2012  2012

Upvotes: 1

jezrael
jezrael

Reputation: 862431

Use:

df1 = (pd.concat([pd.Series(r.id,np.arange(r.start, r.end + 1)) for r in df.itertuples()])
        .reset_index())
df1.columns = ['data','id']
df1 = df1[['id','data']]
print (df1)
   id  data
0   1  2009
1   1  2010
2   1  2011
3   1  2012
4   1  2013
5   1  2014
6   2  2010
7   2  2011
8   2  2012

Upvotes: 1

DJK
DJK

Reputation: 9274

create the enumeration of dates between years grouped by ['id']. Additional reformatting of the index is optional 

import numpy as np
import pandas as pd
melted = df.groupby('id').apply(lambda x:pd.Series(np.arange(x['start'],x['end']+1)))

melted.index = melted.index.droplevel(1)

id
1    2009
1    2010
1    2011
1    2012
1    2013
1    2014
2    2010
2    2011
2    2012

Upvotes: 2

Related Questions