Creating a dataframe on Date from two incomplete, not same size dataframes

user_7611112 0
Viewed 202

I am trying to put together a big dataframe wih dates, average sentiment scores (from Twitter), and closing stock price.

Here is what I have so far.

#imports
import pandas as pd
import matplotlib
import matplotlib.pyplot as plt
import numpy as np
import re
import urllib3
import requests
import datetime 

#mydates dataframe that just has the dates from my desired range. Shape is 2008 rows x 1 column
date1='2014-01-01'
date2='2019-07-01'
mydates =pd.date_range(date1,date2).tolist()
newdf =pd.DataFrame({'Date':mydates})

#df with the average daily sentiment scores. Large dataset with 500 rows.
#This currently skips dates that didn't have tweets.I want to include those dates but have sentiment equal 0.
Date        Score
2014-01-13  0.01
2014-01-14  0.035
2014-01-15  0.453
2014-01-20  0.06474

#ts dataframe of dates and stock prices. Shape is 1381 rows x 1 column
Date         Adj Close
2014-01-13  44.8
2014-01-14  45.3
2014-01-15  45.8
2014-01-16  46.5
2014-01-17  46.5
2014-01-21  46.7

Desired output

Date        Score   Close Price
2014-01-13  0.01     44.8
2014-01-14  0.035    45.3
2014-01-15  0.453    45.8
2014-01-16  0.0      46.5
2014-01-17  0.0      46.5
2014-01-18  0.0      46.5
2014-01-19  0.0      46.5
2014-01-20  0.06474  46.5

My plan is to then save this dataset as a csv.

Issues I've run into: Df and ts are NOT the same size. I'd need to go through ts to make all the weekend close prices the same as Friday. How do I do that? Not knowing how to write a loop that can assign the score for a date in one dataframe to a column in another dataframe.

I use pandas-3.

pandas sentiment-analysis stock dataframe python-3.x
3 Answers

Set the index of df as Date and use DataFrame.asfreq to reindex the dataframe on daily frequency, then using DataFrame.merge left merge it with ts on column Date, finally use Series.ffill on column Adj Close:

df1 = (
    df.set_index('Date').
    asfreq('D', fill_value=0).reset_index().merge(ts, on='Date', how='left')
)
df1['Adj Close'] = df1['Adj Close'].ffill()

Result:

print(df1)
        Date    Score  Adj Close
0 2014-01-13  0.01000       44.8
1 2014-01-14  0.03500       45.3
2 2014-01-15  0.45300       45.8
3 2014-01-16  0.00000       46.5
4 2014-01-17  0.00000       46.5
5 2014-01-18  0.00000       46.5
6 2014-01-19  0.00000       46.5
7 2014-01-20  0.06474       46.5
user_128331660

To merge two pandas dataframes on a common column, use the merge function. To specify that you want to keep rows with dates in ts, but not in df, use the how='left' option, like so:

ts.merge(df, on='Date', how='left', sort=False)

# output:
# Date       Close Score
# 2014-01-13 44.8 0.010
# 2014-01-14 45.3 0.035
# 2014-01-15 45.8 0.453
# 2014-01-16 46.5 NaN
# 2014-01-17 46.5 NaN
# 2014-01-21 46.7 NaN

You can also use .fillna(0) method to replace the NaN values with 0, as in your desired output.

ts.merge(df, on='Date', how='left', sort=False).fillna(0)

# output:
# Date       Close Score
# 2014-01-13 44.8 0.010
# 2014-01-14 45.3 0.035
# 2014-01-15 45.8 0.453
# 2014-01-16 46.5 0
# 2014-01-17 46.5 0
# 2014-01-21 46.7 0

This is a common operation known as a left join, which is why you have to specify how='left'.

A left join will keep all rows from the first table, regardless of whether there is a matching row in the second table. The final result will keep all rows of the first table but will omit the un-matched row from the second table. Here's how it works:

enter image description here

user_72280090

You can use pd.date_range with df.reindex:

df['Date'] = pd.to_datetime(df['Date'])
r = pd.date_range(start=df.Date.min(), end=df.Date.max())

df = df.set_index('Date').reindex(r).fillna(0.0).rename_axis('Date').reset_index()

ts['Date'] = pd.to_datetime(ts['Date'])
r1 = pd.date_range(start=ts.Date.min(), end=ts.Date.max()) 

ts = ts.set_index('Date').reindex(r1).ffill().rename_axis('Date').reset_index()

result = df.merge(ts, on='Date')

print(result)

Output:

        Date    Score  Adj_Close
0 2014-01-13  0.01000       44.8
1 2014-01-14  0.03500       45.3
2 2014-01-15  0.45300       45.8
3 2014-01-16  0.00000       46.5
4 2014-01-17  0.00000       46.5
5 2014-01-18  0.00000       46.5
6 2014-01-19  0.00000       46.5
7 2014-01-20  0.06474       46.5
user_58208140
Related