I need to read a huge pipe separated file from s3 with below content:
Q|A|1|X
78|WQ|
123|ABC
Q|V|5|Y
LK|HJ|
BG|78
I want to read file in such a way that my data looks like this:
1|Q|A|1|X|
1|78|WQ|
1|123|ABC|
2|Q|V|5|Y|
2|LK|HJ|
2|BG|78|
(Notice first column added. Each section that starts with 'Q' should have a separate ID)
So, far I am using pandas :
previous_ID =0
for chunk in pd.read_csv(io.BytesIO(s3.get_object(Bucket=bucket, Key=f)['Body'].read()), sep=';', header=None, compression='gzip', chunksize=1000):
chunk = chunk.reset_index(drop=True)
chunk[['CLM1', 'data']] = chunk[0].str.split("|", n=1, expand=True)
chunk = chunk.drop([0], axis=1)
h_count = chunk[chunk['CLM1'] == 'Q'].shape[0]
chunk.loc[chunk['CLM1'] == 'Q', 'ID'] = range(previous_ID + 1,previous_ID + h_count + 1)
if (pd.isnull(chunk.loc[0, 'ID'])) and (previous_ID != 0):
chunk.at[0, 'ID'] = previous_ID
chunk['ID'] = chunk['ID'].ffill()
chunk['ID'].fillna(0, inplace=True)
chunk['ID'] = chunk['ID'].astype(int)
previous_ID = chunk.iloc[-1]['ID']
This works fine but I would like to understand from the community if there is any better and faster way. I don't want to read whole file in memory and I am open to use solution that use something other than pandas