CODEWITHSUNDEEP
pythonsortingpandasgraph
sagar shukla
sagar shukla

Reputation: 11

Python Panda dataframe sorting with month - year

I am a python and pandas beginner and I'm having trouble sorting a data frame after a groupby operation. I can get sorted data after groupby and count but when I put all the data together in another data frame, I dont get sorted data.

Here is my attempt

conn = sqlite3.connect('Demo.sqlite')

df = pd.read_sql("SELECT * FROM Table where Column  LIKE 'x.%'", conn)
pf = pd.read_sql("SELECT * FROM Table", conn)

df['DateTime'] = df['DATE'].apply(lambda x: dt.datetime.fromtimestamp(x).strftime('%b %Y'))
pf['DateTime'] = pf['DATE'].apply(lambda x: dt.datetime.fromtimestamp(x).strftime('%b %Y'))

df1 = df.set_index('DateTime', drop=False)
pf1 = pf.set_index('DateTime', drop=False)

df1 = df1.sort(['DateTime'])
pf1 = pf1.sort(['DateTime'])

R1= df1['DateTime'].groupby(lambda x: x)
R2= pf1['DateTime'].groupby(lambda x: x)
TT= TotalBuild.count() - PrivateBuild.count()

result = pd.DataFrame({'R1': R1.count(),
                       'TT': TT,
                       'R2': R2.count()
                       })

Here is output of result data frame.

Check the output of Result data frame here

I want to sort dataframe by month-year. currently data frame is sorted by month, that's why all the data for January is displaying together. once data is sorted i want to draw bar graphs.

Upvotes: 1

Views: 5709

Answers (1)

pkapka
pkapka

Reputation: 5346

One simple way is to just convert the index to date, sort and then convert back to month-year.

result.index = pd.to_datetime(result.index)
result.sort_index(inplace=True)
result.index = monthly_transpose.index.strftime('%B-%Y')

When converting to date, pandas converts the month-year to the first date of each month nad hence the sorting is made possible. Hope it helps.

Upvotes: 3

Related Questions