Python: Merge Pandas Data Frame with Pivot Table -

-



i have 2 following dataframes want merge.

df1:      id   time                  station 0       22.08.2017 12:00:00   a1 1     b   22.08.2017 12:00:00   a3 2       22.08.2017 13:00:00   a2 ...  pivot:       station               a1     a2     a3 0     time 1     22.08.2017 12:00:00   10     12     11 2     22.08.2017 13:00:00   9      7      3 3     22.08.2017 14:00:00   2      3      4 4     22.08.2017 15:00:00   3      2      7 ...

it should like:

merge:       id   time                  station   value 0       22.08.2017 12:00:00   a1        10 1     b   22.08.2017 12:00:00   a3        11 2       22.08.2017 13:00:00   a2        7 ...

now want add column in data frame right value pivot table. failed including column labels merge. constructed that, not work: 

merge = pd.merge(df1, pivot, how="left", left_on=["time", "station"], right_on=["station", pivot.columns])

any help?

edit:

as advised, instead of pivot table tried use following data:

df2: time                 station   value 22.08.2017 12:00:00  a1        10 22.08.2017 12:00:00  a2        12 22.08.2017 12:00:00  a3        11               ... 22.08.2017 13:00:00  a1        9 22.08.2017 13:00:00  a2        7 22.08.2017 13:00:00  a3        3

the table contains 1300 different stations every timestamp. in have more 115.000.000 rows. df1 have 5.000.000 rows.

now tried merge df1.head(100) , df2, in result values nan. therefore used this:

merge = pd.merge(df1.head(100), df2, how="left", on=["time", "station"])

another problem merge takes few minutes expect whole df1 take several days.

i guess got dataframe pivot using either pivot or pivot_table in pandas, if can perform merge using dataframe had before pivot should work fine.

otherwise have reverse pivot using melt before merging:

melt = pd.concat([pivot[['time']],pivot[['a1']].melt()],axis = 1) melt = pd.concat([melt,pd.concat([pivot[['time']],pivot[['a2']].melt()],axis = 1)]) melt = pd.concat([melt,pd.concat([pivot[['time']],pivot[['a3']].melt()],axis = 1)]) melt.columns = ['time','station','value']

then perform merge expected it:

my_df.merge(melt,on = ['time','station'])      id  time    station value 0     time1   a1      10 1   b   time1   a3      11 2     time2   a2      7

edit:

if dataframes big in edit, indeed have perform merges on chunks of them. try reduce chunk both dataframes.

first, sort df1 in order have close values of time:

df1.sort_values('time',inplace = true)

then chunk it, chunk second dataframe in way sure have rows might need, , merge chunks:

chunk1 = df1.head(100) chunk2 = df2.loc[df2.time.between(chunk1.time.min(),chunk1.time.max())] merge = chunk1.merge(chunk2,on = ['time','station'],how = 'left')




wiki

Comments

Post a Comment

Popular posts from this blog

python - Read npy file directly from S3 StreamingBody -

-
i have many .npy (numpy) , .json files saved in s3, need download , load these files. for json files downloading files , loading dictionary using following code: obj = s3.object(bucket, key) response = obj.get() body = response['body'].read().decode('utf-8') data = json.loads(body) that way avoid writing .json file disk , reading it. now want same .npy files, equivalent? currently downloading file, saving temp file on disk , loading np.load avoid if possible. following https://stackoverflow.com/a/28196540/3509999 : obj = s3.object(bucket, key) io.bytesio(obj.get()["body"].read()) f: # rewind file f.seek(0) arr = np.load(f) wiki
Read more

kotlin - Out-projected type in generic interface prohibits the use of metod with generic parameter -

-
i start using kotlin , defined interface this: interface aadapter<vh : recyclerview.viewholder> { fun oncreateaviewholder(parent: viewgroup): vh fun onbindaviewholder(v: vh, position: int) } and when try use on code: class klasa ( private val adapter: aadapter<*> ) { fun dosth(){ //... val vh = this.adapter.oncreateaviewholder(parent) //on below line error adapter.onbindaviewholder(v, position) //... } } i error out-projected type 'aadapter<*>' prohibits use of 'public abstract fun onbindaviewholder(v: t, position: int): unit i tied add "in" or "out" definition i'm confused. how allow it. replace aadapter<*> aadapter<recyclerview.viewholder> . class klasa ( private val adapter: aadapter<recyclerview.viewholder> ) { // ... because have declared aadapter has type parameter vh type subclass of recyclerview....
Read more

Asterisk AGI Python Script to Dialplan does not work -

-
i'm stuck project have check database of sip extension incoming caller id , dial sip extension. below receive when run script on asterisk cli. -- registered sip '9125' @ 192.168.1.102:50142 > saved useragent "zoiper rv2.8.40" peer 9125 [2017-08-22 13:53:33] notice[5095]: chan_sip.c:24558 handle_response_peerpoke: peer '9125' reachable. (17ms / 2000ms) == using sip rtp tos bits 184 == using sip rtp cos mark 5 -- executing [0112617769@from-trunk:1] answer("sip/obitrunk1-0000000d", "") in new stack > 0x1ca50d0 -- probation passed - setting rtp source address 192.168.1.98:16834 -- executing [0112617769@from-trunk:2] agi("sip/obitrunk1-0000000d", "test.py") in new stack -- launched agi script /var/lib/asterisk/agi-bin/test.py args: ['/var/lib/asterisk/agi-bin/test.py'] env line: agi_request: test.py env line: agi_channel: sip/obitrunk1-0000000d env line: agi_language: en env line: a...
Read more