eshangj/stackoverflow_q_and_a_sample huggingface dataset:
Question: I'm trying to encrypt the same string in PHP and Python using AES-256-CBC with the same keys and IVs. However, the results of both languages ​​are different, even though I am using the same encryption method and the same data. In PHP, I am using openssl_encrypt, while in Python I am using pycryptodome with PKCS7 padding. Below are the two code snippets I'm using, and the results I'm getting. Here is my PHP code: <?php $plaintextstr = 'AAAAAAAAAAAAAAAA'; $encrypt_method = "AES-256-CBC"; $secret_key = "SSSSSSSSSSSS"; $secret_iv = "LLLLLLLLLLLL"; $key = substr(hash('sha256', $secret_key), 0, 32); $iv = substr(hash('sha256', $secret_iv), 0, 16); $encrypted_str = openssl_encrypt($plaintextstr, $encrypt_method, $key, 0, $iv); echo base64_encode($encrypted_str); ?>  Here is my Python code: from Crypto.Cipher import AES from Crypto.Util.Padding import pad from hashlib import sha256 import base64 plaintextstr = 'AAAAAAAAAAAAAAAA' secret_key = "SSSSSSSSSSSS" secret_iv = "LLLLLLLLLLLL" # Generar key y iv key = sha256(secret_key.encode('utf-8')).digest()[:32] iv = sha256(secret_iv.encode('utf-8')).digest()[:16] # Padding padded_data = pad(plaintextstr.encode('utf-8'), AES.block_size) # Crear el cifrador cipher = AES.new(key, AES.MODE_CBC, iv) encrypted = cipher.encrypt(padded_data) # Convertir a base64 encrypted_base64 = base64.b64encode(encrypted).decode('utf-8') print(encrypted_base64)  I have verified the following:  The keys and the IV in both languages ​​are generated in the same way using SHA-256. I am using CBC mode and applying PKCS7 padding in both languages. Both results are Base64 encoded.  Despite these steps, the results remain different. I'm not sure why this happens.

Answer: The php hash function returns a hexadecimal representation of the bytes, so cutting 16 characters of that with substr leads to "20139ebeee312271" for your IV (similar result for key). These are not true bytes, they're characters in the range [0-9a-f]. This is not what you intend to do. The Python sha256 .digest() function returns a byte string, not a hexadecimal represntation. Cutting 16 characters of that leads to 16 true bytes from the hash. This is probably what you intend to do. The results are different because you're encrypting with different keys and initialization vectors. From the documentation, PHP hash can take a third parameter, binary, which defaults to false:   hash( string $algo, string $data, bool $binary = false, array $options = [] ): string  binary When set to true, outputs raw binary data. false outputs lowercase hexits.  Changing the PHP code to: <?php $plaintextstr = 'AAAAAAAAAAAAAAAA'; $encrypt_method = "AES-256-CBC"; $secret_key = "SSSSSSSSSSSS"; $secret_iv = "LLLLLLLLLLLL"; $key = substr(hash('sha256', $secret_key, true), 0, 32); $iv = substr(hash('sha256', $secret_iv, true), 0, 16); $encrypted_str = openssl_encrypt($plaintextstr, $encrypt_method, $key, 0, $iv); echo $encrypted_str; // Result: V7zSp9KHPke9QuPbWoUNvjCHVJ7giluD9YaOnX9E57k=  yields the same result as the python code.
Question: Scenario: I have a dataframe which contains one row of data. Each column is an year and it has the relevant value. I am trying to use the data from this df to create a json style structure to pass to an API requests.post. Sample DF: +-------+-------+-------+-------+-------+-------+-------------+-------------+-------------+-------------+-------------+-------------+ | | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | 2030 | +-------+-------+-------+-------+-------+-------+-------------+-------------+-------------+-------------+-------------+-------------+ | Total | 23648 | 20062 | 20555 | 22037 | 26208 | 28224.88801 | 29975.87934 | 31049.01582 | 32170.68853 | 33190.35298 | 34031.93951 | +-------+-------+-------+-------+-------+-------+-------------+-------------+-------------+-------------+-------------+-------------+  Sample JSON style structure: parameters = { "first_Id":first_id, "version":2, "overrideData":[ { "period":2024, "TOTAL":101.64, }, { "period":2025, "TOTAL":104.20, } ] }  Question: What would be the best approach to use the data from the Df to fill and expand the JSON style object? I tried the following, but this only separates two lines, one for total and one for period, which does not result in the pairings needs:  parameters = {} parameters['first_Id'] = first_id parameters['version'] = 2 parameters['overrideData'] = { } parameters['overrideData']['total'] = test_input.iloc[0].tolist() parameters['overrideData']['period'] = list(test_input.columns)  This results in: { "companyId": 11475, "version": 2, "overrideData": { "TOTAL": [ 23647.999999999996, 20061.999999999996, 20555, 22036.999999999996, 26207.999999999993, 28224.88800768, 29975.879336500002, 31049.015816740008, 32170.68852577, 33190.3529754, 34031.93951397 ], "period": [ 2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028, 2029, 2030 ] } } 

