如何使用Wide_to_long或Pivot重塑数据框? [英] 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.

我正在尝试将df1转换为df2，其中df1和df2是熊猫数据帧

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.

推荐答案

您可以先通过在 _ 上拆分来切换列名称，然后使用

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()

输出

  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

这篇关于如何使用Wide_to_long或Pivot重塑数据框?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！