List with many dictionaries VS dictionary with few lists?

This relates to column oriented databases versus row oriented. Your first example is a row oriented data structure, and the second is column oriented. In the particular case of Python, the first could be made notably more efficient using slots, such that the dictionary of columns doesn't need to be duplicated for every row.

Which form works better depends a lot on what you do with the data; for instance, row oriented is natural if you only ever access all of any row. Column oriented meanwhile makes much better use of caches and such when you're searching by a particular field (in Python, this may be reduced by the heavy use of references; types like array can optimize that). Traditional row oriented databases frequently use column oriented sorted indices to speed up lookups, and knowing these techniques you can implement any combination using a key-value store.

Pandas does convert both your examples to the same format, but the conversion itself is more expensive for the row oriented structure, simply because every individual dictionary must be read. All of these costs may be marginal.

There's a third option not evident in your example: In this case, you only have two columns, one of which is an integer ID in a contiguous range from 0. This can be stored in the order of the entries itself, meaning the entire structure would be found in the list you've called users2['name']; but notably, the entries are incomplete without their position. The list translates into rows using enumerate(). It is common for databases to have this special case also (for instance, sqlite rowid).

In general, start with a data structure that keeps your code sensible, and optimize only when you know your use cases and have a measurable performance issue. Tools like Pandas probably means most projects will function just fine without finetuning.

Time complexity for the lookups in -

• List - O(n)
• Dicts - O(1)

But that wouldn't hurt much if your data isn't that big and also modern day processors are quite efficient.
You should go with the one in which the lookup is syntactically cleaner and readable(readability matters).
The first option is quite appropriate as the variable is a collection of users(which have been assigned an id) while the second would be just a collection of usernames and ids.

Users

1. When you need to append some new user just make a new dict of all user details and append it

2. Easily sortable as @StevenRumbalski suggested

3. Searching will be easy

4. This is more compact and easily manageable as record grows (for some very high number of records I think we will need something better than users too)

Users2

1. Personally I am seeing this for the first time and I wouldn't approach this if I have a high number of records.

PS: But I would like to learn advantages of users2 over users Again a nice question