views_downloads_collection.Rmd

---
title: "views_downloads_collection"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)

# loading required libraries
library(tidyverse)
library(osfr)
library(reticulate)
library(jsonlite)
library(lubridate)
library(here)

Sys.setenv(TZ='UTC')

url <- 'https://api.osf.io/_/metrics/preprints/'
osf_auth <- Sys.getenv("osf_preprintimpact_auth")
auth_header <- httr::add_headers('Authorization' = paste('Bearer', osf_auth))

use_condaenv(condaenv = "myenv", conda = "/Users/courtneysoderberg/opt/anaconda3/bin/python")
```

```{r initial hourly pull table}
# function to create list of dates to pull
pull_dates <- function(start_date){
  dates <- seq(start_date, 
             start_date + days(13), 
             by = 'days')
  
  return(dates)
}

# create tibble with all pps and daily pull windows for each
preprint_pull_dates <- read_csv(here::here('/eligible_preprints.csv')) %>%
                    mutate(dates_to_pull = map(date_published, pull_dates)) %>%
                    unnest(dates_to_pull) %>%
                    rename(gte = dates_to_pull) %>%
                    mutate(lt = gte + days(1),
                           lt_string = gsub(" ", "T", lt),
                           gte_string = gsub(" ", "T", gte))
```

```{python hourly views pull}
pp = r.preprint_pull_dates #get pandas df of R object

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}views/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
views_df = pd.DataFrame(columns=['guid', 'sloan_id', 'coi_shown', 'date', 'view_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  # set up query for getting views per preprint per user_id in timeframe
  query = {
      "query": {
           "term" : { "preprint_id" : guid } # example pp guid
      },
       "aggs" : {
          "views_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
              "aggs": {
                  "users" : {
                      "terms" : {
                          "field" : "sloan_id",
                          "size" : 5000
                      },
                      "aggs" : {
                      "condition" : {
                        "terms" : {
                          "field" : "sloan_coi",
                          "size" : 5000
                        },
                      "aggs": {
                        "views_per_hour" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"hour",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
      }
  }
}
}
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_views_byuser = res.json()['aggregations']['views_timeframe']['users']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_views_byuser) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_views_byuser)):
      
        # for each person, save their dictionary that contatins their actions
        user_actions = pp_views_byuser[x]['condition']
      
        # loop through each item in their 'buckets' which in this case is each timestamp
        for j in range(len(user_actions['buckets'])):
    
          sloan_coi_cond = user_actions['buckets'][j]['key_as_string']
          user_views = user_actions['buckets'][j]['views_per_hour']['buckets']
 
          for k in range(len(user_views)):

            # append new row with info for the preprint, the user, the timestamp, and the action count
            new_row = [{'guid':guid,'sloan_id':pp_views_byuser[x]['key'],'coi_shown':sloan_coi_cond,'date':user_views[k]['key_as_string'],'view_count': user_views[k]['doc_count']}]
    
            views_df = views_df.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r views by hour results}
views_by_hour_results <- py$views_df
views_by_hour_results <- views_by_hour_results %>%
                           as_tibble() %>%
                            unnest(view_count)

write_csv(views_by_hour_results, 'views_by_hour_results.csv')

view_for_minute_pulls <- views_by_hour_results %>%
                          mutate(date = ymd_hms(date)) %>%
                          filter(view_count > 0) %>%
                          group_by(guid, sloan_id, coi_shown) %>%
                          summarize(earliest_view = min(date)) %>%
                          ungroup() %>%
                          select(guid, earliest_view) %>% 
                          distinct(.keep_all = T) %>%
                          rename(gte = earliest_view) %>%
                            mutate(lt = gte + hours(1),
                                     lt_string = gsub(" ", "T", lt),
                                     gte_string = gsub(" ", "T", gte))
  
```

```{python hourly downloads pull}
pp = r.preprint_pull_dates #get pandas df of R object

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}downloads/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
downloads_df = pd.DataFrame(columns=['guid', 'sloan_id', 'coi_shown', 'date', 'download_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  # set up query for getting downloads per preprint per user_id in timeframe
  query = {
      "query": {
           "term" : { "preprint_id" : guid } # example pp guid
      },
       "aggs" : {
          "downloads_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
              "aggs": {
                  "users" : {
                      "terms" : {
                          "field" : "sloan_id",
                          "size" : 5000
                      },
                      "aggs" : {
                      "condition" : {
                        "terms" : {
                          "field" : "sloan_coi",
                          "size" : 5000
                        },
                      "aggs": {
                        "downloads_per_hour" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"hour",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
      }
  }
}
}
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_downloads_byuser = res.json()['aggregations']['downloads_timeframe']['users']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_downloads_byuser) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_downloads_byuser)):
      
        # for each person, save their dictionary that contatins their actions
        user_actions = pp_downloads_byuser[x]['condition']
      
        # loop through each item in their 'buckets' which in this case is each timestamp
        for j in range(len(user_actions['buckets'])):
    
          sloan_coi_cond = user_actions['buckets'][j]['key_as_string']
          user_downloads = user_actions['buckets'][j]['downloads_per_hour']['buckets']
 
          for k in range(len(user_downloads)):

            # append new row with info for the preprint, the user, the timestamp, and the action count
            new_row = [{'guid':guid,'sloan_id':pp_downloads_byuser[x]['key'],'coi_shown':sloan_coi_cond,'date':user_downloads[k]['key_as_string'],'download_count': user_downloads[k]['doc_count']}]
    
            downloads_df = downloads_df.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r downloads by hour results}
downloads_by_hour_results <- py$downloads_df
downloads_by_hour_results <- downloads_by_hour_results %>%
                           as_tibble() %>%
                            unnest(download_count)

write_csv(downloads_by_hour_results, 'downloads_by_hour_results.csv')

downloads_for_sec_pulls <- downloads_by_hour_results %>%
                            mutate(date = ymd_hms(date)) %>%
                            filter(download_count > 0) %>%
                            group_by(guid, sloan_id, coi_shown) %>%
                            summarize(earliest_download = min(date)) %>%
                            ungroup() %>%
                            select(guid, earliest_download) %>% 
                            distinct(.keep_all = T) %>%
                            rename(gte = earliest_download) %>%
                            mutate(lt = gte + hours(1),
                                     lt_string = gsub(" ", "T", lt),
                                     gte_string = gsub(" ", "T", gte))
```

```{python minute view pull}
pp = r.view_for_minute_pulls

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}views/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
views_perminute_df = pd.DataFrame(columns=['guid', 'sloan_id', 'coi_shown', 'date', 'download_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  # set up query for getting downloads per preprint per user_id in timeframe
  query = {
      "query": {
           "term" : { "preprint_id" : guid } # example pp guid
      },
       "aggs" : {
          "views_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
              "aggs": {
                  "users" : {
                      "terms" : {
                          "field" : "sloan_id",
                          "size" : 5000
                      },
                      "aggs" : {
                      "condition" : {
                        "terms" : {
                          "field" : "sloan_coi",
                          "size" : 5000
                        },
                      "aggs": {
                        "views_per_minute" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"minute",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
      }
  }
}
}
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_views_byuser_minutes = res.json()['aggregations']['views_timeframe']['users']['buckets']

  print(calls)
  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_views_byuser_minutes ) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_views_byuser_minutes )):
      
        # for each person, save their dictionary that contatins their actions
        user_actions = pp_views_byuser_minutes [x]['condition']
      
        # loop through each item in their 'buckets' which in this case is each timestamp
        for j in range(len(user_actions['buckets'])):
    
          sloan_coi_cond = user_actions['buckets'][j]['key_as_string']
          user_views = user_actions['buckets'][j]['views_per_minute']['buckets']
 
          for k in range(len(user_views)):

            # append new row with info for the preprint, the user, the timestamp, and the action count
            new_row = [{'guid':guid,'sloan_id':pp_views_byuser_minutes[x]['key'],'coi_shown':sloan_coi_cond,'date':user_views[k]['key_as_string'],'download_count': user_views[k]['doc_count']}]
    
            views_perminute_df = views_perminute_df.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r views by minute results}
views_by_minute_results <- py$views_perminute_df

views_by_minute_results <- views_by_minute_results %>%
                           as_tibble() %>%
                            rename(view_count = 'download_count') %>%
                            unnest(view_count)

write_csv(views_by_minute_results, 'views_by_minute_results.csv')

view_for_sec_pulls <- views_by_minute_results %>%
                          mutate(date = ymd_hms(date)) %>%
                          filter(view_count > 0) %>%
                          group_by(guid, sloan_id, coi_shown) %>%
                          summarize(earliest_view = min(date)) %>%
                          ungroup() %>%
                          select(guid, earliest_view) %>% 
                          distinct(.keep_all = T) %>%
                          rename(gte = earliest_view) %>%
                            mutate(lt = gte + minutes(1),
                                     lt_string = gsub(" ", "T", lt),
                                     gte_string = gsub(" ", "T", gte))
```

```{python seconds view pull}
pp = r.view_for_sec_pulls

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}views/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
views_persecond_df = pd.DataFrame(columns=['guid', 'sloan_id', 'coi_shown', 'date', 'view_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  # set up query for getting downloads per preprint per user_id in timeframe
  query = {
      "query": {
           "term" : { "preprint_id" : guid } # example pp guid
      },
       "aggs" : {
          "views_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
              "aggs": {
                  "users" : {
                      "terms" : {
                          "field" : "sloan_id",
                          "size" : 5000
                      },
                      "aggs" : {
                      "condition" : {
                        "terms" : {
                          "field" : "sloan_coi",
                          "size" : 5000
                        },
                      "aggs": {
                        "views_per_second" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"second",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
      }
  }
}
}
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_views_byuser_seconds = res.json()['aggregations']['views_timeframe']['users']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_views_byuser_seconds ) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_views_byuser_seconds )):
      
        # for each person, save their dictionary that contatins their actions
        user_actions = pp_views_byuser_seconds[x]['condition']
      
        # loop through each item in their 'buckets' which in this case is each timestamp
        for j in range(len(user_actions['buckets'])):
    
          sloan_coi_cond = user_actions['buckets'][j]['key_as_string']
          user_views = user_actions['buckets'][j]['views_per_second']['buckets']
 
          for k in range(len(user_views)):

            # append new row with info for the preprint, the user, the timestamp, and the action count
            new_row = [{'guid':guid,'sloan_id':pp_views_byuser_seconds[x]['key'],'coi_shown':sloan_coi_cond,'date':user_views[k]['key_as_string'],'view_count': user_views[k]['doc_count']}]
    
            views_persecond_df = views_persecond_df.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r views by second results}
views_by_second_results <- py$views_persecond_df

views_by_second_results <- views_by_second_results %>%
                           as_tibble() %>%
                            unnest(view_count)

write_csv(views_by_second_results, 'views_by_second_results.csv')
```

```{python second download pull}
pp = r.downloads_for_sec_pulls

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}downloads/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
downloads_persecond_df = pd.DataFrame(columns=['guid', 'sloan_id', 'coi_shown', 'date', 'download_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  # set up query for getting downloads per preprint per user_id in timeframe
  query = {
      "query": {
           "term" : { "preprint_id" : guid } # example pp guid
      },
       "aggs" : {
          "downloads_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
              "aggs": {
                  "users" : {
                      "terms" : {
                          "field" : "sloan_id",
                          "size" : 5000
                      },
                      "aggs" : {
                      "condition" : {
                        "terms" : {
                          "field" : "sloan_coi",
                          "size" : 5000
                        },
                      "aggs": {
                        "downloads_per_second" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"second",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
      }
  }
}
}
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_downloads_byuser_seconds = res.json()['aggregations']['downloads_timeframe']['users']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_downloads_byuser_seconds ) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_downloads_byuser_seconds )):
      
        # for each person, save their dictionary that contatins their actions
        user_actions = pp_downloads_byuser_seconds [x]['condition']
      
        # loop through each item in their 'buckets' which in this case is each timestamp
        for j in range(len(user_actions['buckets'])):
    
          sloan_coi_cond = user_actions['buckets'][j]['key_as_string']
          user_downloads = user_actions['buckets'][j]['downloads_per_second']['buckets']
 
          for k in range(len(user_downloads)):

            # append new row with info for the preprint, the user, the timestamp, and the action count
            new_row = [{'guid':guid,'sloan_id':pp_downloads_byuser_seconds [x]['key'],'coi_shown':sloan_coi_cond,'date':user_downloads[k]['key_as_string'],'download_count': user_downloads[k]['doc_count']}]
    
            downloads_persecond_df = downloads_persecond_df.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r}
downloads_by_second_results <- py$downloads_persecond_df

downloads_by_second_results <- downloads_by_second_results %>%
                           as_tibble() %>%
                            unnest(download_count)

write_csv(downloads_by_second_results, 'downloads_by_second_results.csv')
```

```{python pull total downloads on pps by hour}
pp = r.preprint_pull_dates

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}downloads/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
overall_downloads_perhour_df = pd.DataFrame(columns=['guid', 'date', 'download_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  # set up query for getting downloads per preprint per user_id in timeframe
  query = {
      "query": {
           "term" : { "preprint_id" : guid } # example pp guid
      },
       "aggs" : {
          "downloads_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
                      "aggs": {
                        "downloads_per_hour" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"hour",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_downloads_hours = res.json()['aggregations']['downloads_timeframe']['downloads_per_hour']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_downloads_hours) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_downloads_hours)):
      
            # append new row with info for the preprint, the user, the timestamp, and the action count
        new_row = [{'guid':guid, 'date':pp_downloads_hours[x]['key_as_string'],'download_count': pp_downloads_hours[x]['doc_count']}]
    
        overall_downloads_perhour_df = overall_downloads_perhour_df.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r total downloads on pps by hour results}
overall_downloads_by_hour_results <- py$overall_downloads_perhour_df

overall_downloads_by_hour_results <- overall_downloads_by_hour_results %>%
                           as_tibble() %>%
                            unnest(download_count)

write_csv(overall_downloads_by_hour_results, 'overall_downloads_by_hour_results.csv')

overall_downloads_byminute_pull <- overall_downloads_by_hour_results %>%
                                      mutate(date = ymd_hms(date)) %>%
                                      filter(download_count > 0) %>%
                                      rename(gte = date) %>%
                                      mutate(lt = gte + hours(1),
                                               lt_string = gsub(" ", "T", lt),
                                               gte_string = gsub(" ", "T", gte))
                                      
```

```{python pull total downloads on pps by minute}
pp = r.overall_downloads_byminute_pull

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}downloads/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
overall_downloads_perminute_df = pd.DataFrame(columns=['guid', 'date', 'download_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  # set up query for getting downloads per preprint per user_id in timeframe
  query = {
      "query": {
           "term" : { "preprint_id" : guid } # example pp guid
      },
       "aggs" : {
          "downloads_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
                      "aggs": {
                        "downloads_per_minute" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"minute",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_downloads_minute = res.json()['aggregations']['downloads_timeframe']['downloads_per_minute']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_downloads_minute) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_downloads_minute)):
      
            # append new row with info for the preprint, the user, the timestamp, and the action count
        new_row = [{'guid':guid, 'date':pp_downloads_minute[x]['key_as_string'],'download_count': pp_downloads_minute[x]['doc_count']}]
    
        overall_downloads_perminute_df = overall_downloads_perminute_df.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r total downloads on pps by minutes results}
overall_downloads_by_minute_results <- py$overall_downloads_perminute_df

overall_downloads_by_minute_results <- overall_downloads_by_minute_results %>%
                           as_tibble() %>%
                            unnest(download_count)

write_csv(overall_downloads_by_minute_results, 'overall_downloads_by_minute_results.csv')

overall_downloads_bysecond_pull <- overall_downloads_by_minute_results %>%
                                      mutate(date = ymd_hms(date)) %>%
                                      filter(download_count > 0) %>%
                                      rename(gte = date) %>%
                                      mutate(lt = gte + minutes(1),
                                               lt_string = gsub(" ", "T", lt),
                                               gte_string = gsub(" ", "T", gte))
                                      
```

```{python pull total downloads on pps by second}
pp = r.overall_downloads_bysecond_pull

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}downloads/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
overall_downloads_persecond_df = pd.DataFrame(columns=['guid', 'date', 'download_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  # set up query for getting downloads per preprint per user_id in timeframe
  query = {
      "query": {
           "term" : { "preprint_id" : guid } # example pp guid
      },
       "aggs" : {
          "downloads_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
                      "aggs": {
                        "downloads_per_second" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"second",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_downloads_second = res.json()['aggregations']['downloads_timeframe']['downloads_per_second']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_downloads_second) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_downloads_second)):
      
            # append new row with info for the preprint, the user, the timestamp, and the action count
        new_row = [{'guid':guid, 'date':pp_downloads_second[x]['key_as_string'],'download_count': pp_downloads_second[x]['doc_count']}]
    
        overall_downloads_persecond_df = overall_downloads_persecond_df.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r}
overall_downloads_by_second_results <- py$overall_downloads_persecond_df

overall_downloads_by_second_results <- overall_downloads_by_second_results %>%
                                        as_tibble() %>% unnest(download_count)

write_csv(overall_downloads_by_second_results, 'overall_downloads_by_second_results.csv')
```

```{python user_ids for all views pull}
import requests
import pandas as pd

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}views/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
view_user_guids_df = pd.DataFrame(columns=['user_id', 'sloan_id'])
empty_lists = 0

