英文:
How can I convert a left table into a summary table?
问题
如何将左侧的表格转换为右侧的汇总表格？
我尝试使用 "get_dummies" 函数将值转换为 0 和 1。
之后我不知道该如何继续。
英文:
How do I convert table left to summary table right?
I tried using get dummies function to convert values to 0 and 1.
I don't know how to proceed after that.
答案1
得分: 1
请尝试以下代码：
import pandas as pd
import numpy as np
col1 = ['']+['Hampshire']*8+['']+['Hampshire']+['']+['Hampshire']+['','']+['Hampshire']*4
col2 = ['Southhampton'] + ['']*12 + ['Southhampton']*2 + ['']*4
col3 = ['']*11 + ['Isle of Wight'] + ['']*7
col4 = ['Met']*5 + [''] + ['Met']*13
col5 = ['']*5 + ['Partially met'] + ['']*13
col6 = ['']*19
df = pd.DataFrame(data = dict(zip(['Hampshire', 'Southhampton', 'Isle of Wight', '5met', '5partially met', '5Not met'],[col1,col2,col3,col4,col5,col6])))
df = df.replace('', np.nan)
df['Hampshire'] = df['Hampshire'].fillna(df['Southhampton'])
df['Hampshire'] = df['Hampshire'].fillna(df['Isle of Wight'])
df[['Hampshire','5met','5partially met', '5Not met']].groupby(by=['Hampshire']).count()
我为您生成了代码，因为您没有提供除了图像之外的数据，但我认为这可以完成任务。希望这有所帮助。
英文:
Try this:
import pandas as pd
import numpy as np
col1 = ['']+['Hampshire']*8+['']+['Hampshire']+['']+['Hampshire']+['','']+['Hampshire']*4
col2 = ['Southhampton'] + ['']*12 + ['Southhampton']*2 + ['']*4
col3 = ['']*11 + ['Isle of wight'] + ['']*7
col4 = ['Met']*5 + [''] + ['Met']*13
col5 = ['']*5 + ['Partially met'] + ['']*13
col6 = ['']*19
df = pd.DataFrame(data = dict(zip(['Hampshire', 'Southhampton', 'Isle of wight', '5met', '5partially met', '5Not met'],[col1,col2,col3,col4,col5,col6])))
df = df.replace('', np.nan)
df['Hampshire'] = df['Hampshire'].fillna(df['Southhampton'])
df['Hampshire'] = df['Hampshire'].fillna(df['Isle of wight'])
df[['Hampshire','5met','5partially met', '5Not met']].groupby(by=['Hampshire']).count()
I had to generate the data for you (since you didn't post any besides the image), but I think this get's the job done. I hope this helps.
答案2
得分: 0
使用 crosstab 在使用 stack 重新排列两个列块之后：
s1 = df[['Hampshire', 'Southhampton', 'Isle of wight']].stack().droplevel(-1)
s2 = df[['5met', '5partially met']].stack().droplevel(-1)
out = (pd.crosstab(s1, s2)
.reindex(columns=['Met', 'Partially met', 'Not met'], fill_value=0)
.rename_axis(columns=None, index=None)
)
输出：
Met Partially met Not met
Hampshire 13 1 0
Isle of wight 1 0 0
Southhampton 3 0 0
英文:
Using a crosstab after reshaping the two blocks of columns with stack:
s1 = df[['Hampshire', 'Southhampton', 'Isle of wight']].stack().droplevel(-1)
s2 = df[['5met', '5partially met']].stack().droplevel(-1)
out = (pd.crosstab(s1, s2)
.reindex(columns=['Met', 'Partially met', 'Not met'], fill_value=0)
.rename_axis(columns=None, index=None)
)
Output:
Met Partially met Not met
Hampshire 13 1 0
Isle of wight 1 0 0
Southhampton 3 0 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论