how to load and save pandas data frame to excel .csv file

CODE:-

from datetime import date
from datetime import timedelta
from nsepy import get_history
import pandas as pd



end1 = date.today()
start1 = end1 - timedelta(days=25)
exp_date1 = date(2022,8,25)
exp_date2 = date(2022,9,29)

# stock = ['HDFCLIFE']
stock = ['RELIANCE','HDFCBANK','INFY','ICICIBANK','HDFC','TCS','KOTAKBANK','LT','SBIN','HINDUNILVR','AXISBANK',
         'ITC','BAJFINANCE','BHARTIARTL','ASIANPAINT','HCLTECH','MARUTI','TITAN','BAJAJFINSV','TATAMOTORS',
         'TECHM','SUNPHARMA','TATASTEEL','M&M','WIPRO','ULTRACEMCO','POWERGRID','HINDALCO','NTPC','NESTLEIND',
         'GRASIM','ONGC','JSWSTEEL','HDFCLIFE','INDUSINDBK','SBILIFE','DRREDDY','ADANIPORTS','DIVISLAB','CIPLA',
         'BAJAJ-AUTO','TATACONSUM','UPL','BRITANNIA','BPCL','EICHERMOT','HEROMOTOCO','COALINDIA','SHREECEM','IOC']

target_stocks = []
# oi_change = []
for stock in stock:
    stock_jan = get_history(symbol=stock,
                        start=start1,
                        end=end1,
                        futures=True,
                        expiry_date=exp_date1)
    stock_feb = get_history(symbol=stock,
                        start=start1,
                        end=end1,
                        futures=True,
                        expiry_date=exp_date2)
    delivery_per_age = get_history(symbol=stock,
                               start=start1,
                               end=end1)
    symbol_s = get_history(symbol=stock,
                       start=start1,
                       end=end1)
    oi_combined = pd.concat([stock_jan['Change in OI'] + stock_feb['Change in OI']])
    total_oi = pd.concat([stock_jan['Open Interest']+stock_feb['Open Interest']])
    delivery_vol = pd.concat([delivery_per_age['Deliverable Volume']])
    # delivery_per = pd.concat([delivery_per_age['%Deliverble']*100])
    na_me = pd.concat([symbol_s['Symbol']])
    close = pd.concat([delivery_per_age['Close']])
    df = pd.DataFrame(na_me)
    df['TOTAL_OPN_INT'] = total_oi
    df['OI_COMBINED'] = oi_combined
    df['%_CHANGE'] = ((df['OI_COMBINED'] / df['TOTAL_OPN_INT']) * 100).__round__(2)
    df['AVG_OI_COMBINED'] = df['OI_COMBINED'].rolling(5).mean()
    # df['DELIVERY_VOL'] = delivery_vol
    # df['AVG_DELIVERY_VOL'] = df['DELIVERY_VOL'].rolling(5).mean()
    # df['DELIVERY_PER'] = delivery_per
    # df['AVG_DELIVERY_%'] = df['DELIVERY_PER'].rolling(5).mean()
    df['_CLOSE_PRICE_'] = close
    pd.set_option('display.max_columns',8)
    pd.set_option('display.width',200)
    # print(df)

    cond = ((df.loc[df.index[-5:-1], '%_CHANGE'].agg(min) > 0) |(df.loc[df.index[-6:-1], '%_CHANGE'].agg(min) > 0)) & (df.loc[df.index[-1], '%_CHANGE'] < 0)
    if(cond):
        target_stocks.append(df)
print(target_stocks)


OUTPUT:-
      From above code I am getting the output for the day 11-aug-2022 which is displayed below.


