Pandas Melt - The following 'id_vars' are not present in the DataFrame

Question

I have a table in a database that I am trying to melt using Pandas. The code below is what I am using

all_data = """select city, State, [OCC Series], Grade, [Step 1], [Step 2], [Step 3], [Step 4], [Step 5],  [Step 6], [Step 7], [Step 8], [Step 9], [Step 10]
from raw_va_pay"""

df = pd.read_sql(sql=all_data, con=conn)

grouped = df.groupby(['City', 'State', 'Occ Series',  'Grade'])
print(grouped.head(3))

avg_pay = grouped.mean()

print(avg_pay)

melt = pd.melt(avg_pay, id_vars=['City','State','Occ Series','Grade'])

print(melt)

This is the results of the print(avg_pay) and the error i get

here is the error i get.

KeyError: "The following 'id_vars' are not present in the DataFrame: ['City', 'Grade', 'Occ Series', 'State']"

I tried the Pandas melt function and i am trying to get each of the steps as an additional column as well as the pay for each of those steps as an additional column.

Answer 1

I got it to work, but unfortunately the solution was to write the avg_pay into another DB table, load that into a dataframe and then melt that.