Unstacking values into different rows in Pandas DataFrame

user_5469184 0
Viewed 272

I have a DataFrame (below). I am interested in unstacking the from and to columns.

enter image description here

I need to unstack from and to columns into multiple rows for each different year between from and to. For example, the first row will produce something like this

enter image description here

I tried everything from pd.melt() to pd.pivot_table().

pandas pivot-table python
1 Answers

Create a column of array-like objects with all of the years between 'from' and 'to' then explode on that column. It won't be the most efficient solution, but it's simple.

Sample Data

   a  b  c  d  e  from    to
0  2  9  1  7  3  1940  1945
1  5  6  2  6  1  1950  1951

Code

df['year'] = [np.arange(f,t+1) for f,t in zip(df['from'], df['to'])]
df = df.explode('year')

   a  b  c  d  e  from    to  year
0  2  9  1  7  3  1940  1945  1940
0  2  9  1  7  3  1940  1945  1941
0  2  9  1  7  3  1940  1945  1942
0  2  9  1  7  3  1940  1945  1943
0  2  9  1  7  3  1940  1945  1944
0  2  9  1  7  3  1940  1945  1945
1  5  6  2  6  1  1950  1951  1950
1  5  6  2  6  1  1950  1951  1951
user_43333590
Related