creating new rows from difference of two columns in pandas dataframe

user_14227528 0
Viewed 89

I have a data frame.


ID     value-a value-b  start-year end-year

 

1       10       15         2010        2012

2       20       24         2011        2013

3       10       20         2012        0

I wanna generate a new column 'year' such that: each row will be repeated for all the year from start year to end year.


ID     value-a value-b    year

 

1       10       15       2010 

1       10       15       2011

1       10       15       2012

2       20       25       2011

2       20       24       2012

2       20       24       2013

3       10       20       2012

I have used the following code, but cant get correct output:


df =pd.concat([pd.DataFrame({'year': pd.date_range(row.start-year, row.end_year, freq='A'),

                           'value-a': row.value-a,

                          'value-b': row.value-b,columns=['year','value-a', 'value-b'])

                              for i, row in df.iterrows()], ignore_index=True)

Any help will be much appreciated.

pandas data-manipulation python
1 Answers

First replace 0 in end-year by start-year if there is 0, create range column in DataFrame.apply and last DataFrame.explode with remove original start and end year columns:

df['end-year'] = df['end-year'].mask(df['end-year'].eq(0), df['start-year'])

df['year'] = df.apply(lambda x: range(x['start-year'], x['end-year'] + 1), axis=1)
df = df.explode('year').drop(['start-year','end-year'], axis=1).reset_index(drop=True)
print (df)
   ID  value-a  value-b  year
0   1       10       15  2010
1   1       10       15  2011
2   1       10       15  2012
3   2       20       24  2011
4   2       20       24  2012
5   2       20       24  2013
6   3       10       20  2012
user_29010020
Related