A Shortcut for Creating Individual Names to Refer to Pandas Columns

Read csv file from url into Pandas Dataframe

In [19]:
from enum import Enum, unique
from string import punctuation, whitespace
import itertools as it
from pprint import pprint
In [2]:
import pandas as pd
from IPython.display import display
In [3]:
df = pd.read_csv('https://static.apps.selfip.com/codio.csv')

Create Enum items

Create a translation map to replace all whitespace and punctuation with an underscore to create a valid variable name.

In [4]:
translation = str.maketrans(dict(zip(punctuation + whitespace, it.cycle('_'))))

Look for an easier and more precise way to define column names as variables than typing them by hand.

In [5]:
df.columns
Out[5]:
Index(['student name', 'first name', 'last name', 'username', 'hashed id',
       'email', 'unit name', 'right answers', 'right count', 'wrong answers',
       'wrong count', 'answered assessments', 'points/max_points', 'points',
       'total points', 'time spent', 'grade', 'comment', 'created',
       'completed', 'completed date', 'answers', 'attempts'],
      dtype='object')

Update from the hacky way I had done this in an earlier version of this post.

The Enum class has a functional API.

In [21]:
names = ((column.translate(translation).upper(), column,) for column in df.columns)
ColumnNames = Enum('ColumnNames', names=names, type=str)
pprint(ColumnNames.__members__, indent=4)
mappingproxy(OrderedDict([   (   'STUDENT_NAME',
                                 <ColumnNames.STUDENT_NAME: 'student name'>),
                             (   'FIRST_NAME',
                                 <ColumnNames.FIRST_NAME: 'first name'>),
                             (   'LAST_NAME',
                                 <ColumnNames.LAST_NAME: 'last name'>),
                             ('USERNAME', <ColumnNames.USERNAME: 'username'>),
                             (   'HASHED_ID',
                                 <ColumnNames.HASHED_ID: 'hashed id'>),
                             ('EMAIL', <ColumnNames.EMAIL: 'email'>),
                             (   'UNIT_NAME',
                                 <ColumnNames.UNIT_NAME: 'unit name'>),
                             (   'RIGHT_ANSWERS',
                                 <ColumnNames.RIGHT_ANSWERS: 'right answers'>),
                             (   'RIGHT_COUNT',
                                 <ColumnNames.RIGHT_COUNT: 'right count'>),
                             (   'WRONG_ANSWERS',
                                 <ColumnNames.WRONG_ANSWERS: 'wrong answers'>),
                             (   'WRONG_COUNT',
                                 <ColumnNames.WRONG_COUNT: 'wrong count'>),
                             (   'ANSWERED_ASSESSMENTS',
                                 <ColumnNames.ANSWERED_ASSESSMENTS: 'answered assessments'>),
                             (   'POINTS_MAX_POINTS',
                                 <ColumnNames.POINTS_MAX_POINTS: 'points/max_points'>),
                             ('POINTS', <ColumnNames.POINTS: 'points'>),
                             (   'TOTAL_POINTS',
                                 <ColumnNames.TOTAL_POINTS: 'total points'>),
                             (   'TIME_SPENT',
                                 <ColumnNames.TIME_SPENT: 'time spent'>),
                             ('GRADE', <ColumnNames.GRADE: 'grade'>),
                             ('COMMENT', <ColumnNames.COMMENT: 'comment'>),
                             ('CREATED', <ColumnNames.CREATED: 'created'>),
                             (   'COMPLETED',
                                 <ColumnNames.COMPLETED: 'completed'>),
                             (   'COMPLETED_DATE',
                                 <ColumnNames.COMPLETED_DATE: 'completed date'>),
                             ('ANSWERS', <ColumnNames.ANSWERS: 'answers'>),
                             ('ATTEMPTS', <ColumnNames.ATTEMPTS: 'attempts'>)]))

Add members of ColumnNames to global

In [15]:
globals().update(ColumnNames.__members__)

print(repr(GRADE))
<ColumnNames.GRADE: 'grade'>

Results!

Now there are variables available to use as keys on the dataframe. Typing them by hand would have been tedious and error prone.

A dict could work here as well. I like the way that a member of the Enum is printed though. The labeling is nice.

Display the desired subset of columns.

In [16]:
df[[FIRST_NAME, GRADE, COMMENT]]
Out[16]:
first name grade comment
0 Andy NaN NaN
1 Student A 100.0 NaN
2 Student B NaN NaN
3 Andy 100.0 NaN
4 Student A 87.0 NaN
5 Andy NaN NaN

Iterate over column names.

In [17]:
for column in ColumnNames:
    display(df[column])
