How to format data for panel data analysis in python?

Question

I need to conduct time-series analysis on panel data. the data is currently formatted like the table below:


+------+---------+---------+---------+---------+---------+---------+---------+---------+
|      |   Q1    |   Q2    |   Q3    |   Q4    |   Q1    |   Q2    |   Q3    |   Q4    |
+------+---------+---------+---------+---------+---------+---------+---------+---------+
| ID 1 | Value 1 | Value 1 | Value 1 | Value 1 | Value 2 | Value 2 | Value 2 | Value 2 |
| ID 2 | Value 1 | Value 1 | Value 1 | Value 1 | Value 2 | Value 2 | Value 2 | Value 2 |
| ID 3 | Value 1 | Value 1 | Value 1 | Value 1 | Value 2 | Value 2 | Value 2 | Value 2 |
| ID 4 | Value 1 | Value 1 | Value 1 | Value 1 | Value 2 | Value 2 | Value 2 | Value 2 |
+------+---------+---------+---------+---------+---------+---------+---------+---------+

I need the quarterly data on values 1 and 2 to stack like this:

+------+---------+---------+----+
|  ID  |    X    |    Y    | T  |
+------+---------+---------+----+
| ID 1 | Value 1 | Value 2 | Q1 |
| ID 1 | Value 1 | Value 2 | Q2 |
| ID 1 | Value 1 | Value 2 | Q3 |
| ID 1 | Value 1 | Value 2 | Q4 |
| ID 2 | Value 1 | Value 2 | Q1 |
| ID 2 | Value 1 | Value 2 | Q2 |
| ID 2 | Value 1 | Value 2 | Q3 |
| ID 2 | Value 1 | Value 2 | Q4 |
| ID 3 | Value 1 | Value 2 | Q1 |
| ID 3 | Value 1 | Value 2 | Q2 |
| ID 3 | Value 1 | Value 2 | Q3 |
| ID 3 | Value 1 | Value 2 | Q4 |
| ID 4 | Value 1 | Value 2 | Q1 |
| ID 4 | Value 1 | Value 2 | Q2 |
| ID 4 | Value 1 | Value 2 | Q3 |
| ID 4 | Value 1 | Value 2 | Q4 |
+------+---------+---------+----+

The data set is very large, several thousands data points in total.

I'm a beginner, so i was lost.

Does the data exist like that in a `pandas.DataFrame`, with duplicated column names? Or is that some other table you need to first get into a DataFrame? — ALollz, Jul 15 '19 at 14:40

score 3 · Answer 1 · answered Jul 15 '19 at 14:54

New Columns Object, `stack`, `reset_index`

df.columns = [
    df.columns.to_series().groupby(level=0).cumcount().map({0: 'X', 1: 'Y'}),
    df.columns
]

df.stack().rename_axis(['ID', 'T']).reset_index()

      ID   T        X        Y
0   ID 1  Q1  Value 1  Value 2
1   ID 1  Q2  Value 1  Value 2
2   ID 1  Q3  Value 1  Value 2
3   ID 1  Q4  Value 1  Value 2
4   ID 2  Q1  Value 1  Value 2
5   ID 2  Q2  Value 1  Value 2
6   ID 2  Q3  Value 1  Value 2
7   ID 2  Q4  Value 1  Value 2
8   ID 3  Q1  Value 1  Value 2
9   ID 3  Q2  Value 1  Value 2
10  ID 3  Q3  Value 1  Value 2
11  ID 3  Q4  Value 1  Value 2
12  ID 4  Q1  Value 1  Value 2
13  ID 4  Q2  Value 1  Value 2
14  ID 4  Q3  Value 1  Value 2
15  ID 4  Q4  Value 1  Value 2

score 2 · Answer 2 · answered Jul 15 '19 at 14:52

Rename the columns, then pd.wide_to_long:

Sample Data

np.random.seed(123)
df = pd.DataFrame(np.random.randint(1,10,(4,8)), 
                  index=['ID 1', 'ID 2', 'ID 3', 'ID 4'])
df.columns = ['Q1', 'Q2', 'Q3', 'Q4']*2

      Q1  Q2  Q3  Q4  Q1  Q2  Q3  Q4
ID 1   3   3   7   2   4   7   2   1
ID 2   2   1   1   4   5   1   1   5
ID 3   2   8   4   3   5   8   3   5
ID 4   9   1   8   4   5   7   2   6

Code:

s = pd.Series(df.columns)
df.columns = [f'{y}_{x}' for x,y in zip(s, s.groupby(s).cumcount())]
#Index(['0_Q1', '0_Q2', '0_Q3', '0_Q4', '1_Q1', '1_Q2', '1_Q3', '1_Q4'])    

pd.wide_to_long(df.reset_index(), i='index', j='Quarter',
                stubnames=map(str, range(0, s.groupby(s).cumcount().max()+1)), 
                sep='_', suffix='.*').sort_index()

               0  1
index Quarter      
ID 1  Q1       3  4
      Q2       3  7
      Q3       7  2
      Q4       2  1
ID 2  Q1       2  5
      Q2       1  1
      Q3       1  1
      Q4       4  5
ID 3  Q1       2  5
      Q2       8  8
      Q3       4  3
      Q4       3  5
ID 4  Q1       9  5
      Q2       1  7
      Q3       8  2
      Q4       4  6

How to format data for panel data analysis in python?

2 Answers2

New Columns Object, stack, reset_index

Sample Data

Code:

New Columns Object, `stack`, `reset_index`