Balancing a panel data for regression

You are close to the solution. You can amend your code slightly as follows:

idx = pd.MultiIndex.from_product([df['id'].unique(),range(df.year.min(),df.year.max()+1)],names=['id','year'])

df2 = df.set_index(['id', 'year']).reindex(idx).reset_index()

df2['city'] = df2.groupby('id')['city'].ffill().bfill()

Changes to your codes:

1. Create the MultiIndex by using unique values of id instead of from index
2. Set index on both id and year before reindex()
3. Fill-in the NaN values of column city by non-NaN entries of the same id

Result:

print(df2)

   id  year   city  value
0   1  2008    abc   10.0
1   1  2009    abc   20.0
2   1  2010    abc   30.0
3   2  2008  def10   10.0
4   2  2009  def10    NaN
5   2  2010  def10   20.0
6   3  2008    ghk    NaN
7   3  2009    ghk   30.0
8   3  2010    ghk    NaN

Optionally, you can re-arrange the column sequence, if you like:

df2.insert(2, 'year', df2.pop('year'))

print(df2)

   id   city  year  value
0   1    abc  2008   10.0
1   1    abc  2009   20.0
2   1    abc  2010   30.0
3   2  def10  2008   10.0
4   2  def10  2009    NaN
5   2  def10  2010   20.0
6   3    ghk  2008    NaN
7   3    ghk  2009   30.0
8   3    ghk  2010    NaN

Edit

You can also do it using stack() and unstack() without using reindex(), as follows:

(df.set_index(['id', 'city', 'year'], append=True)
   .unstack()
   .groupby(level=[1, 2]).max()
   .stack(dropna=False)
).reset_index()

Output:

   id   city  year  value
0   1    abc  2008   10.0
1   1    abc  2009   20.0
2   1    abc  2010   30.0
3   2  def10  2008   10.0
4   2  def10  2009    NaN
5   2  def10  2010   20.0
6   3    ghk  2008    NaN
7   3    ghk  2009   30.0
8   3    ghk  2010    NaN

Pivot the table and stack year without drop NaN values:

>>> df.pivot(["id", "city"], "year", "value") \
      .stack(dropna=False) \
      .rename("value") \
      .reset_index()

   id   city  year  value
0   1    abc  2008   10.0
1   1    abc  2009   20.0
2   1    abc  2010   30.0
3   2  def10  2008   10.0
4   2  def10  2009    NaN
5   2  def10  2010   20.0
6   3    ghk  2008    NaN
7   3    ghk  2009   30.0
8   3    ghk  2010    NaN

Edit: case of duplicate entries

I slightly modified your original dataframe:

df = pd.DataFrame({"id": [1,1,1,2,2,3,3], "city": ['abc','abc','abc','def10','def10','ghk','ghk'], "year": [2008,2009,2010,2008,2010,2009,2009], "value": [10,20,30,10,20,30,40]})

>>> df
   id   city  year  value
0   1    abc  2008     10
1   1    abc  2009     20
2   1    abc  2010     30
3   2  def10  2008     10
4   2  def10  2010     20
5   3    ghk  2009     30  # The problem is here
6   3    ghk  2009     40  # same (id, city, year)

You need to take a decision. Do you want to keep the row 5 or 6 or apply a math function (mean, sum, ...). Imagine you want the mean for (3, ghk, 2009):

>>> df.pivot_table(index=["id", "city"], columns="year", values="value", aggfunc="mean") \
      .stack(dropna=False) \
      .rename("value") \
      .reset_index()

   id   city  year  value
0   1    abc  2008   10.0
1   1    abc  2009   20.0
2   1    abc  2010   30.0
3   2  def10  2008   10.0
4   2  def10  2009    NaN
5   2  def10  2010   20.0
6   3    ghk  2008    NaN
7   3    ghk  2009   35.0  # <- mean of (30, 40)
8   3    ghk  2010    NaN