在pandas中重新格式化数据帧

Question

我有一个格式很奇怪的数据帧：

id      Code   Week1   Week2  week3
sunday   nan    nan    nan    nan
id      Code   Week1   Week2  week3
 1      100     y       y      n
 2      200     n       y      n
 3      300     n       n      y
Monday   nan    nan    nan    nan
id      Code   Week1   Week2  week3
 1      500     n       y      y
 2      600     y       y      y
Tuesday nan     nan    nan     nan 
id      Code   Week1   Week2  week3
 1      800     n       y      y
 2      900     y       n      y     

我想用这种格式：

^{pr2}$

也就是说，如果某个代码在一周内的值为y，则该代码将在该周内访问。在

有没有办法在熊猫身上做到这一点？在

Answer 1

基于@piRsquared's的答案，对于那些想要伪单音的人

In [2689]: (df.query('id != "id"').replace(dict(id={'\d+': np.nan}), regex=True)
              .assign(id=lambda x: x.ffill()).dropna()
            .set_index(['id', 'Code'])
              .replace({'y': 1, 'n': np.nan})
              .rename(columns=lambda x: x.lower().replace('week', ''))
              .stack()
              .reset_index()
              .rename(columns={'id': 'Day', 'level_2': 'Week'})
              .drop(0, 1))
Out[2689]:
        Day Code Week
0    sunday  100    1
1    sunday  100    2
2    sunday  200    2
3    sunday  300    3
4    Monday  500    2
5    Monday  500    3
6    Monday  600    1
7    Monday  600    2
8    Monday  600    3
9   Tuesday  800    2
10  Tuesday  800    3
11  Tuesday  900    1
12  Tuesday  900    3

Answer 2

不是我最好的作品。。。但我不想再尝试了。。。它伤害了我的灵魂。在

d = df.query('id != "id"').replace(dict(id={'\d+': None}), regex=True).ffill()
s = d[d.duplicated('id')].set_index(['id', 'Code']).replace({'y': 1, 'n': np.nan}).stack()
s.rename_axis(['Day', 'Code', 'Week']).reset_index('Week').Week.str.replace(
    'week', '', flags=re.IGNORECASE
).reset_index()

        Day Code Week
0    sunday  100    1
1    sunday  100    2
2    sunday  200    2
3    sunday  300    3
4    Monday  500    2
5    Monday  500    3
6    Monday  600    1
7    Monday  600    2
8    Monday  600    3
9   Tuesday  800    2
10  Tuesday  800    3
11  Tuesday  900    1
12  Tuesday  900    3

Answer 3

您可以使用：

df.index = df['id'].where(df['Code'].isnull()).ffill()
df = df[(df['Code'] != 'Code') & (df['id'] != df.index)]
df = df.rename_axis('Day').rename_axis('Week', 1)
df = df.set_index(['id','Code'], append=True)
       .replace({'n':np.nan})
       .stack().reset_index(name='val')
df['Week'] = df['Week'].str.extract('(\d+)', expand=False).astype(int)

cols = ['Code','Day','Week']
df = df.drop(['val','id'], axis=1)[cols].sort_values(['Week','Code']).reset_index(drop=True)
print (df)
    Code      Day  Week
0    100   sunday     1
1    600   Monday     1
2    900  Tuesday     1
3    100   sunday     2
4    200   sunday     2
5    500   Monday     2
6    600   Monday     2
7    800  Tuesday     2
8    300   sunday     3
9    500   Monday     3
10   600   Monday     3
11   800  Tuesday     3
12   900  Tuesday     3

对于具有所有y和n值的常规输出-id列，请删除replace：

^{pr2}$