I have a number of fairly large .json (4-6 GB) files with Reddit data that I am processing using pandas. Since I only need certain cases from the original .json files, I have been reading them into pandas, and then writing the relevant cases to a .csv file. However, I am running into an issue with maintaining consistent column orders.
Given the size of the files, I'm using the chunksize option to read them into pandas. Here is the gist of the code I'm using:
with pd.read_json(filename+".json", lines=True, chunksize=10000) as reader:
reader
for chunk in reader:
df = pd.DataFrame(chunk)
df.info()
df = df[(df.subreddit == 'subreddit1') | (df.subreddit == 'subreddit2')
if os.path.exists(filename+".csv"):
df.to_csv(filename+".csv", mode='a', header=False, index=False)
else:
df.to_csv(filename+".csv", mode='w', header=True, index=False)
This works fine for writing the relevant rows to .csv, except that I'm running into an issue where the column order seems to change between chunks. For example, for one chunk df.info() might display:
Data columns (total 48 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 retrieved_on 10000 non-null int64
1 ups 10000 non-null int64
2 media 888 non-null object
3 selftext 10000 non-null object
4 permalink 10000 non-null object
5 link_flair_text 1115 non-null object
6 title 10000 non-null object
7 secure_media_embed 10000 non-null object
....
while another might show
Data columns (total 50 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 title 10000 non-null object
1 secure_media_embed 10000 non-null object
2 distinguished 31 non-null object
3 downs 10000 non-null int64
4 thumbnail 10000 non-null object
5 domain 10000 non-null object
6 hide_score 10000 non-null bool
7 media_embed 10000 non-null object
....
So, different chunks are giving me a slightly different number of columns, and the columns wind up being in different orders in the .csv file. I assume this is do to slight variation between .json objects, but as a result my .csv has big chunks of rows where the data is in a different order than other rows.
I'm fairly new to working with this kind of data, and I'm having a hard time figuring out how to 1) ensure the columns are in a consistent order when writing them to .csv and 2) ensure that I still keep every column. I initially tried sending the column names to a list named column_titles and using df = df.reindex(columns=column_titles) to set the order manually. This does work to keep the order of the data consistent in the .csv file; however, I have a pretty large number of these files to process, and since the total names/numbers of columns varies across file as well as across chunks (apparently), having to manually specify the column order every time is going to be incredibly cumbersome.
I feel like there has to be a fairly straightforward way to 1) ensure that the column order for the first chunk of data becomes the column order for all subsequent chunks and 2) ensure that if anything after the first chunk has additional columns, that these additional columns simply get added on to the end of the dataframe if they don't already exist. However, I haven't found anything addressing this specific issue when searching. How can I accomplish this?