# set up query for getting downloads per preprint per user_id in timeframe
query = {
    "query": {
          "exists" : { "field" : "user_id" } # has a user_id
    },
      "aggs" : {
        "views_timeframe": {
            "filter": {
                "range" : {
                    "timestamp" : {
                        "gte" : "2020-04-21T20:37:53",
                        "lte" : "2020-05-26T20:37:53"
                    }
                }
            },
                    "aggs": {
                      "user" : {
                        "terms" :{
                          "field":"user_id",
                          "size" : 5000
                        },
                        "aggs": {
                          "users" : {
                              "terms" : {
                                  "field" : "sloan_id",
                                  "size" : 5000
                              }
                          }
                      }
                      }
                    }
                }
            }
        }
  
payload = {
    'data': {
        'type': 'preprint_metrics',
        'attributes': {
            'query': query
        }
    }
}
  
  # make call using query & payload from above
res = requests.post(post_url, headers=headers, json=payload)
pp_view_user_guids = res.json()['aggregations']['views_timeframe']['user']['buckets']

# loop through each person in output
for x in range(len(pp_view_user_guids)):
  
  user_guid = pp_view_user_guids[x]['key']
  sloan_ids = pp_view_user_guids[x]['users']['buckets']
  
  for y in range(len(sloan_ids)):
    
    if len(sloan_ids) < 1:
      empty_lists = empty_lists + 1
    
    else:
      
      # append new row with info for the preprint, the user, the timestamp, and the action count
      new_row = [{'user_id':user_guid,'sloan_id': sloan_ids[y]['key']}]
    
      view_user_guids_df = view_user_guids_df.append(new_row, ignore_index = True)
