如何将pandas DataFrame转换为适配Plotly Express绘制条形图的格式

转换思路 #

• 原始数据为宽表结构，每行代表1位受访者，Q3_3开头的列对应不同选项，值为-99代表受访者未选择该选项，文本值代表选中
• 先把宽表转为长表，保留受访者类型（Type）字段
• 过滤掉未选中的无效记录后，按问题和受访者类型分组统计人数，再调整为绘图需要的宽表格式

完整实现代码 #

import pandas as pd
import plotly.express as px

# 原始DataFrame定义
df = pd.DataFrame({'Q3_3_1': {'R_2cedWe4sx09CKlb': -99.0,
  'R_3smCukGdFbm4i2t': -99.0,
  'R_3Oj484bqZHepbmT': -99.0,
  'R_2Wxyhyo1ZtxL0f6': -99.0,
  'R_eh84KSBtWy9OWZ3': -99.0,
  'R_1pndKdTJ0GC0crY': -99.0,
  'R_3MF4nebUAJ130N1': -99.0,
  'R_1rrd0yEcpoziBXX': 'I have not attended a course on entrepreneurship so far.',
  'R_3J3ZATf90VmSonA': 'I have not attended a course on entrepreneurship so far.',
  'R_aaP0vu2FJGdIrNT': -99.0},
 'Q3_3_2': {'R_2cedWe4sx09CKlb': -99.0,
  'R_3smCukGdFbm4i2t': -99.0,
  'R_3Oj484bqZHepbmT': 'I have attended at least one entrepreneurship course as elective.',
  'R_2Wxyhyo1ZtxL0f6': -99.0,
  'R_eh84KSBtWy9OWZ3': -99.0,
  'R_1pndKdTJ0GC0crY': -99.0,
  'R_3MF4nebUAJ130N1': -99.0,
  'R_1rrd0yEcpoziBXX': -99.0,
  'R_3J3ZATf90VmSonA': -99.0,
  'R_aaP0vu2FJGdIrNT': 'I have attended at least one entrepreneurship course as elective.'},
 'Q3_3_3': {'R_2cedWe4sx09CKlb': 'I have attended at least one entrepreneurship course as compulsory part of my studies.',
  'R_3smCukGdFbm4i2t': 'I have attended at least one entrepreneurship course as compulsory part of my studies.',
  'R_3Oj484bqZHepbmT': 'I have attended at least one entrepreneurship course as compulsory part of my studies.',
  'R_2Wxyhyo1ZtxL0f6': 'I have attended at least one entrepreneurship course as compulsory part of my studies.',
  'R_eh84KSBtWy9OWZ3': 'I have attended at least one entrepreneurship course as compulsory part of my studies.',
  'R_1pndKdTJ0GC0crY': -99.0,
  'R_3MF4nebUAJ130N1': 'I have attended at least one entrepreneurship course as compulsory part of my studies.',
  'R_1rrd0yEcpoziBXX': -99.0,
  'R_3J3ZATf90VmSonA': -99.0,
  'R_aaP0vu2FJGdIrNT': -99.0},
 'Q3_3_4': {'R_2cedWe4sx09CKlb': -99.0,
  'R_3smCukGdFbm4i2t': -99.0,
  'R_3Oj484bqZHepbmT': -99.0,
  'R_2Wxyhyo1ZtxL0f6': -99.0,
  'R_eh84KSBtWy9OWZ3': -99.0,
  'R_1pndKdTJ0GC0crY': 'I am studying in a specific program on entrepreneurship.',
  'R_3MF4nebUAJ130N1': -99.0,
  'R_1rrd0yEcpoziBXX': -99.0,
  'R_3J3ZATf90VmSonA': -99.0,
  'R_aaP0vu2FJGdIrNT': -99.0},
 'Q3_3_5': {'R_2cedWe4sx09CKlb': -99.0,
  'R_3smCukGdFbm4i2t': -99.0,
  'R_3Oj484bqZHepbmT': -99.0,
  'R_2Wxyhyo1ZtxL0f6': -99.0,
  'R_eh84KSBtWy9OWZ3': -99.0,
  'R_1pndKdTJ0GC0crY': -99.0,
  'R_3MF4nebUAJ130N1': -99.0,
  'R_1rrd0yEcpoziBXX': -99.0,
  'R_3J3ZATf90VmSonA': -99.0,
  'R_aaP0vu2FJGdIrNT': -99.0},
 'Type': {'R_2cedWe4sx09CKlb': 'student',
  'R_3smCukGdFbm4i2t': 'nascent',
  'R_3Oj484bqZHepbmT': 'nascent',
  'R_2Wxyhyo1ZtxL0f6': 'student',
  'R_eh84KSBtWy9OWZ3': 'student',
  'R_1pndKdTJ0GC0crY': 'student',
  'R_3MF4nebUAJ130N1': 'student',
  'R_1rrd0yEcpoziBXX': 'nascent',
  'R_3J3ZATf90VmSonA': 'student',
  'R_aaP0vu2FJGdIrNT': 'active'}})

# 数据转换步骤
# 1. 宽表转长表，保留受访者类型字段
melted_df = df.melt(id_vars='Type', 
                    value_vars=['Q3_3_1', 'Q3_3_2', 'Q3_3_3', 'Q3_3_4', 'Q3_3_5'],
                    var_name='Question', 
                    value_name='Answer')
# 2. 过滤未选中该选项的无效记录（值为-99）
filtered_df = melted_df[melted_df['Answer'] != -99]
# 3. 分组统计人数并调整为目标格式
df1 = filtered_df.groupby(['Question', 'Type']).size().unstack(fill_value=0).reset_index()
# 按需求调整列顺序
df1 = df1[['Question', 'student', 'nascent', 'active']]

# 绘图
fig = px.bar(df1, x='Question', y=['student', 'nascent','active'], barmode='group', title='Final Term')
fig.show()

内容的提问来源于stack exchange，提问作者fredooms