Python Pandas load csv is not loading full CSV

Question

Working on a project that creates a pdf report from a set of CSV metadata exported from Davinci Resolve.

The program runs and gernerates a report but there are some columns missing, I can't figure out what is happening. I have passed the usecols attribute with the columns that I want but it still misses some out.

I thought that this might be somthing to do with data types not getting loaded into the data frame correctly, but I have tried that and it does not work.

I would like the PDF report to contain the colums in the order that I have specified in the usecols section in the example code. example csv:

https://www.dropbox.com/s/jsup7f5qrfnqc7e/A003.csv?dl=0

Project uses pandas, jinja2 and weasyprint

print(sourcefile, outfile)

df = pd.read_csv(sourcefile,
    encoding="utf-16",
    usecols=['File Name', 'Camera #', 'Resolution', 'Duration TC', 'Video Codec', 'Camera FPS', 'Comments'],
    dtype={"File Name": str,
        "Camera #": str,
        "Resolution": str,
        "Duration TC": str,
        "Video Codec": str,
        "Camera FPS": float,
        "Comments": str,
        'Date Modified': str,
        'Date Recorded': str
        },
    na_values=['.', '??', ' ']  # Take any '.' or '??' values as NA
)

env = Environment(loader=FileSystemLoader('.'))
template = env.get_template("report_template.html")

template_vars = {"title": title,
                    "data": df.to_html(),
                    "date": self.date,
                    }

html_output = template.render(template_vars)
HTML(string=html_output).write_pdf(outfile)

df.head() outputs the following:

                  File Name  Duration TC   Resolution        Video Codec Comments Camera #  Camera FPS
0  A003C001_191024_R48G.mov  00:01:21:08  3200 x 1800  Apple ProRes 4444     PASS        A     50000.0
1  A003C002_191024_R48G.mov  00:01:04:20  3200 x 1800  Apple ProRes 4444     PASS        A     50000.0
2  A003C003_191024_R48G.mov  00:01:16:14  3200 x 1800  Apple ProRes 4444     PASS        A     50000.0
3  A003C004_191024_R48G.mov  00:01:16:08  3200 x 1800  Apple ProRes 4444     PASS        A     50000.0
4  A003C005_191024_R48G.mov  00:02:30:04  3200 x 1800  Apple ProRes 4444     PASS        A     50000.0

output example below:

Please, spend some time reading [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). — accdias, Feb 18 '20 at 11:50
To place the columns in the desired order, define your columns in a `list` (for example `mycols`), then mask your DataFrame as `df = df[mycols]`. — S3DEV, Feb 18 '20 at 12:20

S3DEV · Accepted Answer · 2020-02-18T14:02:16.527

Caveat:

This is not a complete answer. This answer is to address the request to show column re-ordering in more detail from the comment on the OP's question.

Source Data:

File Name,Duration TC,Resolution,Video Codec,Comments,Camera #,Camera FPS
A003C001_191024_R48G.mov,00:01:21:08,3200 x 1800,Apple ProRes 4444,PASS,A,50000.0
A003C002_191024_R48G.mov,00:01:04:20,3200 x 1800,Apple ProRes 4444,PASS,A,50000.0
A003C003_191024_R48G.mov,00:01:16:14,3200 x 1800,Apple ProRes 4444,PASS,A,50000.0
A003C004_191024_R48G.mov,00:01:16:08,3200 x 1800,Apple ProRes 4444,PASS,A,50000.0
A003C005_191024_R48G.mov,00:02:30:04,3200 x 1800,Apple ProRes 4444,PASS,A,50000.0

Sample Code:

This example code shows how to re-order the DataFrame columns using a filtering technique, per user requirement.

import pandas as pd

# Define column order.
col_order = ['File Name', 'Camera #', 'Resolution', 'Duration TC', 
             'Video Codec', 'Camera FPS', 'Comments']
# Read CSV file.
df = pd.read_csv('./cameradata.csv')
# Use column name filter to re-order columns.
df = df[col_order]
# Show results.
print(df)

Output:

                  File Name Camera #   Resolution  Duration TC  \
0  A003C001_191024_R48G.mov        A  3200 x 1800  00:01:21:08   
1  A003C002_191024_R48G.mov        A  3200 x 1800  00:01:04:20   
2  A003C003_191024_R48G.mov        A  3200 x 1800  00:01:16:14   
3  A003C004_191024_R48G.mov        A  3200 x 1800  00:01:16:08   
4  A003C005_191024_R48G.mov        A  3200 x 1800  00:02:30:04   

         Video Codec  Camera FPS Comments  
0  Apple ProRes 4444     50000.0     PASS  
1  Apple ProRes 4444     50000.0     PASS  
2  Apple ProRes 4444     50000.0     PASS  
3  Apple ProRes 4444     50000.0     PASS  
4  Apple ProRes 4444     50000.0     PASS

Python Pandas load csv is not loading full CSV

1 Answers1

Caveat:

Source Data:

Sample Code:

Output: