0

I have date-time data which is stored in dataframe, as shown below. this data frame includes more than 3 years data<example: 2015,2016, 2017 , 2018 and 2019> as shown below

0 2015-02-06 00:00:00  10.397
1 2015-02-06 00:15:00  10.541
2 2015-02-06 00:30:00  10.166
3 2015-02-06 00:45:00   9.187
4 2015-02-06 01:00:00   9.158
....

138699 2019-01-20 22:45:00  6.077
138700 2019-01-20 23:00:00  5.933
138701 2019-01-20 23:15:00  5.962
138702 2019-01-20 23:30:00  6.048
138703 2019-01-20 23:45:00  6.077


Name: 0, dtype: datetime64[ns]

Now I want filter data between two years, say 2015 and 2016 and convert it into Json format as follows,

[
  {
    "data": [
      [
        1423180800000,
        10.397
      ],
      [
        1423184400000,
        9.158
      ],
      [
        1423185300000,
        9.36
      ],
      [
        1423186200000,
        9.216
      ],
      [
        1423187100000,
        9.043
      ]
    ]
  }
]

Could you please let me know how can achieve this using python pandas.

Improve this question
1
  • Can you add print (df.head()) ? there are 2 columns? Commented Jan 2, 2020 at 11:12

3 Answers 3

Reset to default
3

Use:

print (df)
                  date    data
0  2015-02-06 00:00:00  10.397
1  2016-02-06 00:15:00  10.541
2  2017-02-06 00:30:00  10.166
3  2018-02-06 00:45:00   9.187
4  2019-02-06 01:00:00   9.158

import json

#convert column to datetimes
df['date'] = pd.to_datetime(df['date'])

#filter by years
df = df[df['date'].dt.year.between(2015, 2016)]
#convert to unix times
df['date'] = df['date'].astype(np.int64) // 10**6

#convert to lists
d = df.to_dict(orient='l')
print (d)
{'date': [1423180800000, 1454717700000], 'data': [10.397, 10.540999999999999]}

#create json by lists
j = json.dumps([{'data': list(map(list, zip(d['date'], d['data'])))}])
print (j)
[{"data": [[1423180800000, 10.397], [1454717700000, 10.540999999999999]]}]
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

yours answer perefect , but d.to_json(orient='values') will give the 90% for json format<< [ 1423180800000, 10.397 ], [ 1423184400000, 9.158 ]> just add extra dict object like this [ {data:[]}] do we need to loop whole data?
1

very simple, set date column as datetime then sort data between two dates.let say start date 01/01/2015 and end date 31/12/2016.Then new data can be converted into json,lets try :

df['date'] = pd.to_datetime(df['date']) 
new_data=df[(df['date'] >= start_date) & (df['date'] <= end_date)]

new data contain only data between start and end date,it convert in to json as follows:

j = new_data.to_json(orient='records')
Improve this answer

1 Comment

how can append df_to_json() to new dict like to this, {data: [.....]}?
0

To filter the data between two years:

df[0] = pd.to_datetime(df[0])
df = df[df[0].dt.year.between(2015, 2016)]
Improve this answer

8 Comments

Thank you for your answer, but what is this df.mydate
It's your date column. What's the name of the column in your dataframe where you have dates?
I did not name the column, do I need to name the columns?
@niran it looks like it's called 0, so I've updated my answer with that
Getting this error, {AttributeError}Can only use .dt accessor with datetimelike values
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.