I have a CSV file which basically looks like the following (I shortened it to a minimal example showing the structure):
ID1#First_Name
TIME_BIN,COUNT,AVG
09:00-12:00,100,50
15:00-18:00,24,14
21:00-23:00,69,47
ID2#Second_Name
TIME_BIN,COUNT,AVG
09:00-12:00,36,5
15:00-18:00,74,68
21:00-23:00,22,76
ID3#Third_Name
TIME_BIN,COUNT,AVG
09:00-12:00,15,10
15:00-18:00,77,36
21:00-23:00,55,18
As one can see, the data is separated into multiple blocks. Each block has a headline (e.g. ID1#First_Name) which contains two peaces of information (IDx and x_Name), separated by #.
Each headline is followed by the column headers (TIME_BIN, COUNT, AVG) which stay the same for all blocks.
Then follow some lines of data which belong to the column headers (e.g. TIME_BIN=09:00-12:00, COUNT=100, AVG=50).
I would like to parse this file into a Pandas dataframe which would look like the following:
ID Name TIME_BIN COUNT AVG
ID1 First_Name 09:00-12:00 100 50
ID1 First_Name 15:00-18:00 24 14
ID1 First_Name 21:00-23:00 69 47
ID2 Second_Name 09:00-12:00 36 5
ID2 Second_Name 15:00-18:00 74 68
ID2 Second_Name 21:00-23:00 22 76
ID3 Third_Name 09:00-12:00 15 10
ID3 Third_Name 15:00-18:00 77 36
ID3 Third_Name 21:00-23:00 55 18
This means that the headline may not be skipped but has to be split by the # and then linked to the data from the block it belongs to. Besides, the column headers are only needed once since they do not change later on.
Somehow I managed to achieve my goal with the following code. However, the approach looks kind of overcomplicated and not robust to me and I am sure that there are better ways to do this. Any suggestions are welcome!
import pandas as pd
from io import StringIO (<- Python 3, for Python 2 use from StringIO import StringIO)
pathToFile = 'mydata.txt'
# read the textfile into a StringIO object and skip the repeating column header rows
s = StringIO()
with open(pathToFile) as file:
for line in file:
if not line.startswith('TIME_BIN'):
s.write(line)
# reset buffer to the beginning of the StringIO object
s.seek(0)
# create new dataframe with desired column names
df = pd.read_csv(s, names=['TIME_BIN', 'COUNT', 'AVG'])
# split the headline string which is currently found in the TIME_BIN column and insert both parts as new dataframe columns.
# the headline is identified by its start which is 'ID'
df['ID'] = df[df.TIME_BIN.str.startswith('ID')].TIME_BIN.str.split('#').str.get(0)
df['Name'] = df[df.TIME_BIN.str.startswith('ID')].TIME_BIN.str.split('#').str.get(1)
# fill the NaN values in the ID and Name columns by propagating the last valid observation
df['ID'] = df['ID'].fillna(method='ffill')
df['Name'] = df['Name'].fillna(method='ffill')
# remove all rows where TIME_BIN starts with 'ID'
df['TIME_BIN'] = df['TIME_BIN'].drop(df[df.TIME_BIN.str.startswith('ID')].index)
df = df.dropna(subset=['TIME_BIN'])
# reorder columns to bring ID and Name to the front
cols = list(df)
cols.insert(0, cols.pop(cols.index('Name')))
cols.insert(0, cols.pop(cols.index('ID')))
df = df.ix[:, cols]
import pandas as pd
from StringIO import StringIO
import sys
pathToFile = 'mydata.txt'
f = open(pathToFile)
s = StringIO()
cur_ID = None
for ln in f:
if not ln.strip():
continue
if ln.startswith('ID'):
cur_ID = ln.replace('\n',',',1).replace('#',',',1)
continue
if ln.startswith('TIME'):
continue
if cur_ID is None:
print 'NO ID found'
sys.exit(1)
s.write(cur_ID + ln)
s.seek(0)
# create new dataframe with desired column names
df = pd.read_csv(s, names=['ID','Name','TIME_BIN', 'COUNT', 'AVG'])