Pandas KeyError value not in index

Question

I have the following code    df   pd read csv CsvFileName   p   df pivot table index   Hour    columns  DOW   values  Changes   aggfunc np mean  round 0  p fillna 0  inplace True   p   1Sun    2Mon    3Tue    4Wed    5Thu    6Fri    7Sat      p   1Sun    2Mon    3Tue    4Wed    5Thu    6Fri    7Sat    astype int    It has always been working until the csv file doesn t have enough coverage  of all week days   For e g   with the following  csv file    DOW Hour Changes 4Wed 01 237 3Tue 07 2533 1Sun 01 240 3Tue 12 4407 1Sun 09 2204 1Sun 01 240 1Sun 01 241 1Sun 01 241 3Tue 11 662 4Wed 01 4 2Mon 18 4737 1Sun 15 240 2Mon 02 4 6Fri 01 1 1Sun 01 240 2Mon 19 2300 2Mon 19 2532   I ll get the following error   KeyError     5Thu   7Sat   not in index    It seems to have a very easy fix  but I m just too new to Python to know how to fix it

User · Answer

I had the same issue    During the 1st development I used a  csv file  comma as separator  that I ve modified a bit before saving it   After saving the commas became semicolon   On Windows it is dependent on the  Regional and Language Options  customize screen where you find a List separator  This is the char Windows applications expect to be the CSV separator   When testing from a brand new file I encountered that issue   I ve removed the  sep  argument in read csv method before    df1   pd read csv  myfile csv   sep         after    df1   pd read csv  myfile csv      That way  the issue disappeared

User · Answer

I had a very similar issue  I got the same error because the csv contained spaces in the header  My csv contained a header  Gender   and I had it listed as       Gender      If it s easy enough for you to access your csv  you can use the excel formula trim   to clip any spaces of the cells   or remove it like this    df columns   df columns to series   apply lambda x  x strip

User · Answer

Use reindex to get all columns you need   It ll preserve the ones that are already there and put in empty columns otherwise   p   p reindex columns   1Sun    2Mon    3Tue    4Wed    5Thu    6Fri    7Sat      So  your entire code example should look like this   df   pd read csv CsvFileName   p   df pivot table index   Hour    columns  DOW   values  Changes   aggfunc np mean  round 0  p fillna 0  inplace True   columns     1Sun    2Mon    3Tue    4Wed    5Thu    6Fri    7Sat   p   p reindex columns columns  p columns    p columns  astype int

User · Answer

please try this to clean and format your column names   df columns    df columns str strip   str upper                  str replace                          str replace                         str replace

[python] Pandas KeyError: value not in index

Examples related to python

Examples related to pandas

Examples related to indexing

Examples related to dataframe