Answer: You could transpose, rename_axis, reset_index, convert to_dict as records: test_input.T.rename_axis('period').reset_index().to_dict('records')  In your case: parameters['overrideData'] = (test_input.T.rename_axis('period') .reset_index().to_dict('records') )  Output: [{'period': '2020', 'Total': 23648.0}, {'period': '2021', 'Total': 20062.0}, {'period': '2022', 'Total': 20555.0}, {'period': '2023', 'Total': 22037.0}, {'period': '2024', 'Total': 26208.0}, {'period': '2025', 'Total': 28224.88801}, {'period': '2026', 'Total': 29975.87934}, {'period': '2027', 'Total': 31049.01582}, {'period': '2028', 'Total': 32170.68853}, {'period': '2029', 'Total': 33190.35298}, {'period': '2030', 'Total': 34031.93951}]
Question: Say I have: data = { 'id': ['a', 'a', 'a', 'b', 'b', 'b', 'b'], 'd': [1,2,3,0,1,2,3], 'sales': [5,1,3,4,1,2,3], }  I would like to add a column with a rolling mean with window size 2, with min_periods=2, over 'id' In Polars, I can do: import polars as pl df = pl.DataFrame(data) df.with_columns(sales_rolling = pl.col('sales').rolling_mean(2).over('id'))  shape: (7, 4) ┌─────┬─────┬───────┬───────────────┐ │ id ┆ d ┆ sales ┆ sales_rolling │ │ --- ┆ --- ┆ --- ┆ --- │ │ str ┆ i64 ┆ i64 ┆ f64 │ ╞═════╪═════╪═══════╪═══════════════╡ │ a ┆ 1 ┆ 5 ┆ null │ │ a ┆ 2 ┆ 1 ┆ 3.0 │ │ a ┆ 3 ┆ 3 ┆ 2.0 │ │ b ┆ 0 ┆ 4 ┆ null │ │ b ┆ 1 ┆ 1 ┆ 2.5 │ │ b ┆ 2 ┆ 2 ┆ 1.5 │ │ b ┆ 3 ┆ 3 ┆ 2.5 │ └─────┴─────┴───────┴───────────────┘  What's the DuckDB equivalent? I've tried import duckdb duckdb.sql(""" select *, mean(sales) over ( partition by id order by d range between 1 preceding and 0 following ) as sales_rolling from df """).sort('id', 'd')  but get ┌─────────┬───────┬───────┬───────────────┐ │ id │ d │ sales │ sales_rolling │ │ varchar │ int64 │ int64 │ double │ ├─────────┼───────┼───────┼───────────────┤ │ a │ 1 │ 5 │ 5.0 │ │ a │ 2 │ 1 │ 3.0 │ │ a │ 3 │ 3 │ 2.0 │ │ b │ 0 │ 4 │ 4.0 │ │ b │ 1 │ 1 │ 2.5 │ │ b │ 2 │ 2 │ 1.5 │ │ b │ 3 │ 3 │ 2.5 │ └─────────┴───────┴───────┴───────────────┘  This is very close, but duckdb still calculates the rolling mean when there's only a single value in the window. How can I replicate the min_periods=2 (default) behaviour from Polars?

