I have some data that's in a Dataframe with the above format. I'm accessing it using sqlite3 and using matplotlib to graph the data. I am trying to compare employee raises with the yearly CPI(one section of the bar chart with 2019 percentages for each location and the CPI that year, another for 2020, 2021, and 2022). To do so I'd like to create bins by year, so the table would look more like this:

Is there any easy way to do this using pandas queries/sqlite3?

CodePudding user response：

Assuming (df) is your dataframe, here is one way to do it :

out = (
        df
          .drop("Type", axis=1)
          .set_index("Location")
          .pipe(lambda df_: df_.set_axis(df_.columns.str[:4], axis=1))
          .transpose()
          .reset_index(names="Year")
          .rename_axis(None, axis=1)​
      )

Output :

print(out)

   Year  Crawford  Deck  Peoria
0  2019      1.55  0.80    1.62
1  2020      1.85  1.76    1.64
2  2021      1.10  3.00    0.94
3  2022      1.10  2.50    2.20

Plot (with

CodePudding user response：

Consider