I am trying to transfer Google Search Console data into a Pivot Table in Pandas and sort it. I use the searchconsole module in Python to request this data from the API.

Code

    report = webproperty.query.range(DATA).get().to_dataframe()
    #Name columns
    report.columns=['zoekwoord','pagina','klikken','vertoningen','ctr','positie']

    #Make Pivot
    pivot = report.pivot_table(index=['pagina','zoekwoord'], values=['klikken','vertoningen','ctr','positie'])

    #Define output
    writer = pd.ExcelWriter(r'~/Downloads/gsc_output.xlsx', engine='xlsxwriter')

    # Write each dataframe to a different worksheet.
    report.to_excel(writer, sheet_name='Data', index=False)
    pivot.to_excel(writer, sheet_name='Draaitabel')

    # Close the Pandas Excel writer and output the Excel file.
    writer.save()

Example Data

I want to see the queries per page and sort the amount of clicks, like:

If I use .sort_values I don't get the data I want:

How to do this? :)

CodePudding user response：

You can use pandas.DataFrame.sort_values.

Try this :

(
    df.sort_values(by=["Page", "Query", "Clicks"],
                   ascending=[True, False, False],
                   inplace=True,
                   ignore_index=True)
)

df.loc[df["Page"].duplicated(), "Page"]= ""

# Output :

print(df)

      Page    Query  Clicks
0  /page-a  query 3      40
1           query 1      20
2  /page-b  query 2      40

# Input used:

df= pd.DataFrame({'Page': ['/page-a', '/page-b', '/page-a'],
                  'Query': ['query 1', 'query 2', 'query 3'],
                  'Clicks': [20, 40, 40]})