Pandas long to wide with a twist

Question

I have a dataframe that looks something like this:

ex_years = [2016, 2016, 2016, 2017, 2017, 2017, 2017, 2018, 2018, 2018]
ex_xs = [3, 1, 3, 2, 2, 5, 2, 1, 4, 1]
ind = [1, 2, 3, 1, 2, 3, 4, 1, 2, 3]
prev_ind = [np.nan, np.nan, np.nan, np.nan, 2, np.nan, 3, 1, 1, 4]
prev_year = [np.nan, np.nan, np.nan, np.nan, 2016, np.nan, 2016, 2016, 2017, 2017]
df = pd.DataFrame({'Year':ex_years, 'ind':ind, 'prev_ind':prev_ind, 'prev_year':prev_year, 'x':ex_xs})

df
   Year  ind  prev_ind  prev_year  x
0  2016    1       NaN        NaN  3
1  2016    2       NaN        NaN  1
2  2016    3       NaN        NaN  3
3  2017    1       NaN        NaN  2
4  2017    2       2.0     2016.0  2
5  2017    3       NaN        NaN  5
6  2017    4       3.0     2016.0  2
7  2018    1       1.0     2016.0  1
8  2018    2       1.0     2017.0  4
9  2018    3       4.0     2017.0  1

I need to convert it to a more rectangular format. Note that the link between observations is prev_ind and prev_year, meaning that the index in prev_year was prev_ind for that observation. Here is what the output should look like for this example:

   2016  2017  2018
0   3.0   NaN   1.0
1   NaN   2.0   4.0
2   1.0   2.0   NaN
3   NaN   5.0   NaN
4   3.0   2.0   1.0

I'm getting tripped up by the wierd linking format where there may randomly be missing years. Any ideas on how to do this would be greatly appreciated. I should mention that the data isn't too huge so efficiency isn't a first order concern here.

Edit:

I need to clarify. In this example there are 5 people. The index for each person is not constant across the years - this is the challenge.

Take the last row of the input dataframe. This is telling you that this person in 2018 had x=1. It is also telling you how to locate this person in previous years. In this case, we can see that this person is showing up in 2017, when their index is 4. So then you can see that in 2017 they had x=2, and that you can see them again in 2016 under ind=3.

Some people only show up in one year; some have missing years, like the person in row 0 of the example output. Hopefully this helps clarify.

Gonçalo Peres · Answer

IIUC, and if OP only wants an output similar to that one, then most of the columns would not be required.

Even though one can also do the operation with the complete dataframe, for the sake of this answer let's consider the dataframe df with only three columns: Year, ind, and x

import pandas as pd

ex_years = [2016, 2016, 2016, 2017, 2017, 2017, 2017, 2018, 2018, 2018]
ex_xs = [3, 1, 3, 2, 2, 5, 2, 1, 4, 1]
ind = [1, 2, 3, 1, 2, 3, 4, 1, 2, 3]
df = pd.DataFrame({'Year':ex_years, 'ind':ind, 'x':ex_xs})

[Out]:
   Year  ind  x
0  2016    1  3
1  2016    2  1
2  2016    3  3
3  2017    1  2
4  2017    2  2
5  2017    3  5
6  2017    4  2
7  2018    1  1
8  2018    2  4
9  2018    3  1

What OP seems to be looking for is what we call pivot the dataframe, and for that pandas.DataFrame.pivot comes in handy. With that, one can do the following

df2 = df.pivot(index='ind', columns='Year', values='x').reset_index()

and the result is

      ind  2016  2017  2018
0       1   3.0   2.0   1.0
1       2   1.0   2.0   4.0
2       3   3.0   5.0   1.0
3       4   NaN   2.0   NaN

It is not exactly the same expected output that OP shared in the question, but it might be what OP is looking for.

Pandas long to wide with a twist

1 Answers