Pertanyaan Gabungkan pandas dataframe di mana satu nilai berada di antara dua lainnya [duplikat]


Pertanyaan ini sudah memiliki jawaban di sini:

Saya perlu menggabungkan dua pandas dataframe pada identifier dan kondisi di mana tanggal dalam satu dataframe adalah antara dua tanggal dalam dataframe lainnya.

Dataframe A memiliki tanggal ("fdate") dan ID ("cusip"):

enter image description here

Saya harus menggabungkan ini dengan database B:

enter image description here

di A.cusip==B.ncusip dan A.fdate adalah antara B.namedt dan B.nameenddt.

Dalam SQL ini akan sepele, tetapi satu-satunya cara saya bisa melihat bagaimana melakukan ini di panda adalah untuk pertama menggabungkan tanpa syarat pada identifier, dan kemudian filter pada kondisi tanggal:

df = pd.merge(A, B, how='inner', left_on='cusip', right_on='ncusip')
df = df[(df['fdate']>=df['namedt']) & (df['fdate']<=df['nameenddt'])]

Apakah ini cara terbaik untuk melakukan ini? Tampaknya akan jauh lebih baik jika seseorang dapat menyaring dalam penggabungan sehingga untuk menghindari potensi data yang sangat besar setelah penggabungan tetapi sebelum filter selesai.


32
2018-06-03 18:33


asal


Jawaban:


Seperti yang Anda katakan, ini cukup mudah dalam SQL, jadi mengapa tidak melakukannya di SQL?

import pandas as pd
import sqlite3

#We'll use firelynx's tables:
presidents = pd.DataFrame({"name": ["Bush", "Obama", "Trump"],
                           "president_id":[43, 44, 45]})
terms = pd.DataFrame({'start_date': pd.date_range('2001-01-20', periods=5, freq='48M'),
                      'end_date': pd.date_range('2005-01-21', periods=5, freq='48M'),
                      'president_id': [43, 43, 44, 44, 45]})
war_declarations = pd.DataFrame({"date": [datetime(2001, 9, 14), datetime(2003, 3, 3)],
                                 "name": ["War in Afghanistan", "Iraq War"]})
#Make the db in memory
conn = sqlite3.connect(':memory:')
#write the tables
terms.to_sql('terms', conn, index=False)
presidents.to_sql('presidents', conn, index=False)
war_declarations.to_sql('wars', conn, index=False)

qry = '''
    select  
        start_date PresTermStart,
        end_date PresTermEnd,
        wars.date WarStart,
        presidents.name Pres
    from
        terms join wars on
        date between start_date and end_date join presidents on
        terms.president_id = presidents.president_id
    '''
df = pd.read_sql_query(qry, conn)

df:

         PresTermStart          PresTermEnd             WarStart  Pres
0  2001-01-31 00:00:00  2005-01-31 00:00:00  2001-09-14 00:00:00  Bush
1  2001-01-31 00:00:00  2005-01-31 00:00:00  2003-03-03 00:00:00  Bush

17
2018-03-14 20:49



Anda harus dapat melakukan ini sekarang menggunakan paket pandasql

import pandasql as ps

sqlcode = '''
select A.cusip
from A
inner join B on A.cusip=B.ncusip
where A.fdate >= B.namedt and A.fdate <= B.nameenddt
group by A.cusip
'''

newdf = ps.sqldf(sqlcode,locals())

Saya pikir jawabannya dari @ChuHo bagus. Saya yakin pandasql melakukan hal yang sama untuk Anda. Saya belum mengukur keduanya, tetapi lebih mudah dibaca.


9
2017-08-04 15:32



Tidak ada cara pandamic untuk melakukan hal ini pada saat ini.

Jawaban ini digunakan untuk mengatasi masalah dengan polimorfisme, yang disebarkan menjadi ide yang sangat buruk.

Maka itu numpy.piecewise fungsi muncul dalam jawaban lain, tetapi dengan sedikit penjelasan, jadi saya pikir saya akan menjelaskan bagaimana fungsi ini dapat digunakan.

Numpy cara dengan piecewise (Memory berat)

Itu np.piecewise fungsi dapat digunakan untuk menghasilkan perilaku bergabung kustom. Ada banyak overhead yang terlibat dan tidak terlalu efisien, tetapi melakukan pekerjaan.

Memproduksi ketentuan untuk bergabung

import pandas as pd
from datetime import datetime


presidents = pd.DataFrame({"name": ["Bush", "Obama", "Trump"],
                           "president_id":[43, 44, 45]})
terms = pd.DataFrame({'start_date': pd.date_range('2001-01-20', periods=5, freq='48M'),
                      'end_date': pd.date_range('2005-01-21', periods=5, freq='48M'),
                      'president_id': [43, 43, 44, 44, 45]})
war_declarations = pd.DataFrame({"date": [datetime(2001, 9, 14), datetime(2003, 3, 3)],
                                 "name": ["War in Afghanistan", "Iraq War"]})

start_end_date_tuples = zip(terms.start_date.values, terms.end_date.values)
conditions = [(war_declarations.date.values >= start_date) &
              (war_declarations.date.values <= end_date) for start_date, end_date in start_end_date_tuples]

> conditions
[array([ True,  True], dtype=bool),
 array([False, False], dtype=bool),
 array([False, False], dtype=bool),
 array([False, False], dtype=bool),
 array([False, False], dtype=bool)]

Ini adalah daftar array di mana setiap array memberitahu kita jika rentang waktu yang cocok untuk masing-masing dari dua deklarasi perang yang kita miliki. Kondisinya dapat meledak dengan dataset yang lebih besar karena akan menjadi panjang df kiri dan df kanan dikalikan.

