I've been trying various ways from all across the internet to take a list that has 27 cities and create a dataframe that has 151 instances of the city name for EACH of those 27 cities grouped in the order I put them in the list (4077 row total).
I've tried various ways of isolating the data I need by using .loc but the problem there is that there are cities that share the same name in the excel file I've imported. Those cities with the same name do have different state abbreviations but I can't find anything (I can understand) on if I can drop the rows based on the state and name. Is there a way to do that?
Another thing I tried was to create a list of the 27 city names and multiply it by 151. But that doesn't work for this because I need the data to read out as I've made it in the list and not just repeat the list itself 27 times.
I'm writing this from my phone so I don't have the code to paste in here but:
Let's say I needed this for just three cities and I wanted it to create a df with 5 instances (15 rows) for each of the 3 cities listed in their respective order as a small scale example:
City_name
- Philadelphia
- Boston
- Chicago
I'm trying to get something that looks like this:
City_name
- Philadelphia
- Philadelphia
- Philadelphia
- Philadelphia
- Philadelphia
- Boston
- Boston
- Boston
- Boston
- Boston
- Chicago
- Chicago
- Chicago
- Chicago
- Chicago
(Forgive me, I don't know how to format here)
How can I best achieve this without writing 27 dataframes (1 for each city), multiply those dataframes individually to get the 151 instances/rows, and then append them later?
I can do it that way but I'm sure there must be a cleaner method that I haven't been able to find/understand on the internet.
Thank you!
CodePudding user response:
Have you tried np.repeat
? You have your list with the 27 cities, and np.repeat` you change the number to the repetitions you want.
City_name = ["Philadelphia", "Boston", "Chicago"]
df = pd.DataFrame({"City_name": np.repeat(City_name, repeats=5)})
print(df)
City_name
0 Philadelphia
1 Philadelphia
2 Philadelphia
3 Philadelphia
4 Philadelphia
5 Boston
6 Boston
7 Boston
8 Boston
9 Boston
10 Chicago
11 Chicago
12 Chicago
13 Chicago
14 Chicago