Auto Transform into New Data

Auto Transform into New Data - Pandas

I am a newbie in python, I search in StackOverflow for my case but I couldn't find the technical answer. I have a big number of BS row.

my problem like this, I have a dataframe:

dataframe

df BS N BS1 - BS5 1 BS2 - BS7 2 BS1 - BS9 2 BS9 - BS1 1

I want to make new data automatically. My expected result like this:

New_BS BS1 - BS5 BS2 - BS7 BS1 - BS9 BS9 - BS1 Total BS1-2 1 2 3 BS2-3 1 2 2 5 BS3-4 1 2 2 5 BS4-5 1 2 2 5 BS5-6 2 2 4 BS6-7 2 2 4 BS7-8 2 2 BS8-9 2 2 BS9-8 1 1 BS8-7 1 1 BS7-6 1 1 BS6-5 1 1 BS5-4 1 1 BS4-3 1 1 BS3-2 1 1 BS2-1 1 1

thank you in advance for helping me

It is not clear at all to me how you got those rows. What is the difference from BS7-8 and BS7-5 e.g.?
– RafaelC
Jul 2 at 2:02

BS7-8

BS7-5

this data about how many people moving from bus stop to bus stop (BS)... BS7-8 mean from bus stop 7 to 8. BS7-5 mean someone from BS7 go to BS 5.
– Arief
Jul 2 at 2:23

Is the data in BS always the same format? Is the stops always from 1 to 9? This maybe impossible without knowing how to iterate through the Bus Stops. For example, how would you know that going from BS9 - BS1 involves BS9->BS7->BS5->BS4 when BS1 - BS9 goes in sequential order?
– Bill Armstrong
Jul 2 at 4:39

BS

BS9 - BS1

BS9->BS7->BS5->BS4

BS1 - BS9

@BillArmstrong , BS1-9 means someone is moving from BS1 to BS 9, but the bus will stop at every BUS stop such as BS2, BS3, BS4, BS5, BS6, BS7 and BS8. in this case I want to know whether someone moving from BS1 to BS9 passes every stop bus.
– Arief
Jul 2 at 5:49

What about from BS9 - BS1? This doesn't seem to follow that same pattern.
– Bill Armstrong
Jul 2 at 5:52

BS9 - BS1

1 Answer
1

Well - it's a total hack - but it was fun...

import pandas as pd import numpy as np df = df_flat = pd.DataFrame({"BS": ['BS1 - BS5', 'BS2 - BS7', 'BS1 - BS9', 'BS9 - BS1'], "N" : [1, 2, 2, 1]}) df = df.pivot(columns='BS', values='N') df_flat = df_flat.pivot_table( columns='BS', values='N') for column_name, column in zip(list(df), df): if int(column[2:3]) < int(column[8:9]): for stop in range(int(column[2:3]), int(column[8:9])): index = "BS" + str(stop) + "-" + str(stop + 1) if index not in list(df.index.values): df.loc[index] = np.nan df.loc[index, column] = df_flat.loc['N', column] else: for stop in range(int(column[2:3]), int(column[8:9]), -1): index = "BS" + str(stop) + "-" + str(stop - 1) if index not in list(df.index.values): df.loc[index] = np.nan df.loc[index, column] = df_flat.loc['N', column] df['Total'] = df.sum(axis=1) df = df.iloc[len(list(df_flat)):] print(df.fillna(''))

$ python bus.py BS BS1 - BS5 BS1 - BS9 BS2 - BS7 BS9 - BS1 Total BS1-2 1 2 3.0 BS2-3 1 2 2 5.0 BS3-4 1 2 2 5.0 BS4-5 1 2 2 5.0 BS5-6 2 2 4.0 BS6-7 2 2 4.0 BS7-8 2 2.0 BS8-9 2 2.0 BS9-8 1 1.0 BS8-7 1 1.0 BS7-6 1 1.0 BS6-5 1 1.0 BS5-4 1 1.0 BS4-3 1 1.0 BS3-2 1 1.0 BS2-1 1 1.0

There are about a 1,000 ways to improve on this - but its an ok start...

Note that the slicing is a very significant constraint on the data set - - You'll have to really rework this to make it dynamic.

excellent way,,, thank you in advance.. How about if my data BS N1 N2 N3?
– Arief
Jul 2 at 6:51

BS N1 N2 N3

You will have to add another iterative for each level based on the above code - but there are likely better ways to implement with a better defined workspace.
– Bill Armstrong
Jul 2 at 6:55

thank you so much for the idea,,, big insight for me
– Arief
Jul 2 at 6:56

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Gtjkyu