Dealing with changing column orders when writing .json to .csv using pandas?

Viewed 91

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?

1 Answers

Het did you try something like this :

import pandas as pd

with open('review.json') as json_file:      
    data = json_file.readlines()
    # this line below may take at least 8-10 minutes of processing for 4-5 million rows. It converts all strings in list to actual json objects. 
    data = list(map(json.loads, data)) 

pd.DataFrame(data)

Just to check if you computer is able to manage all the data at once

Related