Ytukyg

I'm trying to create a mini program that will calculate the closest, open restaurant closest to my location. I have a dataset that includes restaurant names, locations, stars, and hours. However, there is a problem: Sometimes a restaurant will have multiple open/close times in a day.

For example:

Name, location, type, and hours



Blue Duck Tavern, 1201 24th St NW, American Restaurant, 6:30-10:30AM, 11:30AM-2PM,5:30-10:30PM

I'm trying to get the data into a CSV, but for restaurants with multiple hours (like in the example), it can't properly parse it.

The easiest solution for this would (I think) create another line with the same information, but the next set of hours. So, the example would then read:

Blue Duck Tavern, 1201 24th St NW, American Restaurant, 6:30-10:30AM



Blue Duck Tavern, 1201 24th St NW, American Restaurant, 11:30AM-2PM



Blue Duck Tavern, 1201 24th St NW, American Restaurant, 5:30-10:30PM

So the program wouldn't show the restaurant if it wasn't open.

So I have three general questions.
1) Is there a better way to go about this than the solution I mentioned above (creating a new row for every iteration of multiple open/close hours)
2) Below, I'm having trouble with the following implementation:

import pandas as pd

import numpy as np



data = pd.import_csv(data.csv)

for row in data: 

    if data['hours'].str.contains(',') == 'True':

        count = data['hours'].str.count(',')

        data.append.. 

        <create new row with Name[row], location[row], type[row], and hours[row] for the # of count>

I've tried google-ing around, and I get this error: ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

So I tried to switch it up to:

if data['Monday'].any('Monday').str.contains(',') == 'True': 

which results in: ValueError: No axis named Monday for object type

And I'm a bit unclear on the next steps here, or what I'm doing wrong, because if I just do:

print data[data['Monday'].astype(str).str.contains(',')]

It works and returns the result. But I can't do any kind of conditional without it throwing an error.

3) I'm also a bit confused on what to do if there are more than one comma in the row.. I have a vague idea, but if you have any hints, I'd love to hear them :)

Thanks for reading!

If I understand correctly, you can load the data with a regular expression as the separator, making sure that what precedes the comma is not AM or PM (using a negative lookbehind). You can then use str.split and stack, after setting all the columns that you don't want to modify to the index. For example:

data = pd.read_csv('data.csv', sep='(?<!AM|PM),')

# Get rid of spaces in your column names

data.columns = data.columns.str.strip(' ')



>>> data

               Name          location                  type   hours

0  Blue Duck Tavern   1201 24th St NW   American Restaurant   6:30-10:30AM, 11:30AM-2PM,5:30-10:30PM 





new_data = (data.set_index(['Name', 'location', 'type'])

          .hours.str.split(',', expand=True)

          .stack()

          .reset_index(level=['Name', 'location', 'type']))



>>> new_data

               Name          location                  type              0

0  Blue Duck Tavern   1201 24th St NW   American Restaurant   6:30-10:30AM

1  Blue Duck Tavern   1201 24th St NW   American Restaurant    11:30AM-2PM

2  Blue Duck Tavern   1201 24th St NW   American Restaurant   5:30-10:30PM

try to combine multiple hours with '_' or any other delimiter as mentioned below and take it as a whole.

6:30-10:30AM_11:30AM-2PM_5:30-10:30PM

Blue Duck Tavern, 1201 24th St NW, American Restaurant, 6:30-10:30AM_11:30AM-2PM_5:30-10:30PM