Answer: You can use case statement and count: duckdb.sql(""" from df select *, case when count(*) over rolling2 = 2 then mean(sales) over rolling2 end as sales_rolling window rolling2 as ( partition by id order by d rows between 1 preceding and current row ) """).sort('id', 'd') ┌─────────┬───────┬───────┬───────────────┐ │ id │ d │ sales │ sales_rolling │ │ varchar │ int64 │ int64 │ double │ ├─────────┼───────┼───────┼───────────────┤ │ a │ 1 │ 5 │ NULL │ │ a │ 2 │ 1 │ 3.0 │ │ a │ 3 │ 3 │ 2.0 │ │ b │ 0 │ 4 │ NULL │ │ b │ 1 │ 1 │ 2.5 │ │ b │ 2 │ 2 │ 1.5 │ │ b │ 3 │ 3 │ 2.5 │ └─────────┴───────┴───────┴───────────────┘  Note I've used named window and row framing here.
Question: I'm trying to insert new users to a table in MySQL when they register. I am using a FlaskApp on PythonAnywhere. Here is my query: INSERT INTO user_profile (email, user_name, first_foo) VALUES (%s, %s, 0);  This is run from my flask_app code: def connect_db(query, params): db_connection= MySQLdb.connect("<username>.mysql.eu.pythonanywhere-services.com","<username>","<password","<db_name>", cursorclass=MySQLdb.cursors.DictCursor) cursor=db_connection.cursor() cursor.execute(query, params) result = cursor.fetchone() return result connect_db(query, (email, username,))  Here is my table structure: +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | user_id | int | NO | PRI | NULL | auto_increment | | email | varchar(50) | YES | UNI | NULL | | | user_name | varchar(15) | YES | UNI | NULL | | | first_foo | tinyint(1) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+  Unfortunately, I keep getting: MySQLdb._exceptions.OperationalError: (1048, "Column 'user_id' cannot be null")  I have tried several queries including: INSERT INTO user_profile (user_id, email, user_name, first_foo) VALUES (NULL, %s, %s, 0); INSERT INTO user_profile (user_id, email, user_name, first_foo) VALUES (DEFAULT, %s, %s, 0); INSERT INTO user_profile (user_id, email, user_name, first_foo) VALUES (0, %s, %s, 0);  but all return the same error. If I run the first query in the MySQL console on Python Anywhere, the query is successful. Thanks in advance for any help.

Answer: From the OP's comment this worked, after each transaction such as INSERT, UPDATE, DELETE queries, adding commit() was pivotal after execute(). cursor = db_connection.cursor() cursor.execute(query, params) db_connection.commit() # <- add this
Question: Basically I want to obtain a part of the variable "cubote". I tried two methods that should work in the same way, but it didn't. My code:  import numpy as np # Create a 3x3x3 cube cubote = np.arange(27).reshape(3,3,3) # Compare the results result1 = cubote[0:2,0:2,0:2] result2 = cubote[0:2][0:2][0:2] print(result1) print("Shape of result1:", result1.shape) print(result2) print("Shape of result2:", result2.shape)  OUTPUT: result1:  [[[ 0 1] [ 3 4]] [[ 9 10] [12 13]]] Shape of result1: (2, 2, 2)  result2:  [[[ 0 1 2] [ 3 4 5] [ 6 7 8]] [[ 9 10 11] [12 13 14] [15 16 17]]] Shape of result2: (2, 3, 3)  I expected the two results to be the same. I refer to the result 1 makes sense, but result 2 did not work as I expected. WHY?

