How to extend panel data to balanced panel data on three dimensions in python?

leilei 0
Viewed 20

I want to extend panel data to balanced panel data on three dimensions. Below is an example:

This is the original dataframe:

city Type Year
New York A 1993
New York B 1994
New York B 1995
Los Angeles A 1992
Los Angeles B 1997
Chicago C 1996

This is what I want:Every city should have all the Type and Year presented in the dataframe. Below is what I want:

city Type Year
New York A 1992
New York A 1993
New York A 1994
New York A 1995
New York A 1996
New York A 1997
New York B 1992
New York B 1993
New York B 1994
New York B 1995
New York B 1996
New York B 1997
New York C 1992
New York C 1993
New York C 1994
New York C 1995
New York C 1996
New York C 1997
Los Angeles A 1992
Los Angeles A 1993
Los Angeles A 1994
Los Angeles A 1995
Los Angeles A 1996
Los Angeles A 1997
Los Angeles B 1992
Los Angeles B 1993
Los Angeles B 1994
Los Angeles B 1995
Los Angeles B 1996
Los Angeles B 1997
Los Angeles C 1992
Los Angeles C 1993
Los Angeles C 1994
Los Angeles C 1995
Los Angeles C 1996
Los Angeles C 1997
Chicago A 1992
Chicago A 1993
Chicago A 1994
Chicago A 1995
Chicago A 1996
Chicago A 1997
Chicago B 1992
Chicago B 1993
Chicago B 1994
Chicago B 1995
Chicago B 1996
Chicago B 1997
Chicago C 1992
Chicago C 1993
Chicago C 1994
Chicago C 1995
Chicago C 1996
Chicago C 1997

I wonder how to solve this problem in Python? Thanks a lot for your kind help!

panel dataframe python
1 Answers

You can use itertools.product for this. Set the input iterables to unique (sorted) list-likes of your columns.

from itertools import product
df_new = pd.DataFrame(list(product(df.city.unique(), 
                                   sorted(df.Type.unique()), 
                                   sorted(df.Year.unique())
                                   )), 
                      columns=df.columns)

print(df_new.loc[df_new.city == 'New York'])

        city Type  Year
0   New York    A  1992
1   New York    A  1993
2   New York    A  1994
3   New York    A  1995
4   New York    A  1996
5   New York    A  1997
6   New York    B  1992
7   New York    B  1993
8   New York    B  1994
9   New York    B  1995
10  New York    B  1996
11  New York    B  1997
12  New York    C  1992
13  New York    C  1993
14  New York    C  1994
15  New York    C  1995
16  New York    C  1996
17  New York    C  1997
ouroboros10
Related