-1

I attached screenshot of the data [sample data][1]

When I imported the csv, i set the index to be columns[0,1,2] The remaining columns [3: ] are dates ranging from 2015-07-31 to 2021-08-31.

My goal is to be able to create a plot based on 'Budget Name' and be able to drill down to 'Level 02' or 'Level 03'.

I want to create a line plot where the x-axis are the date columns and the y-axis are the values. However, what I attempted resulted in the x-axis being the 'Budget Code'.

The total data set has 17 items for budget name and each budget name has 'Level 02' and 'Level 03' data.

import pandas as pd
import numpy as np

# These are the first 30 rows of the csv file 
'Budget Code,Level 02,Level 03,2015-07-31,2015-08-31,2015-09-30,2015-10-31,2015-11-30,2015-12-31,2016-01-31,2016-02-29,2016-03-31,2016-04-30,2016-05-31,2016-06-30,2016-07-31,2016-08-31,2016-09-30,2016-10-31,2016-11-30,2016-12-31,2017-01-31,2017-02-28,2017-03-31,2017-04-30,2017-05-31,2017-06-30,2017-07-31,2017-08-31,2017-09-30,2017-10-31,2017-11-30,2017-12-31,2018-01-31,2018-02-28,2018-03-31,2018-04-30,2018-05-31,2018-06-30,2018-07-31,2018-08-31,2018-09-30,2018-10-31,2018-11-30,2018-12-31,2019-01-31,2019-02-28,2019-03-31,2019-04-30,2019-05-31,2019-06-30,2019-07-31,2019-08-31,2019-09-30,2019-10-31,2019-11-30,2019-12-31,2020-01-31,2020-02-29,2020-03-31,2020-04-30,2020-05-31,2020-06-30,2020-07-31,2020-08-31,2020-09-30,2020-10-31,2020-11-30,2020-12-31,2021-01-31,2021-02-28,2021-03-31,2021-04-30,2021-05-31,2021-06-30'
data = pd.read_csv(r'example_data.csv', index_col=[0,1,2]
data = data.fillna(0)
data.groupby('Budget Code').plot()

Is there a way to create this using pandas/seaborn/matplotlib ? The end result would be similar to a line plot in MS Excel. Thanks

devafri
  • 1
  • 3
  • Always provide a [mre], with **code, data, errors, current output, and expected output, as text**. [SO Discourages Screenshots](https://meta.stackoverflow.com/questions/303812/). It is likely the question will be down-voted and closed. You are discouraging assistance because no one wants to retype your data or code, and screenshots are often illegible. [edit] the question and **add text**. Please see [How to provide a reproducible copy of your DataFrame using `df.head(30).to_clipboard(sep=',')`](https://stackoverflow.com/questions/52413246) – Trenton McKinney Oct 03 '20 at 16:30

1 Answers1

0

I figured out a workable solution below. The aesthetics of the plots can certainly be improved.

noJune=data.drop(columns=['2016-06-30','2017-06-30','2018-06-30','2019-06-30','2020-06-30', 'Unnamed: 75'])
# Convert column names from strings to datetime
for i in range(3,len(noJune.keys())):
    noJune.rename(columns={noJune.keys([i]:datetime.datetime.strptime(noJune.keys()[i], '%Y-%m-%d')}, inplace=True)

Plot for the sum of all budget codes:

ax = noJune.sum(axis=0).T[3:70].plot(figsize=(30,15))
ax.set_xticks(noJune.keys()[3:70])
ax.grid(True)

Plot by budget code:

noJune.groupby('Budget Code').sum().T[3:70].plot(figsize=(30,50), subplots=True, x_compat=True)

Plot by GL account:

glPlot = noJune.groupby('Level 02').sum().T[3:70].plot(figsize=(30,50), subplots=True, x_compat=True)

Plot by sub-Gl:

detailPlot = noJune.groupby('Level 03').sum().T[3:70].plot(figsize=(30,50), subplots=True, x_compat=True)
devafri
  • 1
  • 3