Answer: The difference in the results between result1 and result2 comes down to how indexing works in NumPy. Explanation of result1: result1 = cubote[0:2, 0:2, 0:2]  In this case, you are applying slicing across all three dimensions at once. This means you are extracting a sub-cube with indices in the ranges [0:2] in all three dimensions, which gives you a 2x2x2 cube. Explanation of result2: result2 = cubote[0:2][0:2][0:2]  Here, you're doing something different. Let’s break it down: 1.cubote[0:2] extracts the first two "layers" (2D arrays) from the cube, so the result of this step is: array([[[ 0, 1, 2], [ 3, 4, 5], [ 6, 7, 8]], [[ 9, 10, 11], [12, 13, 14], [15, 16, 17]]])  This is a 2x3x3 array.  Then, [0:2] is applied to this result, which further extracts the first two rows (along the first axis) of the new array. But since the array is already 2x3x3, this doesn’t reduce the size, and you still have a 2x3x3 array.  Finally, [0:2] is applied again, but since you're now operating on the first axis of the current 2D slices (each of shape 3x3), it doesn't behave as expected because you've already "peeled off" dimensions.   Key Difference:  Slicing across all dimensions at once (as in result1) extracts the correct sub-cube.  Chained indexing (as in result2) performs indexing sequentially, and since each index slice operates on the current result rather than on all dimensions at once, you don't get the same shape or result.   You should use slicing in all dimensions simultaneously, like in result1, to obtain the expected 2x2x2 sub-cube. cubote[0:2, 0:2, 0:2]: Extracts a sub-cube across all three dimensions. cubote[0:2][0:2][0:2]: Extracts slices sequentially, leading to an unexpected result. To get consistent results, always apply slicing across all dimensions at once if you are trying to extract a subarray.
Question: I am trying to extract the size of an PNG image from a datastream Consider the starting data of the stream 137 80 78 71 13 10 26 10 0 0 0 13 73 72 68 82 0 0 2 84 0 0 3 74 8 2 0 0 0 195 81 71 33 0 0 0 ... ^ ^ ^ ^ ^ ^  which contains the following information  signature: 137 80 78 71 13 10 26 10 IHDR chunk of:  length 0 0 0 13 type 73 72 68 82 data 0 0 2 84 0 0 3 74 8 2 0 0 0 crc: 195 81 71 33   then a new chunk start.  The information about the size of the image are encoded in the 8 bytes of the data chunk:  width 0 0 2 84 or in bytes b'\x00\x00\x02T' height 0 0 3 74 or in bytes b'\x00\x00\x03J'.  I know that the image has a width of 596 px and a height of 842 px but I cannot figure out how to compute the actual size of the image. PS the values are given in Python and the here the datastream in binary form b'\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x02T\x00\x00\x03J\x08\x02\x00\x00\x00\xc3QG!\x00\x00\x00\tpHY'

Answer: You can think of each byte as a base-256 digit of the respective dimension. So 0 * 256^3 + 0 * 256^2 + 2 * 256 + 84 = 596, and 0 * 256^3 + 0 * 256^2 + 3 * 256 + 74 = 842. The next two bytes are important as well, where 8 is the bit depth, and 2 is the color type. 8 means 8 bits per component, and 2 means three components per pixel: red, green, and blue.
Question: This is the first time I'm working with Pandas so I'm completely new to this. I was able to group an instance list per account. Now while iterating into that list I would need the account number (group key) to be able to do something with it. This is an example of the csv file: enter image description here #Using Pandas df = pd.read_csv(os.path.join(__location__, 'instances.csv')) df_group = df.groupby('account')['instance-id'].apply(list) print(df_group.groups.keys()) for account in df_group: #Initialize Prettytable t = PrettyTable(['Instance ID', 'Instance Name', 'AMI-ID', 'AMI Name']) #Initialize the EC2 client to assume role sts = boto3.client('sts') assume_role = sts.get_caller_identity().get('Arn').split('/')[1] assumed_role=sts.assume_role( RoleArn="arn:aws:iam::" + str(account[0]) + ":role/" + assume_role, RoleSessionName= 'Temporary_Session' )  I've read about getting the key using groups.key() but it seems that when it has been already converted to a list is not possible to do it that way as I get the following error when trying. line 9, in <module> print(df_group.groups.keys()) line 6299, in __getattr__return object.__getattribute__(self, name) AttributeError: 'Series' object has no attribute 'groups'. Did you mean: 'groupby'?  What I'm trying to do at the end is pulling the account number to be able to use sts to assume another role. Reproducible input: import pandas as pd data = { 'account': [ '111111111111111', '111111111111111', '111111111111111', '111111111111111', '111111111111111', '111111111111111', '222222222222222', '222222222222222', '222222222222222', '222222222222222', '222222222222222', '222222222222222' ], 'instance-id': [ 'i-124f1c3c401ijk3c4', 'i-124f1c3c401ijk3c4', 'i-124f1c3c401ijk3c4', 'i-124f1c3c401ijk3c7', 'i-124f1c3c401ijk3c8', 'i-124f1c3c401ijk3c9', 'i-124f1c3c401ijk3c176', 'i-124f1c3c401ijk3c177', 'i-124f1c3c401ijk3c178', 'i-124f1c3c401ijk3c179', 'i-124f1c3c401ijk3c180', 'i-124f1c3c401ijk3c182' ] } df = pd.DataFrame(data) 

