CODEWITHSUNDEEP
pythonpandas
user8465900
user8465900

Reputation: 33

Reshape dataframe based on column prefixes

I have a csv file that I read into a dataframe that looks like the following 

    Time  AP1  AQ1  AP2  AQ2  AP3  AQ3  AP4  AQ4  AP5  AQ5
0   2000  100   10  300   30  500   50  700   70  900   90
1   2001  105   15  305   35  505   55  705   75  905   95
2   2002  110   20  310   40  510   60  710   80  910  100
3   2003  115   25  315   45  515   65  715   85  915  105
4   2004  120   30  320   50  520   70  720   90  920  110
5   2005  125   35  325   55  525   75  725   95  925  115

How do I reshape the dataframe without using loops such that I only have the columns Time, AP and AQ?

    Time  AP    AQ
0   2000  100   10
0   2000  300   30
0   2000  500   50  
0   2000  700   70
0   2000  900   90
1   2001  105   15
....

Thank you for much in advance for any advice.

Upvotes: 2

Views: 50

Answers (1)

yatu
yatu

Reputation: 88276

You can use pd.wide_to_long setting AP and AQ as stubnames:

(pd.wide_to_long(df, stubnames=['AP','AQ'], i='Time', j='j')
   .droplevel(1).reset_index())

    Time   AP   AQ
0   2000  100   10
1   2001  105   15
2   2002  110   20
3   2003  115   25
4   2004  120   30
5   2005  125   35
6   2000  300   30
7   2001  305   35
8   2002  310   40
9   2003  315   45
10  2004  320   50
11  2005  325   55
12  2000  500   50
13  2001  505   55
14  2002  510   60
15  2003  515   65

Update

For a Time column with duplicates, you can do:

(pd.wide_to_long(df.reset_index(), stubnames=['AP','AQ'], i='index', j='j')
  .reset_index(drop=True))

    Time  AP   AQ
0   2000  100   10
1   2000  105   15
2   2002  110   20
3   2003  115   25
4   2004  120   30
5   2005  125   35
6   2000  300   30
7   2000  305   35
8   2002  310   40
9   2003  315   45
10  2004  320   50
11  2005  325   55
12  2000  500   50

Upvotes: 5

Related Questions