```

```{python user_ids for all downloads pull}
import requests
import pandas as pd

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}downloads/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
download_user_ids_df = pd.DataFrame(columns=['user_id', 'sloan_id'])
empty_lists = 0

# set up query for getting downloads per preprint per user_id in timeframe
query = {
    "query": {
          "exists" : { "field" : "user_id" } # has a user_id
    },
      "aggs" : {
        "download_timeframe": {
            "filter": {
                "range" : {
                    "timestamp" : {
                        "gte" : "2020-04-21T20:37:53",
                        "lte" : "2020-05-26T20:37:53"
                    }
                }
            },
                    "aggs": {
                      "user" : {
                        "terms" :{
                          "field":"user_id",
                          "size" : 5000
                        },
                        "aggs": {
                          "users" : {
                              "terms" : {
                                  "field" : "sloan_id",
                                  "size" : 5000
                              }
                          }
                      }
                      }
                    }
                }
            }
        }
  
payload = {
    'data': {
        'type': 'preprint_metrics',
        'attributes': {
            'query': query
        }
    }
}
  
  # make call using query & payload from above
res = requests.post(post_url, headers=headers, json=payload)
pp_download_user_ids = res.json()['aggregations']['download_timeframe']['user']['buckets']

# loop through each person in output
for x in range(len(pp_download_user_ids)):
  
  user_guid = pp_download_user_ids[x]['key']
  sloan_ids = pp_download_user_ids[x]['users']['buckets']
  
  for y in range(len(sloan_ids)):
    
    if len(sloan_ids) < 1:
      empty_lists = empty_lists + 1
    
    else:
      
      # append new row with info for the preprint, the user, the timestamp, and the action count
      new_row = [{'user_id':user_guid,'sloan_id': sloan_ids[y]['key']}]
    
      download_user_ids_df = download_user_ids_df.append(new_row, ignore_index = True)