[               Symbol  TOTAL_OPN_INT  OI_COMBINED  %_CHANGE  AVG_OI_COMBINED  _CLOSE_PRICE_
Date                                                                                       
2022-07-18  EICHERMOT         489650        61250     12.51              NaN        3036.50
2022-07-19  EICHERMOT         547400        57750     10.55              NaN        3077.70
2022-07-20  EICHERMOT         556150         8750      1.57              NaN        3045.00
2022-07-21  EICHERMOT         572250        16100      2.81              NaN        3081.20
2022-07-22  EICHERMOT         728000       155750     21.39          59920.0        3147.60
2022-07-25  EICHERMOT        1358700       630700     46.42         173810.0        3086.70
2022-07-26  EICHERMOT        1789900       431200     24.09         248500.0        3023.30
2022-07-27  EICHERMOT        2226700       436800     19.62         334110.0        3057.40
2022-07-28  EICHERMOT        2843750       617050     21.70         454300.0        3054.00
2022-07-29  EICHERMOT        2878400        34650      1.20         430080.0        3093.45
2022-08-01  EICHERMOT        3047100       168700      5.54         337680.0        3088.40
2022-08-02  EICHERMOT        3491250       444150     12.72         340270.0        3120.95
2022-08-03  EICHERMOT        3871700       380450      9.83         329000.0        3138.20
2022-08-04  EICHERMOT        3943100        71400      1.81         219870.0        3145.80
2022-08-05  EICHERMOT        4058950       115850      2.85         236110.0        3089.60
2022-08-08  EICHERMOT        4060000         1050      0.03         202580.0        3116.75
2022-08-10  EICHERMOT        4165000       105000      2.52         134750.0        3154.55
2022-08-11  EICHERMOT        3880450      -284550     -7.33           1750.0        3176.45,                Symbol  TOTAL_OPN_INT  OI_COMBINED  %_CHANGE  AVG_OI_COMBINED  _CLOSE_PRICE_
Date                                                                                       
2022-07-18  COALINDIA        7631400      1965600     25.76              NaN         195.60
2022-07-19  COALINDIA        8400000       768600      9.15              NaN         198.25
2022-07-20  COALINDIA        9361800       961800     10.27              NaN         197.85
2022-07-21  COALINDIA       10042200       680400      6.78              NaN         198.60
2022-07-22  COALINDIA       11020800       978600      8.88        1071000.0         197.10
2022-07-25  COALINDIA       18131400      7110600     39.22        2100000.0         200.90
2022-07-26  COALINDIA       25368000      7236600     28.53        3393600.0         202.30
2022-07-27  COALINDIA       29454600      4086600     13.87        4018560.0         203.45
2022-07-28  COALINDIA       31941000      2486400      7.78        4379760.0         202.85
2022-07-29  COALINDIA       33121200      1180200      3.56        4420080.0         211.25
2022-08-01  COALINDIA       32928000      -193200     -0.59        2959320.0         212.75
2022-08-02  COALINDIA       33398400       470400      1.41        1606080.0         215.25
2022-08-03  COALINDIA       32646600      -751800     -2.30         638400.0         212.10
2022-08-04  COALINDIA       33734400      1087800      3.22         358680.0         207.15
2022-08-05  COALINDIA       33780600        46200      0.14         131880.0         208.45
2022-08-08  COALINDIA       37044000      3263400      8.81         823200.0         215.40
2022-08-10  COALINDIA       38186400      1142400      2.99         957600.0         219.85
2022-08-11  COALINDIA       35653800     -2532600     -7.10         601440.0         218.60,               Symbol  TOTAL_OPN_INT  OI_COMBINED  %_CHANGE  AVG_OI_COMBINED  _CLOSE_PRICE_
Date                                                                                      
2022-07-18  SHREECEM          30675         2850      9.29              NaN       20055.70
2022-07-19  SHREECEM          34800         4125     11.85              NaN       20068.20
2022-07-20  SHREECEM          38250         3450      9.02              NaN       20208.20
2022-07-21  SHREECEM          41800         3550      8.49              NaN       20442.95
2022-07-22  SHREECEM          58250        16450     28.24           6085.0       20780.00
2022-07-25  SHREECEM         118700        60450     50.93          17605.0       20679.05
2022-07-26  SHREECEM         194375        75675     38.93          31915.0       20652.35
2022-07-27  SHREECEM         241500        47125     19.51          40650.0       21023.50
2022-07-28  SHREECEM         285400        43900     15.38          48720.0       20415.45
2022-07-29  SHREECEM         294975         9575      3.25          47345.0       20498.00
2022-08-01  SHREECEM         295275          300      0.10          35315.0       20947.00
2022-08-02  SHREECEM         297050         1775      0.60          20535.0       21110.95
2022-08-03  SHREECEM         303500         6450      2.13          12400.0       20956.45
2022-08-04  SHREECEM         319375        15875      4.97           6795.0       20687.90
2022-08-05  SHREECEM         322725         3350      1.04           5550.0       21237.40
2022-08-08  SHREECEM         327450         4725      1.44           6435.0       21195.60
2022-08-10  SHREECEM         333275         5825      1.75           7245.0       21104.90
2022-08-11  SHREECEM         332225        -1050     -0.32           5745.0       21192.95]

Now when I run the code I am getting this output. How to load this output into the excel as .csv file with the name as ’11-08-2022.csv’. And suppose if I run the code on 12-08-2022 then another .csv file should add in the same folder where first .csv file has saved and now the file name should be 12-08-2022……in this way when I run the code each time there should be one .csv file created with the name as todays date.

thank you.

Answer

first import libraries

import pandas as pd
import datetime

to load data from csv file

df = pd.read_csv('file_path')

example

df = pd.read_csv('/content/sample_data/california_housing_test.csv')

to save data to csv file

df.to_csv('file_name')

file will be saved to your current directory

to save some other folder

df.to_csv('absolute_path/file_name.csv')

example

df2.to_csv('/content/drive/MyDrive/some_folder/modified_df.csv')

in your case, you want something like this

file_name = f'{datetime.datetime.now().day}-{datetime.datetime.now().month}-{datetime.datetime.now().year}.csv'

target_stocks.to_csv(f'file_path/{file_name}')