0

Excuse rookie errors, I'm pretty new to Python and even newer to using pandas.

The script so far counts the rows of below csv file (generated by a different script) and that value is stored as totalDailyListings correctly. There is some formatting with the date as I want to get to a point whereby this script counts the day befores listings.

The current structure of CSV file that rows are being counted from:

Address Price
123 Main St £305,000
55 Chance Way £200,000 
from datetime import date, timedelta

oneday = timedelta(days=1)
yesterdayDate = date.today() - oneday
filename = 'ballymena-' + str(yesterdayDate) + '.csv'

results = pd.read_csv(filename)
totalDailyListings = (len(results))
# calculates the correct number of rows in csv

listings = pd.DataFrame(
    [yesterdayDate, totalDailyListings], columns=['Date', 'Total'])
listings.to_csv('listings-count.csv')

I'm struggling however to create a new CSV file with just two columns: "Date" and "Total" and that would allow that CSV file to be updated (rows added) daily for long term monitoring. Can anyone offer some tips to point me back in the right direction?

Desired CSV format:

Date Total
2022-10-04 305
2022-10-05 200
cts
  • 908
  • 1
  • 9
  • 30
  • Does the initial CSV file created look ok ? And are you just trying to update that CSV file every time you run the script ? If so - here is a link on how to append to an existing CSV file: https://stackoverflow.com/questions/2363731/how-to-append-a-new-row-to-an-old-csv-file-in-python – ScottC Oct 05 '22 at 12:59
  • @cts : Skip the headers if any in your `filename` otherwise you will end up getting wrong no of rows in file. One more thing use some scheduler and looping condition with dictionary to update file based on match of key... little tricky but you will – codeholic24 Oct 05 '22 at 12:59
  • @cts : One more thing show us the data how it is in your `file` which you are reading using pandas – codeholic24 Oct 05 '22 at 13:00
  • I have updated the question to show the original CSV file format (from where the `totalDailyListings` value is being counted). I want to then take the number of rows from CSV number 1 and populate them into CSV number 2. Hope that makes the issue a little clearer and thanks for the help so far! – cts Oct 05 '22 at 13:08

1 Answers1

1

The important part is the structure of the resulting dataframe

df = pd.DataFrame({'Date':['2022-10-04', '2022-10-05'], 'Total': [305, 200]})
df.to_csv('listing-count-default_index.csv')
df.set_index('Date', inplace=True)
df.to_csv('listing-count.csv')

This should give you just two columns

Dima Chubarov
  • 16,199
  • 6
  • 40
  • 76
  • Thanks - that's helpful. But my OP outlined a desire so that the CSV file could be updated (with new rows) every time the script is run. This seems to generate two CSV files? – cts Oct 05 '22 at 13:23
  • @cts on appending to csv consider the `mode="a"` option. Check out https://stackoverflow.com/a/17975690/1328439 for details. – Dima Chubarov Oct 05 '22 at 13:32
  • Thanks - adding append mode worked. Lastly though, is it possible to achieve the same outcome (desired CSV format in OP) but just create one CSV instead of two. When removing the default I seem to lose headers etc? – cts Oct 05 '22 at 13:56
  • @cts just removing the first `df.to_csv()` call should work. I added it just for comparison. – Dima Chubarov Oct 05 '22 at 14:53