```

```{r save user_guids for sloan_ids views and download files}
viewer_user_guids <- py$view_user_guids_df %>%
                    as_tibble()

write_csv(viewer_user_guids, 'viewer_user_guids.csv')

downloader_user_guids <- py$download_user_ids_df  %>%
                    as_tibble()

write_csv(downloader_user_guids, 'downloader_user_guids.csv')
```

```{python get missing sloan_id pp views}
pp = r.preprint_pull_dates #get pandas df of R object

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}views/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
views_byhour_nosloanid = pd.DataFrame(columns=['guid', 'user_id', 'coi_shown', 'date', 'view_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  
  # set up query for getting views per preprint per user_id in timeframe
  query = {
      "query": {
    "bool": {
      "must": {
        "term" : {"preprint_id" : guid}
      },
      "must_not": {
        "exists": {
          "field": "sloan_id"
        }
      }
    }
  },
       "aggs" : {
          "views_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
              "aggs": {
                  "users" : {
                      "terms" : {
                          "field" : "user_id",
                          "size" : 5000
                      },
                      "aggs" : {
                      "condition" : {
                        "terms" : {
                          "field" : "sloan_coi",
                          "size" : 5000
                        },
                      "aggs": {
                        "views_per_hour" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"hour",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
      }
  }
}
}
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_views_byuser_guid_byhour = res.json()['aggregations']['views_timeframe']['users']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_views_byuser_guid_byhour) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_views_byuser_guid_byhour)):
      
        # for each person, save their dictionary that contatins their actions
        user_actions = pp_views_byuser_guid_byhour[x]['condition']
      
        # loop through each item in their 'buckets' which in this case is each timestamp
        for j in range(len(user_actions['buckets'])):
    
          sloan_coi_cond = user_actions['buckets'][j]['key_as_string']
          user_views = user_actions['buckets'][j]['views_per_hour']['buckets']
 
          for k in range(len(user_views)):

            # append new row with info for the preprint, the user, the timestamp, and the action count
            new_row = [{'guid':guid,'user_id':pp_views_byuser_guid_byhour[x]['key'],'coi_shown':sloan_coi_cond,'date':user_views[k]['key_as_string'],'view_count': user_views[k]['doc_count']}]
    
            views_byhour_nosloanid = views_byhour_nosloanid.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r views by hour no sloan_id}
views_byhour_nosloanid <- py$views_byhour_nosloanid %>%
                                      as_tibble() %>%
                                      unnest(view_count)

write_csv(views_byhour_nosloanid, 'views_byhour_nosloanid.csv')

views_bysecond_nosloanid_pull <- views_byhour_nosloanid %>%
                                    mutate(date = ymd_hms(date)) %>%
                                    filter(view_count > 0) %>%
                                    group_by(guid, user_id, coi_shown) %>%
                                    summarize(earliest_view = min(date)) %>%
                                    ungroup() %>%
                                    select(guid, earliest_view) %>% 
                                    distinct(.keep_all = T) %>%
                                    rename(gte = earliest_view) %>%
                                      mutate(lt = gte + hours(1),
                                               lt_string = gsub(" ", "T", lt),
                                               gte_string = gsub(" ", "T", gte))
                                      
```

```{python get missing sloan_id pp views per second}
pp = r.views_bysecond_nosloanid_pull #get pandas df of R object

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}views/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
views_bysecond_nosloanid = pd.DataFrame(columns=['guid', 'user_id', 'coi_shown', 'date', 'view_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  
  # set up query for getting views per preprint per user_id in timeframe
  query = {
      "query": {
    "bool": {
      "must": {
        "term" : {"preprint_id" : guid}
      },
      "must_not": {
        "exists": {
          "field": "sloan_id"
        }
      }
    }
  },
       "aggs" : {
          "views_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
              "aggs": {
                  "users" : {
                      "terms" : {
                          "field" : "user_id",
                          "size" : 5000
                      },
                      "aggs" : {
                      "condition" : {
                        "terms" : {
                          "field" : "sloan_coi",
                          "size" : 5000
                        },
                      "aggs": {
                        "views_per_second" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"second",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
      }
  }
}
}
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_views_byuser_guid_bysecond = res.json()['aggregations']['views_timeframe']['users']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_views_byuser_guid_bysecond) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_views_byuser_guid_bysecond)):
      
        # for each person, save their dictionary that contatins their actions
        user_actions = pp_views_byuser_guid_bysecond[x]['condition']
      
        # loop through each item in their 'buckets' which in this case is each timestamp
        for j in range(len(user_actions['buckets'])):
    
          sloan_coi_cond = user_actions['buckets'][j]['key_as_string']
          user_views = user_actions['buckets'][j]['views_per_second']['buckets']
 
          for k in range(len(user_views)):

            # append new row with info for the preprint, the user, the timestamp, and the action count
            new_row = [{'guid':guid,'user_id':pp_views_byuser_guid_bysecond[x]['key'],'coi_shown':sloan_coi_cond,'date':user_views[k]['key_as_string'],'view_count': user_views[k]['doc_count']}]
    
            views_bysecond_nosloanid = views_bysecond_nosloanid.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r views by second no sloan_id}
views_bysecond_nosloanid <- py$views_bysecond_nosloanid %>%
                                      as_tibble() %>%
                                      unnest(view_count)

write_csv(views_bysecond_nosloanid, 'views_bysecond_nosloanid.csv')
```

```{python get missing sloan_id pp downloads}
pp = r.preprint_pull_dates #get pandas df of R object

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}downloads/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
downloads_byhour_nosloanid = pd.DataFrame(columns=['guid', 'user_id', 'coi_shown', 'date', 'download_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  
  # set up query for getting downloads per preprint per user_id in timeframe
  query = {
      "query": {
    "bool": {
      "must": {
        "term" : {"preprint_id" : guid}
      },
      "must_not": {
        "exists": {
          "field": "sloan_id"
        }
      }
    }
  },
       "aggs" : {
          "downloads_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
              "aggs": {
                  "users" : {
                      "terms" : {
                          "field" : "user_id",
                          "size" : 5000
                      },
                      "aggs" : {
                      "condition" : {
                        "terms" : {
                          "field" : "sloan_coi",
                          "size" : 5000
                        },
                      "aggs": {
                        "downloads_per_hour" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"hour",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
      }
  }
}
}
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_downloads_byuser_guid_byhour = res.json()['aggregations']['downloads_timeframe']['users']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_downloads_byuser_guid_byhour) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_downloads_byuser_guid_byhour)):
      
        # for each person, save their dictionary that contatins their actions
        user_actions = pp_downloads_byuser_guid_byhour[x]['condition']
      
        # loop through each item in their 'buckets' which in this case is each timestamp
        for j in range(len(user_actions['buckets'])):
    
          sloan_coi_cond = user_actions['buckets'][j]['key_as_string']
          user_downloads = user_actions['buckets'][j]['downloads_per_hour']['buckets']
 
          for k in range(len(user_downloads)):

            # append new row with info for the preprint, the user, the timestamp, and the action count
            new_row = [{'guid':guid,'user_id':pp_downloads_byuser_guid_byhour[x]['key'],'coi_shown':sloan_coi_cond,'date':user_downloads[k]['key_as_string'],'download_count': user_downloads[k]['doc_count']}]
    
            downloads_byhour_nosloanid = downloads_byhour_nosloanid.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r downloads by hour no sloan_id}
downloads_byhour_nosloanid <- py$downloads_byhour_nosloanid %>%
                                      as_tibble() %>%
                                      unnest(download_count)

write_csv(downloads_byhour_nosloanid, 'downloads_byhour_nosloanid.csv')

downloads_bysecond_nosloanid_pull <- downloads_byhour_nosloanid %>%
                                    mutate(date = ymd_hms(date)) %>%
                                    filter(download_count > 0) %>%
                                    group_by(guid, user_id, coi_shown) %>%
                                    summarize(earliest_download = min(date)) %>%
                                    ungroup() %>%
                                    select(guid, earliest_download) %>% 
                                    distinct(.keep_all = T) %>%
                                    rename(gte = earliest_download) %>%
                                      mutate(lt = gte + hours(1),
                                               lt_string = gsub(" ", "T", lt),
                                               gte_string = gsub(" ", "T", gte))
```

```{python get missing sloan_id pp downloads per second}
pp = r.downloads_bysecond_nosloanid_pull #get pandas df of R object

import requests
import pandas as pd
import time

METRICS_BASE = r.url
TOKEN = r.osf_auth

headers = {
    'Content-Type': 'application/vnd.api+json',
    'Authorization': 'Bearer {}'.format(TOKEN)
}

post_url = '{}downloads/'.format(METRICS_BASE)

# set up empty dataframe with the right column names
downloads_bysecond_nosloanid = pd.DataFrame(columns=['guid', 'user_id', 'coi_shown', 'date', 'download_count'])
empty_lists = 0
calls = 1

# loop through all the guids that need to be called that day
for i in range(len(pp.guid)):

  # get guid, and start and end days to use in query for each preprint
  lte_date = pp['lt_string'][i]
  gte_date = pp['gte_string'][i]
  guid = pp['guid'][i]
  
  
  # set up query for getting downloads per preprint per user_id in timeframe
  query = {
      "query": {
    "bool": {
      "must": {
        "term" : {"preprint_id" : guid}
      },
      "must_not": {
        "exists": {
          "field": "sloan_id"
        }
      }
    }
  },
       "aggs" : {
          "downloads_timeframe": {
              "filter": {
                  "range" : {
                      "timestamp" : {
                          "gte" : gte_date,
                          "lte" : lte_date
                      }
                  }
              },
              "aggs": {
                  "users" : {
                      "terms" : {
                          "field" : "user_id",
                          "size" : 5000
                      },
                      "aggs" : {
                      "condition" : {
                        "terms" : {
                          "field" : "sloan_coi",
                          "size" : 5000
                        },
                      "aggs": {
                        "downloads_per_second" : {
                          "date_histogram" :{
                            "field":"timestamp",
                            "interval":"second",
                            "format": "yyyy-MM-dd HH:mm:ss"
                          }
                        }
                      }
                  }
              }
          }
      }
  }
}
}
  
  payload = {
      'data': {
          'type': 'preprint_metrics',
          'attributes': {
              'query': query
          }
      }
  }
  
  # make call using query & payload from above
  res = requests.post(post_url, headers=headers, json=payload)
  pp_downloads_byuser_guid_bysecond = res.json()['aggregations']['downloads_timeframe']['users']['buckets']

  calls = calls + 1
  
  if calls % 100 == 0:
    time.sleep(1)
  
  else:
  
    if len(pp_downloads_byuser_guid_bysecond) > 0: # only parse query output if there were actually views/downloads to parse
    
      # loop through each person in output
      for x in range(len(pp_downloads_byuser_guid_bysecond)):
      
        # for each person, save their dictionary that contatins their actions
        user_actions = pp_downloads_byuser_guid_bysecond[x]['condition']
      
        # loop through each item in their 'buckets' which in this case is each timestamp
        for j in range(len(user_actions['buckets'])):
    
          sloan_coi_cond = user_actions['buckets'][j]['key_as_string']
          user_downloads = user_actions['buckets'][j]['downloads_per_second']['buckets']
 
          for k in range(len(user_downloads)):

            # append new row with info for the preprint, the user, the timestamp, and the action count
            new_row = [{'guid':guid,'user_id':pp_downloads_byuser_guid_bysecond[x]['key'],'coi_shown':sloan_coi_cond,'date':user_downloads[k]['key_as_string'],'download_count': user_downloads[k]['doc_count']}]
    
            downloads_bysecond_nosloanid = downloads_bysecond_nosloanid.append(new_row, ignore_index = True)
      
    else:
      empty_lists = empty_lists + 1
```

```{r downloads by second no sloan_id}
downloads_bysecond_nosloanid <- py$downloads_bysecond_nosloanid %>%
                                      as_tibble() %>%
                                      unnest(download_count)

write_csv(downloads_bysecond_nosloanid, 'downloads_bysecond_nosloanid.csv')
```