The "sihir" piecewise

Sekarang piecewise akan mengambil president_id dari ketentuan dan tempatkan di war_declarations dataframe untuk masing-masing perang yang sesuai.

war_declarations['president_id'] = np.piecewise(np.zeros(len(war_declarations)),
                                                conditions,
                                                terms.president_id.values)
    date        name                president_id
0   2001-09-14  War in Afghanistan          43.0
1   2003-03-03  Iraq War                    43.0

Sekarang untuk menyelesaikan contoh ini kita hanya perlu secara teratur bergabung dalam nama presiden.

war_declarations.merge(presidents, on="president_id", suffixes=["_war", "_president"])

    date        name_war            president_id    name_president
0   2001-09-14  War in Afghanistan          43.0    Bush
1   2003-03-03  Iraq War                    43.0    Bush

Polimorfisme (tidak berfungsi)

Saya ingin berbagi upaya penelitian saya, jadi bahkan jika ini tidak memecahkan masalahSaya berharap itu akan terjadi diizinkan untuk tinggal di sini sebagai balasan yang bermanfaat paling sedikit. Karena sulit menemukan kesalahan, orang lain dapat mencoba ini dan berpikir mereka memiliki solusi yang berfungsi, padahal sebenarnya tidak.

Satu-satunya cara lain yang bisa saya ketahui adalah dengan membuat dua kelas baru, satu PointInTime dan satu Timespan

Keduanya seharusnya __eq__ metode di mana mereka mengembalikan nilai true jika PointInTime dibandingkan dengan Timespan yang memuatnya.

Setelah itu Anda dapat mengisi DataFrame Anda dengan objek-objek ini, dan bergabung di kolom tempat mereka tinggal.

Sesuatu seperti ini:

class PointInTime(object):

    def __init__(self, year, month, day):
        self.dt = datetime(year, month, day)

    def __eq__(self, other):
        return other.start_date < self.dt < other.end_date

    def __ne__(self, other):
        return not self.__eq__(other)

    def __repr__(self):
        return "{}-{}-{}".format(self.dt.year, self.dt.month, self.dt.day)

class Timespan(object):
    def __init__(self, start_date, end_date):
        self.start_date = start_date
        self.end_date = end_date

    def __eq__(self, other):
        return self.start_date < other.dt < self.end_date

    def __ne__(self, other):
        return not self.__eq__(other)

    def __repr__(self):
        return "{}-{}-{} -> {}-{}-{}".format(self.start_date.year, self.start_date.month, self.start_date.day,
                                             self.end_date.year, self.end_date.month, self.end_date.day)

Catatan penting: Saya tidak subclass datetime karena panda akan mempertimbangkan dtype dari kolom objek datetime menjadi datetime dtype, dan karena rentang waktu tidak, pandas diam-diam menolak untuk bergabung pada mereka.

Jika kita instantiate dua objek dari kelas-kelas ini, mereka sekarang dapat dibandingkan:

pit = PointInTime(2015,1,1)
ts = Timespan(datetime(2014,1,1), datetime(2015,2,2))
pit == ts
True

Kami juga dapat mengisi dua DataFrames dengan objek-objek ini:

df = pd.DataFrame({"pit":[PointInTime(2015,1,1), PointInTime(2015,2,2), PointInTime(2015,3,3)]})

df2 = pd.DataFrame({"ts":[Timespan(datetime(2015,2,1), datetime(2015,2,5)), Timespan(datetime(2015,2,1), datetime(2015,4,1))]})

Dan kemudian penggabungan jenis karya:

pd.merge(left=df, left_on='pit', right=df2, right_on='ts')

        pit                    ts
0  2015-2-2  2015-2-1 -> 2015-2-5
1  2015-2-2  2015-2-1 -> 2015-4-1

Tetapi hanya jenis.

PointInTime(2015,3,3) seharusnya juga disertakan dalam hal ini bergabung Timespan(datetime(2015,2,1), datetime(2015,4,1))

Tapi ternyata tidak.

Saya pikir panda membandingkan PointInTime(2015,3,3) untuk PointInTime(2015,2,2) dan membuat asumsi bahwa karena mereka tidak setara, PointInTime(2015,3,3) tidak bisa sama dengan Timespan(datetime(2015,2,1), datetime(2015,4,1)), karena rentang waktu ini sama dengan PointInTime(2015,2,2)

Semacam seperti ini:

Rose == Flower
Lilly != Rose

Karena itu:

Lilly != Flower

Edit:

Saya mencoba membuat semua PointInTime sama satu sama lain, ini mengubah perilaku bergabung untuk menyertakan 2015-3-3, tetapi 2015-2-2 hanya disertakan untuk Timespan 2015-2-1 -> 2015-2 -5, jadi ini memperkuat hipotesis saya di atas.

Jika ada yang punya ide lain, tolong beri komentar dan saya dapat mencobanya.


6
2018-06-03 21:16



Solusi panda akan sangat bagus jika diimplementasikan mirip dengan foverlaps () dari paket data.table di R. Sejauh ini saya telah menemukan nishy's piecewise () menjadi efisien. Saya telah memberikan kode berdasarkan diskusi sebelumnya Penggabungan frame data berdasarkan rentang tanggal

A['permno'] = np.piecewise(np.zeros(A.count()[0]),
                                 [ (A['cusip'].values == id) & (A['fdate'].values >= start) & (A['fdate'].values <= end) for id, start, end in zip(B['ncusip'].values, B['namedf'].values, B['nameenddt'].values)],
                                 B['permno'].values).astype(int)

3
2018-02-13 07:20