Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas - Sorting a dataframe by using datetimeindex

The following is my dataframe which holds values from multiple Excel files. I wanted to do a time series analysis, so I made the index as datetimeindex. But my index is not arranged according to the date. The following is my dataframe:

    Item Details    Unit    Op. Qty Price   Op. Amt.    Cl. Qty Price.1 Cl. Amt.
Month                               
2013-04-01  5 In 1  Pcs -56.0   172.78  -9675.58    -68.0   175.79  -11953.96
2013-04-01  Adaptor Pcs -17.0   9.00    -152.99 -17.0   9.00    -152.99
2013-04-01  Agro Tape   Pcs -2.0    26.25   -52.50  -2.0    26.25   -52.50
...
2014-01-01  12" Angal   Pcs -6.0    31.50   -189.00 -6.0    31.50   -189.00
2014-01-01  13 Mm Electrical Drill Check    Set -1.0    247.50  -247.50 -1.0    247.50  -247.50
2014-01-01  14" Blad    Pcs -5.0    157.49  -787.45 -5.0    157.49  -787.45
...
2013-09-01  Zinc Bolt 1/4 X 2"(box) Box -1.0    899.99  -899.99 -1.0    899.99  -899.99
2013-09-01  Zorik 88 32gram Pcs -1.0    45.00   -45.00  -1.0    45.00   -45.00
2013-09-01  Zorrik 311 Gram Pcs -1.0    270.01  -270.01 -1.0    270.01  -270.01

It is not sorted according to the date. I wanted to sort the index and its respective rows also. I googled it and found that there is a way to sort the datetimeindex and is as follows:

all_data.index.sort_values()

DatetimeIndex(['2013-04-01', '2013-04-01', '2013-04-01', '2013-04-01',
           '2013-04-01', '2013-04-01', '2013-04-01', '2013-04-01',
           '2013-04-01', '2013-04-01',
           ...
           '2014-02-01', '2014-02-01', '2014-02-01', '2014-02-01',
           '2014-02-01', '2014-02-01', '2014-02-01', '2014-02-01',
           '2014-02-01', '2014-02-01'],
          dtype='datetime64[ns]', name=u'Month', length=71232, freq=None)

But it is sorting only the index, how can I sort the entire dataframe according to the sorted index? Kindly help.

like image 593
Jeril Avatar asked Oct 26 '16 12:10

Jeril


People also ask

What is DateTimeIndex pandas?

class pandas. DatetimeIndex [source] Immutable ndarray of datetime64 data, represented internally as int64, and which can be boxed to Timestamp objects that are subclasses of datetime and carry metadata such as frequency information.

How do you sort a DataFrame based on a date column in Python?

Python. Output: One thing to notice here is our DataFrame gets sorted in ascending order of dates, to sort the DataFrame in descending order we can pass an additional parameter inside the sort_values() function that will set ascending value to False and will return the DataFrame in descending order.

Can you sort a pandas DataFrame?

You can sort a DataFrame by row or column value as well as by row or column index. Both rows and columns have indices, which are numerical representations of where the data is in your DataFrame. You can retrieve data from specific rows or columns using the DataFrame's index locations.

What is the correct way to sort a data frame DF?

In order to sort the data frame in pandas, function sort_values() is used. Pandas sort_values() can sort the data frame in Ascending or Descending order.


1 Answers

I think you need sort_index:

all_data = all_data.sort_index()
like image 85
jezrael Avatar answered Oct 08 '22 09:10

jezrael