Answer: You should use Series.items: for group, account in df_group.items(): print(f'{group=}') print(account)  Or, maybe better, don't aggregate and loop over the GroupBy object: for group, account in df.groupby('account')['instance-id']: print(f'{group=}') print(list(account))  Output: group='111111111111111' ['i-124f1c3c401ijk3c4', 'i-124f1c3c401ijk3c4', 'i-124f1c3c401ijk3c4', 'i-124f1c3c401ijk3c7', 'i-124f1c3c401ijk3c8', 'i-124f1c3c401ijk3c9'] group='222222222222222' ['i-124f1c3c401ijk3c176', 'i-124f1c3c401ijk3c177', 'i-124f1c3c401ijk3c178', 'i-124f1c3c401ijk3c179', 'i-124f1c3c401ijk3c180', 'i-124f1c3c401ijk3c182']
Question: Consider the following dataframe, where Date is in the format DD-MM-YYY: Date Time Table 01-10-2000 13:00:03 B 01-10-2000 13:00:04 A 01-10-2000 13:00:05 B 01-10-2000 13:00:06 A 01-10-2000 13:00:07 B 01-10-2000 13:00:08 A  How can I 1) group the observations by Table, 2) sort the rows according to Date and Time within each group, 3) show the groups in chronological order according to Date and Time of their first observation? Date Time Table 01-10-2000 13:00:03 B 01-10-2000 13:00:05 B 01-10-2000 13:00:07 B 01-10-2000 13:00:04 A 01-10-2000 13:00:06 A 01-10-2000 13:00:08 A   Input data: data = { 'Date': ['01-10-2000', '01-10-2000', '01-10-2000', '01-10-2000', '01-10-2000', '01-10-2000'], 'Time': ['13:00:03', '13:00:04', '13:00:05', '13:00:06', '13:00:07', '13:00:08'], 'Table': ['B', 'A', 'B', 'A', 'B', 'A'] } df = pd.DataFrame(data) 

Answer: Use groupby.transform and numpy.lexsort: date = pd.to_datetime(df['Date']+' '+df['Time']) out = df.iloc[np.lexsort([ date, df['Table'], date.groupby(df['Table']).transform('min') ])]  Alternatively, using an intermediate column: date = pd.to_datetime(df['Date']+' '+df['Time']) out = (df.assign(date=date, min_date=date.groupby(df['Table']).transform('min')) .sort_values(by=['min_date', 'Table', 'date']) .drop(columns=['date', 'min_date']) )  Output:  Date Time Table 0 01-10-2000 13:00:03 B 2 01-10-2000 13:00:05 B 4 01-10-2000 13:00:07 B 1 01-10-2000 13:00:04 A 3 01-10-2000 13:00:06 A 5 01-10-2000 13:00:08 A
Question: I'm digging into a codebase containing thousands of occurrences of foo in list(bar), e.g.:  as a boolean expression: if foo in list(bar) or ...: ...   in a for loop: for foo in list(bar): ...   in a generator expression: ",".join(str(foo) for foo in list(bar))    Is there a scenario (like a given version of Python, a known behavior with a type checker, etc.) where foo in list(bar) is not just a memory-expensive version of foo in bar? What am I missing here?

Answer: I've sometimes done/seen that when bar got modified in the loop, e.g.: bar = {1, 2, 3} for foo in list(bar): bar.add(foo + 1)  With your replacement, that raises RuntimeError: Set changed size during iteration. Attempt This Online! An example from Python's standard library  for k in list(_config_vars): if k.startswith(_INITPRE): del _config_vars[k]  Dozens more (many done for the above reason, though not all).
Question: I have this code: import polars as pl df = pl.DataFrame({'size': [34.2399, 1232.22, -479.1]}) df.with_columns(pl.format('{:,.2f}', pl.col('size')))  But is fails: ValueError - Traceback, line 3 2 df = pl.DataFrame({'size': [34.2399, 1232.22, -479.1]}) ----> 3 df.with_columns(pl.format('{:,.2f}', pl.col('size'))) File polars\functions\as_datatype.py:718, in format(f_string, *args) 717 msg = "number of placeholders should equal the number of arguments" --> 718 raise ValueError(msg) ValueError: number of placeholders should equal the number of arguments  How can I format a float or int column using a format specifier like '{:,.2f}'?