0        Andy Weiss
1    Student A Test
2    Student B Test
3        Andy Weiss
4    Student A Test
5        Andy Weiss
Name: student name, dtype: object
0         Andy
1    Student A
2    Student B
3         Andy
4    Student A
5         Andy
Name: first name, dtype: object
0    Weiss
1     Test
2     Test
3    Weiss
4     Test
5    Weiss
Name: last name, dtype: object
0                   aweiss6
1    stest-wyncode-academy1
2    stest-wyncode-academy2
3                   aweiss6
4    stest-wyncode-academy1
5                   aweiss6
Name: username, dtype: object
0    5b15908826b16c2fbbb6c25efcccefa17807c6b0
1    613a2940b95692a0edb208998ee82f4e5946e304
2    74dd20928d48e82a070737d2d4e3c7f40c709bc2
3    5b15908826b16c2fbbb6c25efcccefa17807c6b0
4    613a2940b95692a0edb208998ee82f4e5946e304
5    5b15908826b16c2fbbb6c25efcccefa17807c6b0
Name: hashed id, dtype: object
0               andyweiss1982@gmail.com
1    student+wyncode-academy1@codio.com
2    student+wyncode-academy2@codio.com
3               andyweiss1982@gmail.com
4    student+wyncode-academy1@codio.com
5               andyweiss1982@gmail.com
Name: email, dtype: object
0                       Fundamentals Of HTML
1    Setting up your development environment
2    Setting up your development environment
3    Setting up your development environment
4                Fundamentals Of Programming
5                Fundamentals Of Programming
Name: unit name, dtype: object
0                                        "The Warm Up"
1    "Use the correct OS, Install the Chrome Browse...
2                                 "Use the correct OS"
3    "Use the correct OS, Install the Chrome Browse...
4    "Intro to command line, More Command Line Prac...
5    "Intro to command line, More Command Line Prac...
Name: right answers, dtype: object
0     1
1    17
2     1
3    17
4     7
5     7
Name: right count, dtype: int64
0                      "Intro to HTML"
1                                   ""
2                                   ""
3                                   ""
4    "Learn Command Line The Hard Way"
5                                   ""
Name: wrong answers, dtype: object
0    1
1    0
2    0
3    0
4    1
5    0
Name: wrong count, dtype: int64
0     2
1    17
2     1
3    17
4     8
5     7
Name: answered assessments, dtype: int64
0      1/3
1    17/17
2     1/17
3    17/17
4      7/8
5      7/8
Name: points/max_points, dtype: object
0     1
1    17
2     1
3    17
4     7
5     7
Name: points, dtype: int64
0     3
1    17
2    17
3    17
4     8
5     8
Name: total points, dtype: int64
0    295
1    232
2     44
3    450
4    139
5    368
Name: time spent, dtype: int64
0      NaN
1    100.0
2      NaN
3    100.0
4     87.0
5      NaN
Name: grade, dtype: float64
0   NaN
1   NaN
2   NaN
3   NaN
4   NaN
5   NaN
Name: comment, dtype: float64
0     Sep 24th 2019 3:17pm
1    Sep 16th 2019 12:28pm
2     Sep 18th 2019 9:07pm
3     Sep 24th 2019 1:13am
4     Sep 17th 2019 3:55pm
5     Sep 24th 2019 1:22am
Name: created, dtype: object
0    False
1     True
2    False
3     True
4     True
5    False
Name: completed, dtype: bool
0                                        NaN
1    Wed Sep 18 2019 13:07:54 GMT+0000 (UTC)
2                                        NaN
3    Tue Sep 24 2019 01:21:48 GMT+0000 (UTC)
4    Wed Sep 18 2019 13:11:32 GMT+0000 (UTC)
5                                        NaN
Name: completed date, dtype: object
0    "The Warm Up:\nstdout='complete'\nstderr=''\nI...
1    "Use the correct OS:\nstdout=''complete''\nstd...
2    "Use the correct OS:\nstdout=''complete''\nstd...
3    "Use the correct OS:\nstdout=''complete''\nstd...
4    "Intro to command line:\nstdout=''complete''\n...
5    "Intro to command line:\nstdout=''complete''\n...
Name: answers, dtype: object
0     "resume:\n0\nThe Warm Up:\n1\nIntro to HTML:\n1"
1    "Use the correct OS:\n1\nInstall the Chrome Br...
2    "Use the correct OS:\n1\nInstall the Chrome Br...
3    "Use the correct OS:\n1\nInstall the Chrome Br...
4    "Intro to command line:\n1\nLearn Command Line...
5    "Intro to command line:\n1\nLearn Command Line...
Name: attempts, dtype: object