Python turn a hash into a dataframe

Question

I have a hash file looks like this and the data is separated line by line:

Amy:0001:[{'name': 'Amy', 'age': '14', 'grade': '7', 'award': '0'}]
Carl:0024:[{'name': 'Carl', 'age': '12', 'grade': '6', 'award': '2'}, {'name': 'Carl', 'age': '18', 'grade': '12', 'award': '4'}, {'name': 'Carl', 'age': '13', 'grade': '6', 'award': '7'}]

and more ...

I want to have a dataframe that look like this:

           name     age      grade     award
Amy:0001    Amy     14        7         0
Carl:0024   Carl    12        6         2
Carl:0024   Carl    18        12        4
Carl:0024   Carl    13        6         7

I tried to strip the hash line by line

lines = [line.rstrip('\n') for line in open("my_file.txt")]

it's-yer-boy-chet · Accepted Answer · 2019-01-04 18:55:02Z

2

Start with an empty DataFrame:

df = pd.DataFrame(columns=['key','name','age','grade','award'])

Line by line read the hash file into the dataframe:

import json
with open(hash_path, 'r') as f:
    for line in f:
        key = ":".join(line.split(":", 2)[:2])
        rows = line.split(":", 2)[-1]
        # json requires double quotes for strings
        rows = json.loads(rows.replace("'",'"'))
        for row in rows:
            row['key'] = key
            df = df.append(pd.Series(row), ignore_index=True)
# set the 'key' column to the index
df.set_index('key', inplace=True)

edited Jan 4, 2019 at 18:55

answered Jan 4, 2019 at 18:49

it's-yer-boy-chet

2,0462 gold badges14 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jpp · Accepted Answer · 2019-01-04 21:25:20Z

Here's a solution using ast.literal_eval which doesn't require explicit line-by-line iteration. You should find it considerably more efficient.

from io import StringIO
from ast import literal_eval

x = """Amy:0001:[{'name': 'Amy', 'age': '14', 'grade': '7', 'award': '0'}]
Carl:0024:[{'name': 'Carl', 'age': '12', 'grade': '6', 'award': '2'}, {'name': 'Carl', 'age': '18', 'grade': '12', 'award': '4'}, {'name': 'Carl', 'age': '13', 'grade': '6', 'award': '7'}]"""

df = pd.read_csv(StringIO(x), delimiter='[', header=None, names=['id', 'data'])

df['id'] = df['id'].str[:-1]
df['data'] = df['data'].map(lambda x: literal_eval(f'[{x}'))

lens = df['data'].str.len()

df = pd.DataFrame({'id': np.repeat(df['id'].values, lens)})\
       .join(pd.DataFrame(list(chain.from_iterable(df['data']))))\
       .set_index('id')

print(df)

          age award grade  name
id                             
Amy:0001   14     0     7   Amy
Carl:0024  12     2     6  Carl
Carl:0024  18     4    12  Carl
Carl:0024  13     7     6  Carl

Collectives™ on Stack Overflow

Python turn a hash into a dataframe

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related