Answer: As outlined by @mozway, general format strings are not yet supported as part of pl.format. The corresponding feature request already contains a nice polars implementation of (the most common) C-style sprint formatting. If efficiency is not too much of an issue (e.g. in exploratory data analysis), you can simply use pl.Expr.map_elements and fall back to the naive python solution. df.with_columns( pl.col("size").map_elements(lambda x: f"{x:,.2f}", return_dtype=pl.String) )  shape: (3, 1) ┌──────────┐ │ size │ │ --- │ │ str │ ╞══════════╡ │ 34.24 │ │ 1,232.22 │ │ -479.10 │ └──────────┘
Question: I'm trying to get abstract properties to work, enforcing property getter & setter definitions in downstream classes. from abc import ABC, abstractmethod class BaseABC(ABC): @property @abstractmethod def x(self): pass @x.setter @abstractmethod def x(self, value): pass class MyClass(BaseABC): def __init__(self, value): self._x = value @property def x(self): return self._x # @x.setter # def x(self, val): # self._x = val obj = MyClass(10) print(obj.x) obj.x = 20 print(obj.x)  Having read the documentation it seems to indicate the above should trigger a TypeError, when the class is being build, but it only triggers an AttributeError once the attribute is being set. Why does the absent setter, explicitly defined in BaseABC through an @abstractmethod, not trigger the expected TypeError? How does one ensure a setter is required in the daughter class?

Answer: TL;DR property doesn't just override the getter; it overrides the setter with None as well.  In MyClass, property creates a brand new property with the given getter and no setter; it doesn't simply override the getter of the inherited property. The definition of MyClass.x is equivalent to def x_getter(self): return self._x x = property(x_getter, None)  and that None as a setter is "good enough" as far as ABC is concerned with respect to overriding the abstract setter. To "inherit" the abstract setter as well, use class MyClass(BaseABC): @BaseABC.x.getter def x(self): return self._x  This creates a new property not from scratch, but from the existing BaseABC.x property, using its (abstract) setter but a new getter (just like x.setter before created a new property using the old getter but a new setter). To make MyClass instantiable, you still need to provide a concrete setter using x.setter.  Unfortunately, nothing forces you to to use BaseABC.x.getter in place of property. ABC only cares that x gets set to something appropriate. This is a general problem with ABC, not properties in particular. There is only so much you can do at the library level; abstract base classes are not a feature of Python itself.
Question: I am currently working with a dataframe looking at Kentucky oil wells with pandas and want to create a pivot table using an API identifier. Since ther are various duplicates, I also wanted to join non unique values. Below is an example of the dataframe: import pandas as pd df = pd.DataFrame({'API': ['16101030580000', '16101030580000', '16129057600000','16013006300000'], 'Date': ['0000/00/00','6/15/2007', '5/25/2020', '7/31/2014'], 'Annual_Oil':[300,'nan',150, 360], 'State':['KY','None', 'None', 'KY']})  Additionally, I created a list of values that I did not want to join. However, when runnning the code, I get some values in the dataframe that should not be there. list_none = ['none', 'nan', 'NAN','None', '0000/00/00','000'] df1 = pd.pivot_table(df, index = 'API', aggfunc = lambda x: (','.join(x.unique().astype(str)) if x not in list_none else x), sort = False)  The output for this example dataframe looks like  Date Annual_Oil State API 16101030580000 0000/00/00,6/15/2007 300,nan KY,None 16129057600000 5/25/2020 150 None 16013006300000 7/31/2014 360 KY  Is there a way to restructure the lambda function within the pivot table or would I have to get rid of the unwanted joins manually?

Answer: You should filter the values within the join: list_none = ['none', 'nan', 'NAN', 'None', '0000/00/00', '000'] df1 = pd.pivot_table( df, index='API', aggfunc=lambda x: ','.join( i for i in x.unique().astype(str) if i not in list_none ), sort=False, )  Alternative using a custom function: def cust_join(x): x = x.dropna().astype(str) return ','.join(x[~x.isin(list_none)].unique()) df1 = pd.pivot_table( df, index='API', aggfunc=cust_join, sort=False, )  Output:  Date Annual_Oil State API 16101030580000 6/15/2007 300 KY 16129057600000 5/25/2020 150 16013006300000 7/31/2014 360 KY