CODEWITHSUNDEEP
pandasformatpivot
Arshad Nawaz
Arshad Nawaz

Reputation: 45

How to reshape dataframe with wide_to_long or pivot?

This should be fairly simple but have not been able to wrap my brain around it.

I am trying to convert df1 to df2, where df1 and df2 are pandas dataframes

df1 = pd.DataFrame({'site': ['1', '2'],
                    'sat_open': ['0900', '0900'],
                    'sat_close': ['1900','1900'],
                    'sun_open': ['1000', '1000'],
                    'sun_close': ['1800', '1800'],
                    'mon_open': ['0900', '0900'],
                    'mon_close': ['2100', '2100']               
                  })

df2 = pd.DataFrame({'store': ['1', '1', '1', '2', '2','2'],
                    'day': ['sat', 'sun', 'mon','sat', 'sun', 'mon'],
                    'open': ['09:00','10:00','09:00','09:00','10:00','09:00'],
                    'close': ['19:00','18:00','21:00','19:00','18:00','21:00']})

I tried out regex and pivot but unable to figure out the best way to do it. Any help here is highly appreciated.

Upvotes: 3

Views: 53

Answers (2)

Erfan
Erfan

Reputation: 42886

You can first switch the columns names by splitting on _, then use pd.wide_to_long:

df1.columns = [f'{col.split("_")[1]}_{col.split("_")[0]}' if '_' in col else col 
               for col in df1.columns]

df2 = pd.wide_to_long(df1, stubnames=['open', 'close'], i='site', j='day', sep='_', suffix='\D+')\
        .sort_index(level=0)\
        .reset_index()

Output

  site  day  open close
0    1  mon  0900  2100
1    1  sat  0900  1900
2    1  sun  1000  1800
3    2  mon  0900  2100
4    2  sat  0900  1900
5    2  sun  1000  1800

Upvotes: 3

Quang Hoang
Quang Hoang

Reputation: 150735

You can try:

(pd.wide_to_long(df1, 
                 stubnames=['sat', 'sun','mon'], 
                 i='site', 
                 j='day', 
                 sep='_', suffix='(open|close)'
                )
   .stack()
   .unstack('day')
)

Output:

day      close  open
site                
1    sat  1900  0900
     sun  1800  1000
     mon  2100  0900
2    sat  1900  0900
     sun  1800  1000
     mon  2100  0900

Upvotes: 1

Related Questions